PostgreSQL¶

What experienced PostgreSQL engineers forget before an interview, grouped by subtopic. For SQL vs NoSQL, storage engines, and the scaling ladder, see system.md; for replication theory and consistency models, see distributed.md.

Indexes¶

B-tree indexes¶

• The default index is a B+tree: balanced, sorted, O(log n), serves equality, range scans, and ORDER BY.
• Tables are heaps: rows are unordered and every index entry points to a physical location (ctid), which changes when the row is updated.
• Deduplication (PostgreSQL 13+) stores repeated keys once, shrinking indexes on low-cardinality columns.

Composite indexes¶

• Leftmost-prefix rule: (a, b, c) seeks efficiently on a, a,b, a,b,c — not on b alone.
• Skip scan (PostgreSQL 18+) serves b alone by probing each distinct a; cheap only when a has few values.
• Put equality columns first, range columns last; columns after a range condition only filter inside the scanned range (unless skip scan applies).

Covering indexes¶

• A covering index holds every column the query needs, enabling an index-only scan; INCLUDE (PostgreSQL 11+) adds non-key payload columns.
• An index-only scan still checks the visibility map; recently modified pages force heap visits until VACUUM runs.

Partial and expression indexes¶

• Partial (WHERE deleted_at IS NULL) and expression (ON lower(email)) indexes cover only what queries use.
• Sargability: a function or implicit cast on an indexed column (WHERE lower(email) = ...) blocks index use unless a matching expression index exists.
• The planner uses a partial index only when it can prove the query's WHERE implies the index predicate; a generic prepared-statement plan, which sees $1 instead of the value, can't prove it.

Specialized index types¶

Building indexes online¶

• Plain CREATE INDEX takes a SHARE lock: reads continue, writes block for the whole build.
• CREATE INDEX CONCURRENTLY doesn't block writes, but scans the table twice, can't run in a transaction, and leaves an INVALID index if it fails — drop it and retry.
• REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds a bloated index without blocking writes.
• Index every foreign key you join or cascade-delete on — PostgreSQL doesn't create them automatically.

Transactions and isolation¶

Isolation levels¶

Lost update and write skew¶

• Lost update: two read-modify-write cycles overwrite each other; Read Committed allows it — use an atomic UPDATE ... SET n = n + 1, SELECT ... FOR UPDATE, or a version check.
• Write skew: two transactions read overlapping data and write different rows, together breaking an invariant (both doctors go off call); only Serializable or explicit locks prevent it.

Read Committed re-check¶

• Each statement in Read Committed takes a new snapshot, so two SELECTs in one transaction can disagree.
• An UPDATE that waits on a row locked by a concurrent writer re-evaluates its WHERE against the newest committed version, then updates or skips it — it never sees rows that newly started matching.

Repeatable Read and Serializable¶

• Repeatable Read is snapshot isolation: one snapshot from the first statement; updating a row a concurrent transaction changed fails with "could not serialize access", so no lost updates — but write skew is possible.
• Serializable is SSI: predicate (SIReadLock) tracking detects dangerous read-write cycles and aborts one transaction.
• Both abort with SQLSTATE 40001; the app must retry the whole transaction, not just the failed statement.

Optimistic locking¶

• Add a version column: UPDATE ... SET version = version + 1 WHERE id = ? AND version = ?; zero rows updated means someone else won — reload and retry.
• Best when conflicts are rare; pessimistic locks when they're frequent.

Errors inside a transaction¶

• After any error, the transaction is aborted: every later statement fails with "current transaction is aborted" until ROLLBACK.
• SAVEPOINT + ROLLBACK TO SAVEPOINT recovers part of the work; psql ON_ERROR_ROLLBACK and pgJDBC autosave wrap each statement in one, costing an extra round trip.

Timeouts¶

