The conventional wisdom was simple: SQLite for local dev and testing, Postgres for everything real. That line blurred significantly in 2025–2026. SQLite grew up — WAL mode, strict mode, and a wave of hosted services like Turso turned it into a credible production choice for a much wider range of applications. The question is no longer "when do you graduate from SQLite to Postgres" but "which one actually fits your workload?"
What changed in 2026
- SQLite WAL mode is now the default in most wrappers, delivering much better concurrent read performance — easily 5–10× better than the old journal mode.
- Turso (libSQL fork) offers multi-region SQLite replicas with a synchronization model, making the "one file per region" edge architecture viable.
- Cloudflare D1 hit general availability with point-in-time recovery and larger storage limits, removing the main complaints from 2024.
- Postgres 17 improved logical replication and added more JSONB operators; pgvector 0.7 landed HNSW index improvements that make it the default vector store for many AI stacks.
- The "SQLite in production" discourse peaked — DHH and the Basecamp team published detailed performance numbers that made the community take SQLite more seriously.
Architecture differences
SQLite is a serverless, embedded database: the library and the data file live inside the process. There is no separate DB server, no network round-trip, no connection pool to manage. This simplicity is both its greatest strength and its hard ceiling.
Postgres is a client-server database: a separate process manages data, handles concurrent clients, enforces roles, and exposes a TCP socket. The network hop costs microseconds but buys you everything that requires coordination.
Head-to-head comparison
| Factor |
SQLite 3.47 (WAL) |
Postgres 17 |
| Concurrent reads |
High (WAL, many readers) |
Very high |
| Concurrent writes |
Low (single writer) |
High (MVCC) |
| Network latency |
Zero (in-process) |
~0.1–1 ms local |
| Replication |
Turso/Litestream |
Native logical/streaming |
| Full-text search |
FTS5 |
tsvector + pg_trgm |
| Vector search |
sqlite-vss (limited) |
pgvector (HNSW, IVFFlat) |
| JSON |
JSON1 extension |
JSONB (indexed, operators) |
| Extensions |
C extensions only |
Rich ecosystem (PostGIS, etc.) |
| Managed hosting cost |
~$0–$30/mo (Turso free tier) |
~$20–$100+/mo |
| Multi-region |
Turso replicas |
Read replicas, Citus |
When SQLite is the right choice
- Single-writer web apps — blogs, portfolios, internal tools, SaaS with sequential writes.
- Edge and serverless — Cloudflare D1 / Turso give you a database with zero cold-start and minimal latency at the edge.
- Multi-tenant apps where each tenant gets their own DB — one SQLite file per tenant, trivially backed up with Litestream.
- Local-first apps — SQLite is the database of the device; no network required.
# Litestream continuous backup to S3 — a single config line
# litestream.yml
dbs:
- path: /data/app.db
replicas:
- url: s3://my-bucket/app.db
When Postgres is the right choice
- High write concurrency — SQLite serializes all writes; Postgres MVCC handles hundreds of concurrent writers.
- AI / vector workloads — pgvector with HNSW indexes is the production standard; sqlite-vss lags.
- Complex queries and analytics — window functions, CTEs, and JSONB operators are more capable in Postgres.
- Multi-region with strong consistency — Postgres streaming replication with failover is battle-tested.
- Team of multiple engineers — roles, row-level security, and audit logging are first-class in Postgres.
-- pgvector similarity search with HNSW index (Postgres 17)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.12, 0.34, ...]'::vector
LIMIT 10;
How to pick
- Single writer, low ops budget, edge deployment? → SQLite + Turso or D1.
- Multiple concurrent writers or background jobs? → Postgres.
- Need pgvector for AI? → Postgres, no contest.
- Multi-tenant SaaS with per-tenant isolation? → SQLite per tenant is a great fit.
- Already on Postgres? → Stay there; migration cost never pays back unless you're hitting hard limits.
Common mistakes
Benchmarking SQLite without WAL mode. The old default (DELETE journal) serializes reads and writes. Always enable WAL before comparing: PRAGMA journal_mode=WAL;.
Using SQLite on a network file system. File locking is not reliable over NFS or most cloud shared storage. SQLite must live on local disk or in-memory.
Postgres overkill for a static site. If your CMS has 500 posts and 10 writes per day, you are paying for connection poolers, PgBouncer, and read replicas that will never carry load.
Ignoring Litestream. SQLite without a replication / backup solution is a data loss risk. Litestream is free, trivial to configure, and streams WAL frames to S3 in near-real-time.
What to skip
- SQLite for a multi-writer job queue — advisory locks and
SELECT FOR UPDATE are Postgres features; you will re-implement them poorly.
- Postgres on a $5 VPS for a hobby project — the memory overhead of a Postgres instance with a connection pool is real; SQLite costs nothing.
- "Hybrid" setups where you write to SQLite and sync to Postgres — the sync logic becomes the hardest part of the system.
FAQ
Is SQLite actually safe for production?
Yes, for single-writer workloads. WhatsApp used SQLite as its message store for years. The risks are well-understood: concurrent writes and network-mounted files.
What about PlanetScale or Neon in 2026?
Both are serverless Postgres. They eliminate operational overhead but add latency and cost at scale. Good middle ground for teams that want Postgres without running a server.
Can I use SQLite with an ORM?
Yes — Drizzle, Prisma, and SQLAlchemy all support SQLite. Drizzle is especially clean with better-sqlite3 for synchronous queries.
How do I migrate from SQLite to Postgres later?
pgloader handles most schemas automatically. The main gotchas are strict type casting and AUTOINCREMENT vs SERIAL semantics.
Where to go next