For years the advice was “SQLite for development, PostgreSQL for production”. That’s no longer automatically true. Modern SQLite with WAL mode handles real production traffic on a single server, and frameworks like Rails 8 now default to it for many components. But PostgreSQL remains the stronger choice in several situations.

Architecture

  • SQLite — a library inside your app process; the database is one file. No server, no network round trips, no connection management.
  • PostgreSQL — a separate server process; clients connect over a socket or TCP; many users, roles and databases.

Comparison

SQLite PostgreSQL
Setup and operations None — a file Server to install, secure, tune and upgrade
Query latency Microseconds (in-process) Sub-millisecond + network/socket overhead
Concurrent reads Excellent Excellent
Concurrent writes One writer at a time (WAL helps a lot) Many concurrent writers (MVCC)
Multiple app servers No (single machine) Yes
Users/permissions File permissions only Roles, row-level security
Replication / HA Litestream (backup streaming), LiteFS Streaming and logical replication
Data types Dynamic typing (STRICT tables optional) Rich: JSONB, arrays, ranges, UUID, PostGIS…
Full-text search FTS5 Built-in tsvector, pg_trgm
Extensions Limited Huge ecosystem (pgvector, TimescaleDB, PostGIS)
Backups Copy with .backup/VACUUM INTO, Litestream pg_dump, pgBackRest, PITR
Max practical size Hundreds of GB is fine Terabytes

When SQLite is a great choice

  • One server runs the app (most small and medium sites).
  • Read-heavy workloads: blogs, documentation, content sites, internal tools.
  • Moderate write rates with short transactions.
  • You want minimal operations: no database server to patch or tune.
  • Embedded or edge deployments, CLI tools, single-tenant apps (a database per customer).

With journal_mode=WAL, synchronous=NORMAL and busy_timeout, SQLite on SSD storage handles thousands of reads per second and a few hundred to thousands of writes per second. See SQLite on a server.

When PostgreSQL is the better choice

  • Several app servers or processes on different machines need the same database.
  • High write concurrency — many users writing simultaneously (busy SaaS, marketplaces).
  • You need roles and permissions in the database, row-level security, or analysts connecting with their own credentials.
  • Advanced features: JSONB indexing, PostGIS, pgvector for AI embeddings, partitioning, logical replication.
  • High availability with automatic failover.

Migration paths

Starting with SQLite doesn’t lock you in:

  • ORMs (Django, Rails ActiveRecord, Prisma, SQLAlchemy, Laravel Eloquent) abstract most differences.
  • Tools like pgloader migrate SQLite databases to PostgreSQL.
  • Watch out for SQLite-specific behaviours: dynamic typing, datetime stored as text, case-insensitive LIKE by default.

Test on PostgreSQL early if you expect to move.

Operational reality on a VPS

SQLite: the database is part of your app’s deployment — back it up like a file (consistently), keep it out of the web root, and make sure only one machine writes to it.

PostgreSQL: one more service to run — configuration, authentication (pg_hba.conf), tuning, upgrades, monitoring. Well worth it when you need its capabilities. See installing PostgreSQL and PostgreSQL performance tuning.

Decision guide

  1. Will more than one machine write to the database? → PostgreSQL.
  2. Need roles, replication, PostGIS/pgvector? → PostgreSQL.
  3. Single server, mostly reads, want simplicity? → SQLite.
  4. Unsure and the app may grow into a multi-server SaaS? → PostgreSQL from the start avoids a migration later.

Related: MySQL vs PostgreSQL and NoSQL vs SQL.

  • SQLite
  • PostgreSQL
  • Databases