POSTGRESQL · MEDIUM CODEX

PostgreSQL Interview Questions — Medium

Q: What is transaction ID wraparound and why does it cause outages?

xid is 32-bit comparing modulo wraparound space (2^31 per epoch); Postgres must freeze rows (mark permanently-committed) before old xids risk future comparison ambiguity. If wraparound approaches, aut

Real-world mechanisms, state management, edge cases, performance trade-offs, and practical coding.

25 Theory Questions5 Free Model Answers

THEORY QUESTIONS & SOLUTIONSShowing 5 of 25 questions

Q1Explain MVCC in Postgres and how it avoids read-write blocking.

Every row version carries xmin/xmax transaction ids; readers see snapshots where versions are committed-visible - writers create NEW versions instead of mutating in place.
Result: readers never block writers, writers never block readers.
Cost: dead tuple accumulation requiring VACUUM; long transactions hold back horizon causing bloat.

Q2What do xmin/xmax/cmin/cmax hide and how are they used practically?

System columns on every row: xmin (creating txid), xmax (deleting/locking txid).
Practical uses: optimistic concurrency checks (WHERE id=$1 AND xmin=$2 detecting concurrent modification without version column), debugging visibility issues.
Interview favorite: "how do you detect a row changed between SELECT and UPDATE" answered via xmin.

Q3What is VACUUM's full job and what is the free space map?

Removes dead tuples (marking space reusable in FSM), freezes old xids preventing wraparound, updates visibility map enabling index-only scans.
Aggressive vs regular vacuum thresholds; autovacuum workers tuned per table.
Long-running transactions pin the xmin horizon → bloat explosion - monitoring oldest-xid/backed-up-tuples essential.

Q4What is transaction ID wraparound and why does it cause outages?

xid is 32-bit comparing modulo wraparound space (~2^31 per epoch); Postgres must freeze rows (mark permanently-committed) before old xids risk future comparison ambiguity.
If wraparound approaches, autovacuum goes aggressive anti-wraparound mode; at limit, system REFUSES writes (single-user mode cleanup) - the famous outage class. Monitoring: datfrozenxid age alerts well before danger.

Q5Compare B-tree, GIN, GiST, BRIN and Hash indexes with use cases.

B-tree: equality/range/sort default.
GIN: inverted index for jsonb containment, arrays, full-text - fast lookups, slower updates.
GiST: extensible R-tree family - ranges (tstzrange overlap exclusion constraints), geometric, kNN distance ordering.
BRIN: block-range summaries for huge append-only time-series - tiny, fast builds.
Hash: equality-only, WAL-logged since v10 - rarely wins over btree. Exclusion constraints (GiST) power booking-no-overlap rules declaratively.

Unlock the remaining 20 PostgreSQL (Medium) questions

You've completed the 5 free sample questions. Get unrestricted lifetime access to every question, model answer, implementation challenge, and all 27+ technologies for a single payment.

Unlock Everything — Already Enrolled? Sign In

One-time settlement () · Lifetime access · Zero subscription

CROSS-DIFFICULTY NAVIGATION

Continue Preparing PostgreSQL

EASY LEVEL

Foundations & Core Concepts

HARD LEVEL

Internals & System Architecture