SQLite in Production: When the Simplest Database Is the Right One

Source: Dev.to

Introduction

For years the advice was automatic: SQLite is for prototypes, mobile apps, and test suites. If you’re building anything real, you need PostgreSQL. That advice was reasonable in 2015, but it’s increasingly wrong in 2026.

SQLite powers every iPhone, every Android device, every Firefox and Chrome installation. The engine processes more queries per day than every other database engine combined. What changed isn’t SQLite’s reliability — it’s the ecosystem around it.

Production‑grade tooling

Litestream

Litestream solves SQLite’s most critical production gap. Ben Johnson’s tool continuously replicates SQLite databases to S3‑compatible storage by streaming WAL changes in near‑real‑time. Recovery point objectives drop to seconds. There’s no cron job, no pg_dump window, no backup interval to worry about — just a single sidecar binary and a bucket policy.

LiteFS (Fly.io)

LiteFS uses a FUSE‑based filesystem to replicate SQLite across multiple nodes. A primary handles writes; replicas in Tokyo, London, or São Paulo serve reads locally. For read‑heavy applications, the latency improvement over a centralized Postgres instance is measurable and real.

libSQL / Turso

The libSQL/Turso fork adds a network protocol, native replication primitives, and user‑defined functions without recompiling. You keep the embedded local performance while gaining the ability to access the database over HTTP for tooling and admin use.

Concurrency model

The most persistent myth about SQLite is that it can’t handle concurrent access. Under WAL mode — which should be your default for any production deployment — concurrent readers never block writers and writers never block readers.

The real constraint is serialized writes: SQLite processes one write transaction at a time. On modern NVMe storage, that single writer can sustain tens of thousands of write transactions per second. For the vast majority of web applications (where reads outnumber writes 10:1 or more), this isn’t a bottleneck.

Essential WAL configuration

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;      -- milliseconds
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;      -- ~64 MiB
PRAGMA foreign_keys = ON;

The busy_timeout pragma is critical — without it, concurrent write attempts return SQLITE_BUSY immediately instead of retrying.

Typical use cases

Read‑heavy single‑server apps

When the dataset fits in memory or on fast NVMe, every query is a local function call. Latency drops from milliseconds to microseconds.

Edge deployments

Apps on Fly.io, Cloudflare Workers, or similar platforms benefit enormously from a database that lives on the same machine as the application.

Internal tools and dashboards

The operational overhead of a dedicated Postgres instance for a five‑person admin panel is hard to justify.

Bounded data volumes

If your SaaS tool generates a few gigabytes per tenant at most, SQLite’s theoretical ceiling (terabytes) is irrelevant.

When to choose PostgreSQL instead

• Write‑heavy transactional workloads (payments, inventory) that require high concurrent write throughput.
• Multi‑server horizontal scaling with many writers.
• Complex queries involving CTEs, window functions, or advanced analytics.

The choice isn’t ideological — it’s workload‑specific.

Conclusion

WAL mode changes the concurrency equation. Default journal mode SQLite and WAL‑mode SQLite are meaningfully different tools; always use WAL for production.

Litestream unlocks continuous backup and disaster recovery, solving the historic “SQLite isn’t production‑ready” narrative. Match the database to the deployment model: SQLite shines on single‑server or edge deployments, while genuine multi‑writer horizontal scaling calls for PostgreSQL.

Read the full article at for a detailed workload comparison table, LiteFS setup walkthrough, and an honest assessment of where SQLite still falls short.