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

81–90 of 169 posts

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

#81

Post by chasil »

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…

As long as WAL mode is not enabled, connections over NFS/SMB or other file sharing protocols will work.

I'm not saying that this is a good idea, and it could fail in a spectacular manner, but it can be done. DML over this is just asking for trouble.

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

#82

Post by otoolep »

vhodges wrote: post #79
janci wrote: post #63

How does SQLite handle HA setups? The minimum I want is reliable automatic failover in reasonable time for user-facing service. Ideally an active-active setup.

sqlite is just a library (in C) A few projects: * https://github.com/rqlite/rqlite Distributed, fault tolerant cluster * https://litestream.io/ Replication to S3 (or compatible) - more disaster recovery than fail over * https://fly.io/docs/litefs/ Same Author as litestream). Distributed replication. Requires writes to be redirected to the primary. I am debating Postgres vs sqlite (probably with litestream) for a proj…

rqlite creator here, happy to answer any questions.

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

#83

Post by tomasol »

andersmurphy wrote: post #80
tomasol wrote: post #72

Author is setting PRAGMA synchronous="normal", meaning fsync is not issued as part of every write tx, but eventually. In order to make the comparison fair it should be set to "full".

PRAGMA synchronous="normal" is fine if you are in WAL mode. The database cannot be corrupted by power loss unlike in journal mode. > The synchronous=NORMAL setting provides the best balance between performance and safety for most applications running in WAL mode. You lose durability across power lose with synchronous NORMAL in WAL mode, but that is not important for most applications. Transactions are still atomic, c…

fsync is the most expensive operation during a write. NORMAL mode means you don't care whether last ~100 ms of transactions before a process crash / VM restart are going to be persisted or not. My suggestion is either to use synchronous="full" or disable `synchronous_commit` on Postgres to avoid comparing apples to oranges.

Edit: Also, the example indicates financial transactions. Can you explain why you need serializability but not durability?

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

#84

Post by andersmurphy »

kiitos wrote: post #68
andersmurphy wrote: post #42

Earlier quoted context omitted.

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…

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

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

#85

Post by gunnarmorling »

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…

You could do that, but you'd run into exactly the same bottleneck the author describes with a remote Postgres instance. The workload exposes high contention on hot rows. If transactions are kept open for several milliseconds due to this being a remote network call between client and DB server, throughput will be equally limited also when using SQLite.

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

#86

Post by hedora »

bombela wrote: post #52
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.

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 not clear backups are going to even be the maximum duration downtime while the system is healthy.

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

#87

Post by nchmy »

janci wrote: post #63

How does SQLite handle HA setups? The minimum I want is reliable automatic failover in reasonable time for user-facing service. Ideally an active-active setup.

there's various options now, but im most interested in Marmot - which is multi-master and just came out of a 2 year hibernation with a big overhaul that introduced a native gossip mechanism to replace NATS/Raft

https://github.com/maxpert/marmot/

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

#88

Post by ethanwillis »

Nextgrid wrote: post #44
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…

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

That's fair and I don't have any major issues with that.

I guess my concern on the bureaucracy is if you are unlucky enough to get flagged as a false positive it can be an annoying experience. And I can't really blame them too hard for having to operate that way in an environment of bad actors.

You're definitely right that the budget providers do attract the types of people trying to do bad things/exploit them in some way.

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

#89

Post by Yodan2025 »

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.

yep, then add an AWS worker in-between

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

#90

Post by ethanwillis »

earthnail wrote: post #48
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…

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 seriously and what kind of headaches it could cause me while re-verifying everything with them.

From people I know if everything is going good then their service is great. Server performance is good, pricing is good, etc.

Post reply on HN