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

41–50 of 169 posts

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

#41

Post by barfoure »

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…

> Paradoxically, raw throughput matters a lot more if you are going to scale on a single box.

There’s absolutely nothing paradoxical about any of this.

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

#42

Post by andersmurphy »

tomconnors wrote: post #37

Cool stuff as usual, Anders. One of the nice things about running a networked DB is that it makes redeploying the application a bit simpler. You can spin up a new EC2 instance or whatever and once it's online kill the old one. That gets 0 or close to 0 downtime. If the DB is on the same instance, replacing it requires loading up the DB onto the new instance, which seems more error prone than just restarting the app o…

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 wasn't a good replication story.

With litestream it's much easier to have a backup on standby. That being said where I have used it in production some amount of downtime has been acceptable so mileage may vary.

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

#43

Post by formerly_proven »

BugsJustFindMe wrote: post #39
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. No it isn't? You can run a thin sqlite wrapping process on another server just fine. Ultimately all any DB service is, PostgreSQL included, is a request handler and a storage handler. SQLite is just a storage handler, but you can easily put it behind a request handler too. Putting access to sqlite behind a seri…

Well that's just dqlite/rqlite.

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

#44

Post by Nextgrid »

ethanwillis wrote: post #40
tuhgdetzhh wrote: post #5

Earlier quoted context omitted.

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.

This might be true in terms of direct monetary costs. I want to like Hetzner but the bureaucratic paper process of interacting with them and continuing to interact with them is just... awful. Not that the other clouds don't also have their own insane bureaucracies so I guess it's a wash. I'm just saying, I want a provider that leaves me alone and lets me just throw money at them to do so. Otherwise, I think I'd rathe…

> I want a provider that leaves me alone and lets me just throw money at them to do so.

That’s been my experience with Hetzner.

A lot of people get butthurt that a business dares to verify who they’re dealing with as to filter out the worst of the worst (budget providers always attract those), but as long as you don’t mind the reasonable requirement to verify your ID/passport they’re hands-off beyond that.

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

#45

Post by BugsJustFindMe »

formerly_proven wrote: post #43
BugsJustFindMe wrote: post #39

Earlier quoted context omitted.

> If you have to keep the initial requirement for your software, then SQLite is completely out of equation. No it isn't? You can run a thin sqlite wrapping process on another server just fine. Ultimately all any DB service is, PostgreSQL included, is a request handler and a storage handler. SQLite is just a storage handler, but you can easily put it behind a request handler too. Putting access to sqlite behind a seri…

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?

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

#46

Post by sethev »

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.

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

#47

Post by koakuma-chan »

slashdave wrote: post #38
badmonster wrote: post #20

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

Sure. Now keep everything in memory and use redis or memcache. Easy to get performance if you change the rules.

You can use SQLite for persistence and a hash map as cache. Or just go for Mongo since it's web scale.

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

#48

Post by earthnail »

ethanwillis wrote: post #40
tuhgdetzhh wrote: post #5

Earlier quoted context omitted.

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.

This might be true in terms of direct monetary costs. I want to like Hetzner but the bureaucratic paper process of interacting with them and continuing to interact with them is just... awful. Not that the other clouds don't also have their own insane bureaucracies so I guess it's a wash. I'm just saying, I want a provider that leaves me alone and lets me just throw money at them to do so. Otherwise, I think I'd rathe…

Can you elaborate on what the bureaucracy is you experienced? I'm a Hetzner customer since last month and so far I thoroughly enjoy it. Have not encountered any bureaucracy yet.
Post reply on HN