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. 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.
What Actually Changed
Litestream solved 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 from Fly.io 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 forked SQLite to add a network protocol, native replication primitives, and user-defined functions without recompiling. You get embedded local performance and the ability to access the database over HTTP for tooling and admin use.
The Concurrency Misconception
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.
The essential WAL mode setup:
PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=5000;
PRAGMA synchronous=NORMAL;
PRAGMA cache_size=-64000;
PRAGMA foreign_keys=ON;
The busy_timeout pragma is critical — without it, concurrent write attempts return SQLITE_BUSY immediately instead of retrying.
Where SQLite Beats PostgreSQL
- Read-heavy single-server apps: When your 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 or Cloudflare 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 is irrelevant.
Where PostgreSQL Still Wins
Write-heavy transactional workloads (payments, inventory), multi-server horizontal scaling, and complex queries with CTEs and window functions are still PostgreSQL territory. The choice isn't ideological — it's workload-specific.
Three Key Takeaways
- WAL mode changes the concurrency equation. Default journal mode SQLite and WAL mode SQLite are meaningfully different tools. Always use WAL for production.
- Litestream is the unlock. The backup and disaster recovery story that made SQLite unsuitable for production is solved. A single binary handles continuous replication to object storage.
- Match the database to the deployment model. SQLite's advantages are most pronounced on single-server or edge deployments. The moment you need genuine multi-writer horizontal scaling, move to PostgreSQL.
Read the full article at novvista.com for a detailed workload comparison table, LiteFS setup walkthrough, and an honest assessment of where SQLite still falls short.
Originally published at NovVista
Top comments (0)