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

51–60 of 169 posts

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

#51

Post by ErroneousBosh »

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.

Getting on for ten year's worth of forum posts on https://rangerovers.pub/ comes to about 32MB of SQL dump.

So yeah, easily.

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

#52

Post by bombela »

weitendorf wrote: post #18

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

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

Did you consider using a filesystem with atomic snapshots? For example sqlite with WAL on BTRFS. As far as I can tell, this should have a decent mechanical sympathy.

edit: I didn't really explain myself. This is for zero downtime backups. Snapshot, backup at your own pace, delete the snapshot.

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

#53

Post by [deleted] »

sethev wrote: post #46
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…

Right - but SQLite handily beats the case where postgres is on the same box as well. And it's completely reasonable to test technology in the configuration in which it would actually run. As an industry, we seem to have settled on patterns that actually are quite inefficient. There's no problem that requires the solution of doing things inefficiently just because someone said databases should run on a different host.

[deleted]

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

#54

Post by kiitos »

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.

and that your application doesn't need to be resilient to host or network faults

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

#55

Post by ducktective »

Does anyone have rough numbers (max daily users etc) on viability of SQLite vs PostgreSQL for a typical user-facing webapp or e-commerce application?

I know due to some recent update, SQLite can support concurrent reads but still only a single writer. For which cases this would be a problem?

Some recommend it's better to start with postgres anyway if you have any remote thoughts of scaling in mind....

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

#57

Post by otoolep »

BugsJustFindMe wrote: post #45
formerly_proven wrote: post #43

Earlier quoted context omitted.

Well that's just dqlite/rqlite.

Dqlite and Rqlite are primarily for buildling fault-tolerant clusters. But if you just take the network access part, then ok sure, but also so what?

rqlite[1] creator here.

Nit: dqlite is a library, it is not a network-exposed database like rqlite is. Sure, it requires connecting to other nodes over the network, but local access is via in-process. In contrast one connects with rqlite over the network - HTTP specifically.

[1] https://rqlite.io

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

#58

Post by emseetech »

andersmurphy wrote: post #19
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…

Also important is just how fast cheap hardware has gotten which means vertical scaling is extremely effective. People could get a lot farther with sqlite in wal mode on a single box with an nvme drive than they imagine. Feels like our intuition has not caught up with the material reality of current hardware.

And now that there are solid streaming backup systems, the only real issue is redundancy not scaling.

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

#59

Post by paulddraper »

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.

How does 25 TiB fit in RAM when the max machine has 24 TB?

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

#60

Post by jaketoronto »

Are 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 connection pool. (Also, might be nice for you to share what the CPU and thread usage on the Postgres side was during this). (Side note: I'm a big fan of sqlite.. but I've run into lots of performance bottlenecks caused by arbitrarily low connection pools and the like)
Post reply on HN