SQLite vs PostgreSQL

Both engines run SQL, and 90% of your queries port between them unchanged. The other 10% is what this page is about — plus the operational and CLI differences you meet on day one.

Architecture

SQLite is a C library you link into your process. Your application calls functions; those functions read and write a single file with pread/pwrite. There is no separate process, no socket, no authentication, no configuration file. “Connecting” means opening a file.

The consequences follow directly:

• Zero operational surface. Nothing to install, monitor, patch, back up separately, or secure on a network.
• Zero network latency. A query is a function call — microseconds, not a round trip.
• No access control. Anyone who can read the file can read every row. Filesystem permissions are the only security boundary.
• Only processes on the same machine (and same filesystem) can use it. Network filesystems like NFS are explicitly unsupported for concurrent access, because their locking is unreliable.

PostgreSQL is a server. A postmaster process forks a backend per connection; backends share memory and coordinate through a WAL, a background writer, autovacuum workers, and more. Clients speak a wire protocol over TCP or a Unix socket.

• Roles, passwords, pg_hba.conf, TLS, row-level security.
• Many machines can connect at once; connection pooling (PgBouncer) becomes a thing you think about.
• Replication, point-in-time recovery, logical decoding.
• Real operational cost: memory tuning, vacuum, upgrades, monitoring.

	SQLite	PostgreSQL
Process model	In-process library	Server + one backend process per connection
Storage	One file (+ -wal, -shm)	A data directory managed by the server
Connection	sqlite3_open("app.db")	TCP/Unix socket + authentication
Query latency	Function call	Network round trip (~0.1–1 ms locally)
Access control	Filesystem permissions	Roles, pg_hba.conf, TLS, RLS
Backup	Copy the file, VACUUM INTO, .backup	pg_dump, pg_basebackup, WAL archiving
Extension model	Loadable C extensions (FTS5, R-Tree, …)	CREATE EXTENSION (PostGIS, pgvector, …)

Typing

Covered in depth in data types and DDL; the summary:

SQLite is dynamically typed. Values carry types, columns don’t. A declared type only sets an affinity — a preferred conversion attempted on insert. There are five storage classes: NULL, INTEGER, REAL, TEXT, BLOB. No boolean, no date, no decimal, no UUID, no arrays. STRICT tables (3.37+) opt into real enforcement, but only for those five types plus ANY.

PostgreSQL is statically typed with a large and extensible type system: exact numeric, boolean, date/time/timestamptz/interval, uuid, bytea, inet, json/jsonb, ranges, arrays of any type, user-defined enums and composites, and types added by extensions.

The practical fallout:

-- SQLite: no error, stores the text 'oops' in an INTEGER column
INSERT INTO books (id, title, year) VALUES (9, 'X', 'oops');

-- PostgreSQL
ERROR:  invalid input syntax for type integer: "oops"

SQLite’s leniency is genuinely useful for exploratory work on messy data and genuinely dangerous in production. Use STRICT.

Concurrency

This is the single biggest functional difference.

SQLite: one writer at a time, database-wide. Locks are taken on the whole file, not on rows or tables. In the default rollback-journal mode a writer also blocks readers. In WAL mode (PRAGMA journal_mode = WAL) readers and the writer proceed concurrently, but there is still exactly one writer.

That limit is fine for a huge number of workloads — a single application server doing a few hundred writes per second, with each write taking under a millisecond, never notices. It is fatal for many application servers writing to a shared file.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;   -- wait up to 5s for the write lock instead of erroring

PostgreSQL: MVCC with row-level locking. Each UPDATE writes a new row version; old versions remain visible to older snapshots and are later reclaimed by vacuum. Readers never block writers; writers never block readers; two writers conflict only when they touch the same row.

	SQLite	PostgreSQL