• statement_timeout caps one statement; lock_timeout caps the wait for a lock.
• idle_in_transaction_session_timeout kills sessions left in an open transaction, which otherwise hold locks and block VACUUM cleanup.
• transaction_timeout (PostgreSQL 17+) caps a whole transaction.
• All default to off; set them per role or per session rather than server-wide.

MVCC and VACUUM¶

Row versions¶

• An UPDATE writes a new row version and marks the old one dead; versions carry xmin/xmax, and each snapshot sees only the versions committed before it.
• Plain SELECTs never block or wait for row writers; writers (and FOR UPDATE) still lock rows against each other, and DDL's ACCESS EXCLUSIVE lock blocks even SELECT.
• The new version needs a new entry in every index unless the update is HOT, so wide-indexed, update-heavy tables pay heavy write amplification.

HOT updates¶

• A HOT update skips index maintenance when no indexed column changed and the new version fits on the same page.
• A lower fillfactor (e.g. 90) leaves free space per page to make HOT more likely on update-heavy tables.
• Indexing a frequently updated column (updated_at) disables HOT for every update that touches it; since PostgreSQL 16, columns covered only by BRIN indexes don't.

VACUUM and autovacuum¶

• VACUUM makes dead versions' space reusable but rarely shrinks the file; VACUUM FULL rewrites the table under an ACCESS EXCLUSIVE lock (pg_repack does it online).
• Autovacuum runs when dead rows exceed 50 + 20% of the table; since PostgreSQL 18 autovacuum_vacuum_max_threshold caps that at 100 million rows. Large tables still need a lower per-table scale factor.
• Anything holding an old snapshot stops cleanup everywhere: long transactions, idle-in-transaction sessions, abandoned replication slots, and replicas with hot_standby_feedback — the result is bloat.

Transaction ID wraparound¶

• Transaction IDs are 32-bit, so only ~2 billion are "in the past"; VACUUM freezes old rows so they stay visible.
• When a table's oldest unfrozen XID passes autovacuum_freeze_max_age (200 million), an anti-wraparound vacuum runs even if autovacuum is off.
• With fewer than 3 million XIDs left, the server refuses new write transactions until a manual VACUUM catches up — an outage, usually caused by something blocking vacuum for weeks.

Locking¶

Table lock levels¶

DDL locks and the lock queue¶

• Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock. If it waits behind a long query, every later query queues behind it — a brief outage from a "fast" migration.
• Set lock_timeout (a few seconds) on migrations and retry; rollout patterns in devops.md.

Safe schema changes¶

• ADD COLUMN with a constant default is metadata-only since PostgreSQL 11; a volatile default (clock_timestamp()) rewrites the table.
• Changing a column's type usually rewrites the table and its indexes; widening varchar(n) or switching to text doesn't.
• Add foreign keys and CHECKs as NOT VALID, then VALIDATE CONSTRAINT, which scans under SHARE UPDATE EXCLUSIVE without blocking writes.
• SET NOT NULL scans the table under ACCESS EXCLUSIVE, skipped if a validated CHECK (col IS NOT NULL) already proves it; since PostgreSQL 18, NOT NULL itself can be added NOT VALID.

Row locks and queue tables¶

• SELECT ... FOR UPDATE locks the rows it returns; FOR NO KEY UPDATE is weaker and doesn't block inserts of rows referencing them by foreign key.
• SKIP LOCKED lets workers claim different rows of a queue table without blocking; NOWAIT errors instead of waiting.
• Row locks live in the row header on disk, not in memory: there's no limit on rows locked and no lock escalation, but locking writes to the page.

Advisory locks¶

• Advisory locks are app-defined locks on a number, not a row — for leader election or job dedup without a table to lock.
• Session-level locks (pg_advisory_lock) persist until unlock or disconnect; transaction-level ones (pg_advisory_xact_lock) release at commit.
• Session-level locks break behind a transaction-mode pooler: the next transaction may run on a different server connection.

Deadlocks¶

