Interview Q&A / PostgreSQL

1. 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
2. How does PostgreSQL implement transaction isolation levels?

   PostgreSQL offers Read Committed (the default), Repeatable Read and Serializable. Dirty reads never happen, even if you ask for Read Uncommitted. Read Committed takes a new snapshot per statement, Repeatable Read keeps one snapshot for the whole transaction, and Serializable adds checks that abort a transaction if the result could not have come from running transactions one at a time.

   AdvancedIn-depth4 min read
3. Is it safe to run EXPLAIN ANALYZE on an UPDATE or DELETE?

   No, not without care. EXPLAIN ANALYZE really executes the statement, so the rows are changed. Wrap it in BEGIN and ROLLBACK to inspect the plan and timings without keeping the change.

   BeginnerQuick answer1 min read
4. What does SELECT ... FOR UPDATE do in PostgreSQL?

   It locks the selected rows until the transaction ends, so other transactions cannot update, delete or lock them meanwhile. Use it to read a value and change it safely, like a balance or a seat count.

   BeginnerQuick answer1 min read
5. What is a DEFERRABLE constraint in PostgreSQL and when is it needed?

   A deferrable constraint can be checked at COMMIT instead of after each statement. This lets you insert rows that reference each other, or swap unique values, as long as everything is consistent by the end of the transaction.

   AdvancedQuick answer1 min read