Learning a few things about running SQLite

simonw

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

datadrivenangel

"Maybe one day I’ll learn to read a query plan." Query plans aren't that hard to read! [0] 0 - https://xkcd.com/2501/

striking

> 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 (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!

masklinn

> and presumably other things? 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

What does he mean by "I do usually try to monitor them with a dead man’s switch.", when talking about backups?

ryan42

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.

andrewaylett

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

Kalanos

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.

noxer

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

pianopatrick

If you are worried about the cleanup operation having python code running in it, maybe you could use the SQLite CLI to run that operation instead.

arlattimore

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?

ktzar

Is it me or this is one of the worst and knowingly less informed articles that has hit HN in a while?

wwind123

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.

luciana1u

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

jackhalford

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.

dksmart

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

dangantiban

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.