SQLite in Production: ANALYZE, WAL, and Backups with Litestream

⏱️ Lectura: 14 min

A full-text search query in SQLite was taking 5 seconds on a table of barely 4,000 rows. After running a single command (ANALYZE), the same query dropped to 0.05 seconds: a hundred times faster, without touching a single line of SQL.

📑 En este artículo

1. TL;DR
2. What running SQLite in production means and why it matters
3. How WAL mode works under the hood
   1. Diagram: reader and writer in parallel
4. ANALYZE: the command your SQLite in production was missing
5. Practical examples: from slow query to fast query
6. Getting started: SQLite in production step by step
   1. 1. Install the sqlite3 CLI
   2. 2. Enable WAL and set a busy_timeout
   3. 3. Run ANALYZE after loading real data
   4. 4. Schedule a backup
7. Backups: restic vs Litestream
8. The multiple-database pattern
9. Real-world use cases
10. Common mistakes and how to avoid them
11. SQLite in production vs Postgres: when to migrate
12. Going deeper: what ANALYZE does under the hood
13. Frequently Asked Questions
    1. Is it safe to use SQLite in production for a site with real traffic?
    2. How often should you run ANALYZE?
    3. Does WAL replace the need for backups?
    4. Restic or Litestream, which one should you start with?
    5. When does it make sense to split a SQLite database into multiple files?
    6. Does SQLite in production work for Django applications?
14. References

That finding, documented by developer Julia Evans while running a Django site on SQLite, sums up something many developers underestimate: running SQLite in production still means administering a real database, with its own locking rules, its own maintenance, and its own ways of failing. This guide covers what you need to know to operate it seriously: from WAL mode to backups that actually work when you need to restore.

TL;DR

• You’ll understand why ANALYZE can cut a query from 5 seconds to 0.05 without changing a single line of SQL.
• You’ll know how to enable WAL mode so reads and writes don’t block each other.
• You’ll be able to design a batched DELETE that doesn’t take down the other workers in your application.
• You’ll compare restic and Litestream to decide how to back up your SQLite database.
• You’ll learn the pattern of splitting a database into multiple .db files to isolate workloads.
• You’ll identify the signals that indicate it’s time to migrate from SQLite to Postgres.

What running SQLite in production means and why it matters

SQLite doesn’t need a separate server: the engine lives inside your application’s own process, and the data sits in a .db file on disk. That simplicity is why frameworks like Django ship with it configured by default, and why more and more small and medium sites choose it instead of spinning up a Postgres container.

The problem is that same simplicity hides the real complexity: there’s no database administrator watching over indexes, no separate process absorbing odd locks. When something runs slow or gets stuck, it’s entirely the responsibility of whoever wrote the application. Operating SQLite in production seriously means knowing at least three things: how to keep reads and writes from blocking each other, how to keep the statistics the query planner relies on up to date, and how to back up the file without corrupting it.

How WAL mode works under the hood

By default, SQLite uses rollback journaling mode: when someone writes, it takes an exclusive lock on the entire file, and readers have to wait. For a site with real traffic, that means one slow write blocks every simultaneous read.

Write-Ahead Logging (WAL) mode changes that dynamic. Instead of writing directly to the main file, changes are appended to the end of a separate -wal file. Readers keep reading from the main file (and from the WAL when needed) while the writer appends records, so reads almost never get blocked by an in-progress write. Periodically, a checkpoint flushes the WAL’s contents back into the main file.

-- Enable WAL once per database
PRAGMA journal_mode=WAL;
-- Verify it's active
PRAGMA journal_mode;
-- Should respond: wal

That first block is the hello world: two lines of SQL that change the locking behavior of the entire database. The second command confirms the change took effect, something worth checking after every deploy to a new environment, because journal_mode is stored in the .db file itself, not in the application’s configuration.

In Django, WAL is enabled by adding an option to DATABASES:

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "calendar.db",
        "OPTIONS": {
            "init_command": (
                "PRAGMA journal_mode=WAL; "
                "PRAGMA busy_timeout=5000;"
            ),
        },
    }
}

This second example is more realistic: besides WAL, it sets busy_timeout to 5,000 milliseconds. That value tells SQLite how long to wait before returning a database is locked error when another process is writing. It’s exactly the number that, as we’ll see below, can become a problem if a DELETE takes longer than those 5 seconds.

💭 Key point: WAL solves the blocking between readers and the writer, but there’s still only one writer at a time. SQLite never stops being single-writer, even with WAL enabled.

Diagram: reader and writer in parallel

sequenceDiagram
    participant W as Writer
    participant WAL as WAL File
    participant R as Reader
    participant DB as .db Database
    W->>WAL: appends changes
    R->>DB: reads committed data
    R->>WAL: reads recent changes
    Note over W,DB: writer and reader don't block each other
    WAL->>DB: checkpoint flushes changes

