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

101–110 of 169 posts

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

#101

Post by abrookewood »

andrewvc wrote: post #75

The HN SQLite worship posts have gotten out of hand. What’s next a post on how appending to files is faster than Kafka? It’s great that some people have workloads that this is a fit for. What’s more common is the use case managed databases like RDS etc solves for. You have some quantity of data you want to always be there, be available over a network for whatever app(s) need it and want backups, upgrades, access cont…

Honestly, I think it's a reaction to all of the over-optimisation that everyone gets caught up in - immediately starting on AWS, Kubernetes and micro-services. Most of the projects people work on will never reach the performance limits of SQLite and a single server.

I'm not saying that there aren't valid reasons to use AWS & clustered solutions etc, but we shouldn't always take that as our starting position.

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

#103

Post by cortesoft »

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.

There are no Hetzner servers that have 24TBs of RAM

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

#104

Post by wbsun »

BenjiWiebe wrote: post #73
bob1029 wrote: post #61

This is great until you encounter a customer with a hard RPO requirement of 0. SQLite has a few replication options, but I would never trust this in high stakes domains over PGSQL/MSSQL/DB2/Oracle/etc.

I'm curious, is an RPO of 0 truly expected or needed? I can easily believe that some places would "require" it. What kind of data is so critical that the data from a quarter second before catastrophic destruction must be saved? I guess weapons testing, at least... But that wouldn't be streaming data of that importance for a very large % of time.

My experience: customers with $$$ will always believe they are very important, so important that losing a single bit is the end of world.

So you may not want to convince customers waving huge $$$ checks that their data are not that important. But instead, providing options to keep them once they realize that: their pockets are not that deep, and they are also totally ok losing some data.

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

#105

Post by kiitos »

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.

1h of downtime per month means you're delivering at best two 9s of availability. again that may be fine for lots of applications but it's trivial scale, and certainly a couple orders of magnitude below what aws and cloudflare provide

taking a step back, if your application's db requirements can be satisfied by sqlite [+replication] then that's great, but that set of requirements is much narrower, and much easier to solve, than what postgres is for

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

#106

Post by SJC_Hacker »

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.

SQLite can also do in memory

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

#107

Post by vachina »

ethanwillis wrote: post #90
earthnail wrote: post #48

Earlier quoted context omitted.

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.

I think I was still being a bit too harsh even after throwing into my comment that other providers aren't perfect either. But basically after the initial paperwork I had some issues with my account getting flagged even though I wasn't using it 99.999% of the time. It's not a huge deal for me because I wasn't trying them out for anything serious. I just questioned how often that might happen if I was actually using it…

You’re renting an entire infrastructure, I think a bit of KYC is reasonable.

I had more trouble onboarding AWS SES, with a process that felt more like me begging. With which I said fuck it and went with self hosting ever since (on a bare metal server no less)

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

#108

Post by aquariusDue »

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…

It's weird seeing people on HN complain about this aspect regarding Hetzner because it's the complete opposite of my experience. Two years I've rented a dedicated server for around 40 euros monthly from Hetzner as a business customer and I had no issues whatsoever. They didn't ask for a business license or personal ID or anything really, I provided a VAT ID along with a business name and address but it wasn't anything extra compared to what I also provided Migadu or Porkbun for example.

I suppose they might have more KYC procedures for personal accounts based outside the EU otherwise I have no clue.

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

#109

Post by slashdave »

SJC_Hacker wrote: post #106
slashdave wrote: post #38

Earlier quoted context omitted.

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

SQLite can also do in memory

Yeah, very good point. It all comes down to requirements. If you require persistence, then we can start talking about redundancy and backup, and then suddenly this performance metric becomes far less relevant.

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

#110

Post by benjiro »

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

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

Does Sqlite now not have a build in rsync for replicas?

Searches, yep ... https://sqlite.org/rsync.html

Post reply on HN