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

111–120 of 169 posts

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

#111

Post by gip »

aquariusDue wrote: post #108
ethanwillis wrote: post #40

Earlier quoted context omitted.

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

I'm based in the US and I tried twice to create an account for Hetzner (a personal account as well as a company / startup account). They rejected all my attempts. I don't quite understand their business model :)

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

#112

Post by johnthescott »

gip wrote: post #111
aquariusDue wrote: post #108

Earlier quoted context omitted.

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

I'm based in the US and I tried twice to create an account for Hetzner (a personal account as well as a company / startup account). They rejected all my attempts. I don't quite understand their business model :)

similar experience, as well. not sure what's going on with hetzner.

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

#113

Post by thunderfork »

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.

[dead]

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

#115

Post by superze »

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 love their pricing and the simplicity, but they don't give the impression of being highly skilled. They have zero managed services, not even managed K8. Their s3 (very mature tech at this point) is utterly garbage even one year after their launch.

Then the bureaucracy you mention which is just a reflection how they work internally as well.

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

#116

Post by bombela »

hedora wrote: post #86
bombela wrote: post #52

Earlier quoted context omitted.

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

If it’s at 5-15ms of downtime already, you’re in the space where the “zero” downtime FS might actually cause more downtime. In addition to pauses while the snapshot is taken, you’d need to carefully measure things like performance degradation while the snapshot exists (incurring COW costs) and while it’s being GCed in the background. Also, the last time I checked the Linux scheduling quanta was about 10ms, so it’s no…

I am not so sure you know what you are talking about. Feel free to provide some reading material for my education.

Why would the scheduler tick frequency even matters for this discussion. Even on a single cpu/core/thread system. For what is worth, the default scheduler tick rate has been 2.5ms since 2005. Earlier this year somebody proposed switching back to 1ms.

https://btrfs.readthedocs.io/en/latest/dev/dev-btrfs-design.... https://docs.kernel.org/admin-guide/pm/cpuidle.html https://docs.redhat.com/en/documentation/red_hat_enterprise_... https://sqlite.org/wal.html#ckpt https://www.phoronix.com/news/Linux-2025-Proposal-1000Hz

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

#118

Post by runako »

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

So much this. My inner perf engineer shudders every time I see one of these "modern" architectures that involve databases sited hundreds of miles from the application servers.

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

#119

Post by rich_sasha »

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well:

- it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too

- but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, can't write.

- in some cases, WAL does not checkpoint. This leads to drastic growth in the size of the WAL file, and down the line in catastrophic slowdown of queries - things that take 10ms suddenly take 10 seconds. In my particular case, no tweaking of SQLite params fixed it. I had to monitor for it, and periodically force WAL file to be rolled into the main DB.

- all of this gets harder on Windows, where eg.you cannot just 'lsof' a file.

- the performance stats change somewhat for the worse in the cloud on drives that look local but actually aren't. Of course that is not sqlite's fault, but the blazing fast performance doesn't apply to all commonly encountered environments that look like real local drives.

I'm not dissing SQLite, I use it despite these shortcomings. Equally, I'm happy to reach for something like Postgres, which, well, hasn't burned me yet.

EDIT I should add that despite all this I never managed to corrupt the DB, or break any of the SQL promises - never messed up indices, never saw broken ACID compliance etc. And that's a massive endorsement, on reflection.

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

#120

Post by asa400 »

rich_sasha wrote: post #119

I concur that sqlite is quite amazing. That said, I was a heavy user and have grown some skepticism as well: - it is not that hard to lock the db. Usually killing the process that caused the deadlock solves the issue - but you need to identify it / monitor for it. And yes, it happens with WAL too - but when it does happen, it is quite scary. Simply, anything that touches your DB suddenly stops working - can't read, c…

From your experience, would you call these behaviors bugs, or are they more known issues that result from SQLites specific implementation quirks? What kinds of workloads were you throwing at it when these types of issues happened? Asking as someone who really enjoys and respects SQLite but hasn't encountered these specific behaviors before.
Post reply on HN