- SQLite is typically used on same-host without any networking…
- Meaning that latency is much, much lower…
- Meaning that you will have much fewer overlapping transactions at any given point in time…
- Which results in less memory usage and…
- An SQLite instance will remain “low-latency” in wall-clock time even under pressure…
- And you have more leeway for ping-ponging (the N+1 problem) which can reduce query complexity, amount of indices, etc
In short, the performance you can squeeze out of a SQLite system is is unexpectedly high, as long as you can fit app+db on the same machine. Instead, I believe you’d choose a single-master networked db often for other (legitimate, but typcially not perf) reasons.
As a meager indie dev, this is a very appealing trade off. I’m not a db expert, and looking at the total complexity and churn in the field, I really don’t want to become one. SQLite has like three knobs. And as a bonus, I can run the same system on client machines.
Of course, SQLite is great for a lot of things, including small websites, or nearly anything mostly read without a ton of writes. It's not much at all over the top of modern disk io... nvme drives are crazy fast. My only thinking is in terms of additional concurrency, or for that matter, multiple shared access since FB can grow from embedded to dedicated server... I often think of it as a few steps between SQLite and Postgres for some scenarios.
