Hacker News Reader

Comment by dana321 | Hacker News Reader

It does allow multiple applications to access the same database at the same time, but when you do so it really hurts performance. I noticed this when i wrote a web crawler in go and used sqlite as the backend.

As soon as i connect using the command line interface, it slows down significantly.

Just something to bear in mind if you want to use it with multiple processes!

View on HN

I use it for real time data (around 22G/month, compressed) and I can do all sorts of filthy stuff with the database while the real time processes are running.

Increasing the retry timeout can help, using WAL can help. I'm sure you've tried all this though.

Multiple processes is an instant anti-pattern for a single SQLite database. I would stop and reconsider your approach before trying to build this solution using it. The way I see it there are 3 options:

1) Implement another process which will have exclusive ownership of the shared SQLite database, and then use some IPC scheme to delegate database operations from multiple processes.

2) Give each process its own copy of a SQLite db if there is no effective shared state between these processes (I.e. you are just map-reducing web crawler results). Upon completion of each process you could aggregate each into a final combined db.

3) Use a hosted database solution such as Postgres.

The bigger question for me would be what are you going to do with this data once you collect it. If you plan on having another series of processes that then use the SQLite db to provide reporting views or execute business logic, I think a hosted solution might be a better option. If scalability is a serious concern, option 2 is probably your best bet.

It's ironic how using a severless database require your app to be the server and do top-level access management to the database

It may be ironic but there are benefits to rolling your own. Having your persistence layer talk in terms of your business models instead of raw SQL could be seen as a benefit in some contexts. The process which is the "server" can be written to allow for relaxed consistency based on specific business operation being performed (e.g. no need for transactions around log entries). This could be used to leverage substantial performance gains. The benefits afforded by guarantees of exclusivity between application and database are difficult to overstate.

These advantages of purpose-built functionality would also extend into the arena of handling replication and clustering. I.e. multiple distributed processes each with independent databases synchronized via some custom protocol that operates in terms of specific business models and processes.

you can just turn off these things if you so desire - in reality the overhead is a lot less than you'd think. You'd be way better off spending the time you'd use to write half a database to optimising your application and just use a database. Or maybe you don't really need a database.

Nothing of the sort is required. If you want to write a simple single-threaded synchronous program, sqlite works great for that.

Yes, I thought the same - so you have to write a server. What about locking? roll your own I suppose. Multi user - roll your own? What about hot backups? Rollbacks?

I always conclude these things are advocated by people who have no experience in large multi user systems. The same as the NoSQL movement. They'll eventually build a database server. They build a system using the cool thing which works fine when they test it on their single user system. Go live - aagghh what's happening, why are all these people trying to access my data simultaneously? and so on.

One I'll always remember was when XML was the next big thing - they decided to store the raw XML in a database. It was a commercial product, and we were interfacing to it from our system. Once we found out this we started asking questions - no no it works fine we were told, laughing at us old database guys. Went live couldn't handle 5 TPS - what a surprise, it never worked as far as I'm aware. There is this continuous circle of databases are bad, no no do this you don't need to do this, no things have changed - what do you database guys know. Its entertaining to watch if nothing else, my advice - learn SQL, and some database tuning, its not that hard, at least compared to writing your own database engine.

You don't need to write a server to use it. You need to recognize you're using the wrong tool to solve your problem.

I've used SQLite as an embedded database, as a log file, it's even possible to use it as a virtual filesystem in tcl starkits. But a high demand, multiaccess data solution it is not. Yes you can make it work but you need to justify the costs of doing all that server work when you could just use a SQL server that already meets your needs.

Centralise your writes to be only done through a single thread/process and then you can read from as many threads/processes you like with no noticeable difference in performance (at least for the several hobby dataset importing projects I tried).

«just write your own server on top of serverless embedded SQLite to get to acceptable performance without needing a client/server database»

(Honestly, once you are at the point where concurrency causes performance issues with SQLite, you are better off moving to databases designed to handle concurrency rather than trying to cobble together your own workaround - you have reached the point where the drawback of SQLite‘s architecture outweigh its advantages and the advantages of other databases architectures outweigh their drawbacks)

Sure, I don't disagree. I've ditched sqlite for Postgres many times.

