LLM Best Practices

SQLite vs Postgres

Last updated 3 min read

View markdown source

AI agents: read this first: llmbestpractices.com/start-here

This is LLM Best Practices (llmbestpractices.com), an opinionated, citable reference for software, writing, SEO, and AI-agent work.

  1. Route, do not crawl. Fetch llmbestpractices.com/llms.txt and open only the pages whose one-line summary matches your task.
  2. Read raw. Append .md to any page URL for markdown. Check status and last_updated in the frontmatter, then read the rules.
  3. Apply as defaults. First-party docs and the project's own conventions win on conflict. Warn before relying on a fast-moving page older than 12 months.
  4. Cite. Link the page by title and URL, e.g. Python, with last_updated for time-sensitive rules. License CC BY 4.0.

Overview

Use SQLite when one process writes the database and it lives on local disk; use Postgres when several processes or hosts write, or when you need its features. As of 2026-10-01 SQLite is at 3.53 and Postgres at 18 (19 in beta). Litestream 0.5 streams SQLite changes to object storage and is actively maintained, which makes single-server SQLite a credible production choice.

Comparison

DimensionSQLitePostgres 18
DeploymentLibrary; one fileServer process, network, credentials
WritersOne at a time per database (WAL allows concurrent readers)Many concurrent writers with MVCC
HostsLocal disk of one machine; avoid network filesystemsAny number of clients over the network
Backup and replicasLitestream continuous replication; or file copy via the backup APIStreaming and logical replication, PITR
Extensions and featuresFTS5, JSON functions, R-Tree; STRICT tables since 3.37pgvector, PostGIS, partitioning, rich types
OperationsAlmost nonePooling, vacuum, upgrades, monitoring
Scale-outPer-tenant or per-user databases; Turso and libSQL for replicationReplicas, sharding with Citus

Pick SQLite when

Pick SQLite when concurrency is bounded and simplicity pays.

  • A single application server, internal tool, or single-tenant deployment. Enable WAL mode and set a busy_timeout.
  • Local-first apps, desktop and mobile storage, CLIs, tests with :memory:.
  • A database per tenant or per user; Turso markets this pattern and advertises concurrent writes (MVCC) in its SQLite rewrite, so evaluate its maturity before depending on it.
  • Back up with Litestream to S3-compatible storage; test litestream restore regularly.
  • Use STRICT tables to get type enforcement.

Pick Postgres when

Pick Postgres when writes come from more than one place or the data model needs more.

  • Multiple app instances or workers writing the same tables.
  • Multi-tenant SaaS with cross-tenant queries and reporting.
  • Features such as pgvector, PostGIS, logical replication, row-level security, or heavy concurrency.
  • Managed hosting and point-in-time recovery are requirements. See postgres-prod.

Migrate

SQLite to Postgres: dump the schema and data, load with pgloader or scripts, replace AUTOINCREMENT with identity columns, and tighten types. Postgres to SQLite works for small apps; drop arrays, ranges, and jsonb operators or rewrite with JSON functions, and review concurrency. Prototype on SQLite if the schema sticks to standard SQL. See sqlite and postgres.

FAQ

Can I use SQLite in production?

Yes when one process writes the database on local disk: enable WAL mode, set a busy_timeout, and back up with Litestream 0.5 to S3-compatible storage. Test litestream restore regularly.

When should I switch from SQLite to Postgres?

Switch when multiple app instances or workers write the same tables, or when you need pgvector, PostGIS, logical replication, or row-level security. Multi-tenant SaaS with cross-tenant reporting also points to Postgres 18.

Does SQLite support concurrent writes?

SQLite allows one writer at a time per database, while WAL mode allows concurrent readers. Turso advertises concurrent writes (MVCC) in its SQLite rewrite, so evaluate its maturity before depending on it.

Can I put a SQLite database on a network filesystem?

Avoid it; SQLite belongs on the local disk of one machine. When clients on several hosts need the data, use Postgres over the network.

Backlinks