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 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
3. 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