Concurrent readers	Many	Many
Concurrent writers	One, database-wide	Many, conflicting only per row
Reader/writer blocking	None in WAL mode	None
Isolation levels	Serializable in effect	Read Committed (default), Repeatable Read, Serializable
Row locks	None	FOR UPDATE, FOR SHARE, SKIP LOCKED
Deadlocks	Not possible between statements	Possible; detected and one transaction aborted
Space reclamation	VACUUM (manual, rewrites the file)	Autovacuum (automatic, background)

Caution

PostgreSQL’s MVCC means dead row versions accumulate and must be vacuumed. A long-running transaction holds back the vacuum horizon and causes table bloat. Watch for idle-in-transaction sessions — they are the usual culprit.

Features PostgreSQL has and SQLite doesn’t

Feature	Notes
Arrays	text[], integer[], with ANY, unnest, GIN indexes
Enums	CREATE TYPE ... AS ENUM
Ranges	int4range, tstzrange, with exclusion constraints
Stored procedures & functions	PL/pgSQL, plus PL/Python, PL/Perl, SQL functions
Triggers with full procedural logic	SQLite triggers exist but bodies are SQL statements only
Materialized views	REFRESH MATERIALIZED VIEW [CONCURRENTLY]
Full-text search	tsvector/tsquery, GIN indexes, ranking, stemming — built in
LISTEN / NOTIFY	Pub/sub from inside the database
Extensions	PostGIS (geo), pgvector (embeddings), pg_stat_statements, TimescaleDB
Partitioning	Declarative range/list/hash partitioning
Replication	Streaming and logical replication, read replicas
Foreign data wrappers	Query other databases as if they were local tables
Row-level security	CREATE POLICY
Parallel query	Multiple workers per query
Advanced index types	GIN, GiST, BRIN, SP-GiST, hash
GROUPING SETS/ROLLUP/CUBE, LATERAL, MERGE, DISTINCT ON

SQLite has its own answers to a few of these: FTS5 for full-text search, R-Tree for spatial indexing, and generated columns — all as compiled-in extensions.

Features SQLite has that PostgreSQL doesn’t

Fewer, but real:

• Zero-configuration deployment. Ship a file.
• VACUUM INTO 'backup.db' — an atomic, consistent single-file backup with one statement.
• In-memory databases (:memory:) — perfect for tests.
• ATTACH DATABASE — query across several database files in one statement.
• The database is the file format. Application state, save files, and data interchange in one artefact.
• sqlite3_analyzer, .dump, .recover — file-level forensics on a corrupt database.
• Radically simpler: the entire engine is a few hundred thousand lines with famously exhaustive test coverage.

Auto-incrementing keys

	SQLite	PostgreSQL
Idiomatic form	id INTEGER PRIMARY KEY	id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
Legacy form	INTEGER PRIMARY KEY AUTOINCREMENT	id serial PRIMARY KEY / bigserial
Mechanism	Alias for the hidden rowid	A sequence object with a column default
Reuse after delete	Yes (unless AUTOINCREMENT)	No
Get the new value	RETURNING id (3.35+) or last_insert_rowid()	RETURNING id or currval('seq')

Two traps:

• In SQLite the auto-numbering only happens when the type is written exactly INTEGER. INT PRIMARY KEY or BIGINT PRIMARY KEY is a plain column that will not auto-fill.
• In PostgreSQL, loading data with explicit IDs into a serial/IDENTITY BY DEFAULT column leaves the sequence behind, and the next insert collides. Fix it after a load:

SELECT setval(pg_get_serial_sequence('books', 'id'), COALESCE(MAX(id), 1)) FROM books;

Date and time handling

The biggest source of migration bugs.

SQLite has no date type. Store one of three shapes and be consistent:

Shape	Example	Notes
ISO-8601 text	'2026-08-09 12:00:00'	Sorts and compares correctly as text. The usual choice.
Unix seconds (integer)	1786377600	Compact, needs conversion for display.
Julian day (real)	2461262.0	Rarely used outside date arithmetic.

