Interview Q&A / PostgreSQL

1. 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
2. What is a deadlock in PostgreSQL and how do you avoid it?

   A deadlock occurs when two transactions each hold a lock the other needs, so neither can proceed. PostgreSQL detects it after deadlock_timeout (1 second by default) and aborts one transaction. Prevent it by locking rows in a consistent order.

   BeginnerQuick answer1 min read
3. What is the difference between lock_timeout and statement_timeout?

   lock_timeout cancels a statement that waits too long to acquire a lock. statement_timeout cancels any statement whose total running time exceeds the limit, including time spent working.

   BeginnerQuick answer1 min read
4. What is the difference between VACUUM and VACUUM FULL?

   VACUUM marks dead space reusable and runs alongside normal reads and writes, but usually does not shrink the file. VACUUM FULL rewrites the table into a new compact file, returning space to the OS, but takes an ACCESS EXCLUSIVE lock for the whole run.

   BeginnerQuick answer1 min read
5. 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
6. 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
7. 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
8. 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
9. 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
10. 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