SQLite in Production: When It's the Right Choice, WAL Mode, Backups, and Litestream Replication

SQLite has a reputation problem. Most developers learned it as the toy database — the thing you use in mobile apps, unit tests, and prototypes before graduating to a “real” database like PostgreSQL or MySQL. The assumption is that SQLite doesn’t belong in production.

That assumption is wrong in a specific and interesting way. SQLite is the most widely deployed database engine in the world — it runs in every Android phone, every iPhone, every browser, every macOS and Windows installation, and billions of embedded devices. The SQLite authors estimate it handles more database queries per day than all other database engines combined.

The question isn’t “is SQLite production-ready?” It is. The question is: “is SQLite the right choice for your production workload?” This guide gives you the framework to answer that, plus the operational knowledge to run it well when the answer is yes.

What SQLite Actually Is

SQLite is not a client-server database. There’s no daemon, no network socket, no connection pool, no authentication layer. It’s a library that reads and writes a single file on disk. Your application links against the library and talks to the file directly.

This architecture has profound implications:

What you gain:

• Zero operational overhead — no server to install, configure, monitor, or update separately
• Zero network latency — queries run in-process, not over a socket
• Zero connection setup cost — no TCP handshake, no authentication round-trip
• Atomic transactions on a single file — the database is just a file you can copy
• Trivially portable — copy the file, you’ve moved the database

What you give up:

• Multiple processes writing simultaneously (concurrent reads are fine, concurrent writes serialize)
• Network access from multiple hosts (the file must be local or on a reliable network mount)
• Fine-grained access control (file permissions are your only ACL)
• Some advanced SQL features (no window functions with frame exclusion, no RIGHT JOIN historically, limited ALTER TABLE)

The architecture that makes SQLite wrong for a replicated multi-region service is the same architecture that makes it perfect for a single-server application that wants zero database operational overhead.

When SQLite Is the Right Choice

The Read-Heavy Single-Server Application

The majority of web applications follow the same traffic pattern: many more reads than writes, a single primary server, and database queries that take microseconds. For these applications, SQLite is not just adequate — it’s often faster than PostgreSQL because queries run in-process rather than over a socket.

Benchmarks consistently show SQLite outperforming PostgreSQL for read-heavy in-process workloads. The absence of network round-trips is simply faster than even a local socket.

Embedded Tools and CLIs

Any tool that needs persistent state without imposing database installation on users is a natural fit. CLIs, desktop apps, developer tools, agents that store conversation history or job queues — SQLite is the obvious choice.

Low-to-Medium Write Throughput Applications

SQLite’s serialized writes are only a bottleneck if you actually have high concurrent write throughput. Most applications don’t. A blog, a personal finance tool, a documentation site, an internal dashboard, a webhook processor — these rarely push SQLite’s write limits.

The real-world threshold where SQLite’s write serialization becomes a bottleneck is higher than most people think. With WAL mode (covered below), a modern SSD, and well-structured transactions, SQLite handles hundreds to thousands of writes per second on realistic workloads.

When You Want the Database as a File

SQLite’s single-file format is an operational superpower. Want to take a point-in-time backup? Copy the file (with proper locking — more on this below). Want to move the database to a different server? scp the file. Want to diff two versions of the database? Tools like sqldiff can do it. Want to query production data locally? Copy the file to your laptop.

The “One App, One Server” Pattern

Applications that run on a single VPS or dedicated server with no multi-region requirement are perfect SQLite candidates. K3s on Raspberry Pi, personal tools, small SaaS applications in early stages, internal tools for a team of 50 — all of these can be served well by SQLite with a fraction of the operational complexity of a Postgres deployment.

When SQLite Is the Wrong Choice

• Multiple application servers writing to the same database — SQLite requires the file to be local; shared network filesystems (NFS, EFS) work for reads but are unreliable for writes
• Write throughput above a few thousand transactions per second — at this point you need PostgreSQL’s parallel write capability
• Complex multi-statement transactions across unrelated data — SQLite handles this but the serialization becomes more painful
• Strict compliance requirements — some compliance frameworks require server-based databases for audit trail reasons
• Team unfamiliarity — operational knowledge of SQLite in production is less common than PostgreSQL; if your team doesn’t know it, factor in the learning curve

