Interview Q&A / PostgreSQL

1. 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
2. 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
3. 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
4. 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