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 the difference between VACUUM and VACUUM FULL?
VACUUM marks dead space reusable and runs alongside normal reads and writes, but usually does not shrink the file. VACUUM FULL rewrites the table into a new compact file, returning space to the OS, but takes an ACCESS EXCLUSIVE lock for the whole run.
BeginnerQuick answer1 min read - What is table bloat in PostgreSQL and how do you detect it?
Bloat is space in a table or index taken by dead row versions that vacuum has marked reusable but not returned to the operating system. It makes scans slower and files larger. Check n_dead_tup in pg_stat_user_tables or use pgstattuple.
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 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