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 index types does PostgreSQL support, and why is my index sometimes not used?

   B-tree is the default and handles equality, ranges and sorting. GIN indexes values that contain many items, like JSONB, arrays and full-text search. BRIN is a tiny index for huge tables whose rows are stored in a natural order, like time-series data. The planner skips an index when a full scan looks cheaper, or when the query is written in a way the index cannot match.

   IntermediateIn-depth4 min read
3. 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