Interview Q&A / PostgreSQL

1. How do you find which query is blocking another in PostgreSQL?

   Look at pg_stat_activity for sessions with wait_event_type Lock, and use pg_blocking_pids(pid) to get the PIDs holding the lock. Then inspect those sessions and, if needed, cancel or terminate the blocker.

   IntermediateQuick answer1 min read
2. How does FOR UPDATE SKIP LOCKED help build a job queue in PostgreSQL?

   SKIP LOCKED makes a query ignore rows another transaction has already locked instead of waiting. Several workers can each grab different pending jobs at the same time without blocking or double-processing.

   IntermediateQuick answer1 min read
3. What are advisory locks in PostgreSQL and when would you use one?

   Advisory locks are application-defined locks on an arbitrary number, managed by PostgreSQL but not tied to any table or row. Use them to make sure only one process runs a task, like a nightly report job, at a time.

   IntermediateQuick answer1 min read
4. What does CREATE INDEX CONCURRENTLY do and what are its trade-offs?

   It builds the index without blocking writes to the table, by scanning the table in multiple passes. It takes longer, cannot run inside a transaction block, and can leave an INVALID index behind if it fails.

   IntermediateQuick answer1 min read
5. 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