WAL Mode: The Most Important Setting

By default, SQLite uses a “delete” journal mode: before modifying a page, it copies the original to a rollback journal file. This means readers and writers block each other — a write blocks all reads until it completes.

Write-Ahead Logging (WAL) mode inverts this: changes are first written to a separate WAL file, then applied to the database file during a “checkpoint.” Readers see a consistent snapshot of the database at the point their read began. Writers and readers no longer block each other.

Enable WAL mode:

1

PRAGMA journal_mode = WAL;

This is a persistent setting — run it once and it sticks for the lifetime of the database file.

The Impact

With WAL mode, you get true read concurrency: any number of readers can proceed simultaneously while a write is in progress. This resolves the most common SQLite performance complaint for web applications.

Full WAL Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

-- Enable WAL (persistent)
PRAGMA journal_mode = WAL;

-- Increase the WAL file size before auto-checkpoint (default: 1000 pages)
-- Larger = fewer checkpoints, more consistent write throughput
PRAGMA wal_autocheckpoint = 10000;

-- Synchronous setting — NORMAL is safe with WAL and faster than FULL
-- Data is durable after each transaction; only risk is losing the last
-- transaction if power fails during the WAL write (OS crash, not app crash)
PRAGMA synchronous = NORMAL;

-- Increase the page cache (default: 2MB — absurdly small for production)
-- -131072 = 128MB (negative values = kibibytes)
PRAGMA cache_size = -131072;

-- Memory-mapped I/O — significant speedup for read-heavy workloads
-- 128MB mapped region
PRAGMA mmap_size = 134217728;

-- Busy timeout — wait up to 5s for a write lock instead of immediately failing
PRAGMA busy_timeout = 5000;

These pragmas need to be set on each connection open (except journal_mode which persists). In practice, set them in your connection initialization code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# Python with sqlite3
import sqlite3

def get_connection(db_path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(db_path, timeout=5.0)
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA cache_size = -131072")   # 128MB
    conn.execute("PRAGMA mmap_size = 134217728")  # 128MB
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("PRAGMA foreign_keys = ON")      # enforce FK constraints
    conn.row_factory = sqlite3.Row               # dict-like rows
    return conn

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// Go with database/sql + mattn/go-sqlite3
import (
    "database/sql"
    _ "github.com/mattn/go-sqlite3"
)

func openDB(path string) (*sql.DB, error) {
    dsn := path + "?_journal_mode=WAL&_synchronous=NORMAL&_cache_size=-131072&_busy_timeout=5000&_foreign_keys=ON"
    db, err := sql.Open("sqlite3", dsn)
    if err != nil {
        return nil, err
    }
    // SQLite only supports 1 writer at a time — limit the pool
    db.SetMaxOpenConns(1)
    return db, nil
}

The Connection Pool Gotcha

When using SQLite through a connection pool (most ORMs and database drivers use pools by default), you must configure the pool carefully:

• Writers: Set max connections to 1. Multiple goroutines/threads attempting concurrent writes will serialize at the SQLite level anyway, and a pool size > 1 adds overhead without benefit.
• Readers: Can use multiple connections. Consider a separate read-only connection pool.

The most common “SQLite is slow” complaint in Go and Java applications is traced to connection pool misconfiguration — multiple writer connections fighting over the write lock and triggering SQLITE_BUSY errors.

1
2
3
4
5
6

// Separate pools for reads and writes
writeDB, _ := sql.Open("sqlite3", path+"?_journal_mode=WAL&mode=rwc")
writeDB.SetMaxOpenConns(1)   // serialized writes

readDB, _ := sql.Open("sqlite3", path+"?_journal_mode=WAL&mode=ro")
readDB.SetMaxOpenConns(8)    // parallel reads

Schema Design for SQLite

SQLite is flexible with types but has some specifics worth knowing.

Type Affinity

SQLite uses type affinity rather than strict types. A column declared INTEGER will coerce values that look like integers but will also accept text. This is usually fine but can surprise developers from strict-typed databases.

For strict typing (SQLite 3.37+), use STRICT tables:

1
2
3
4
5
6

CREATE TABLE users (
  id        INTEGER PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,
  name      TEXT NOT NULL,
  created_at INTEGER NOT NULL  -- store as Unix timestamp (INTEGER, not DATETIME)
) STRICT;

Storing Dates and Times

SQLite has no native datetime type. The common patterns:

1
2
3
4
5
6
7
8

-- Unix timestamp (INTEGER) — fast, sortable, unambiguous
created_at INTEGER NOT NULL DEFAULT (unixepoch())

-- ISO 8601 text — human-readable, sortable, timezone-unambiguous if stored as UTC
created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))

