Comment by lll-o-lll
17 hours ago
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.