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 into territory where the default answer would be 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 where the default answer would be 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.

Frequently asked questions

How much data and traffic can SQLite handle as a main database?

On modest hardware it handles thousands of writes per second and hundreds of thousands of reads per second. In volume, it works comfortably up to ~100 GB and reaches ~1 TB with discipline. That holds as long as the app is a single process (single-instance VM or monolithic Lambda) with simple-to-moderate queries and no cross-server joins. The payoff is zero latency, because the database lives in the same process, and a deployment with no separate DB server.

How do I back up and replicate SQLite in production without losing durability?

Litestream streams the WAL to S3 or any compatible storage in near-realtime, configured in a litestream.yml that declares the database path, bucket and region. That makes durability nearly comparable to Postgres without a DB server. If you need more than one node, LiteFS (from Fly.io) replicates via a FUSE filesystem with writes to the primary and reads on any replica. rqlite uses Raft consensus for clusters of 3+ nodes, more robust but more complex to operate.

When is SQLite not enough and time to move to PostgreSQL?

When more than one process writes at the same time, because write serialisation becomes the bottleneck. When the app runs multiple instances, since you cannot shard SQLite between servers without complexity. When you need Postgres-specific extensions such as serious pgvector or PostGIS, or when you require DB-level roles and permissions, which SQLite does not have. For massive analytical queries the alternative is not Postgres but DuckDB.

Sources

  1. Litestream
  2. LiteFS
  3. rqlite