Updated: 2026-07-07

The myth: SQLite is for mobile apps and prototypes. The reality: thousands of production applications (from Tailscale to Fly.io, Expensify, and small SaaS) use it as their main DB. The SQLite + WAL mode + Litestream + (optionally) LiteFS combination enables scaling beyond where many teams assume they need PostgreSQL. This article covers how, when, and its real limits.

Key takeaways

  • WAL mode turns SQLite into an engine with concurrent reads that don’t block writes, the change that makes server-side use viable.

  • Litestream streams the WAL to S3 in near-realtime: durability comparable to Postgres without a separate DB server.

  • LiteFS (from Fly.io) replicates SQLite between multiple nodes with strong consistency via leasing.

  • SQLite beats Postgres for 1-process apps, simple queries, and up to ~100 GB of data — where zero DB latency matters.

  • SQLite doesn’t scale well with multiple writing processes simultaneously or massive analytical queries (that’s DuckDB territory).

Why server-side SQLite is viable

Traditional assumptions are obsolete:

  • "Doesn’t scale": false. SQLite handles thousands of writes/s and hundreds of thousands of reads/s on modest hardware.

  • "No concurrent writes": historically true, but WAL mode mitigates much.

  • "No replication": Litestream, LiteFS, rqlite solve.

  • "No backup tool": Litestream streams to any S3-compatible storage.

For 1-process / 1-instance apps, SQLite is spectacularly productive.

WAL mode: the fundamental change

Enable Write-Ahead Logging:

PRAGMA journal_mode = WAL;

Benefits:

  • Concurrent reads don’t block writes.

  • Concurrent writes still serialise but with less contention.

  • Better durability vs traditional journal_mode.

  • Incremental WAL checkpointing.

The difference between defaults and optimised config can be 3-10×:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;    -- durability/perf trade-off
PRAGMA cache_size = -64000;     -- 64 MB cache
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;     -- 5s before SQLITE_BUSY
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 268435456;   -- 256 MB memory-mapped

Litestream: Replication to S3

Litestream[1] streams the WAL to S3 (or any compatible) in near-realtime:

# /etc/litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: my-backups
        path: app.db
        region: eu-west-1
        access-key-id: ${AWS_ACCESS_KEY_ID}
        secret-access-key: ${AWS_SECRET_ACCESS_KEY}

For productive backup, Litestream makes SQLite nearly comparable to Postgres in durability without a separate DB server.

LiteFS: multi-node replication

LiteFS[2] (from Fly.io) replicates SQLite between nodes using FUSE filesystem:

  • Primary + replicas: writes to primary, reads on any node.

  • Strong consistency with distributed leasing.

  • Automated failover.

rqlite: distributed SQLite with Raft

rqlite[3] takes a different approach: SQLite with Raft for distributed consensus. For 3+ node clusters with automatic failover and strict replication, rqlite is more robust than LiteFS, though more complex to operate.

Real-world cases

  • Tailscale: coordination server on SQLite + Litestream.

  • Expensify: Bedrock (distributed wrapper) over SQLite, millions of users.

  • PocketBase: full BaaS built on SQLite.

  • Linear and Notion: SQLite in the client via WASM.

Common pattern: one application node + local SQLite + offsite Litestream.

When SQLite beats Postgres

SQLite is the best choice when:

  • 1-process app (single-instance VM, monolithic Lambda).

  • Simple to moderate queries, no cross-server joins.

  • Data volume up to ~100 GB comfortably, ~1 TB with discipline.

  • Zero latency to DB (same process).

  • Simple deployment without a separate DB server.

When SQLite isn’t enough

Honestly:

  • Multiple writing processes: write serialisation is the bottleneck.

  • Multiple app instances: can’t shard SQLite between servers without complexity.

  • Massive analytical queries: use DuckDB.

  • Postgres-specific extensions (serious pgvector, PostGIS).

  • DB-level roles/permissions: SQLite doesn’t have them.

Conclusion

SQLite is a serious option for single-instance production applications. With WAL mode, optimised pragmas, Litestream, and optionally LiteFS or rqlite for HA, it covers cases many teams assume require PostgreSQL. The operational advantage is huge: no DB server, no network, no permissions, no complex backup. For the vast majority of small-to-medium apps, starting with SQLite and growing to Postgres only if really needed is the correct pragmatic strategy.

Sources

  1. Litestream
  2. LiteFS
  3. rqlite