What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?
[deleted]
Learning a few things about running SQLite
21–30 of 101 posts
Re: Learning a few things about running SQLite
#22It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.
> However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests
That’s not really accurate any longer.
Mostly depends on how you layout your tables & files. If you shard the databases then multiple machines can act as writers for their shard. You can also split read requests from write requests and have read only machines scale up/down as much as you’d like. You can use multiple files in a query (there is a limit there).
So for example you can split the user table based on the first letter of the username and then depending on the rest of the database either a database file per user or per customer (organization). Of course more of everything is manual but it’s not as hard as you’d expect if you build for it.
https://rivet.dev/blog/2025-02-16-sqlite-on-the-server-is-mi...
If you need sqlite over the network you can look at https://turso.tech/ it’s a almost drop in replacement for sqlite (https://github.com/tursodatabase/turso/blob/main/COMPAT.md)
Re: Learning a few things about running SQLite
#23As for the DELETE issue the easy solutions are: -Delete it batches -Delay between batches -Preload the rowids before deleteing with SELECT (Select does not block) Additionally if data was added sequentially primary to the same table the data is likely stored this way in the file and deleting it in this or in reversed order can be faster (depends on storage medium and other factors).
Row ID preloading is an extremely effective technique—and not just for SQLite. I’ve also used it to great effect on massive Aurora MySQL or Postgres clusters since I could send the SELECT to a replica, and the whole point of deletions was that index memory pressure from the row filtering was putting tons of CPU and buffer cache pressure on the db.
If you’re in a situation where partition pruning or other strategies for getting useless data out of the hot path don’t make sense, this is a killer strategy.
Re: Learning a few things about running SQLite
#24I run my backups like this: OUT="${i}.sql.zst" PART="${OUT}.part" sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" - mv "${PART}" "${OUT}" That doesn't block writers (when the writer uses WAL), and gives me a dump that's compressed well while also being easy to sync. My Home Assistant DB is 1.8GB, my dump is 286MB compressed, and I'd guess 90% of that is consistent from one day to the next.
> That doesn't block writers (when the writer uses WAL)
Neither does VACUUM INTO or ".backup" (which uses the backup API) or sqlite3_rsync or litestream.
Re: Learning a few things about running SQLite
#25> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…
I've worked with large MySQL databases that used row-based replication and things like an UPDATE or DELETE that affected millions of rows had to be applied in batches there, because otherwise one SQL query might result in a million updated rows needing to be sent to all of the replicas at once.
I've built custom batch-processors because percona-toolkit's automatic stuff was far too aggressive :|
Every DB needs it, eventually. Even NoSQL darlings like Cassandra - I've seen it go into a resource-constrained death-spiral on stuff that should be async / non-blocking and safe. If you need to stay up, it's always worth planning on, and making sure your logic works during long-running gradual migrations.
Re: Learning a few things about running SQLite
#26Is it me or this is one of the worst and knowingly less informed articles that has hit HN in a while?
Re: Learning a few things about running SQLite
#27It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.
Is this something that the authors of SQLite actually claim? I don't think anyone else can decide what something is meant for.
Re: Learning a few things about running SQLite
#28What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?
Dead-man's switch means triggering when something doesn't happen. (The name comes from a switch that an alive operator would need to hold in such a way that if they died they would stop holding.) So in this case she means that her monitoring will fire if there wasn't a successful backup within some configured period of time. I assume this is opposed to alerting when the backup job fails, which is an issue if the job…
Once hired a DBA that reworked backup scripts. He got real annoyed at the idea of testing the backups with real restores and clearly never did on the real databases (only on his smaller samples).
Turns out the backups took 30 hours. The daily backups. That then overwrote each other on the assumption that the backups would not take that long.
Of course we found out the hard way.
Re: Learning a few things about running SQLite
#29> Maybe one day I’ll learn to read a query plan. With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite... sqlite> CREATE TABLE x1(a, b, c); -- Create table in database sqlite> .expert sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Analyze this SELECT CREATE INDEX x1_idx_000123a7 ON x1(a, b); 0|0|0|SEARCH TABLE x1 USING INDEX x1_idx_000123a7 (…
Another side effect of deleting 10 million rows in some databases (e.g., Oracle) is that the database writes out 10 million rows' worth of undo, which can swamp the disk space set aside for archive logs if you can't back it up and clear it off fast enough. Committing more frequently help, but if you have large databases and regularly need to purge, the best way in my experience is to use partitioning. Dropping the oldest (or whatever) partition is nearly instant and painless.
I wasn't clear exactly what the author was doing. "The worker crashes because it couldn’t write to the database and the VM shuts down" - why would the VM shut down? I assume VM here means the Virtual Machine (OS).
Re: Learning a few things about running SQLite
#30It's great! However, it's only meant for local systems. Once you need to connect over a network or robustly handle simultaneous requests, you need something like postgres.
Is this something that the authors of SQLite actually claim? I don't think anyone else can decide what something is meant for.
Once you release software to the world, the world can choose use it however it wants. Still, the author's of SQLite document their intention for it to be used locally on their "when to use" page.
> SQLite strives to provide local data storage for individual applications and devices.