Lessons Learned: Running SQLite in Production with Django
Learning a few things about running SQLite
I recently used SQLite for a Django site and discovered that even small databases require careful management. I learned that running ANALYZE is crucial for query performance, while cleanup operations can cause timeouts and crashes. I also explored backup strategies using restic and litestream, and considered splitting tables across multiple database files. Despite its simplicity, SQLite demands respect as a real database.
SQLite is still a database, databases are complicated, and I do not know a lot about operating databases.
- 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!
- 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.
- stevoski
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.
- rollulus
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.
- 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.