• Two transactions each hold a lock the other needs; after deadlock_timeout (1 s) PostgreSQL detects the cycle and aborts one.
• Prevent with a consistent lock order (e.g. update rows sorted by ID) and short transactions.

Query planning and tuning¶

Reading EXPLAIN¶

• EXPLAIN ANALYZE executes the query (wrap writes in a rolled-back transaction); since PostgreSQL 18 it includes BUFFERS by default.
• Estimated vs actual rows far apart means stale or insufficient statistics — run ANALYZE or add extended statistics for correlated columns.
• Scan types: Seq Scan, Index Scan, Index Only Scan, and Bitmap Heap Scan (collects matches from one or more indexes, then reads pages in physical order).
• pg_stat_statements ranks normalized queries by total time — start tuning there.

Why an index isn't used¶

• Low selectivity: when a filter matches a large share of rows, the planner prefers a sequential scan, since one sequential pass beats many random heap reads.
• LIKE 'x%' uses a B-tree only with C collation or text_pattern_ops; LIKE '%x' needs a trigram GIN or GiST index (pg_trgm).
• A function or implicit cast on the column, and OR across columns without an index on each, also defeat it.
• Small tables: a seq scan of a few pages is cheaper than any index.

Join algorithms¶

Memory per query¶

• work_mem (4 MB) applies per sort or hash node, per parallel worker; hashes get work_mem × hash_mem_multiplier (2.0) — one query can use several multiples, times every connection.
• A sort or hash that exceeds it spills to disk (external merge in EXPLAIN ANALYZE); raise it per session for reporting queries, not globally.

Prepared statements and generic plans¶

• A prepared statement runs its first five executions with custom plans, then switches to a cached generic plan if its estimated cost isn't much higher.
• On skewed data a generic plan can be badly wrong for rare values; plan_cache_mode = force_custom_plan forces replanning.
• Drivers may prepare implicitly: pgJDBC after 5 executions of a statement, pgx by default.

Pagination and counting¶

• OFFSET still reads and discards every skipped row, so deep pages get slower; keyset pagination seeks by index — API side in backend.md.
• count(*) scans the table or an index (MVCC means no stored row count); for a rough number, read pg_class.reltuples.

SQL gotchas¶

Logical evaluation order¶

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT — why a SELECT alias works in ORDER BY but not in WHERE.

NULL and three-valued logic¶

• Any comparison with NULL — even NULL = NULL — is UNKNOWN, and WHERE drops it; use IS [NOT] NULL or IS [NOT] DISTINCT FROM.
• NOT IN with a NULL in the list returns no rows — use NOT EXISTS.
• COUNT(*) counts rows; COUNT(col), SUM, AVG, MIN/MAX skip NULLs, but array_agg/json_agg keep them.
• A UNIQUE column accepts many NULLs; UNIQUE NULLS NOT DISTINCT (PostgreSQL 15+) allows only one.

Window functions¶

• fn() OVER (PARTITION BY ... ORDER BY ...) computes per row without collapsing rows.
• ROW_NUMBER 1,2,3; RANK 1,1,3 (gaps); DENSE_RANK 1,1,2.
• The default frame with ORDER BY is RANGE ... CURRENT ROW, which includes all peer rows with equal sort keys — use ROWS for a true running total.

CTE materialization¶

• Since PostgreSQL 12, a non-recursive, side-effect-free CTE referenced once is inlined into the outer query; one referenced several times is materialized.
• MATERIALIZED / NOT MATERIALIZED override the default.

Join pitfalls¶

• A null-rejecting condition on the right table in WHERE (r.x = 1, unlike IS NULL or COALESCE) discards the NULL-extended rows and turns a LEFT JOIN into an inner join; put it in ON.
• Joining a parent to two one-to-many children multiplies rows (fan-out) and inflates SUM/COUNT; aggregate each child in a subquery first.
• EXISTS / NOT EXISTS are semi- and anti-joins: they never duplicate rows, unlike JOIN + DISTINCT.