It's just that for 99% of the projects I ever worked on, writes are like 100x less than reads so wrapping the writes in a queue of sorts has been quite okay and performant.

It can and it has been coming to a point when it's easier to use a full-blown database server, too.

I was simply pointing out that for a lot of classic workflows wrapping/centralising writes works quite fine.

As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes.

so if everyone's saying this, is there such a standard dummy program?

> As you quoted, people in this discussion are saying that writing a dummy program that has its own sqlite and does nothing but pass messages to it that it receives from all other processes on the system that need to talk to that sqlite file results in much better performance than accessing that file "directly" from the separate processes.

And use what protocol for inter-process communication with the daemon managing SQLite? At the end of the day you just created the equivalent of a server anyway...

Any protocol. People are saying this is a common solution.

So is there a standard dummy tool like that?

> So is there a standard dummy tool like that?

My point is that by that time SQlite doesn't fit "Serveless" as defined by the linked article anymore.

True

There shouldn’t be should a such standard program. It’s up to you to implement it in-process so you reap all the benefits of sqlite’s speed and lack of dedicated server process.

In the case of Erlang/Elixir where I most actively work in the last few years, it’s really easy to centralise write access (dedicated actor receiving messages). With other languages it should also be fairly easy.

Given the extensions that people compile into SQLite, I don't think one can even say that... there's mention of ActorDB in another thread that seems to cover this...

Wrapping a typical API in either GRPC or even over HTTP isn't too hard.. you take in a parameterized query with parameters using library X and return JSON stringified to that library... you may want more specific interfaces, or even GraphQL over the database for that matter... it really depends on your needs.

Depends on what you're up to, I suppose. Having a particular chunk of data wrapped in a microservice has other benefits. If that's the direction one is headed anyhow, then wrapping SQLite + single access can be more tractable than allowing multiple access that you'll eventually have to rip out again. I know it works for plenty of people, but for me "database as integration layer" has always been an antipattern.

Depends on what you're doing... If you're working in descrete projects that need to be run, then archived.. using an RDBMS vs a service over SQLite is an actual consideration... if you have a remote API interface that talks to different SQLite db files on a per project basis, then backup and archival become trivial matters... if you're using a classic RDBMS then it can become much more complicated.

Aside, adjusting schema over time also becomes easier as archived projects don't need to be updated, they just continue to exist with the older schema.

Presumably multiple readers are fine, with only multiple writers being an issue?

I vaguely recall trying to use multiple threads to write to an SQLite DB some years ago, and I think it actually locked the entire file for writes. I might remembering wrongly, but I think I switched to reader/writer locks in c# instead, and seeing a huge perf boost.

WAL mode means readers do not block writers and a writer does not block readers. If it locked the entire database you likely weren’t using WAL.

I was definitely using WAL - I know I mentioned using reader/write locks, but that was so the C# locking mechanism worked the way I wanted; I realise that SQLite doesn't block readers on writes.

My point, not well made :), was that a lightweight locking mechanism worked much faster than SQLite's file-based locking mechanism. This was on Windows, mind, so things might be very different on Linux.

Even with WAL enabled?

Let's say there are concurrent writes to a single table with few indexes. Process of updating said table and indices eventually boils down to update of a single B+ tree from a multiple threads. In general until serialized it is physically impossible without corrupting the tree. Sure there might be other specially crafted implementation of table more friendly to concurrent writes but there will be trade-offs. There are no miracles in the world.

As for particular case with WAL, all it does in this particular scenario is act as a queue. If your database load is spiky then it can even out load and make an impression of faster response. Under constant load it will not speed up the things and will internally serialize all actual updates

If you need concurrent/parallel writes, SQLite is not the right tool for you. You may as well lament that hammers are no good for driving screws.

Can you please point me to where did I say anything about SQLite being right tool for concurrent write? My point was that true concurrent writes to a generic database table with indexes are are physically impossible in a general case disregarding what database it is.

Some concurrent writes are "a transaction can wait for others to finish, up to how long it takes for a person to grow bored" and some concurrent writes are "if a transaction cannot finish without waiting the application is already struggling with a severe performance problem" (for example, because it needs multiple concurrent writes on different disks to keep up with incoming data).

Yes, using WAL. I tuned all of the settings that i could at the time.

Reply on news.ycombinator.com