--- title: "SQLite vs Postgres" slug: "sqlite-vs-postgres" category: "comparisons" tags: ["comparisons", "sqlite", "postgres", "database", "embedded"] status: "stable" last_updated: 2026-05-14 summary: "Pick SQLite for single-writer apps and local-first tools where Litestream handles durability; pick Postgres the moment two processes write concurrently." related: ["[[backend/sqlite]]", "[[backend/postgres]]", "[[comparisons/postgres-vs-mysql]]", "[[comparisons/mongodb-vs-postgres]]", "[[ops/postgres-prod]]", "[[backend/index]]"] --- ## Overview Use SQLite when one process writes the database. That covers most internal tools, single-tenant apps deployed on a Fly or Hetzner box, mobile apps, and CLI state stores. Switch to Postgres the moment two processes need to write the same table, or the app is multi-tenant with cross-tenant queries. Litestream and libsql changed the calculus around durability; SQLite is now a serious production choice for a much wider set of apps. See [[backend/sqlite]] for the SQLite rule set and [[backend/postgres]] for Postgres. ## When SQLite wins SQLite is the right pick when concurrency is bounded, the data fits on one disk, and the deployment story benefits from one file. - Single-writer apps: a Rails-style monolith on Fly with one machine, an Astro blog with a comments DB, an internal admin tool. One process, one writer, no contention. - Local-first: desktop apps, CLI tools, mobile apps, and PWAs ship SQLite as the on-device store and sync upstream. - Edge: libsql on Turso replicates a SQLite database to every region; reads are local, writes go to the primary. - Operational cost: zero. No server, no port, no connection pool, no replication topology. The database is `app.db`. - Backups with Litestream stream WAL frames to S3 or R2 every few seconds; recovery is `litestream restore` plus the latest snapshot. - Performance: 100k reads per second per core, 10k to 50k writes per second on a single writer. Faster than Postgres for many embedded workloads because there is no network hop. - Schema migrations are one transaction; no replica lag, no logical replication choreography. ## When Postgres wins Postgres is the right pick when more than one process writes, the app is multi-tenant, or the workload needs features SQLite does not have. - Multi-process writes: Postgres uses MVCC and row-level locks; SQLite serializes writers at the database level. Two workers writing the same SQLite file will queue. - Concurrent connections: Postgres with pgbouncer handles thousands; SQLite is fine with tens, painful past a few hundred. - Cross-host: any app running on more than one machine that writes to the database wants Postgres. SQLite over a network filesystem is a footgun. - Feature surface: pgvector, PostGIS, partial indexes on expressions, materialized views, logical replication, full-text search with `tsvector`. SQLite has FTS5 and JSON1 but the gap is real. - Strict types in older SQLite: pre-3.37 the type system was advisory; even with `STRICT` tables today the type coercion rules are more permissive than Postgres. - Hot standby and read replicas: Postgres has mature streaming replication. SQLite uses Litestream or libsql, which serve different shapes of read scale. ## Trade-offs at a glance | Dimension | SQLite | Postgres | | ------------------ | --------------------------------------- | ----------------------------------------- | | Writers | One per database | Many; row-level locking | | Deploy unit | One file | Server, port, credentials | | Hosting cost | Disk only | Managed service or VM | | Replication | Litestream or libsql | Native streaming, logical | | Concurrency | Excellent for reads, serialized writes | Excellent for both | | Extensions | FTS5, JSON1, R-Tree, pgvector via libsql | pgvector, PostGIS, partman, TimescaleDB | | Backups | `cp app.db` or Litestream | `pg_dump`, base backup, WAL archive | | Connection pool | Not needed | pgbouncer at scale | | Edge replication | Turso (libsql) replicates per region | Neon, Aurora with regional reads | | Test runtime | In-memory `:memory:` in tests | Testcontainers or shared dev DB | | Schema strictness | `STRICT` tables; still flexible | Strong by default | ## Migration cost Both directions are mechanical once the data model is clean. - SQLite to Postgres: dump with `sqlite3 app.db .dump`, transform with `pgloader` or a hand-written script, and reapply. Most SQL ports directly; rewrite any `AUTOINCREMENT` to `IDENTITY` and any `INTEGER PRIMARY KEY` defaults to explicit types. Plan one engineer-week for a 50-table app plus another for connection handling and tests. - Postgres to SQLite: works for small apps that outgrew the operational cost of Postgres. Drop unsupported types (arrays, ranges, JSONB containment operators) or rewrite with JSON1. Plan one to two engineer-weeks plus a careful look at concurrency. - Cheaper path: deploy SQLite with Litestream as the prototype. If concurrency becomes the bottleneck, migrate; the schema rewrite is small if you stuck to SQL standard features. ## Recommendation - New SaaS where the team owns the infrastructure: SQLite with Litestream on one box until a real concurrency limit shows up. Then Postgres. - Single-tenant per-customer deployment (one DB per customer): SQLite. The footprint is unbeatable. - Multi-tenant SaaS with cross-tenant queries: Postgres on day one; the migration off SQLite would be painful. - Local-first app or mobile sync: SQLite. The only realistic answer. - Anything that wants pgvector, PostGIS, or `tsvector`: Postgres, unless you use libsql with pgvector compatibility. - Existing Postgres operational depth and no SQLite story: stay on Postgres; the cost saving is rarely worth the rewrite. - See [[ops/postgres-prod]] for production-grade Postgres operation. ## Related - [[backend/sqlite]] - [[backend/postgres]] - [[comparisons/postgres-vs-mysql]] - [[ops/postgres-prod]] - [[backend/index]]