Summary

Julia Evans shares practical lessons learned while running SQLite as a production database for a Django site, her fourth SQLite-backed project. She found that SQLite's simplicity is real, but it still requires operational knowledge she didn't have. The post covers four concrete discoveries: running ANALYZE to fix a 100x query slowdown, handling write contention during bulk deletes, backup strategies using restic and Litestream, and the option to split tables across multiple database files. She reflects that even after using SQLite since 2022, she only just discovered ANALYZE, and expects to keep learning basic things incrementally.

Key Insight

SQLite's simplicity lowers the barrier to entry, but real production use still surfaces the same operational complexities — query planning, write contention, backups — that make databases hard in general.

Spicy Quotes (click to share)

• 5

  SQLite is still a database, databases are complicated, and I do not know a lot about operating databases.

• 3

  Immediately the problem query went from taking 5 seconds to like 0.05 seconds (or some other number small enough that I didn't care to investigate further).

• 4

  Maybe one day I'll learn to read a query plan.

• 4

  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.

• 3

  It's always kind of fun to see how long it takes me to learn sort of basic things about the technologies I'm using.

• 4

  I think I used SQLite for a web project for the first time in 2022 and I only learned that ANALYZE existed today!

• 3

  Mess with DNS has been running on SQLite for 4 years now (since 2022) and it's been great, I think the move from Postgres was a great choice for that project.

Tone

curious, self-deprecating, pragmatic