Interview Q&A / PostgreSQL
PostgreSQL interview questions
Indexes, EXPLAIN, transactions and isolation, JSONB and the Postgres specifics backend interviews probe.
Prefer rapid revision? Practise PostgreSQL short interview answers →
- What do VACUUM and autovacuum do in PostgreSQL?
Because of MVCC, updates and deletes leave dead row versions behind. VACUUM marks that space as reusable, updates the visibility map and freezes old transaction IDs so they cannot wrap around. Autovacuum is a background process that runs VACUUM and ANALYZE automatically when enough rows have changed.
IntermediateIn-depth3 min read - What is MVCC in PostgreSQL, and why don't readers block writers?
MVCC (Multi-Version Concurrency Control) means PostgreSQL keeps several versions of a row. An UPDATE writes a new version instead of overwriting the old one, so a reader keeps seeing the version that was valid when its snapshot was taken while the writer works on the new one. Readers never wait for writers, and writers never wait for readers.
IntermediateIn-depth4 min read - What are xmin and xmax on a PostgreSQL row?
They are hidden system columns recording the transaction ID that created the row version (xmin) and the one that deleted or replaced it (xmax). MVCC uses them to decide which versions a given snapshot may see.
IntermediateQuick answer1 min read - Why can a long-running or idle-in-transaction session make PostgreSQL slow?
Vacuum can only remove dead rows that no open transaction might still need. One session that sits idle in transaction holds back the cleanup horizon for the whole database, so bloat grows and queries slow down.
IntermediateQuick answer1 min read - What is a HOT update in PostgreSQL and how does fillfactor help?
A HOT (heap-only tuple) update happens when no indexed column changes and the new row version fits on the same page, so no new index entries are needed. Lowering fillfactor leaves free space on each page to make that more likely.
AdvancedQuick answer1 min read - What is transaction ID wraparound in PostgreSQL and how is it prevented?
Transaction IDs are 32-bit numbers, so after about two billion, old ones would look like the future and rows would vanish. VACUUM freezes old row versions to prevent this, and PostgreSQL forces aggressive vacuums and finally stops accepting writes if it falls too far behind.
AdvancedQuick answer1 min read