Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

100k TPS over a billion rows: the unreasonable effectiveness of SQLite

andersmurphy.com

Post reply on HN
Print view View original

141–150 of 169 posts

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#141

Post by andersmurphy »

janandonly wrote: post #135
stickfigure wrote: post #74

Earlier quoted context omitted.

If you're going to run on more than one piece of hardware, something is going to be remote to your single writer database. As an industry, we've generally decided against "one big box", for reasons that aren't necessarily performance related.

I sometimes dream of a local-first world in which all software works with local DB and only writes to the cloud as an afterthought, maybe as a backup or a way to pick up work on another machine. It just boggles my mind that more software nowadays relies on an always on internet connection for no good reason other then the design itself.

I think people's reaction to cloud vendors is to go local first. But, there's a middle ground VPS, rented server, even self hosting.

My problem with local first is it's fine for solo apps with the occasional sync. But doesn't work for medium to large datasets and the stuff I work in is generally real-time and collaborative. To me multiplayer is one of the strengths of the web.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#142

Post by andersmurphy »

benjiro wrote: post #110
andersmurphy wrote: post #42

Earlier quoted context omitted.

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…

> Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. Does Sqlite now not have a build in rsync for replicas? Searches, yep ... https://sqlite.org/rsync.html

Pretty sure rsync was only added in 2024, litestream predates it by quite a bit.

What's cool is the newest version of rsync lets you replicate while in journal mode (which litestream doesn't support).

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#143

Post by zbentley »

andersmurphy wrote: post #132
rich_sasha wrote: post #119

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…

These are some really good points. - WAL checkpointing is very important (litestream handles this well). As you said not checkpointing can cause massive query slow down. - SQLITE_LOCK and SQLITE_BUSY can be avoided by ensuring your application only has a single write connection ideally behind an MPSC queue. After WAL this is probably one of the biggest SQLite quality of life improvements. - 100% avoid cloud drives in…

> a single write connection ideally behind an MPSC queue

That’s a pretty tall order. What if I want read-after-write consistency for code that issues a write? Did you mean some kind of a fair mutex around writes instead (in which case, how is this different from what SQLite already does?)? What if writes are coming from multiple uncoordinated processes? Do I then need to bring in a daemon or IPC?

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#144

Post by andersmurphy »

zbentley wrote: post #143
andersmurphy wrote: post #132

Earlier quoted context omitted.

These are some really good points. - WAL checkpointing is very important (litestream handles this well). As you said not checkpointing can cause massive query slow down. - SQLITE_LOCK and SQLITE_BUSY can be avoided by ensuring your application only has a single write connection ideally behind an MPSC queue. After WAL this is probably one of the biggest SQLite quality of life improvements. - 100% avoid cloud drives in…

> a single write connection ideally behind an MPSC queue That’s a pretty tall order. What if I want read-after-write consistency for code that issues a write? Did you mean some kind of a fair mutex around writes instead (in which case, how is this different from what SQLite already does?)? What if writes are coming from multiple uncoordinated processes? Do I then need to bring in a daemon or IPC?

It's really not. You have multiple read connections and a single write connection. You batch over that single write connections. The items of a batch is just a function with a sequence of queries and or application logic. That means these functions can read their own writes (as they can read using the write connection). This gives you read-after-write consistency. Because, all these functions are in a batch transaction they can read the writes of functions that have been run before them in the same batch.

Generally I find SQLite works best with a CQRS model, where you push view updates to your clients. So reads and writes are separate. In this model you only need read-after-write for transaction logic (i.e doing a read to check for something before doing an update).

Writes can come from multiple threads, they will just be put on a queue. Personally, I use an optimistic concurrency queue for batches, and there's only one "batcher" that has the write connection.

The key difference between this and what SQLite does is quite significant.

SQLite doesn't queue those writes instead they storm the place, taking turns at asking "can I go now" and sleeping for (tens, hundreds of) milliseconds at a time.

This only gets "worse" as computers get faster: imagine how many write transactions a serial writer could complete (WAL mode and normal synchronous mode) while all your writers are sleeping after the previous one left.