Upsert and MERGE¶

• INSERT ... ON CONFLICT (...) DO UPDATE replaces a racy check-then-write; it needs a unique index matching the conflict target, and EXCLUDED.col holds the proposed row.
• ON CONFLICT DO NOTHING ... RETURNING returns nothing for conflicting rows, so "get or create" needs a follow-up SELECT.
• MERGE (PostgreSQL 15+) doesn't handle concurrent inserts like ON CONFLICT does — it can fail with a unique violation.
• MERGE ... RETURNING exists since PostgreSQL 17; OLD/NEW in RETURNING (any DML) since 18.

WAL and durability¶

Write-ahead log¶

• A commit is durable once its WAL record is fsynced; dirty data pages are flushed later (checkpoints, background writer), never before their WAL.
• Crash recovery only redoes WAL from the last checkpoint; uncommitted rows stay invisible by their commit status, so there's no undo phase.
• Group commit batches many commits into one fsync.

Checkpoints¶

• A checkpoint runs every checkpoint_timeout (5 min) or when WAL reaches max_wal_size (1 GB), whichever comes first.
• The first change to a page after a checkpoint logs the whole page (full_page_writes) to survive torn writes, so frequent checkpoints inflate WAL volume.
• Longer intervals mean less WAL and I/O but slower crash recovery.

Commit durability levels¶