-- SQLite
SELECT date('now'), datetime('now', '+7 days'), strftime('%Y-%m', '2026-08-09');
SELECT datetime(1786377600, 'unixepoch');
SELECT unixepoch('2026-08-09T12:00:00Z');

There is no time-zone type. 'localtime' and 'utc' modifiers convert using the OS zone, which makes results machine-dependent — store UTC.

PostgreSQL has real temporal types. timestamptz stores an absolute instant and renders it in the session’s time zone. Interval arithmetic is native.

-- PostgreSQL
SELECT now(), now() + interval '7 days', date_trunc('month', now());
SELECT to_timestamp(1786377600);
SELECT EXTRACT(epoch FROM now())::bigint;
SELECT now() AT TIME ZONE 'Europe/Paris';

Task	SQLite	PostgreSQL
Now	datetime('now')	now() / current_timestamp
Today	date('now')	current_date
Add 7 days	date(d, '+7 days')	d + interval '7 days'
Start of month	date(d, 'start of month')	date_trunc('month', d)
Format	strftime('%Y-%m-%d', d)	to_char(d, 'YYYY-MM-DD')
Extract year	CAST(strftime('%Y', d) AS INTEGER)	EXTRACT(year FROM d)
Difference in days	julianday(a) - julianday(b)	a::date - b::date
To Unix seconds	unixepoch(d)	EXTRACT(epoch FROM d)

Choosing between them

Choose SQLite when:

• The database is used by exactly one process, or by processes on one machine.
• Writes are modest and short — a single writer is not a bottleneck.
• You want zero operations: desktop apps, mobile apps, CLI tools, embedded devices.
• You need a test database that starts empty in milliseconds.
• You are analysing a data file someone handed you.
• The data is content that changes rarely and is read constantly (a docs site, a catalogue).

Choose PostgreSQL when:

• More than one application server writes to the same data.
• You need real types — money as numeric, instants as timestamptz, documents as indexed jsonb.
• You need concurrent write throughput, or long analytical queries running beside OLTP traffic.
• You need replication, point-in-time recovery, or read replicas.
• You need features only it has: full-text search, PostGIS, pgvector, partitioning, row-level security.
• The data will outgrow one machine’s memory, or one machine entirely.

Tip

Starting a new project on SQLite and migrating later is a reasonable plan, provided you write portable SQL from day one: STRICT tables, NOT NULL everywhere it applies, PRAGMA foreign_keys = ON, no INSERT OR REPLACE, no bare columns in GROUP BY, single-quoted strings, and dates as ISO-8601 UTC text. Following those rules costs nothing and makes the migration mechanical.

Caution

Do not use SQLite where multiple machines write to the same file over a network filesystem. SQLite’s locking depends on POSIX advisory locks working correctly, and NFS/SMB implementations frequently do not. This corrupts databases.

Migration checklist: SQLite → PostgreSQL

Work through this before moving data.

Schema

• Replace INTEGER PRIMARY KEY [AUTOINCREMENT] with integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY (or bigint).
• Give every column a real type: BOOLEAN columns holding 0/1 become boolean; text dates become timestamptz or date; money columns become numeric(p, s), never real.
• Replace TEXT used for JSON with jsonb if you query it.
• Convert CHECK (x IN (...)) to an enum or keep the check — both work.
• Add NOT NULL where SQLite let nulls into primary keys.
• Recreate indexes; add an index on every foreign key column (SQLite tolerated their absence better than you think, PostgreSQL will not on cascades).
• Lowercase all identifiers, or be ready to quote them forever.

Data

• Convert booleans: 0/1 → false/true.
• Normalise timestamps to a single ISO-8601 UTC format before loading.
• Find and fix rows where SQLite’s dynamic typing stored the wrong type: SELECT * FROM t WHERE typeof(col) <> 'integer';
• Fix orphaned foreign key rows: PRAGMA foreign_key_check; in SQLite before exporting.
• After loading with explicit IDs, setval every sequence.