If you have a single limited pool, your readers will now be stuck waiting for an available connection taken by sleeping writers etc.

It's fairer and more efficient if your application handles the writer queue.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#145

Post by rich_sasha »

andersmurphy wrote: post #144
zbentley wrote: post #143

Earlier quoted context omitted.

> a single write connection ideally behind an MPSC queue That’s a pretty tall order. What if I want read-after-write consistency for code that issues a write? Did you mean some kind of a fair mutex around writes instead (in which case, how is this different from what SQLite already does?)? What if writes are coming from multiple uncoordinated processes? Do I then need to bring in a daemon or IPC?

It's really not. You have multiple read connections and a single write connection. You batch over that single write connections. The items of a batch is just a function with a sequence of queries and or application logic. That means these functions can read their own writes (as they can read using the write connection). This gives you read-after-write consistency. Because, all these functions are in a batch transacti…

This may be a good approach, but it departs a little from "it's just a file with ACID semantics, abuse it all you like". If you have multiple reader/writer processes then, if I read your post correctly, you'd need a gatekeeper process that batches the writes. And it needs, I suppose, to support more than query+data. I remember running a transaction that inserts the data, queries last inserted rowid and returns that to the user. It's not super straightforward to implement via a separate process.

But in any case, by the time you do that, you need a monitored (hand-rolled) service and at least some of the allure of a db-in-a-file goes away.

Again I'm not being mean about SQLite, it's a great piece of technology. Just sharing my war stories with others who may want to push it hard one day too.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#146

Post by cluckindan »

The first example uses a pattern where application code is run in the middle of a db transaction. That is almost certainly an anti-pattern, unless the computation is very lightweight - and even in that case it could probably be done in SQL.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#147

Post by andersmurphy »

rich_sasha wrote: post #145
andersmurphy wrote: post #144

Earlier quoted context omitted.

It's really not. You have multiple read connections and a single write connection. You batch over that single write connections. The items of a batch is just a function with a sequence of queries and or application logic. That means these functions can read their own writes (as they can read using the write connection). This gives you read-after-write consistency. Because, all these functions are in a batch transacti…

This may be a good approach, but it departs a little from "it's just a file with ACID semantics, abuse it all you like". If you have multiple reader/writer processes then, if I read your post correctly, you'd need a gatekeeper process that batches the writes. And it needs, I suppose, to support more than query+data. I remember running a transaction that inserts the data, queries last inserted rowid and returns that t…

SQLite, for me at least, is an embedded database it should be relatively tightly coupled to your application if you want to get the most out of it in the context of web servers. After all it's part of your application.

Again, maybe it's because I'm using Clojure on the JVM (with both real and virtual threads as well as bunch of great concurrent data-structure). But, setting up a "process" (thread) to do that batching is not hard, it's also easy for the individual functions to return results to their call sites via the Java promise API (after the batch completes).

All of this runs in a single process. With a single deployable uberjar/artefact.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#148

Post by andersmurphy »

cluckindan wrote: post #146

The first example uses a pattern where application code is run in the middle of a db transaction. That is almost certainly an anti-pattern, unless the computation is very lightweight - and even in that case it could probably be done in SQL.

You can definitely mitigate the power/amdahl's law problems with pure SQL and/or triggers/stored procedures. I mention that in the article. However, sacrificing interactive transactions is rough and not always possible.

It's worth keeping in mind that the problem here is not heavyweight computation, but the network latency to the database. Your computations can be fast, but if your latency is still at best 1ms for each time you go to the database during that transaction. Every ms that high contention row is locked is kept your throughput degrades dramatically.

Re: 100k TPS over a billion rows: the unreasonable effectiveness of SQLite

#149

Post by closeparen »

tuhgdetzhh wrote: post #5
matthewaveryusa wrote: post #3

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

Or rent a bare-metal machine from hetzner with 2-3x performance per core and 90% less costs[1]. [1] Various HN posts regarding Hetzner vs AWS in terms of costs and perf.

It’s too bad that there does not seem to be a comparable provider with datacenters in North America.
Post reply on HN