-- Millisecond precision
created_at INTEGER NOT NULL DEFAULT (unixepoch('subsec') * 1000)

Unix timestamps are generally preferable — they’re smaller, faster to sort and compare, and unambiguous.

JSON Support

SQLite has solid JSON support via json_extract, json_object, json_array, and the JSON5 dialect:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  type    TEXT NOT NULL,
  data    TEXT NOT NULL DEFAULT '{}'   -- store JSON as TEXT
);

-- Query JSON fields
SELECT json_extract(data, '$.user_id') AS user_id
FROM events
WHERE type = 'login';

-- Filter by JSON field
SELECT * FROM events
WHERE json_extract(data, '$.amount') > 100;

-- Index a JSON field (expression index)
CREATE INDEX idx_events_user_id ON events(json_extract(data, '$.user_id'));

Backup Strategies

The Naive Approach (Dangerous)

1
2

# DO NOT DO THIS for a running SQLite database
cp app.db app.db.backup

Copying a live SQLite file without proper locking can produce a corrupt backup — pages mid-write may be copied in an inconsistent state.

The Right Approach: SQLite Online Backup API

SQLite’s built-in backup API is designed exactly for this: it takes a consistent snapshot of a running database without locking writers for the entire duration.

1
2
3
4
5

# sqlite3 CLI — safe online backup
sqlite3 app.db ".backup 'app.db.backup'"

# Or via the .dump command (SQL text format — portable but large)
sqlite3 app.db .dump > app.db.sql

In Python:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

import sqlite3

def backup_database(source_path: str, dest_path: str):
    """Online backup using SQLite's built-in API."""
    source = sqlite3.connect(source_path)
    dest = sqlite3.connect(dest_path)
    with dest:
        source.backup(dest, pages=100, progress=lambda status, remaining, total:
            print(f"Backup: {total - remaining}/{total} pages copied"))
    dest.close()
    source.close()

backup_database("app.db", "app.db.backup")

Scripted Backup to S3 / Object Storage

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

#!/usr/bin/env bash
# backup-sqlite.sh — nightly backup to S3-compatible storage
set -euo pipefail

DB_PATH="/opt/app/app.db"
BACKUP_DIR="/tmp/sqlite-backups"
S3_BUCKET="s3://my-backups/sqlite"
TIMESTAMP=$(date +%Y%m%d-%H%M%S)
BACKUP_FILE="$BACKUP_DIR/app-$TIMESTAMP.db"

mkdir -p "$BACKUP_DIR"

# Safe online backup
sqlite3 "$DB_PATH" ".backup '$BACKUP_FILE'"

# Compress
gzip "$BACKUP_FILE"

# Upload to S3
aws s3 cp "${BACKUP_FILE}.gz" "${S3_BUCKET}/app-${TIMESTAMP}.db.gz" \
  --storage-class STANDARD_IA

# Cleanup local backup
rm "${BACKUP_FILE}.gz"

# Prune old backups (keep 30 days)
aws s3 ls "${S3_BUCKET}/" \
  | awk '{print $4}' \
  | sort \
  | head -n -30 \
  | xargs -I{} aws s3 rm "${S3_BUCKET}/{}"

echo "Backup complete: app-${TIMESTAMP}.db.gz"

As a systemd timer:

1
2
3
4
5
6
7

# /etc/systemd/system/sqlite-backup.timer
[Timer]
OnCalendar=*-*-* 03:00:00
Persistent=true

[Install]
WantedBy=timers.target

Litestream: Continuous Replication

Nightly backups are fine until something goes wrong at 2:59 AM. Litestream is an open-source tool that streams SQLite WAL changes to S3-compatible object storage in near real-time, giving you continuous replication with sub-second RPO.