ANALYZE: the command your SQLite in production was missing

SQLite’s query planner decides, for each SELECT, which index to use and in what order to scan the tables. Without information about how many rows each table has and how the values are distributed, the planner guesses. And sometimes it guesses wrong, especially in queries with FTS5 (the full-text search module) that combine multiple conditions.

The ANALYZE command scans the tables and indexes and stores statistics (row counts, value distribution) in an internal table called sqlite_stat1. With that data, the planner stops guessing and picks the actual shortest path.

ANALYZE;
-- Verify it generated statistics
SELECT * FROM sqlite_stat1 LIMIT 5;

That final SELECT is how you confirm ANALYZE did its job: if the sqlite_stat1 table has rows, the planner now has real statistics to work with. If it’s empty, you’re still operating blind.

Practical examples: from slow query to fast query

The typical case (the same one Julia Evans describes on her blog) is a table with FTS5 search that starts misbehaving as it grows. The symptom isn’t an error: it’s that a query that used to take milliseconds starts taking full seconds, without anyone touching the code.

import sqlite3

conn = sqlite3.connect("calendar.db")
cur = conn.cursor()

cur.execute("""
    EXPLAIN QUERY PLAN
    SELECT eventos.titulo
    FROM eventos_fts
    JOIN eventos ON eventos.id = eventos_fts.rowid
    WHERE eventos_fts MATCH ?
""", ("reunion",))

for fila in cur.fetchall():
    print(fila)

Before running ANALYZE, the result of EXPLAIN QUERY PLAN usually shows a full scan of the auxiliary table, a sign that the planner has nothing to guide it. After running ANALYZE on the same database, the plan switches to directly using the FTS5 index, and response time drops by the same order of magnitude Julia Evans reported: from seconds to hundredths of a second.

💡 Tip: Since SQLite 3.18, PRAGMA optimize; exists, a lightweight version of ANALYZE meant to run when each connection closes. Calling it in your pool’s close() keeps statistics from going stale without needing a separate cron job.

Getting started: SQLite in production step by step

These are the minimum steps to get a SQLite database into production in reasonable shape, with the CLI installed on any of the three major operating systems.

1. Install the sqlite3 CLI

# Windows (with winget)
winget install SQLite.SQLite

# macOS (with Homebrew)
brew install sqlite3

# Linux (Debian/Ubuntu)
sudo apt install sqlite3

2. Enable WAL and set a busy_timeout

sqlite3 calendar.db "PRAGMA journal_mode=WAL;"
sqlite3 calendar.db "PRAGMA busy_timeout=5000;"

3. Run ANALYZE after loading real data

sqlite3 calendar.db "ANALYZE;"

4. Schedule a backup

This is covered in the next section, but the minimum step is having some mechanism running before the database grows, not after.

Backups: restic vs Litestream

Running cp on the .db file while the application is writing is the fastest way to end up with a corrupted backup. SQLite offers VACUUM INTO as a safe way to generate a consistent copy without stopping the process:

sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar_backup.db'"
gzip /tmp/calendar_backup.db

From there, there are two common paths for uploading that copy to external storage: periodic snapshots with restic, or continuous replication with Litestream.

Tool	When to use it	Advantage	Limitation
restic	Periodic backups (every hour or every day) of a copy generated with VACUUM INTO	Deduplication and encryption ready to use; snapshots that are easy to list and prune	Each run processes the entire compressed file, and can run out of memory on large databases
Litestream	Near real-time continuous replication, minimizing data loss in the event of a crash	Uploads only the WAL changes, not the entire database, on each cycle	Requires a process running at all times alongside the application

Litestream is configured with a YAML file and a single command:

litestream replicate -config litestream.yml

Inside litestream.yml, it’s worth setting how much history to retain, for example retention: 400h to keep just over two and a half weeks of incremental changes.

flowchart TD
    A["Django App"] --> B["local calendar.db"]
    B --> C["litestream replicate"]
    C --> D[("S3 Bucket")]
    subgraph Continuous Backup
    C
    D
    end

⚠️ Watch out: A backup that’s never been tested for restoration isn’t a backup, it’s a promise. Schedule at least one test restore before trusting your chosen mechanism.

The multiple-database pattern

A little-known technique for easing contention in SQLite is splitting data across several .db files instead of putting everything into one. If two groups of tables don’t need joint transactions, separating them into distinct databases reduces how long one writer blocks everyone else.

It’s the same pattern used by the Mess With DNS project, which has run on SQLite since 2022, splitting its tables across three separate files instead of one. Four years later, the database is still SQLite, and the migration away from Postgres is described as a decision that paid off.

Real-world use cases

SQLite in production works well for: sites with low to moderate concurrent write volume, internal team tools, read-heavy cached APIs, and personal projects that need to last for years with minimal maintenance. The Mess With DNS case (four years in production without going back to Postgres) shows that, with the right practices, SQLite can handle real, sustained loads.

