POSTGRESQL · MEDIUM CODEX
PostgreSQL Interview Questions — Medium
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=$2detecting 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:
datfrozenxidage 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.
₹399 India / $9 International · One-time settlement · Zero subscription