This is great until you encounter a customer with a hard RPO requirement of 0. SQLite has a few replication options, but I would never trust this in high stakes domains over PGSQL/MSSQL/DB2/Oracle/etc.
100k TPS over a billion rows: the unreasonable effectiveness of SQLite
61–70 of 169 posts
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#62Are you limiting your # of connections to postgres to 8? Is this unnecessarily throttling your throughput? This seems like quite the bottleneck... connection pools are good when your app is overwhelming your db.. but in this case, you really should be trying to put more load on Postgres... I'm concerned that this whole experiment is tainted by this choke point. I would love to see this tested again with a much larger…
To further explain:
You mention setting the conn pool to 8 to match your # of cores. That would be fine if you didn't have any sleeps inside of your txns... But the moment you added the sleeps inside the txns, your limit of 8 kills through throughput... because no other thread can access the DB once 8 of them grab connections and start the 20ms of total sleep. Imagine instead if you had 64 connections... you would 8x your throughput... What if you were to go even higher? At some point you might start overloading the DB... at that point, you could consider tuning the db to accept more connections... or... maybe you've truly reached the DB's peak performance limit.
I just don't think that 8 connections represents that limit... you need to do everything you can to open up your client config until you reach PG's limitations.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#63How does SQLite handle HA setups? The minimum I want is reliable automatic failover in reasonable time for user-facing service. Ideally an active-active setup.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#64Earlier quoted context omitted.
Scale-up solves a lot of problems for stable workloads. But elasticity is poor, so you either live with overprovisinoed capacity (multiples, not percentages) or fail under spiky load which often time is the most valuable moment (viral traffic, Black Friday, etc). No one has solved this problem. Scale out is typically more elastic, at least for reads.
That's a good point, but when one laptop can do 102545 transactions per second, overprovisioned capacity is kind of a more reasonable thing to use than back when you needed an Amdahl mainframe to hit 100 transactions per second.
As compute becomes cheaper your argument becomes more and more true.
But it only works if workloads remain fixed. If workloads grow at similar rates you’re back to the same problem.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#65Sqlite is very cool. But what was the point of slowing postgress down?
> But, wait our transactions are not serialisable, which they need to be if we want consistent transaction processing
You either don't know what serializable does or trying to mislead the reader. There is zero reason to use searializable here.
> Let's say you have 5ms latency between your app server and your database.
5ms latency is unrealistic. Unless you use wifi or you database is in another datacenter.
> I'm talking about transactions per second, specifically interactive transactions that are common when building web applications
No they are not common at all. You probably invented them just to make pg look bad.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#66I've been getting some good results from sqlite-vec on my current project. I need to look at geospatial extensions next.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#67> Hopefully, this post helps illustrate the unreasonable effectiveness of SQLite as well as the challenges you can run in with Amdahl's law and network databases like postgres. No, it does not. This article first says that normally you would run an application and the database on separate servers and then starts measuring the performance of a locally embedded database. If you have to keep the initial requirement for…
> If you have to keep the initial requirement for your software, then SQLite is completely out of equation. No it isn't? You can run a thin sqlite wrapping process on another server just fine. Ultimately all any DB service is, PostgreSQL included, is a request handler and a storage handler. SQLite is just a storage handler, but you can easily put it behind a request handler too. Putting access to sqlite behind a seri…
Exactly. People forget that “SQLite can’t do X” often really means “SQLite doesn’t ship with X built in.” If you wrap it with a lightweight request handler or a queue, you essentially recreate the same pattern every other DB uses. The fact that PostgreSQL bundles its own coordinator doesn’t make SQLite fundamentally incapable. It just means you choose whether you want that layer integrated or external.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#68Cool stuff as usual, Anders. One of the nice things about running a networked DB is that it makes redeploying the application a bit simpler. You can spin up a new EC2 instance or whatever and once it's online kill the old one. That gets 0 or close to 0 downtime. If the DB is on the same instance, replacing it requires loading up the DB onto the new instance, which seems more error prone than just restarting the app o…
Oh there are a bunch of considerations. You're going to want persistent storage on your server, not ephemeral. You'll also want NVME. A lot of the time you're going to end up on bare metal running a single server anyway. You're going to have down time for migrations unless you're very clever with your schema and/or replicas. Litestream for me at least is what makes SQLite viable for a web app as prior to that there w…
> You're going to have down time for migrations unless you're very clever with your schema and/or replicas.
probably worth stating these kinds of design considerations/assumptions up-front
i'm sure lots of applications are fine with "downtime for [database] migrations" but lots more are definitely not, especially those interested in synthetic metrics like TPS
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#69Earlier quoted context omitted.
That's a good point, but when one laptop can do 102545 transactions per second, overprovisioned capacity is kind of a more reasonable thing to use than back when you needed an Amdahl mainframe to hit 100 transactions per second.
As compute becomes cheaper your argument becomes more and more true. But it only works if workloads remain fixed. If workloads grow at similar rates you’re back to the same problem.
Well, it doesn't work for the newly added workloads. But for the most part we instead have the same workloads performed less efficiently.
Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite
#70The only caveat being this assumes all your data can fit on a single machine, and all your processing can fit on one machine. You can get a a u-24tb1.112xlarge with 448 vcores, 24TB RAM for 255/hour and attach 64TB of EBS -- that's a lot of runway.
Scale-up solves a lot of problems for stable workloads. But elasticity is poor, so you either live with overprovisinoed capacity (multiples, not percentages) or fail under spiky load which often time is the most valuable moment (viral traffic, Black Friday, etc). No one has solved this problem. Scale out is typically more elastic, at least for reads.
I love hetzner for internal resources because they're not spikey. For external stuff I like to do co-processing, you can load balance to cloudflare/aws/gcp services like containers/Run/App Runner/etc.