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

11–20 of 169 posts

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

#11

Post by diath »

> 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 your software, then SQLite is completely out of equation. If you can change the requirement, then you can achieve similar performance by tuning the local PGSQL instance -- and then it also becomes a valuation of features and not just raw throughput. I'm not saying SQLite is not an option either, but this article seems confusing in that it compares two different problems/solutions.

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

#12

Post by andersmurphy »

alexwennerberg wrote: post #10

Does anyone have examples of organizations that have leveraged SQLite and written about their experience? I've read a lot of theory and benchmarks about it lately and it seems extremely impressive, but I'm wondering if anyone has written about pushing it to its limits "in production"

https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

Linked at the bottom of the article is the most extreme I've seen.

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

#15

Post by [deleted] »

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.

[deleted]

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

#16

Post by embedding-shape »

diath wrote: post #11

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

It'd be a very short article if so, don't you think? Full article would be something like: "Normally you'd have a remote connection to the database, and since we're supposed to test SQLite's performance, and SQLite is embedded, it doesn't compare. Fin"

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

#17

Post by jandrese »

embedding-shape wrote: post #9
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.

> The only caveat being this assumes all your data can fit on a single machine Does my data fit in RAM? https://yourdatafitsinram.net/ Not sure using EC2/AWS/Amazon is a good example here, if you're squeezing for large single-node performance you most certainly go for dedicated servers, or at least avoid vCPUs like a plague.

That site is a bit questionable. I entered "64TB" as the answer and it was very happy to show me a bunch of servers that maxed out at 6 or 8TB. Even the one server that listed 64TB of RAM might be questionable since it's not leaving room for the OS or your applications. That said 64 TB is a gargantuan amount of data, so I'm not too worked up over it not fitting in RAM. Lord help you if you have a power outage and have to reload the data from disk.

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

#18

Post by weitendorf »

I've been working on a hybrid protobuf ORM/generic CRUD server based on sqlite

(code at https://github.com/accretional/collector - forgive the documentation. I'm working on a container-based agent project and also trialling using agents heavily to write the individual features. It's working pretty well but the agents have been very zealous at documenting things lol).

This is my first real project using sqlite and we've hit some similarly cool benchmarks:

* 5-15ms downtime to backup a live sqlite db with a realistic amount of data for a crud db

* Capable of properly queueing hundreds of read/write operations when temporarily unavailable due to a backup

* e2e latency of basically 1ms for CRUD operations, including proto SerDe

* WAL lets us do continuous, streaming, chunked backups!

Previously I'd only worked with Postgres and Spanner. I absolutely love sqlite so far - would still use Spanner for some tasks with an infinite budget but once we get Collector to implement partitions I don't think I would ever use Postgres again.

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

#19

Post by andersmurphy »

diath wrote: post #11

> 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…

Paradoxically, raw throughput matters a lot more if you are going to scale on a single box. SQLite is 10x PG on a single box in this example. Considering databases tend to be the bottle neck that can take you an order of magnitude further. PG on the same server will also be slower the more complex the transaction as unix sockets are still going to be considerably slower than a function call.

The other thing to point out is in this article is that the PG network example CANNOT scale horizontally due to the power law. You can throw a super cluster at the problem and still fundamentally do around 1000 TPS.

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

#20

Post by badmonster »

The real insight here is recognizing when network latency is your bottleneck. For many workloads, even a mediocre local database beats a great remote one. The question isn't "which database is best" but "does my architecture need to cross network boundaries at all?"
Post reply on HN