SQLite in production: when it beats Postgres

SQLite is the most deployed database in the world — it's in every iPhone, every Android device, every browser. But for years, "don't use SQLite in production" was standard advice. That advice aged out in 2025. Between WAL mode, Litestream for replication, and a new generation of edge database platforms built on it, there's a real production case. Here's when we'd reach for it.

What changed in 2025

Three things happened that made SQLite a serious production option:

• Litestream reached maturity. Litestream continuously replicates a SQLite database to S3-compatible object storage. A crashed instance restores in seconds, not minutes. The backup problem — the main reason people avoided SQLite in production — is solved.
• Edge database platforms built on SQLite.Cloudflare D1, Turso, and Fly.io's LiteFS all hit production maturity. SQLite at the edge, replicated globally, with microsecond read latency. A genuinely new operating mode.
• WAL mode became the default for serious use. Write-Ahead Logging allows concurrent readers alongside a writer, which removed the harshest limitation for read-heavy apps.

When SQLite beats Postgres

• CLIs and desktop apps.SQLite is the right database for any application that runs on a user's machine. One file, zero server, offline-native.
• Single-server web apps with mostly reads. A blog, a documentation site, a small SaaS with read-heavy traffic and a manageable write rate. SQLite with WAL mode handles thousands of concurrent reads with a single writer.
• Edge deployments.If you're deploying to Cloudflare Workers or Fly.io regions, SQLite (via D1 or Turso) gives you a database co-located with the compute. Postgres at the edge is awkward; SQLite was built for it.
• Embedded tools and internal apps. Admin panels, internal dashboards, low-traffic tools. Zero operational overhead versus a managed Postgres instance.

When Postgres is still correct

• Multiple concurrent writers. SQLite allows one writer at a time. If your app has concurrent write contention — many users writing simultaneously — Postgres wins.
• Large datasets with complex queries.Postgres's query planner is significantly more sophisticated at scale. If you have tens of millions of rows and complex JOINs, you want Postgres.
• Row-level security or LISTEN/NOTIFY.SQLite has no equivalent. If you're using RLS for multi-tenancy, stay on Postgres.
• When your team already knows Postgres.Operational familiarity matters. Don't switch databases to be interesting.

WAL mode: the setting that matters

Enable WAL mode immediately on any SQLite database you take seriously. Default journal mode uses exclusive file locks. WAL uses a separate write- ahead log file that allows concurrent readers while a write is in progress.

-- Run once at startup. Persists across connections.
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;   -- safe with WAL, faster than FULL
PRAGMA busy_timeout = 5000;    -- wait up to 5s before "database is locked"
PRAGMA foreign_keys = ON;      -- off by default, always enable

Litestream: the backup problem solved

Litestream runs as a sidecar or subprocess alongside your application. It tails SQLite's WAL file and continuously streams changes to an S3 bucket. Recovery is pulling the latest snapshot and replaying the WAL.

# litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - url: s3://your-bucket/app.db
        retention: 24h

SQLite in Go

Use github.com/mattn/go-sqlite3 (CGo, well-tested) or modernc.org/sqlite (pure Go, no CGo required — simpler cross-compilation). Both support database/sql.

import _ "modernc.org/sqlite"

db, err := sql.Open("sqlite", "file:app.db?_journal=WAL&_timeout=5000&_fk=on")
if err != nil {
  return err
}
// SQLite is not goroutine-safe for concurrent writers.
// Set max open connections to 1 for the write pool.
db.SetMaxOpenConns(1)

Use a separate read pool with higher concurrency alongside the single- connection write pool — most apps are read-heavy and this pattern keeps reads fast.