Comment by andersmurphy
20 hours ago
Thing is SQLite scales better than both those network databases [1] if you're prepared to stick with one big machine (+ a standby).
This is even more obvious when you start doing transactions processing an row locks across the network limit you to 1-3k TPS that you cannot scale out of (Pareto distribution is merciless).
[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...
Seeing as I can get about 200K TPS from a networked DB in my environment, I have to question your setup here.
In the real world we are looking at things like RPO (recovery point objective) and RTO (recovery time objective). You need to consider HA and DR. It’s in these areas where SQLite does not scale.
That’s why I struggle to see the fit for SQLite in any sort of multi-user server environment. If you need the data to be durable, then the bigger DB’s have the tools. If you don’t need the data to be durable, just keep it in memory. I’m sure there are niches I am missing.
In this demo each T in TPS is two updates over a billion rows and most importantly skewing high on row lock contention. On a 5 year old macbook, using a dynamic language. Isolation level serializable and synchronous full (so max durability).
You can definitely go faster over less data doing single inserts on a better stack, with weaker guarantees.
RPO litestream even in it's default settings gives you point in time streaming backups to the second, which is considerably better than what RDS five minutes. So the funny thing is the durability guarantees are worse with the "bigger DBs".
RTO again you can have a standby that's warm with a copy of the data through litestream. Regional sharding also becomes trivial.
It's a solid set up for a lot of products/apps. Postgres is still fine if you want things like roles and permissions etc. Or if you don't have experience getting the most out of sqlite.
Wow, what an apples and aliens comparison. You add a bunch of transaction delays to your postgresql case because you can access a database over a network, but you use transaction batching for sqlite? Maybe just compare a local postgresql with/without batching to a local sqlite with/without batching to be much less misleading.
Because local postgres is a bad time unlesss it's the only thing running on the server. Even then sqlite will smoke postgres (even with unix sockets).
The point is to survive the Pareto row locking problem you need to move away from a network database (if you want to still have interactive transactions). The network part is the main point of a network database, once you drop that there's not much pointing sticking with the added complexity unless there's another feature you really need.
You know you can host a database like Postgres on the same machine, right?
Yes, it's still slower on the same machine, even with unix domain sockets.
It doesn't play nice with other things running with it in practice. JVM and postgres on the same box is a textbook bad time.