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

161–169 of 169 posts

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

#161

Post by zbentley »

andersmurphy wrote: post #147
rich_sasha wrote: post #145

Earlier quoted context omitted.

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…

> setting up a "process" (thread) to do that batching is not hard

Processes and threads are different things with specific meanings.

How would this approach work in an application server that implements concurrency with forking/multiple child processes (Python, ruby, PHP, node cluster, and many many more)?

The biggest benefit of SQLite is that, as GP says, it can be used like a file. Many places I’ve worked have used it to coordinate between all sorts of processes—often different processes written in different languages!

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

#162

Post by andersmurphy »

zbentley wrote: post #161
andersmurphy wrote: post #147

Earlier quoted context omitted.

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…

> setting up a "process" (thread) to do that batching is not hard Processes and threads are different things with specific meanings. How would this approach work in an application server that implements concurrency with forking/multiple child processes (Python, ruby, PHP, node cluster, and many many more)? The biggest benefit of SQLite is that, as GP says, it can be used like a file. Many places I’ve worked have used…

>How would this approach work in an application server that implements concurrency with forking/multiple child processes (Python, ruby, PHP, node cluster, and many many more)?

Sadly it mostly doesn't without a lot of work. You need to use languages that are not single threaded (have native multithreading support): Clojure/Java/Go/C# etc.

It's why python/ruby/php/node etc are fundamentally constrained. I'd argue those languages are the ones that pushed the horizontal scaling trend as they struggle to get the most out of a single machine.

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

#163

Post by maxmcd »

busymom0 wrote: post #33
maxmcd wrote: post #31

Earlier quoted context omitted.

This will block threads while waiting for other threads to write. That might work great for your threading model but I usually end up putting the writer in one thread and then other threads send writes to the writer thread.

I do open 2 connections: First one for writing with flags: SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE | SQLITE_OPEN_FULLMUTEX Second one for reading with flags: SQLITE_OPEN_READONLY | SQLITE_OPEN_FULLMUTEX As you can note, I have SQLITE_OPEN_FULLMUTEX on both of them. Should I only have it for the writing one?

Oh nice, yes I think your threads should be able to perform reads concurrently when the write lock is not held. Would make sure you are in WAL mode as well, since I think that will improve your concurrency.

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

#164

Post by ngrilly »

andersmurphy wrote: post #84
kiitos wrote: post #68

Earlier quoted context omitted.

> 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

I'd argue the opposite most applications are fine with an hour of downtime a month and arguably much more downtime then that. The recent AWS and Cloudflare outages have proven that. You can achieve zero downtime with Sqlite if you really need to. TPS is not a synthetic metric when you cap out at 100 TPS because of Amdahl's law and your users having a power distribution.

How would you achieve zero downtime?

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

#165

Post by andersmurphy »

ngrilly wrote: post #164
andersmurphy wrote: post #84

Earlier quoted context omitted.

I'd argue the opposite most applications are fine with an hour of downtime a month and arguably much more downtime then that. The recent AWS and Cloudflare outages have proven that. You can achieve zero downtime with Sqlite if you really need to. TPS is not a synthetic metric when you cap out at 100 TPS because of Amdahl's law and your users having a power distribution.

How would you achieve zero downtime?

