SQLite in production in 2026: WAL settings, Litestream backups, when to move on

In 2026 SQLite is no longer “the database for phones and prototypes”. Plenty of small and medium web apps run on one file on one server: no database process, no port, no password. This article covers which jobs it fits, what to configure, how to back it up, and when to move to PostgreSQL.

Tested on Ubuntu 24.04, SQLite 3.45, Python 3.12, Litestream 0.5.

When it is the right choice

SQLite’s own docs say any site with fewer than 100K hits a day works fine on SQLite, call that a conservative estimate, and note it has been shown to handle ten times that. We decide on three conditions:

1. The app runs on one machine. The database is a file on that machine’s disk, not across a network
2. Mostly reads: pages, read APIs, dashboards
3. Tens to a few hundred writes a second, and the writes can take turns

If all three hold, you get no database service to run, no connection pool, no network hop per query (a query answered from pages in memory takes microseconds), and a backup that is a file copy.

The settings that matter

SQLite’s defaults suit desktop programs, not a web server. Four settings go in every time:

pragma	value	why
journal_mode	WAL	readers and the writer do not block each other; set once, sticks to the file
synchronous	NORMAL	fsync only at checkpoints, not every commit; much faster
busy_timeout	5000 (ms)	if another writer holds the lock, wait instead of failing at once
foreign_keys	ON	default is OFF and it is per connection, so set it on every open

WAL (write-ahead log) is the heart of it. Instead of overwriting the main file, SQLite appends changed pages to a -wal file. Readers see their own snapshot without waiting for the writer, and the writer does not wait for readers. The price: one writing transaction at a time, because there is one WAL file.

Why one writer at a time is fine

Think in time, not users. A single-row insert on an SSD takes under 1 ms. If every transaction holds the lock for at most 2 ms, you can serialise 500 writes a second and nobody waits more than 2 ms. What makes SQLite “slow” is never the lock; it is a transaction that holds the lock while doing something else, like calling an external API.

The most common trap is SQLITE_BUSY even with busy_timeout set. It happens when a transaction starts with a SELECT (a read snapshot) and then writes: SQLite refuses to upgrade the lock without waiting, because waiting could make the values already read stale. The fix: every transaction that will write starts with BEGIN IMMEDIATE, so the write lock is taken up front and busy_timeout works. (Rails 8 made IMMEDIATE the default for this reason.)

A writing transaction is always BEGIN IMMEDIATE, and nothing that is not SQL happens inside it.

Python example: all pragmas, writes through one channel

Uses sqlite3 from the standard library, nothing to install:

import sqlite3, threading

DB = "app.db"
_write_lock = threading.Lock()

def connect() -> sqlite3.Connection:
    con = sqlite3.connect(DB, timeout=5, isolation_level=None, check_same_thread=False)
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA synchronous=NORMAL")
    con.execute("PRAGMA busy_timeout=5000")
    con.execute("PRAGMA foreign_keys=ON")
    return con

def write(con, fn):
    """Run fn(con) inside one writing transaction, holding the lock as briefly as possible."""
    with _write_lock:                       # queue inside the process; never fight SQLite's lock
        con.execute("BEGIN IMMEDIATE")       # take the write lock now
        try:
            r = fn(con)
            con.execute("COMMIT")
            return r
        except Exception:
            con.execute("ROLLBACK")
            raise

# usage
con = connect()
write(con, lambda c: c.execute(
    "INSERT INTO events(ts, kind, payload) VALUES (?,?,?)", (1760000000, "login", "{}")))

isolation_level=None turns off Python’s implicit transactions so we control BEGIN/COMMIT ourselves. _write_lock queues writes in memory inside the process instead of contending for the file lock. Several worker processes (gunicorn) are still correct, because SQLite locks across processes; you just see some waiting. On Node, better-sqlite3 is synchronous, so writes serialise naturally; set the same pragmas and use db.transaction(fn).immediate().

Worked example: 50 writes a second, how fast does the file grow?

Say you store events of 200 bytes each (about 300 bytes per row once indexes are counted), at 50 rows a second all day.

	value
Rows per day	50 × 86,400 = 4,320,000
Raw data per day	4.32M × 200 B ≈ 864 MB
With indexes and page overhead	≈ 1.3 GB per day
Per year	≈ 470 GB

Two lessons. SQLite copes with that rate (a file can grow to 17.5 TB per the docs). But the problem is the VPS disk, not SQLite: at 50 writes a second around the clock you need a retention plan whatever database you use. Most apps average under 5 writes a second, about 130 MB a day.

On the WAL side: the default auto-checkpoint fires at 1,000 pages, about 4 MB. A single-row insert touches about 3 pages (table plus two indexes), 12 KB per transaction.