SQL

• IFNULL(a, b) → COALESCE(a, b).
• GROUP_CONCAT(x, ',') → STRING_AGG(x, ',').
• INSTR(a, b) → STRPOS(a, b) (note the argument order is the same).
• PRINTF/FORMAT format strings — verify; PostgreSQL’s FORMAT uses %s/%I/%L, not C-style width specifiers.
• strftime/julianday/datetime → to_char/date_trunc/interval arithmetic.
• INSERT OR REPLACE / REPLACE INTO → INSERT ... ON CONFLICT ... DO UPDATE.
• INSERT OR IGNORE → INSERT ... ON CONFLICT DO NOTHING.
• "double quoted strings" → 'single quoted strings'.
• Bare columns in GROUP BY → add them to GROUP BY or wrap in an aggregate.
• LIKE used case-insensitively → ILIKE or LOWER(x) LIKE LOWER(y).
• Add NULLS FIRST/NULLS LAST where null ordering matters — the defaults are opposite.
• Integer division a / b on integer columns behaves the same, but ROUND(x, n) in PostgreSQL needs numeric: ROUND(x::numeric, 2).
• PRAGMA statements have no PostgreSQL equivalent — delete them, or replace with SET/ALTER SYSTEM where applicable.

Behaviour

• Wrap risky statements in savepoints: in PostgreSQL any error aborts the whole transaction.
• Add retry logic if you use REPEATABLE READ or SERIALIZABLE — serialization failures (SQLSTATE 40001) are expected and must be retried.
• Re-check every place you relied on SQLite returning the first row of a multi-row scalar subquery; PostgreSQL errors instead.

Tooling. pgloader automates much of the schema and data conversion. Treat its output as a first draft, not the final schema — it maps SQLite’s loose types conservatively, and you will want to tighten them.

psql cheat sheet

Backslash commands are client-side; they take no semicolon. \? lists them all, \h SELECT shows SQL syntax help.

Connecting and sessions

psql -d bookstore                      connect to a local database
psql -h host -p 5432 -U user -d db     connect over TCP
psql "postgresql://user:pw@host/db"    connection URI
psql -c "SELECT 1" -d db               run one statement and exit
psql -f script.sql -d db               run a file
psql -v ON_ERROR_STOP=1 -f script.sql  abort the script on the first error
psql -At -c "SELECT 1"                 unaligned, tuples-only (script-friendly output)

Navigation

\l          list databases              \c dbname   connect to another database
\conninfo   show current connection     \q          quit
\!  cmd     run a shell command         \cd dir     change directory

Inspecting the schema

\dt          list tables            \dt+         with size and description
\d  name     describe a table/view/index/sequence
\d+ name     describe with storage, comments, and view definitions
\di          indexes                \dv          views
\dm          materialized views     \ds          sequences
\dn          schemas                \df          functions
\du          roles                  \dT          data types
\dp          table privileges       \sf funcname show a function's source
\dt *.*      include system schemas
\dt pattern  filter by name pattern, e.g. \dt book*

Output control

\x           toggle expanded (one column per line) display
\x auto      expand only when rows are too wide
\pset null '∅'          how to render NULL
\pset format csv        output format: aligned, csv, html, json...
\timing on   report execution time for every statement
\a           toggle aligned/unaligned output
\o file.txt  send subsequent output to a file (\o alone stops)

Running things

\i file.sql       execute a file
\e                open the last query in $EDITOR, run it on save
\ef funcname      edit a function definition
\g                re-run the last query;  \gx  re-run with expanded output
\watch 2          re-run the last query every 2 seconds
\copy books FROM 'b.csv' CSV HEADER      client-side import (no server file access needed)
\copy (SELECT * FROM books) TO 'b.csv' CSV HEADER

Useful SQL-side commands

