New series: what assistive tech really hears on your checkout page. Start with part one →
Shipwright
Home
Databases Sep 15, 2026 2 min read 489 views

SQLite in Production: Where It Shines

Priya Raman

Priya Raman

Database and performance engineer. Priya reads…

Share

Write-ahead logging, backups, and the workloads where one file is enough. Databases are usually the last place teams look when an application slows down, and the first place the problem turns out to be. This post is a practical walk through how we find and fix those problems.

Cover illustration for the article SQLite in Production: Where It Shines
Figure 1. Write-ahead logging, backups, and the workloads where one file is enough.

Look at the plan first

Every slow query investigation starts with EXPLAIN ANALYZE. It shows what the planner expected, what actually happened, and where the two diverged, which is almost always where the fix is.

  • Read the query plan before adding an index.
  • Backfill in batches, never in one transaction.
  • Drop indexes nobody uses.
  • Test migrations against a copy of production data.

The migration we ran

Schema changes on a busy table need care. We add the new column, backfill in batches, switch reads, switch writes, and only then drop the old column, with a deploy between each step.

Programs must be written for people to read, and only incidentally for machines to execute.

Harold Abelson, Structure and Interpretation of Computer Programs, 1985

Quick takeaway

The query plan is the source of truth.

Keeping it fast

🔎

Explain first

Read the plan before changing anything.

🪜

Expand and contract

Migrate in small, reversible steps.

🧹

Prune indexes

Remove the ones nobody reads.

Indexes are not free. Each one slows writes and takes memory, so we review unused indexes every quarter and drop the ones the statistics say nobody reads.

If you try this on your own project, start with one page or one service rather than the whole codebase. A small experiment gives you real numbers to argue with, and it is easy to roll back if the idea does not fit.

We have been running this approach for several months now. The initial setup took about a day, and the ongoing cost is close to zero once the team knows where to look.

None of this is new, and that is the point. The techniques that last are usually the boring ones that are documented well and supported by the platform itself.

As always, the examples are simplified to fit on a page. The production version has more error handling and logging, but the shape of the code is the same.

Questions, corrections, and war stories are welcome in the comments. Several of our best follow-up posts started as a reader pointing out something we had missed.

Wrapping up

As always, the examples are simplified to fit on a page. The production version has more error handling and logging, but the shape of the code is the same.

Priya Raman

Priya Raman

Author

Database and performance engineer. Priya reads query plans for fun and believes every slow page has a story worth measuring.

Related Posts

Comments 7

Sam Whitfield Member

Would love a follow-up on how you test this in CI.

Lena Novak

We run this in production at a fairly large scale and can confirm the caveats are real.

Chloe Martin

Solid write-up. The warning about caching bit us hard last year.

Priya Raman Author

Thanks for catching that, it is fixed now.

Mara Okafor Member

The renaming spree is a rite of passage.

Kofi Mensah

Any thoughts on how this interacts with server-side rendering?

Priya Raman Author

+1, this matches what we saw after the migration.

Leave a comment

Your email will not be published. Sign in for a faster experience.

Supports **bold**, *italic*, and `code`.