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 happens when you run ALTER TABLE ... ALTER COLUMN TYPE on a large table?
In most cases PostgreSQL rewrites the whole table while holding an ACCESS EXCLUSIVE lock, so reads and writes wait. Only some changes, such as widening varchar or dropping a length limit, skip the rewrite.
IntermediateQuick answer1 min read - Why can a quick ALTER TABLE freeze every query on a busy table?
ALTER TABLE needs a strong lock that waits behind any running query on the table, and every new query then queues behind the ALTER. One long transaction can therefore stall the entire table. Set lock_timeout so the DDL gives up quickly.
AdvancedQuick answer1 min read