> I’ve been backing up to AWS, which is always a pain because it’s annoying to navigate the AWS console to generate credentials.
I got so annoyed with that a few years ago that I ended up building a whole tool just to solve that one problem:
uvx s3-credentials create my-existing-s3-bucket
This spits out read-write credentials that are scoped JUST for that bucket. You can add --read-only or --write-only to have credentials that are further locked down, or even add --prefix foo/bar for credentials that can only read/write keys that start with that prefix within the bucket.
> Maybe one day I’ll move away to some other S3-compatible alternative.
I've used Restic with Cloudflare R2 and it worked great.
but i suppose if you're prefixing your entries with enough entropy, it'd be quite difficult for adversaries to predict and overwrite files without an `ls` equivalent
A more general solution for dealing with complex AWS services is learning just enough terraform to let LLMs do the rest. It also makes it much easier to tear stuff down later as you won’t need to remember what you created.
They're harder to completely understand than they are to read; it often happens for example that SQLite won't use existing indexes, for no obvious reason.
They're not hard to read until you see the queries that coworkers at large companies tend to generate. If the query is a novella that joins half of humankind's know data sources, the query plan tends to not be much better. Then there's always the dreaded question: "why is it slow?" "Because you're making the database yearn for the sweet release of death."
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 (a=? AND b>?)
sqlite> CREATE INDEX x1ab ON x1(a, b); -- Create the recommended index
sqlite> .expert
sqlite> SELECT * FROM x1 WHERE a=? AND b>?; -- Re-analyze the same SELECT
(no new indexes)
0|0|0|SEARCH TABLE x1 USING INDEX x1ab (a=? AND b>?)
Also wrt
> My approach so far has been to just do these cleanup operations in small batches so that I don’t need to do database queries that take more than 5 seconds to run. This whole experience has given me more of an appreciation for why someone might want to use a “real” database like Postgres which can have more than one writer at the same time though.
The advice for those " “real” " databases is generally to also do cleanup operations in small batches, they just tend to make it less obvious you're doing something unperformant in the smaller case. You're more right than you thought!
It just is what it is. Sometimes you want to write the obvious query without the DB getting in your way, and other times you want to know as soon as possible that you're doing something that won't scale under exponential load. At this point in my career I prefer the latter, but the former will always have a special place in my heart.
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.
Yeah, I think anyone that's done significant database work has come to the understanding that large updates need to be done in batches, otherwise you nuke performance.
Once you get to about 1M rows of data, batching is essential.
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.
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).
In my experience, SQLite explain plans are by far the most useless of any database out there. No concept of costing, no buffer information, no explain analyze. It's almost like they don't want you looking at it.
A couple of obvious ones would be running as a standalone service, and supporting multiple concurrent writes. Both of these help support transparent handling of multiple concurrent clients, which seems like table stakes for a "real database".
Various statistical views over the value distributions of the indexes, so that the planner can estimate how useful (selective) the index should be.
sqlite_stat1 just gives an average (number of records in the index, and average number of records per value), and if enabled sqlite_stat4 stores histogram data.
I don't call it a "dead man's switch", but I absolutely monitor some directories for new newest file. If the backup monitoring script doesn't find a file less than 24-ish hours old in the backup destination directory at any time it should send me an alert.
In this context it means upon a successful backup, update a timestamp somewhere. Some other system monitors the timestamp and if it ever becomes more than for example 1 day ago, it fires an alert.
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 never runs, or hangs forever, or crashes in a way that doesn't trigger your monitoring.
However I don't see how any of this solves the issue of not testing your backup. Because you can definitely have a backup task succeed regularly but the thing it is backing up is still unusable.
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.
If you're not using them, adding in silk and/or debug toolbar to your django app will be able to get some good automatic reporting and guidance on performance issues.
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.
I seem to recall having trouble with the read only flag and the backup API, but it's quite likely the problem was mine rather than with SQLite.
Anyway, the sync-friendly output is the really important part for me, because it means I can point borg at the zstd-compressed file and it'll only need to store what's changed.
What do you backup from your Home Assistant? The default backups are huge, but I finally settled for just the config and I leave the videos and caches off. I also leave off all the HACS downloaded repos. I'm wondering if I'm missing out by doing what I'm doing.
What's in the DB that makes the HA DB that big? You keep lots of historical time-series?
It'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.
At this point you're building your own networked database using sqlite just as a backing store. You should really reconsider if it's easier than using something designed for it.
If your entire system is sharded by username anyway for other reasons, then maybe what you've described works for you.
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.
So about one per second (up to ten, less conservatively). I concur. But if you think your site might ever scale beyond that, do yourself a favor and use Postgres from the get-go.
The vast, vast majority of websites never see anything even close to that, so it's a safe bet unless you have some specific reason to expect it to reach that kind of traffic, or you are dealing with workloads that SQLite really does not handle well, e.g. many concurrent writes. And if your workload is mostly reads, then you probably can use a cache layer, which allows SQLite to go further still.
Adding a cache layer to keep using the wrong database is an architecture failure - if you use Postgres from the get go you will not need the cache until way way later on anyway. (Side note, I’m team MySql but the same point applies)
I have a 100GB SQLite DB in use that gets 8+ million rows added dally.
It's on an off-the-shelf nvme SSD in a "server" that I build from 5+ year old parts.
Those are writes not hits so not directly comparable.
Use WAL (yes this should be the default, or at least explained much better) and you can have one writer, many readers.
Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections.
Of course if you are building a startup you must consider scaling.
-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.
I wonder if the ORM deletes were slow due to looping through a list calling delete on each object vs having a bulk delete method which accepts a list of IDs?
Don't confuse Julia's humility / accessible writing style for "less informed". She's a deeply knowledgeable programmer who's been doing this for a long time. She works hard to make tech topics feel less intimidating to newcomers.
Why not try a real database like Postgres? It's not as light-weight, but when operations get complicated, real databases are much easier to work with. I had a website that started with SQLLite, but when it got complicated enough, I spent two days to migrate the whole thing to Postgres. With current LLM coding agents, it's not that hard.
PGlite offers the "real database" compiled to WASM, which can then be embedded similarly to SQLite. You don't have to choose between PostgreSQL and "just a file".
Yeah pglite is exquisite. Now I don't have to write separate code for client and server side queries.
For example, I have a compiler that compiles a DSL to a DB query which returns a list. Now that query can either be on data in the browser or can be on data on the server. Now I don't have to write the logic twice!
the best thing about sqlite is that it's one of the few pieces of software where reading the documentation makes you a better engineer instead of just a more confused one
Litestream is super interesting, I managed to get it to run with S3 as a backend. Making apps with sqlite backends (there are a _lot_ lf them) almost stateless, at least no filesystem stare. I feel like s3 state is much more manageable, backups and syncing is done by the provider.
It was a great read Julia. I have also been using SQLite for my Wingman AI bot but had not explore much not for a website or another project. Your notes will be helpful, for sure. Thanks
IMHO for a small DB I’d encourage sending out an email on each successful backup to ensure it’s completed successfully as a safety check, and zipping it up and emailing it to a known account even. With inboxes being able to take gigabytes, it’s a no brainer. This can be done daily or weekly.
And yes, never allow the files to be deleted from outside. The transfer is a one way valve. If uploading, it’s a write-only operation, no delete unless the file has meta data for expiry.
> maybe there’s a bunch of Python code running inside a transaction
Basically, this article has no substance. The author didn't bother to learn anything, didn't look things up. And is then wildly guessing, sometimes wrong.
This is BTW the reason why (as a Debian user) if I search something Linux related and a Ubuntu forum pops up, I don't even open that anymore. Sure, Ubuntu is similar to Debian, but the amount of wrong guessworks in these forums is hefty. I however usually open the Arch Wiki pages, despite Arch very != Debian. But the articles there are written by knowledgeable people.
The Arch wiki is one of the best Linux-related resources out there. I used to look things up there all the time back when I was running Mint (which is basically Ubuntu under the hood). Ironically, now that I'm actually running Arch I think I'm looking things up on the Arch wiki less often than when I was running Mint.
I actually think the article is great - it shows the experience of someone who is a good approximation of a smart user that uses the tech. The posters focus is clearly on running the website, and these are the sorts of things that trip up everyday users that don’t spend their day in these tools.
Off the top of my head, yesterday in work I used 2 programming languages, 2 build systems, a cloud provider, a secrets manager, a very intricate framework for client server communication in both languages, plus my VCS, editor and CI tool. That’s a fairly typical set of tools to use for one feature for me, before you go into the weeds of OS versions, specific runtime versions, databases, reverse proxies, caches, and the domain logic!
If I went deep on every single thread exposed to me, I’d never get anything done, so I have to choose my battles just like OP has done here
She's also one of the best out there at demystifying technology and helping people understand what solving problems actually looks like.
This article doesn't pretend to be a world expert's take on using SQLite. The clue is right there in the title - "learning a few things about running SQLite" - which sets expectations right from the start.
The wider message is consistent throughout all of her writing. You can do this stuff. Here are simple practices to show how to figure out problems and build your knowledge. You don't have to know everything, and you certainly don't have to pretend to know everything. Sharing what you've figured out so far, in as clear a way as possible, is a virtue.
I realized that in the age of LLMs I appreciate Julia’s writing even more. Authentic exploration as an antidote to overconfident know-it-all generated junk articles.
Diving a bit more into databases than your current comfort level/current job demands remains a great way to level up.
I've worked with many web developers who get mental blockage around DB tooling (granted, I have similar mental blockage when it comes to some operations stuff like K8s), and you can go far in life without really having to ask _that many questions_.
But going in and finding out how your SQL turns into data gotten from disk/written to disk is very helpful in just "knowing" what might be a decent idea. That and understanding your DB's locking system (or lack thereof...).
Figuring that stuff out can help reduce the surprise level when you can't seem to get a "simple COUNT" working quickly in Postgres or the like...
As a database person, this was hard to read. I wanted to find out what the problems are and solve them.
A db table with only 10k rows? Even a full table scan should be extremely fast.
And with SQLite - which I assumed runs in-process, but even if not, surely is running on the same physical server? Faster still.
Of course, the magic phrase in my head is “create index”.
I hope Julia posts an update!
Edit: I highly suspect the “slow deletes” problem is a classic “n+1” problem suffered by many ORM users, until they come to understand more about the underlying db interactions.
simonw | a day ago
I got so annoyed with that a few years ago that I ended up building a whole tool just to solve that one problem:
This spits out read-write credentials that are scoped JUST for that bucket. You can add --read-only or --write-only to have credentials that are further locked down, or even add --prefix foo/bar for credentials that can only read/write keys that start with that prefix within the bucket.> Maybe one day I’ll move away to some other S3-compatible alternative.
I've used Restic with Cloudflare R2 and it worked great.
swyx | 19 hours ago
when would you want write only?
simonw | 18 hours ago
Natfan | 16 hours ago
simonw | 16 hours ago
Natfan | 13 hours ago
ufmace | 7 hours ago
I think write-only is worthwhile for database backups to guard against a ransomware compromise.
hiAndrewQuinn | 18 hours ago
bspammer | 13 hours ago
noctune | 2 hours ago
datadrivenangel | a day ago
Query plans aren't that hard to read! [0]
0 - https://xkcd.com/2501/
inigyou | 23 hours ago
bambax | 16 hours ago
deathanatos | 4 hours ago
striking | a day ago
With SQLite's `.expert` mode you can delay that day a little longer: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
Also wrt> My approach so far has been to just do these cleanup operations in small batches so that I don’t need to do database queries that take more than 5 seconds to run. This whole experience has given me more of an appreciation for why someone might want to use a “real” database like Postgres which can have more than one writer at the same time though.
The advice for those " “real” " databases is generally to also do cleanup operations in small batches, they just tend to make it less obvious you're doing something unperformant in the smaller case. You're more right than you thought!
DANmode | a day ago
Is this being positioned as a strength, in your comment?
striking | a day ago
simonw | a day ago
cogman10 | a day ago
Once you get to about 1M rows of data, batching is essential.
Groxx | a day ago
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.
bananamogul | a day ago
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).
inigyou | 23 hours ago
Raw EXPLAIN dumps bytecode, which is usually much more verbose than you want. EXPLAIN QUERY PLAN dumps a summary.
xetera | 14 hours ago
inigyou | 13 hours ago
simonw | 9 hours ago
antonvs | 5 hours ago
masklinn | a day ago
Various statistical views over the value distributions of the indexes, so that the planner can estimate how useful (selective) the index should be.
sqlite_stat1 just gives an average (number of records in the index, and average number of records per value), and if enabled sqlite_stat4 stores histogram data.
m0ose | a day ago
EvanAnderson | a day ago
The_Fox | a day ago
In this context it means upon a successful backup, update a timestamp somewhere. Some other system monitors the timestamp and if it ever becomes more than for example 1 day ago, it fires an alert.
kevincox | a day ago
I assume this is opposed to alerting when the backup job fails, which is an issue if the job never runs, or hangs forever, or crashes in a way that doesn't trigger your monitoring.
However I don't see how any of this solves the issue of not testing your backup. Because you can definitely have a backup task succeed regularly but the thing it is backing up is still unusable.
sroussey | a day ago
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.
fragmede | 22 hours ago
ryan42 | a day ago
andrewaylett | a day ago
formerly_proven | a day ago
Neither does VACUUM INTO or ".backup" (which uses the backup API) or sqlite3_rsync or litestream.
andrewaylett | 23 hours ago
Anyway, the sync-friendly output is the really important part for me, because it means I can point borg at the zstd-compressed file and it'll only need to store what's changed.
arjie | 22 hours ago
What's in the DB that makes the HA DB that big? You keep lots of historical time-series?
second_route | 17 hours ago
Kalanos | a day ago
gabeio | a day ago
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)
inigyou | 23 hours ago
If your entire system is sharded by username anyway for other reasons, then maybe what you've described works for you.
allknowingfrog | a day ago
fragmede | a day ago
> SQLite strives to provide local data storage for individual applications and devices.
https://www.sqlite.org/whentouse.html
dukeyukey | 23 hours ago
> Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.
inigyou | 23 hours ago
rafabulsing | 19 hours ago
maccard | 10 hours ago
simonw | 18 hours ago
dukeyukey | 15 hours ago
noxer | 13 hours ago
noxer | 13 hours ago
Those are writes not hits so not directly comparable.
maccard | 10 hours ago
inigyou | 23 hours ago
tomjen3 | 18 hours ago
Don't move to the network unless you have to - every single request gets massively slowed down because it has replaced local reads with network connections.
Of course if you are building a startup you must consider scaling.
noxer | a day ago
-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).
zbentley | a day ago
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.
pianopatrick | a day ago
arlattimore | a day ago
ktzar | a day ago
the_gastropod | 21 hours ago
wwind123 | 23 hours ago
abrookewood | 23 hours ago
9rx | 20 hours ago
vatsachak | 19 hours ago
For example, I have a compiler that compiles a DSL to a DB query which returns a list. Now that query can either be on data in the browser or can be on data on the server. Now I don't have to write the logic twice!
luciana1u | 21 hours ago
jackhalford | 18 hours ago
dksmart | 18 hours ago
dangantiban | 17 hours ago
And yes, never allow the files to be deleted from outside. The transfer is a one way valve. If uploading, it’s a write-only operation, no delete unless the file has meta data for expiry.
maccard | 10 hours ago
It’s as much work, and scales 1000x farther than the email approach.
second_route | 17 hours ago
holgerschurig | 16 hours ago
and
> my best guess
and
> and presumably other things?)
and
> maybe there’s a bunch of Python code running inside a transaction
Basically, this article has no substance. The author didn't bother to learn anything, didn't look things up. And is then wildly guessing, sometimes wrong.
This is BTW the reason why (as a Debian user) if I search something Linux related and a Ubuntu forum pops up, I don't even open that anymore. Sure, Ubuntu is similar to Debian, but the amount of wrong guessworks in these forums is hefty. I however usually open the Arch Wiki pages, despite Arch very != Debian. But the articles there are written by knowledgeable people.
rmunn | 15 hours ago
argamasa | 11 hours ago
maccard | 10 hours ago
Off the top of my head, yesterday in work I used 2 programming languages, 2 build systems, a cloud provider, a secrets manager, a very intricate framework for client server communication in both languages, plus my VCS, editor and CI tool. That’s a fairly typical set of tools to use for one feature for me, before you go into the weeds of OS versions, specific runtime versions, databases, reverse proxies, caches, and the domain logic!
If I went deep on every single thread exposed to me, I’d never get anything done, so I have to choose my battles just like OP has done here
simonw | 9 hours ago
She's also one of the best out there at demystifying technology and helping people understand what solving problems actually looks like.
This article doesn't pretend to be a world expert's take on using SQLite. The clue is right there in the title - "learning a few things about running SQLite" - which sets expectations right from the start.
The wider message is consistent throughout all of her writing. You can do this stuff. Here are simple practices to show how to figure out problems and build your knowledge. You don't have to know everything, and you certainly don't have to pretend to know everything. Sharing what you've figured out so far, in as clear a way as possible, is a virtue.
rollulus | 16 hours ago
rtpg | 15 hours ago
I've worked with many web developers who get mental blockage around DB tooling (granted, I have similar mental blockage when it comes to some operations stuff like K8s), and you can go far in life without really having to ask _that many questions_.
But going in and finding out how your SQL turns into data gotten from disk/written to disk is very helpful in just "knowing" what might be a decent idea. That and understanding your DB's locking system (or lack thereof...).
Figuring that stuff out can help reduce the surprise level when you can't seem to get a "simple COUNT" working quickly in Postgres or the like...
stevoski | 14 hours ago
A db table with only 10k rows? Even a full table scan should be extremely fast.
And with SQLite - which I assumed runs in-process, but even if not, surely is running on the same physical server? Faster still.
Of course, the magic phrase in my head is “create index”.
I hope Julia posts an update!
Edit: I highly suspect the “slow deletes” problem is a classic “n+1” problem suffered by many ORM users, until they come to understand more about the underlying db interactions.
spikk | 4 hours ago
src73 | 2 hours ago
I just ran it on my self-hosted MediaWiki installation and it took the search from seconds to milliseconds.