Optimizing SQLite for Production: WAL Mode, Concurrency, and VFS Layers
SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers
I explore how modern hardware allows SQLite to power high-performance web servers by eliminating network latency. By configuring Write-Ahead Logging, managing checkpointing strategies, and tuning memory caches, you can achieve sub-millisecond query speeds. I also detail handling concurrency constraints and leveraging custom Virtual File System layers like Litestream and LiteFS for robust cloud deployments.
By running SQLite directly within the application process on the same server, you eliminate the network overhead entirely.
- smartmic
Unfortunately written by an AI - that completely takes the wind out of the content and makes me not even want to read any further. The distinction between “production” and “non-production” is also questionable. For an evaluation, I recommend the following original articles (certainly not AI-generated):
- https://sqlite.org/whentouse.html
- yladiz
I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and I think it's great that it's getting hyped because it truly is a capable database, but after trying myself I think I'd never reach for it in production because it lacks a lot of power that a database like Postgres has, and some of that power is actually relevant to a real production setting:
- Column definitions aren't able to be changed with something like `alter column` after creation. To change a column definition you have to manually update the underlying schema using the `writable_schema` pragma. If you mess this up you can be left with a corrupt database.
- Column types are pretty limited. This isn't too much of an issue in practice since you can handle this somewhat in application code, but it can still be a bit annoying at times.
- You have limited options for dealing with schema migrations. You basically either copy the migrations to the server and run it there (manually or with something like Ansible), or you run the migrations in your application on startup. Ideally you'd perform your schema migrations separately from your application, and having to som […]
- firesteelrain
Where I part ways a little with the recommendation is in the busy_timeout + BEGIN IMMEDIATE suggestion. On an embedded system, that's not really sufficient; I maintain a caching service that is used by thousands of clients. It ingests data over MQTT and has a web interface for queries. In my design, there is a MQTT thread and pruning thread. The MQTT ingestion thread and the pruning thread can both end up "contending" for the write lock, and increasing the busy_timeout only makes them wait longer before noticing that contention and moving on to the next task. What we ended up needing to do was add an application-level lock that only allows a single writer transaction to be in flight at a time; BEGIN IMMEDIATE is nice for preventing other readers from blocking, but it doesn't help much with other writers.
Meanwhile, you might be surprised how often “single-tenant edge deployment” comes up in embedded contexts; a Pi with an SD card is a common configuration for an embedded database, and it's right at the intersection of these problems.
- tnodir
> PRAGMA synchronous = NORMAL;
> In NORMAL mode, the database engine syncs to disk only at critical moments (e.g., during checkpoints) rather than at every single transaction commit. In WAL mode, this is completely safe from database corruption; even if the server crashes, only the uncommitted transactions in the WAL are lost, but the database integrity remains intact.
No, this is not safe.
You can lose the latest committed transaction with this pragma.
- graboid
As someone really tempted to use SQLite in production, the one thing I keep bumping against is how to have a nice GUI to interact with the running database. With our current prod databases, I can connect dbeaver and the like to them and nicely browse the data, query, or even do the occasional fix. Seems like this would be much more of a head scratcher if the database is just a file on the same VPS the app runs on.