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

21–30 of 169 posts

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

#21

Post by adityaathalye »

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.

Heh, the documentation calls out the limits. Maximum (theoretical) DB size is 281TB: https://sqlite.org/limits.html

> This particular upper bound is untested since the developers do not have access to hardware capable of reaching this limit.

> However, tests do verify that SQLite behaves correctly and sanely when a database reaches the maximum file size of the underlying filesystem (which is usually much less than the maximum theoretical database size) and when a database is unable to grow due to disk space exhaustion.

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

#22

Post by tetha »

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.

In my experience, a decently managed database scales very hard.

3x EX44 running Patroni + PostgreSQL would give you 64GB of working memory, at least 512 GB NVMe of dataset (configurable with more for a one-time fee) at HA + 1 maintenance node. Practically speaking, that would have carried the first 5 - 10 years of production at the company I work at with ease, for 120 Euros hardware cost/month + a decent sysadmin.

I also know quite a few companies who toss 3-4x 20k - 30k at DELL every few years to get a database cluster on-prem so that database performance ceases to be a problem (unless the application has bad queries).

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

#23

Post by westurner »

matharmin wrote: post #7
westurner wrote: post #4

That's a helpful TPS Report. TIL `SAVEPOINT` can occur in a BEGIN ... END SQLite transaction, and that works with optimizing batch size on a particular node with a given load. Is there a solution for SQLite WAL corruption? From https://news.ycombinator.com/item?id=45133444 : > "PSA: SQLite WAL checksums fail silently and may lose data" https://news.ycombinator.com/item?id=44672902 > sqlite-parquet-vtable , [...]

As mentioned in those threads, there is no SQLite WAL corruption if you have a working disk & file system. If you don't, then all bets are off - SQLite doesn't protect you against that, and most other databases won't either. And nested transactions (SAVEPOINT) won't have have any impact on this - all it does in this form is reduce the number of transactions you have.

> working disk & file system

And a working ECC or non-ECC RAM bus, and [...].

How bad is recovery from WAL checksum / journal corruption [in SQLite] [with batching at 100k TPS]?

And should WAL checksums be used for distributed replication "bolted onto" SQLite?

>> (How) Should merkle hashes be added to sqlite for consistency? How would merkle hashes in sqlite differ from WAL checksums?

SQLite would probably still be faster over the network with proper Merkleization

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

#24

Post by kragen »

DenisM wrote: post #6
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.

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.

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

#26

Post by stonemetal12 »

embedding-shape wrote: post #16
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"

The table of data at the end of the article has 7 lines, only one has data for both DBs. What was the point of setting up the comparison if there is no comparison made?

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

#27

Post by alexwennerberg »

andersmurphy wrote: post #12
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.

Thank you! I read this a long time ago and could never remember it, thinking it was Shopify that wrote it.

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

#28

Post by busymom0 »

Previously, I had always used Postgres for database and Rust or NodeJS for my backend. For my new website (https://limereader.com/), I used Swift for my backend, SQLite for Database, Vapor for web server in the Swift app and am self-hosting the site on an old Mac mini.

A sqlite related issue I ran into had to do with accessing the SQLite database from multiple threads. Found out a solution easily: for multi-threading use, SQLite needed to be initialized with a `SQLITE_OPEN_FULLMUTEX` flag. Since then, the website has been running seamlessly for about 3 weeks now.

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

#29

Post by andersmurphy »

stonemetal12 wrote: post #26
embedding-shape wrote: post #16

Earlier quoted context omitted.

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

The table of data at the end of the article has 7 lines, only one has data for both DBs. What was the point of setting up the comparison if there is no comparison made?

Because it shows that a network RDBS database cannot get you out of this predicament.

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

#30

Post by yolo3000 »

andersmurphy wrote: post #12
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.

Maybe they should have stuck with traditional tech and used their talent on something else. Stock is down 96%
Post reply on HN