SQLite in Production: When It Works, Scaling Limits, and Litestream Replication

Editorial Team · · 3 min read

SQLite Gets a Bad Rap

Mention SQLite in a conversation about production databases and you'll get raised eyebrows. "That's just for mobile apps." "It can't handle concurrent writes." "You'll outgrow it in a week."

Most of that is wrong, or at least outdated. SQLite handles over a trillion dollars of financial transactions daily (it's embedded in every smartphone, every browser, and most point-of-sale systems). The SQLite website itself runs on SQLite — it handles 400,000+ HTTP requests per day on a $5/month VPS.

The question isn't whether SQLite is "production-ready." It's whether your specific use case fits its concurrency model.

Where SQLite Excels

SQLite is the right choice when:

  • Your application runs on a single server (no horizontal scaling needed)
  • Read-heavy workloads with modest write throughput (under ~1,000 writes per second)
  • You want zero operational overhead — no database process to manage, no connection strings, no network latency
  • The dataset fits on one machine's disk (SQLite handles databases up to 281TB, though practically you'd want to stay under a few hundred GB)

Some real production uses that work great with SQLite: personal SaaS apps, internal tools, content management systems, embedded analytics, IoT data collection, static site generators with dynamic features.

The Concurrency Story: WAL Mode

In its default journal mode, SQLite allows only one writer at a time, and writers block readers. That's the source of the "SQLite can't handle concurrency" reputation.

WAL (Write-Ahead Logging) mode changes this significantly:

PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;      -- Wait up to 5s for locks
PRAGMA synchronous=NORMAL;     -- Good balance of safety and speed
PRAGMA cache_size=-64000;      -- 64MB cache
PRAGMA foreign_keys=ON;
PRAGMA temp_store=memory;

With WAL mode enabled:

  • Multiple readers can run concurrently with a single writer
  • Writers don't block readers (readers see the pre-write state until the write commits)
  • Write throughput improves substantially — I've measured 5,000-10,000 simple inserts per second on modern SSDs

The limitation that remains: still only one writer at a time. If your application needs multiple concurrent write transactions, you'll hit lock contention. For a web app where writes are typically short (insert a row, update a status), the single-writer model is fine. For bulk data processing with overlapping write batches, it's not.

Litestream: Replication Without the Hassle

The biggest concern with SQLite in production used to be backups and disaster recovery. Litestream solves this by continuously replicating your SQLite database to S3 (or any S3-compatible storage like MinIO, Backblaze B2, or Cloudflare R2).

# /etc/litestream.yml
dbs:
  - path: /data/app.db
    replicas:
      - type: s3
        bucket: my-backups
        path: app.db
        endpoint: https://s3.amazonaws.com
        retention: 72h
        sync-interval: 1s

Litestream works by tailing the WAL file and shipping changes to object storage. It's not a full replication solution — you can't run read replicas — but it gives you:

  • Point-in-time recovery (down to ~1 second granularity)
  • Off-site backups with minimal latency
  • Easy database restore: litestream restore -o /data/app.db s3://my-backups/app.db

Ben Johnson, Litestream's creator, has said that his goal is to make SQLite a first-class option for production web applications. I think he's largely succeeded.

Performance Optimization

A few things that make a big difference:

Batch your writes. Individual inserts create individual transactions, each with an fsync. Wrapping 1,000 inserts in a single BEGIN...COMMIT can be 100x faster:

# Slow: 1,000 implicit transactions
for row in data:
    cursor.execute("INSERT INTO events VALUES (?, ?, ?)", row)

# Fast: 1 transaction
cursor.execute("BEGIN")
cursor.executemany("INSERT INTO events VALUES (?, ?, ?)", data)
cursor.execute("COMMIT")

Use prepared statements. SQLite caches compiled queries, so reusing the same SQL text is efficient. Parameterize always.

Appropriate indexing. Same rules as any database — index columns you filter and sort on, use composite indexes wisely, don't over-index write-heavy tables.

When to Move Away from SQLite

Honestly? Later than you think. But here are the real triggers:

  • You need to scale horizontally across multiple servers
  • Write concurrency demands exceed what a single writer can handle
  • You need features SQLite doesn't have: LISTEN/NOTIFY, JSON path queries beyond basic (PostgreSQL's JSONB is far richer), stored procedures, row-level security
  • Your dataset exceeds what fits comfortably on a single server's disk

For everything else, SQLite is a perfectly legitimate choice. And the migration path to PostgreSQL, when you need it, is straightforward — the SQL dialects are similar enough that most queries work unchanged.