Think of it as the logical equivalent of Postgres streaming replication, but for SQLite, outputting to S3 rather than a standby server.

How Litestream Works

Litestream runs as a sidecar process alongside your application. It monitors the SQLite WAL file and periodically (every second by default) uploads WAL frames to S3. On failure, you restore from the most recent snapshot plus all subsequent WAL frames — typically recovering to within seconds of the crash.

Installation

1
2
3
4
5
6
7

# Linux binary
wget https://github.com/benbjohnson/litestream/releases/latest/download/litestream-v0.3.13-linux-amd64.tar.gz
tar -xzf litestream-*.tar.gz
sudo mv litestream /usr/local/bin/

# macOS
brew install litestream

Configuration

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

# /etc/litestream.yml
dbs:
  - path: /opt/app/app.db
    replicas:
      - url: s3://my-backups/litestream/app
        region: us-east-1
        sync-interval: 1s           # WAL sync frequency
        snapshot-interval: 24h      # Full snapshot frequency
        retention: 72h              # How long to keep old WAL frames
        retention-check-interval: 1h

      # Optional: second replica to a different destination
      - url: s3://my-backups-eu/litestream/app
        endpoint: https://fsn1.your-objectstorage.com  # Hetzner, Backblaze, etc.
        access-key-id: ${LITESTREAM_ACCESS_KEY_ID_EU}
        secret-access-key: ${LITESTREAM_SECRET_ACCESS_KEY_EU}
        sync-interval: 5s

Configure S3 credentials via environment variables:

1
2

export LITESTREAM_ACCESS_KEY_ID="your-access-key"
export LITESTREAM_SECRET_ACCESS_KEY="your-secret-key"

Running as a Systemd Service

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# /etc/systemd/system/litestream.service
[Unit]
Description=Litestream SQLite replication
After=network.target

[Service]
User=app
EnvironmentFile=/etc/litestream.env
ExecStartPre=/usr/local/bin/litestream restore -if-replica-exists /opt/app/app.db
ExecStart=/usr/local/bin/litestream replicate -config /etc/litestream.yml
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

The ExecStartPre line is key: when the service starts on a fresh server (disaster recovery scenario), it first attempts to restore the database from the replica before starting replication. If the replica doesn’t exist (first run), it does nothing.

Enable and start:

1
2

sudo systemctl enable --now litestream
sudo journalctl -u litestream -f

Restoring from Litestream

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# Restore to the latest point in time
litestream restore -config /etc/litestream.yml /opt/app/app.db

# Restore to a specific point in time
litestream restore -timestamp "2026-03-25T14:30:00Z" \
  -config /etc/litestream.yml /opt/app/app.db

# List available restore points
litestream snapshots -config /etc/litestream.yml /opt/app/app.db
litestream wal -config /etc/litestream.yml /opt/app/app.db

Litestream in Docker

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# docker-compose.yml
services:
  app:
    image: myapp:latest
    volumes:
      - app-data:/data
    depends_on:
      litestream:
        condition: service_healthy

  litestream:
    image: litestream/litestream:latest
    volumes:
      - app-data:/data
      - ./litestream.yml:/etc/litestream.yml
    environment:
      - LITESTREAM_ACCESS_KEY_ID=${LITESTREAM_ACCESS_KEY_ID}
      - LITESTREAM_SECRET_ACCESS_KEY=${LITESTREAM_SECRET_ACCESS_KEY}
    command: replicate -config /etc/litestream.yml
    healthcheck:
      test: ["CMD", "litestream", "databases", "-config", "/etc/litestream.yml"]
      interval: 10s
      timeout: 5s
      retries: 3

volumes:
  app-data:

Or run Litestream and your app in a single container using the exec subcommand — Litestream wraps your process and handles replication:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

# Dockerfile
FROM litestream/litestream:latest AS litestream

FROM python:3.12-slim
COPY --from=litestream /usr/local/bin/litestream /usr/local/bin/litestream
COPY . /app
WORKDIR /app
RUN pip install -r requirements.txt

COPY litestream.yml /etc/litestream.yml

# Litestream wraps the app process
CMD ["litestream", "replicate", "-exec", "python app.py", "-config", "/etc/litestream.yml"]