• synchronous_commit picks how far a commit waits: off (doesn't wait for the local flush), local, remote_write, on (standby fsync), remote_apply (visible on standby); without synchronous_standby_names, every mode except off just waits for the local flush.
• off (asynchronous commit) can lose up to 3 × wal_writer_delay (600 ms by default) of commits on a crash, but never corrupts data — settable per transaction for low-value writes.
• fsync = off is different: a crash can corrupt the cluster.

Backups and PITR¶

• Logical backups (pg_dump): portable across versions, slow to restore at scale. Physical (pg_basebackup, snapshots): fast restore, same major version.
• PITR = physical base backup + archived WAL replayed to a chosen moment. Replicas are not backups — they replicate mistakes too.
• Incremental backups (pg_basebackup --incremental, PostgreSQL 17+) copy only changed blocks; pg_combinebackup reconstructs a full one.

Replication and high availability¶

Physical vs logical replication¶

• Physical (streaming): ships WAL bytes; an exact, read-only copy of the whole cluster on the same major version, except UNLOGGED tables, which are empty on standbys.
• Logical: row changes per table, works across major versions and subsets — used for zero-downtime upgrades and CDC.
• Logical replication doesn't copy DDL or sequence values, and UPDATE/DELETE need a replica identity (usually the primary key).

Synchronous replication¶

• synchronous_standby_names lists standbys a commit waits for: FIRST n (...) by priority or ANY n (...) as a quorum.
• If the required standbys are down, commits hang rather than fail — list more candidates than n.
• Sync vs async trade-offs in distributed.md.

Replication slots¶

• A replication slot keeps WAL (and, for logical slots, old catalog rows) until its consumer confirms it — a dead consumer fills the disk.
• Cap it with max_slot_wal_keep_size (PostgreSQL 13+, unlimited by default) or idle_replication_slot_timeout (PostgreSQL 18+, off by default); either invalidates the slot.
• Logical slots can be synced to a standby (PostgreSQL 17+), so CDC survives a failover.

Read replicas¶

• Async hot standbys lag, so a read after a write may miss it — route read-your-writes to the primary.
• Replaying a VACUUM that removes rows a standby query still needs cancels that query once replay has been held back for max_standby_streaming_delay (30 s) — a budget for the replay lag, not a per-query grace period.
• hot_standby_feedback avoids those cancellations but holds back vacuum on the primary, causing bloat.

Failover¶

• PostgreSQL has no built-in automatic failover; Patroni and similar tools elect a leader through a consensus store (etcd) and fence the old primary.
• A demoted primary may have WAL the new one never got; pg_rewind discards it so the old primary can rejoin as a standby.
• With async replication, commits acknowledged but not yet shipped are lost on failover.

Architecture and scaling¶

Process model and memory¶

• Each connection is a separate backend process (several MB), so thousands of direct connections hurt — pool them.
• shared_buffers (~25% of RAM is a common start) caches pages, and the OS page cache holds them again; effective_cache_size is only a planner hint.
• Since PostgreSQL 18, sequential scans, bitmap heap scans, and vacuum read through asynchronous I/O (io_method = worker by default; io_uring on Linux).

Connection pooling¶

• PgBouncer session mode keeps SET and session advisory locks; transaction mode scales further but loses session state.
• Protocol-level prepared statements work in transaction mode since PgBouncer 1.21 when max_prepared_statements > 0 (default 0 before 1.24, now 200); SQL PREPARE still doesn't.
• App-side pool sizing in backend.md.

Table partitioning¶

• Range (most common, by date), list, or hash; partition pruning skips irrelevant partitions only when the query filters on the partition key.
• Dropping an old partition is a cheap metadata change, unlike a bloating bulk DELETE, but takes an ACCESS EXCLUSIVE lock on the parent — DETACH PARTITION CONCURRENTLY (PostgreSQL 14+) first avoids that.
• Unique constraints must include the partition key, so a global unique ID across partitions isn't enforced.

Materialized views¶

• A materialized view stores a query result for fast reads that stays stale until refreshed — no incremental refresh built in.
• REFRESH MATERIALIZED VIEW CONCURRENTLY doesn't block reads but needs a unique index and is slower.

Schema design¶

Normalization¶

• 1NF atomic values → 2NF no partial-key dependency → 3NF no transitive dependency through non-key columns: each fact stored once.
• BCNF is stricter: every nontrivial dependency's determinant is a superkey; 3NF still allows it when the dependent column is part of a candidate key.
• Denormalize measured hot paths only (copied columns, counters, materialized views), accepting harder writes.

Keys and sequences¶

• Prefer GENERATED ALWAYS AS IDENTITY (PostgreSQL 10+) over serial; use bigint, since int runs out at ~2.1 billion.
• Sequences are non-transactional: nextval is never rolled back, so IDs have gaps.
• UUIDv4 scatters inserts across the B-tree; UUIDv7 is time-ordered and native via uuidv7() since PostgreSQL 18 — ID schemes in distributed.md.

Constraints and soft deletes¶

• FOREIGN KEY, CHECK, and UNIQUE are the last line of defense — application checks alone race.
• Exclusion constraints (EXCLUDE USING gist (room WITH =, during WITH &&)) forbid overlapping bookings; since PostgreSQL 18 a primary key or unique constraint can say WITHOUT OVERLAPS.
• Soft deletes (deleted_at) keep rows for undo and audit; a partial unique index (WHERE deleted_at IS NULL) frees the value for reuse, so an undelete can then conflict.

JSONB¶

• jsonb is parsed binary: faster to query and indexable, but drops duplicate keys (last wins), key order, and whitespace; json keeps the text as is.
• A GIN index with the default jsonb_ops supports key-existence and containment; jsonb_path_ops is smaller and faster but supports only containment and path queries.
• Updating one key rewrites the whole value; columns you filter or join on belong in real columns.

Row size and TOAST¶

• Pages are 8 KB; values that push a row past ~2 KB are compressed or moved out of line by TOAST, up to 1 GB per field.
• SELECT * fetches and decompresses every toasted column; select only what you need.
• Generated columns are virtual (computed on read) by default since PostgreSQL 18; STORED computes on write and takes space.

Common type choices¶

• timestamptz stores an instant in UTC and stores no time zone; timestamp ignores zones entirely — time handling in backend.md.
• text and varchar(n) perform the same; char(n) pads with spaces.
• numeric is exact for money; float8 rounds, and the money type depends on lc_monetary.