Personally, I'm a fan of event sourcing if you need zero downtime you probably need auditibility. You have one SQLite database that's an append only event log. You build projections from it into any number of SQLite databases. These databases pull from the log database and update themselves. They are completely expendable. So you never have to vacuum them or migrate them. You just build a new projection and then point to it. This is also how you can spread your sqlite over nodes (if that's your thing, with something like NATS).

There are other ways where you're just more disciplined with your schema and indexes. I.e jsonb, partial indexes and existence based programming (from data oriented design).

Edit: In the time it took me to write this cloudflare is down again https://news.ycombinator.com/item?id=46158200

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

#166

Post by ngrilly »

andersmurphy wrote: post #165
ngrilly wrote: post #164

Earlier quoted context omitted.

How would you achieve zero downtime?

Personally, I'm a fan of event sourcing if you need zero downtime you probably need auditibility. You have one SQLite database that's an append only event log. You build projections from it into any number of SQLite databases. These databases pull from the log database and update themselves. They are completely expendable. So you never have to vacuum them or migrate them. You just build a new projection and then poin…

That's indeed a possible (and very resilient) solution. But that's a significant shift for many apps. I'm fan of event sourcing in theory, but I've always been afraid of it making things overly complex in practice, for relatively small apps. But I haven't tried hard enough to have a real opinion on this.

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

#167

Post by andersmurphy »

ngrilly wrote: post #166
andersmurphy wrote: post #165

Earlier quoted context omitted.

Personally, I'm a fan of event sourcing if you need zero downtime you probably need auditibility. You have one SQLite database that's an append only event log. You build projections from it into any number of SQLite databases. These databases pull from the log database and update themselves. They are completely expendable. So you never have to vacuum them or migrate them. You just build a new projection and then poin…

That's indeed a possible (and very resilient) solution. But that's a significant shift for many apps. I'm fan of event sourcing in theory, but I've always been afraid of it making things overly complex in practice, for relatively small apps. But I haven't tried hard enough to have a real opinion on this.

Oh for sure. That's why my default is if you don't need event sourcing and are ok with some down time a month if you need to add a really large index. Keep it simple.

Once you have a decent CQRS set up (which is a natural fit with SQLite) and event sourcing it can be quite easy to spin up on a new project.

I think people don't have an honest assesment of what their project requirements are in terms of uptime and scale. So they start with crazy microservice multinode architectures that are designed to scale to the moon and inevitably end up with more downtime due to complexity.

I'd still recommend people start with managed PG for most things. But, if you're comfortable using VPSs or bare metal servers SQLite can take you very far.

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

#168

Post by bombela »

hedora wrote: post #160
bombela wrote: post #116

Earlier quoted context omitted.

I am not so sure you know what you are talking about. Feel free to provide some reading material for my education. Why would the scheduler tick frequency even matters for this discussion. Even on a single cpu/core/thread system. For what is worth, the default scheduler tick rate has been 2.5ms since 2005. Earlier this year somebody proposed switching back to 1ms. https://btrfs.readthedocs.io/en/latest/dev/dev-btrfs-d…

Well, I haven’t checked it for a while. Still, try measuring FS latencies during checkpoints, or just write a tight loop program that reads cached data and prints max latencies once an hour. Use the box for other stuff while it runs.

https://github.com/accretional/collector/blob/main/pkg/colle...

There is no way btrfs can be slower than this in any shape or form.

If we are comparing something simpler. Like making a copy of the SQLite database periodically. It makes sense for a COW snapshot to be faster than copying the whole database. After reading the btrfs documentation, it seems reasonable to assume that the snapshot latency will stay constant, while a full copy would slow down as the single file database grows bigger.

And so it stands of reason that freezing the database during a full copy is worse than freezing it during a btrfs snapshot. And a full copy of the snapshot can then be performed, optionally with a lower IO priority for good measure.

It should be obvious that the less data is physically read or written on the hot path, the less impact there is on latency.

For what is worth, here is a benchmark comparing IO performance on a few linux filesystem. Including some sqlite tests. https://www.phoronix.com/review/linux-615-filesystems

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

#169

Post by ngrilly »

andersmurphy wrote: post #167
ngrilly wrote: post #166

Earlier quoted context omitted.

That's indeed a possible (and very resilient) solution. But that's a significant shift for many apps. I'm fan of event sourcing in theory, but I've always been afraid of it making things overly complex in practice, for relatively small apps. But I haven't tried hard enough to have a real opinion on this.

Oh for sure. That's why my default is if you don't need event sourcing and are ok with some down time a month if you need to add a really large index. Keep it simple. Once you have a decent CQRS set up (which is a natural fit with SQLite) and event sourcing it can be quite easy to spin up on a new project. I think people don't have an honest assesment of what their project requirements are in terms of uptime and scal…

I fully agree that most projects would work perfectly fine with a monolith and PostgreSQL. And yet they go with microservices, Redis, RabbitMQ, etc, making the system more complex and less available.

SQLite would be perfect if it would allow multiple concurrent writers, which would allow running database migrations without downtime. I’m looking forward to Turso for this.

Post reply on HN