I’ve been using sqlite in production for read-only datasets for 1.5 years now. It has performed much better than postgresql in this use-case and FTS5 has actually been faster on full-text search.

I’ve been planning a complete migration for all workloads from postgresql when litefs is a bit more mature, but it has been close to a drop in replacement after some minor changes.

For those looking to migrate from postgres to sqlite:

# Dump the db and rename some tables from "public"
pg_dump --data-only --inserts db_dev -h localhost -U user > backupName.sql
sed -i 's/public\.//' backupName.sql

# sqlite doesn't support boolean true/false, so you'll have to manually change in an editor
0,.s/true,/1,/
0,.s/false,/0,/

Regarding litefs, https://litefs-liveview.fly.dev/ is multi-region application using an early version and a custom library to forward write requests. ~sheertj/elixir_litefs - sourcehut git is a elixir specific version of fly_rpc and fly_postgres_elixir which is used to forward db requests to the primary on fly. However, I believe litefs may include write forwarding soon, which could make this library mostly redundant.

Regarding write performance, to be honest, I would expect most applications to be fine. I did a quick write benchmark, but unfortunately don’t remember if it was 1k, 10k, or 100k writes per second. My application is very read heavy with between 200k-500k pageviews/events per month (not so easy to measure without instrumenting everything), but only 50k write queries / month. That works out to about 0.02 queries per second which much, much, much, much less than what sqlite can handle. HCTree is not required.

When litefs finishes solving streaming backup, db migration, and write forwarding, there will be very little reason to be using postgresql in production for a large number of applications.

Edit: hctree: Thread Test
Stock sqlite does between 4k and 40k writes on a single thread. HCTree looks like it is roughly double the performance.

SQLite has its own backup functionality as linked by @LostKobrakai – it’s not just copying the files though – and a lot of energy has been expended to prove that it works stably.

Additionally, it also has the concepts of patch-sets and change-sets (section 2.1) – somewhat to the confusion of us in the Elixir space due to the naming clash – that allow you to accumulate changes since a last known snapshot, in a dedicated file.

SQLite has a strong backup apparatus even without litestream. The latter seeks to make backups over the network more or less transparent. The rest is well-covered by sqlite itself.

That’s already covered. You issue a backup command and SQLite itself makes new file(s) while you’re using the same session. It’s fully atomic.

People get needlessly scared there, their team is very aware of the potential issues and took care of them a while ago.