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 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 - Why does PostgreSQL sometimes show Index Only Scan but still read the table?
An index does not store row visibility. PostgreSQL checks the visibility map, and for pages not marked all-visible it must fetch the heap row. EXPLAIN ANALYZE shows this as Heap Fetches, which VACUUM reduces.
AdvancedQuick answer1 min read