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