SQLite in Production: When It's Actually the Right Choice

Overview

I've seen SQLite dismissed in architecture discussions so many times that I stopped bringing it up. Then I built two projects on it that handled more traffic than a lot of Postgres deployments I've worked on, and started telling people: it's not just for mobile apps and prototypes.

But it's also not a drop-in replacement for a database server. The interesting question is where the line is.

The case against, briefly

SQLite has one writer at a time. When a write is in progress, other writers wait. Readers can continue if you've enabled WAL mode, but writers serialize. For a write-heavy workload, this is a real bottleneck.

It's also a single file. No network layer, no replication, no failover. Backup is copying a file. Scaling is vertical. If the machine dies, the database dies with it unless you've set up something clever.

These are real constraints. They just don't apply to as many projects as people assume.

Where SQLite is genuinely good

The read-heavy case is where it shines most. A service that reads 1000x more than it writes will often be faster on SQLite than on Postgres, because there's no network round trip and no connection pool to manage. Queries that took 2ms over a socket take 50 microseconds in-process.

The settings that matter

SQLite's defaults are tuned for maximum compatibility, not performance. Three settings change everything:

WAL mode

PRAGMA journal_mode = WAL;

Write-Ahead Logging changes the locking model. Readers no longer block writers, and writers no longer block readers. The database gets a -wal file alongside it, and checkpointing moves data from WAL back to the main file periodically.

Without WAL, a single writer locks the entire database, and readers block. With WAL, they don't. This one setting is the difference between "SQLite is too slow" and "SQLite is fine."

Busy timeout

PRAGMA busy_timeout = 5000;

Without this, a write that finds the database locked returns SQLITE_BUSY immediately. With it, SQLite waits up to 5 seconds for the lock. For a web app where writes are short, this eliminates almost every busy error.

Synchronous mode

PRAGMA synchronous = NORMAL;

In WAL mode, NORMAL is safe and much faster than the default FULL. You risk losing the last few transactions on a power failure, but you don't risk corruption. For most applications, that's the right trade.

The complete connection setup

For a Go application:

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

func openDB(path string) (*sql.DB, error) {
    db, err := sql.Open("sqlite3", path+"?_journal_mode=WAL&_busy_timeout=5000&_synchronous=NORMAL&_foreign_keys=on")
    if err != nil {
        return nil, err
    }

    // SQLite serializes writes, so a large pool doesn't help
    db.SetMaxOpenConns(1)
    db.SetMaxIdleConns(1)
    db.SetConnMaxLifetime(0)

    return db, nil
}

The SetMaxOpenConns(1) surprises people. It looks like a bottleneck, but it's actually the correct setting for SQLite in most cases. Multiple connections competing for the write lock cause more SQLITE_BUSY errors than they solve. With one connection, writes queue naturally and WAL handles concurrent reads.

If your workload is heavily read-biased, you can raise this. But start with 1 and only increase if you've measured that reads are the bottleneck.

The DSN parameters vary by driver. For mattn/go-sqlite3 (cgo) they're query params as shown. For modernc.org/sqlite (pure Go), the syntax is slightly different — check the driver docs.

Backups: the part people get wrong

Copying the .db file while the application is running is not safe. WAL mode means there's uncommitted state in the -wal file, and a naive copy can produce a corrupt or stale database.

Use the built-in backup API:

sqlite3 mydb.db ".backup '/backups/mydb-$(date +%F).db'"

This is atomic and works while the database is in use. Run it from cron, keep a week of backups, and move them off the machine.

Alternatively, use VACUUM INTO:

VACUUM INTO '/backups/mydb.db';

This produces a compacted, defragmented copy. Slower than .backup, but the result is smaller.

If you're using Litestream (which I'll get to in a moment), backups are handled differently and you don't need either of these.

Litestream: the thing that makes this defensible in production

The objection to SQLite in production is usually "what happens when the machine dies?" Litestream answers that by continuously replicating the WAL to S3-compatible storage.

dbs:
  - path: /var/lib/myapp/data.db
    replicas:
      - type: s3
        bucket: my-backups
        path: myapp
        region: us-east-1

Run litestream replicate alongside your app. It ships changes to S3 within a second or so of them being written. If the server dies, you start a new one, run litestream restore, and you're back online with at most a few seconds of data loss.

This changes the conversation. The single-file objection was "no replication." Litestream provides replication. The remaining objections are about write throughput and multi-region, which are separate concerns.

When to switch to Postgres

SQLite is not the right choice when:

• You need multiple application servers. SQLite is a file on a disk. Multiple servers need a shared database, which means a network protocol, which means Postgres or MySQL.
• Write throughput is heavy. Above roughly 100–200 writes per second sustained, the single-writer limit becomes a real bottleneck. Batch your writes, or switch.
• You need row-level locking or complex transactions. SQLite supports transactions, but not the fine-grained locking model that heavy concurrent workloads need.
• You need to run analytics queries alongside OLTP. A long-running analytical query on SQLite blocks writes. Postgres handles this with MVCC and read replicas.
• You need extensions. PostGIS, pg_trgm, TimescaleDB — Postgres's extension ecosystem is a real reason to choose it.

The migration path from SQLite to Postgres is genuinely painful, so it's worth thinking about the ceiling before you commit. But if the app is a single-instance service and the write volume is modest, SQLite will run for years without intervention.

The projects I've run on SQLite

A webhook receiver handling about 20 requests/second, storing every payload, querying by type and timestamp. Two years, no intervention, database file under 4GB, backed up via Litestream.

An internal dashboard with heavy read traffic and a nightly batch write. Faster on SQLite than the Postgres version it replaced, because everything runs in-process.

Both were single-instance deployments. Neither had any reason to need a database server. Both would have been worse off with Postgres — more moving parts, more to monitor, more to break.

That's the honest framing. SQLite isn't "for small projects." It's for projects where the constraints line up. Which is more projects than people assume.