SQLite for Production Use#

SQLite is not a toy database. It handles more read traffic than any other database engine in the world – every Android phone, iOS device, and major web browser runs SQLite. The question is whether your workload fits its concurrency model: single-writer, multiple-reader. If it does, SQLite eliminates an entire class of operational overhead with no server process, no network protocol, and no connection authentication.

WAL Mode#

Write-Ahead Logging (WAL) mode is the single most important configuration for production SQLite. In the default rollback journal mode, writers block readers and readers block writers. WAL removes this limitation.

PRAGMA journal_mode=WAL;

WAL creates two additional files: database.db-wal (the write-ahead log) and database.db-shm (shared memory). Writes append to the WAL instead of modifying the database file. Readers see a consistent snapshot as of when their transaction started. Periodically, the WAL is checkpointed back to the main database file.

PRAGMA wal_autocheckpoint=1000;       -- checkpoint at 1000 pages (default)
PRAGMA wal_checkpoint(TRUNCATE);      -- manual checkpoint, resets WAL to zero length

Caveat: WAL does not work over network filesystems (NFS, SMB). The shared memory file requires OS-level primitives that network filesystems do not support. Move the database to local storage.

Connection Pooling for Single-Writer#

SQLite allows only one writer at a time. The recommended pattern is a two-pool approach – one dedicated write connection serialized with an application-level lock, and multiple read connections:

import sqlite3
from contextlib import contextmanager
from threading import Lock

class SQLitePool:
    def __init__(self, db_path, read_pool_size=4):
        self._write_lock = Lock()
        self._write_conn = sqlite3.connect(db_path)
        self._write_conn.execute("PRAGMA journal_mode=WAL")
        self._write_conn.execute("PRAGMA busy_timeout=5000")
        self._write_conn.execute("PRAGMA synchronous=NORMAL")

        self._read_connections = []
        for _ in range(read_pool_size):
            conn = sqlite3.connect(db_path)
            conn.execute("PRAGMA query_only=ON")
            self._read_connections.append(conn)

    @contextmanager
    def write(self):
        with self._write_lock:
            try:
                yield self._write_conn
                self._write_conn.commit()
            except Exception:
                self._write_conn.rollback()
                raise

Always set a busy timeout so writes wait instead of immediately failing with SQLITE_BUSY:

PRAGMA busy_timeout=5000;  -- wait up to 5 seconds with exponential backoff

Performance Tuning#

Page size: default is 4096 bytes. Increase to 8192 or 16384 for databases with large rows. Must be set before creating the database or applied with VACUUM:

PRAGMA page_size=8192;
VACUUM;

Cache size: the page cache keeps frequently accessed pages in memory. Increase for read-heavy workloads:

PRAGMA cache_size=-65536;  -- 64MB (negative = kilobytes, positive = pages)

Memory-mapped I/O: maps the database file into process memory, bypassing user-space page cache. Best for read-heavy workloads on databases that fit in RAM:

PRAGMA mmap_size=268435456;  -- 256MB

Synchronous mode: in WAL mode, NORMAL provides full durability guarantees. Do not use OFF in production.

PRAGMA synchronous=NORMAL;

VACUUM and Maintenance#

SQLite does not reclaim space from deleted rows automatically. A maintenance script:

#!/bin/bash
DB_PATH="/var/lib/myapp/production.db"

sqlite3 "$DB_PATH" <<'EOF'
PRAGMA integrity_check;
ANALYZE;
PRAGMA wal_checkpoint(TRUNCATE);
PRAGMA optimize;
EOF

sqlite3 "$DB_PATH" "VACUUM;"

Full VACUUM requires temporary space equal to the database size and holds an exclusive lock. For large databases (10GB+), use incremental auto-vacuum instead:

PRAGMA auto_vacuum=INCREMENTAL;
PRAGMA incremental_vacuum(100);  -- reclaim up to 100 pages

Backup Strategies#

With WAL mode, you cannot simply copy the .db file. Use SQLite’s Online Backup API:

sqlite3 production.db ".backup /backups/production-$(date +%Y%m%d).db"

Litestream for Continuous Replication#

Litestream streams WAL changes to S3-compatible storage for near-real-time backup:

# /etc/litestream.yml
dbs:
  - path: /var/lib/myapp/production.db
    replicas:
      - type: s3
        bucket: my-backups
        path: production
        region: us-east-1
        retention: 168h
        sync-interval: 1s

# Run as a wrapper -- Litestream starts, then launches your app
litestream replicate -config /etc/litestream.yml -exec "myapp serve"

# Restore from S3
litestream restore -config /etc/litestream.yml /var/lib/myapp/production.db

On Kubernetes, run Litestream as an init container (restore on startup) and a sidecar (continuous replication). LiteFS from Fly.io provides an alternative using FUSE for multi-node read replicas.

Cloudflare D1: Managed SQLite#

D1 runs SQLite at Cloudflare’s edge with automatic replication and zero infrastructure management:

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const results = await env.DB.prepare(
      "SELECT * FROM articles WHERE category = ?"
    ).bind("databases").all();

    await env.DB.prepare(
      "INSERT INTO page_views (article_id, timestamp) VALUES (?, ?)"
    ).bind(articleId, Date.now()).run();

    return Response.json(results);
  }
};

D1 handles WAL mode, replication, and backups automatically. The tradeoff is vendor lock-in to Cloudflare Workers. Excellent for read-heavy edge workloads.

When SQLite Is the Wrong Choice#

Choose a client-server database when multiple application servers need simultaneous write access, you need fine-grained user authentication, write throughput exceeds thousands per second, or you need mature built-in replication with automatic failover. For everything else – single-server applications, edge deployments, embedded systems, CLI tools, read-heavy web applications – SQLite eliminates operational complexity that provides no value.