Common mistakes and how to avoid them

• Mass DELETE that takes down workers: deleting thousands of rows at once can take longer than the configured busy_timeout (for example, 5 seconds). If another process tries to write in the meantime, it exhausts the timeout, fails, and in some environments that failure causes the entire process to restart. The fix is to delete in small batches, using a LIMIT and a loop, so each individual transaction finishes quickly.
• Backups that get stuck locked: a backup process that dies without releasing its lock (for example, from running out of memory) can leave the restic repository marked as locked for the next run. Before each backup, it’s worth forcing an unlock if the previous process didn’t finish cleanly.
• Trusting the ORM without checking the query plan: using the Django ORM (or any ORM) without ever reviewing EXPLAIN QUERY PLAN works fine while the table is small. The problem shows up all at once when it grows, as happened with the 4,000-row FTS5 query.
• Forgetting that WAL needs checkpointing: if the application never closes connections or automatic checkpointing is disabled, the -wal file can grow without limit. Running PRAGMA wal_checkpoint(TRUNCATE); periodically prevents that runaway growth.

SQLite in production vs Postgres: when to migrate

Scenario	SQLite in production	Postgres
Concurrent writes from multiple processes	Limited: only one writer at a time, even with WAL	Multiple simultaneous writers with no global lock
Operation and deployment	One file, no separate server to administer	Requires a running service, users, and connections
Performance maintenance	Manual: ANALYZE, PRAGMA optimize, checkpoints	Autovacuum and planner with automatic statistics
Data volume	Comfortable up to tens of thousands of rows per table in most cases	Scales to much larger volumes without redesign

The clearest signal for migrating from SQLite in production to Postgres isn’t the size of the data, it’s the number of processes that need to write at the same time. If your application runs with a single worker, SQLite is probably enough. If you need several workers writing in parallel constantly, the single-writer model becomes the bottleneck.

Going deeper: what ANALYZE does under the hood

The sqlite_stat1 table that ANALYZE generates stores, for each index, the approximate row count and how many distinct rows exist for each prefix of indexed columns. With those numbers, the optimizer can estimate how many rows each branch of a JOIN will return before executing it, and choose the order that produces the fewest intermediate rows.

Without those statistics, SQLite uses generic heuristics that assume a uniform data distribution. For simple indexes, that’s usually enough. For FTS5 queries that combine a MATCH with a JOIN, the generic heuristic can lead the planner to scan entire auxiliary structures instead of using the inverted index, which is exactly the pattern that explains a jump from 5 seconds to 0.05.

Another useful advanced feature is mmap_size, which lets SQLite map the database file directly into memory instead of going through the traditional I/O system, reducing data copies during heavy reads:

PRAGMA mmap_size = 268435456; -- 256 MB
-- Confirm the active value
PRAGMA mmap_size;

📖 Summary on Telegram: View summary

Your next step: load your development database with realistic data, run EXPLAIN QUERY PLAN on your slowest query, run ANALYZE, and compare the plan before and after.

Frequently Asked Questions

Is it safe to use SQLite in production for a site with real traffic?

Yes, as long as the concurrent write load is moderate. WAL mode, a reasonable busy_timeout, and tested backups cover most cases for small and medium sites.

How often should you run ANALYZE?

After large changes in data volume, or periodically with PRAGMA optimize; on every connection close, which applies a lightweight version of ANALYZE without needing a dedicated cron job.

Does WAL replace the need for backups?

No. WAL solves blocking between readers and the writer, but it doesn’t protect against a corrupted disk, an accidental deletion, or losing the entire server. Backups are still mandatory.

Restic or Litestream, which one should you start with?

If the database is small and a daily backup is enough, restic is simpler to set up. If your application can’t afford to lose more than a few minutes of data in a crash, Litestream is the safer option.

When does it make sense to split a SQLite database into multiple files?

When different groups of tables don’t need joint transactions, and one of those workloads generates many writes that would unnecessarily block the rest.

Does SQLite in production work for Django applications?

Yes, it’s Django’s default engine and works well in production for sites with moderate traffic, as long as you configure WAL, busy_timeout, and a backup plan from the start.

References

• Learning a few things about running SQLite: the original article by Julia Evans documenting the ANALYZE finding and backup strategies.
• SQLite: ANALYZE: official documentation for the command and the sqlite_stat1 table.
• SQLite: Write-Ahead Logging: official documentation for WAL mode and its locking model.
• Litestream: official site of the continuous replication tool for SQLite.
• restic: official site of the backup tool with deduplication and encryption.

📱 Like this content? Follow @programacion on Telegram for daily tech content in Spanish: quick summaries, fresh content every day. @programacion

Imagen destacada: Foto de Mohammad Rahmani en Unsplash