SQLite in Production: Optimizing WAL Mode, Concurrency, and VFS Layers

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

- https://sqlite.org/different.html

- https://sqlite.org/quirks.html

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.

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 somehow copy/get the migrations to your server to then run the migration is a bit clunky.

All 3 of these are handled in a more powerful (and not local-only) database, and so I don't get why someone would choose SQLite except for prototyping (or places like the browser or phone apps) where performance concerns aren't really relevant.

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.

If you are going down that path, might also be useful to read through this PowerSync blog post.

- https://powersync.com/blog/sqlite-optimizations-for-ultra-hi...

I love SQLite, and I really want to run it in production, but my clients expect minimal data loss and downtime when one of my servers goes down.

The answer to that being running it on top of LiteFS or LiteStream seems like starts to make the setup a lot less simple and lot less battle tested, which kind of starts to negate the advantages over just running Postgres.

Personally, I think the better way to tackle sqlite_busy is to have a single writer managed at the application level. That effectively eliminates sqlite_busy in the context of a single process.

> To ensure write operations don't suffer from disk synchronization bottlenecks, pair WAL mode with the following pragma

Only do this if you are prepared to sacrifice durability (i.e can afford to lose transactions).

It would be great to include production benchmarks comparing these optimisations with a default SQLite setup.

"If the database size is smaller than the mmap_size, the entire database is mapped into memory, turning disk reads into simple pointer arithmetic." hah so that's how it works?

I am obsessed with the idea of per tenant databases. But I am afraid of migrations. Has anyone tried that?

There should probably be a forum rule to forbid AI written articles or atleast editoralize the title to add an [AI] tag. Would've saved me the click

[flagged]