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

The blog lists ways to optimize sqlite for production, for those who have already made that choice. But these comment links are about choosing sqlite vs others and differences, which is completely unrelated and unnecessary for those who have already made that choice.

I'm seeing a worrying trend on HN. Nearly for all articles, there is one unquantified, unproven comment at the top saying it's 100% AI — no proof, just baseless emotion of what fits the commenter's writing style. This is the new witch hunt, or virtue trolling.

I read the original article and to be honest, this off topic comment definitely looks more like an AI troll bot wrote it than the article. The troll bot is instructed to do a google search and put 3 source links — no matter how irrelevant they are.

So what is it now — one emotion vs another? If an article has sections and blocks, it's AI. What about infinite poor humans like me who have been writing like this since childhood?

Since LLMs were trained on high quality blog posts, we can no longer have posts that fit the pre-AI definition of high quality? The content must be dumbed down with typis and scatter unconventional words, to prove AI trollers that it's not AI? Or a timelapse video of typing the article?

Instead how about: if you don't want to read the article because it doesn't fit your personal style expectation — just don't bother commenting?

I hope HN would ban AI trolling comments, and discuss substance.

I don't think the all-too-common "written by AI" remarks are helpful.

The article was informative and useful. That's what matters IMHO.

1 - https://www.ilfoglio.it/il-foglio-ai/2025/03/22/news/a-first...

You can define constraints on a column that ensure it is text that's valid JSON for example:

  CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT NOT NULL
      CHECK (
        json_valid(data)
        json_type(data) = 'object'
      )
  );

  CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT NOT NULL CHECK (
      json_valid(data)
      AND json_type(data) = 'object'
      AND json_type(data, '$.name') = 'text'
      AND json_type(data, '$.age') = 'integer'
    )
  );

  CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    occurred_on TEXT NOT NULL CHECK (
      length(occurred_on) = 10
      AND occurred_on GLOB
        '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
      AND date(occurred_on, '+0 days') = occurred_on
    )
  );

For alter table it offers a "transform" command which implements the pattern of creating a new table with your desired scheme, copying data to it from the old table, then renaming the tables (all in a transaction): https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

  sqlite-utils transform fixtures.db roadside_attractions \
    --rename pk id \
    --default name Untitled \
    --column-order id \
    --column-order longitude \
    --column-order latitude \
    --drop address

  sqlite-utils migrate creatures.db path/to/migrations.py

  from sqlite_utils import Migrations
  
  migrations = Migrations("creatures")
  
  @migrations()
  def create_table(db):
      db["creatures"].create(
          {"id": int, "name": str, "species": str},
          pk="id",
      )
  
  @migrations()
  def add_weight(db):
      db["creatures"].add_column("weight", float)

Why is that the ideal? With SQLite your database is 1:1 connected to your application (meaning there is no other application using that database), it doesn't make sense to move the app to a new version but not the database or vice versa. Running migrations on startup of the app is ideal.

Migrations are a bit more difficult to write for SQLite than they need to be (DROP column only being added recently...), though. I usually iterate a few times to get the column definitions just right so that I don't have to change them later.

As you say column types are limited (and enforcement lax) but in practice it's a non-issue because you convert the data to application-specific types when reading from db (and enforce by writing only right data types) anyway.

I see the migration argument come up a lot. But, in practice with sqlite you'll be using projections where you have a source of truth database (event log) and project off it into disposable/expendable sqlite databases. So schema changes are often just delete and rebuild the projection.

I don’t think that’s really a positive or negative.

And the point about migrations ideally being separate is really just your own opinion. I prefer having the database definition in the same source tree as the application, ideally just a .sql file in the project.

https://www.sqlite.org/lang_altertable.html

On Go you can embed your migrations in your binary:

https://oscarforner.com/blog/2023-10-10-go-embed-for-migrati...

A lot of people manage to run several containerized applications on a single VPS behind a reverse proxy for personal or small groups. Managing a full rdbms takes work supporting multiple applications, or spinning up multiple instances per app in said containerized flows takes up excess resources, where SQLite would do the job just fine.

Not everything is going to be running 5+ nines of operation with distributed workloads. Plenty of real things run on a decent server with a good enough backup system in place.

Or at least that's how I view it. Whenever I think about using SQLite, I make sure I read these documents to see if I am fine with the limitations.

https://sqlite.org/whentouse.html

https://sqlite.org/quirks.html

No you don’t [0]. It is less convenient than being able to directly alter columns, but you do not need to mess around with writable schemas or risk corruption.

[0]: https://www.sqlite.org/lang_altertable.html#otheralter

If you're saying "do this, get that", you should be able explain how to measure and reproduce that result.

The answer to why someone might choose SQLite in production could be latency but if it is then prove it's worth the trade-offs.

That's the biggest pain of dealign with SQLite.

> 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.

Probably shouldn't be. One of the more high profile use cases for SQLite is per-customer sharding. Particularly with services where at any given time a small number of customers are generating the majority of the traffic. The data for customers that you haven't seen logged in for weeks tends to have a logarithmic effect on the query time for active customers when you use a single shared database for all of the data. Unloading that data reduces the cost of interactive workloads handily, even though the cost of backups may still be either the same or still have a logarithmic cost (eg, rsync detects deltas).

Another downside is that it will take time to make it look nice.

"Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not."

https://sqlite.org/lang_attach.html

I don't think it scales when you have unbounded amounts of tenants.

That being said it can be done and it's pretty normal for mobile apps, desktop apps etc. You just have to make sure the migrations are run when the tenant connects/unlocks/runs the app - and make sure that you minimise the risk of it going wrong!