With -exec, Litestream first restores the database if a replica exists, starts your application, and handles replication for the lifetime of the process. When the app exits, Litestream exits too. This is the cleanest pattern for containerized deployments.

Performance Tuning

Batch Writes with Explicit Transactions

SQLite defaults to auto-commit: each statement is its own transaction, including an fsync. This is the single most common reason people see SQLite as slow.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

# Slow — 1000 fsyncs
for item in items:
    db.execute("INSERT INTO events (type, data) VALUES (?, ?)", (item.type, item.data))

# Fast — 1 fsync
with db:  # context manager handles BEGIN/COMMIT
    for item in items:
        db.execute("INSERT INTO events (type, data) VALUES (?, ?)", (item.type, item.data))

# Even faster for bulk inserts — executemany
with db:
    db.executemany(
        "INSERT INTO events (type, data) VALUES (?, ?)",
        [(item.type, item.data) for item in items]
    )

Batching 1000 individual inserts into a single transaction can speed up write throughput by 100–1000×.

ANALYZE and Query Planning

Like PostgreSQL, SQLite uses query statistics to plan queries. Refresh them periodically:

1
2
3
4
5
6

ANALYZE;       -- analyze all tables
ANALYZE users; -- analyze a specific table

-- Run ANALYZE automatically when the schema changes significantly
-- (SQLite 3.37+ has PRAGMA optimize which does this intelligently)
PRAGMA optimize;

Covering Indexes

A covering index includes all columns a query needs, avoiding a table lookup:

1
2
3
4
5
6

-- Query: SELECT email, name FROM users WHERE status = 'active' ORDER BY created_at
-- Covering index — all three columns referenced are in the index
CREATE INDEX idx_users_status_created_email_name
  ON users(status, created_at, email, name);

-- SQLite will use "Index Only Scan" — no row lookup needed

VACUUM and Auto-Vacuum

SQLite doesn’t reclaim space from deletes by default. Configure auto-vacuum:

1
2
3
4
5
6
7
8

-- Enable incremental auto-vacuum (reclaims pages as they're freed)
PRAGMA auto_vacuum = INCREMENTAL;

-- Run incremental vacuum (reclaim up to N pages)
PRAGMA incremental_vacuum(1000);

-- Full vacuum — defragments and reclaims all free space (slow, locks the DB)
VACUUM;

Monitoring and Introspection

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

-- Database size
SELECT page_count * page_size / 1024 / 1024.0 AS size_mb
FROM pragma_page_count(), pragma_page_size();

-- Table sizes (approximate)
SELECT name,
       SUM(payload) / 1024 / 1024.0 AS payload_mb
FROM dbstat
GROUP BY name
ORDER BY payload_mb DESC;

-- Index usage stats (requires compile-time option, available in recent SQLite)
SELECT * FROM sqlite_stat1;   -- after ANALYZE

-- Check WAL file size
SELECT * FROM pragma_wal_checkpoint;  -- shows WAL frames

-- Schema info
SELECT type, name, sql FROM sqlite_master ORDER BY type, name;

SQLite vs. PostgreSQL: The Decision Framework

Is your application read-heavy (>90% reads)?
  → Yes: SQLite is likely faster due to in-process access
  → No: Continue...

Do multiple application servers need to write to the database?
  → Yes: Use PostgreSQL
  → No: Continue...

Do you need > 1,000 concurrent write transactions/second sustained?
  → Yes: Use PostgreSQL
  → No: SQLite can likely handle it

Do you need streaming replication to a hot standby?
  → Yes: PostgreSQL (or SQLite + Litestream for near-real-time RPO)
  → No: SQLite with scheduled backups may be sufficient

Do you value operational simplicity above all else?
  → Yes: SQLite — no server, no connection management, file is the database
  → Depends on other requirements

The honest summary: SQLite is a legitimate production choice for the majority of web applications and tools that run on a single server. It’s faster than developers expect, simpler than any server database, and with Litestream it has a compelling disaster recovery story. The applications that genuinely need PostgreSQL are those with high write concurrency, multi-server architectures, or complex requirements like logical replication and row-level security — not “we expect to grow someday.”

Start with SQLite. Migrate to PostgreSQL when you have a concrete reason to, not when you think you might eventually need to.