• One row per transaction, 50 a second → 600 KB/s → a checkpoint every ~7 seconds
• 50 rows batched into one transaction a second → repeated pages count once, ~20 KB/s → a checkpoint every ~3 minutes

Batching cuts both lock acquisitions and WAL volume. After a checkpoint the -wal file is overwritten in place, not shrunk; set PRAGMA journal_size_limit to truncate it.

Backups

Option 1: VACUUM INTO writes a consistent copy as a single transaction while the app keeps writing, and compacts it on the way. The target file must not exist yet.

# on the server, nightly cron
sqlite3 app.db "VACUUM INTO '/backup/app-$(date +%F).db'"

Option 2: the online backup API (sqlite3_backup_*, or .backup in the CLI) copies a few pages at a time with short locks, but restarts if another process writes mid-copy. Good for databases that rarely change. Never cp app.db while the app runs: the copy will not match the -wal file.

Option 3: Litestream is what makes SQLite genuinely production-ready. A separate process (a sidecar) tails your WAL and ships it to object storage (S3, Cloudflare R2, DigitalOcean Spaces, Backblaze B2, GCS, Azure, SFTP, or another folder), with no app changes. Version 0.5 (2025) moved to the LTX format, compacted in 30-second / 5-minute / 1-hour tiers, so you can restore to any point in time from a dozen or so files, and added a VFS for read replicas served straight from storage (still young; check the current docs before relying on it).

# /etc/litestream.yml
dbs:
  - path: /srv/app/app.db
    replicas:
      - url: s3://my-bucket/app

# bring the data back on a fresh machine
litestream restore -o app.db s3://my-bucket/app

Limits: Litestream 0.5 supports one replica destination per database and cannot restore 0.3-era files, so an upgrade means a fresh snapshot. You can lose the gap between the last transaction and the next sync (one second by default).

Compared with PostgreSQL on a 2 GB VPS

	SQLite	PostgreSQL 16
Memory	lives in the app process; default cache ~2 MB per connection, the rest rides the OS page cache	separate process; shared_buffers defaults to 128 MB, recommended 25% of RAM ≈ 512 MB, plus ~5-10 MB per connection
Ops	no service, no port, no roles	install, pg_hba, major-version upgrades (pg_upgrade), vacuum tuning
Backup	file copy / VACUUM INTO / Litestream	pg_dump, pg_basebackup, WAL archiving
Minimum cost	rides on the existing server	several hundred MB of RAM on the same box, or the smallest managed instance at roughly $15/month (check current pricing)
Writers on several machines	no	yes
Extensions	FTS5 and JSON built in, little else	PostGIS, pgvector, TimescaleDB and more

On a 2 GB VPS shared with the app, a proxy and a cache (the small-server article covers the layout), dropping Postgres hands back 400-600 MB of RAM and a whole category of maintenance. If your system needs Redis in front of the database, in-process SQLite reads are often fast enough that part of that cache goes away.

Where it breaks

• Several app servers writing one file. Not possible: WAL needs a single host, no network filesystem
• Heavy write contention. Many processes writing constantly with long transactions make busy_timeout expiries routine
• Very large databases. Hundreds of GB still open, but VACUUM, backup and restore scale with size, and once pages no longer fit in RAM, queries that miss an index crawl
• Long-lived readers. A connection left in a read transaction blocks checkpoints and the -wal file grows without bound. Close every cursor
• Needing extensions: serious geospatial, large vector search, hypertable time series
• Loose typing. SQLite accepts wrong-typed values unless the table is STRICT (since 3.37). Use STRICT from the first table

The migration path when you outgrow it

1. Neutral SQL from day one: INTEGER PRIMARY KEY, time as ISO-8601 TEXT or epoch INTEGER, booleans as 0/1, no SQLite-only functions
2. Move data with pgloader, which reads SQLite, creates tables, maps types and copies rows; or export CSV and COPY for a small schema
3. Cut over with short downtime: stop writes → migrate → change the connection string → start. A multi-GB database moves in minutes; no dual-write needed
4. Audit queries that lean on SQLite behaviour: implicit casts, GROUP BY with columns outside the aggregate (Postgres rejects it)

Start on SQLite with STRICT tables and neutral SQL, and the day you move, the hard part is checking queries, not moving data.

Sources

• Write-Ahead Logging — sqlite.org
• Appropriate Uses For SQLite — sqlite.org
• Implementation Limits For SQLite — sqlite.org
• VACUUM (INTO) — sqlite.org and Online Backup API
• Litestream v0.5.0 is Here — fly.io and litestream.io guides
• What to do about SQLITE_BUSY errors despite setting a timeout — Bert Hubert, via Simon Willison