SHOW ALL;                                  -- every configuration setting
SELECT version();
SELECT pg_size_pretty(pg_database_size(current_database()));
SELECT pg_size_pretty(pg_total_relation_size('books'));
SELECT * FROM pg_stat_activity WHERE state <> 'idle';

sqlite3 cheat sheet

Dot commands are client-side; they take no semicolon and must start at the beginning of a line. .help lists them all.

Starting

sqlite3 app.db                          open (creates the file if missing)
sqlite3 :memory:                        temporary in-memory database
sqlite3 app.db "SELECT COUNT(*) FROM books;"    run one statement and exit
sqlite3 app.db < script.sql             run a file
sqlite3 -header -csv app.db "SELECT * FROM books;" > books.csv
sqlite3 app.db ".dump" > dump.sql       full SQL dump

Inspecting

.tables                list tables
.schema                full schema
.schema books          one table's CREATE statement
.fullschema            schema plus stored ANALYZE statistics
.indexes books         indexes on a table
.databases             attached databases and their files
.dbinfo                header details: page size, encoding, journal mode

Output formatting

.headers on            show column names
.mode box              bordered table (3.33+)
.mode column           aligned columns
.mode line             one field per line — good for wide rows
.mode csv              comma-separated
.mode json             JSON array of objects
.mode insert books     emit INSERT statements
.mode markdown         Markdown table
.nullvalue NULL        how to render NULL
.width 20 40           fixed column widths for column mode

Import, export, backup

.import --csv --skip 1 books.csv books   load a CSV into a table
.output out.txt        redirect results to a file (.output alone restores stdout)
.once out.txt          redirect only the next command's output
.read script.sql       execute a file
.dump                  SQL text dump of the whole database
.backup backup.db      online binary backup, safe while other connections write
.save backup.db        write the current database to a new file

VACUUM INTO 'backup.db';   -- SQL equivalent of .backup, 3.27+

Diagnostics

.timer on              wall-clock time per statement
.stats on              per-statement counters
.eqp on                automatically show EXPLAIN QUERY PLAN for each statement
.changes on            report rows changed by each statement
.lint fkey-indexes     suggest indexes for unindexed foreign keys
.quit                  exit (.exit works too)

PRAGMAs worth knowing

PRAGMA foreign_keys = ON;        -- enforce foreign keys (OFF by default!)
PRAGMA journal_mode = WAL;       -- concurrent readers with a writer; persistent
PRAGMA busy_timeout = 5000;      -- ms to wait for a lock before erroring
PRAGMA synchronous = NORMAL;     -- faster; safe against crashes in WAL mode
PRAGMA table_info(books);        -- columns, types, nullability, defaults
PRAGMA index_list(books);        -- indexes on a table
PRAGMA foreign_key_check;        -- report orphaned rows
PRAGMA integrity_check;          -- verify the whole file
PRAGMA optimize;                 -- run before closing a long-lived connection
PRAGMA user_version;             -- a free integer you can use for schema versioning
SELECT sqlite_version();         -- which features you actually have

Key points

• SQLite is an in-process library over one file; PostgreSQL is a server with roles, networking, and replication.
• SQLite’s typing is dynamic (use STRICT); PostgreSQL’s is static, rich, and extensible.
• SQLite allows one writer at a time database-wide; PostgreSQL does row-level MVCC with many concurrent writers.
• PostgreSQL adds arrays, enums, materialized views, full-text search, LISTEN/NOTIFY, extensions, partitioning, and advanced index types.
• SQLite adds zero-configuration deployment, in-memory databases, ATTACH, and single-file backups.
• Date/time is the biggest migration hazard: SQLite has functions over text/integers, PostgreSQL has real types.
• Writing portable SQL from day one makes SQLite → PostgreSQL a mechanical migration; the checklist above covers the real differences.
• \? and .help are the two commands to remember — everything else follows from them.