This page needs a newer browser to run its tools. You can still read everything here.

Concurrency, locks and safe migrations interview questions

Interview questions on concurrency, locks and safe migrations: warm-ups, then what each harder question tests and how a good answer starts.

15 questions: 3 warm-ups and 12 with model answers · Levels: Fresher to staff

Warm-up questions

Short questions with short answers, each answered in full.

Fresher to mid

What isolation levels does PostgreSQL implement, and which one is the default?

Answered in the lesson Isolation in PostgreSQL: RC, RR and SSI
Fresher to mid

When does a PostgreSQL transaction fail with SQLSTATE 40001, and how should the application handle it?

Answered in the lesson Isolation in PostgreSQL: RC, RR and SSI
Fresher to mid

How are a row lock in PostgreSQL and a lock on an advisory key different, and why does each one exist?

A row lock is taken on a row of a table (a SELECT ... FOR UPDATE, a DELETE, or an UPDATE takes one), and the lock lives in the row's xmax, not in a lock table. Its purpose is to let one writer at a time change the row while readers go on reading its old version through MVCC. An advisory lock is a lock on a number of the application's choosing; no row has to exist. Its purpose is to coordinate things no row represents: a nightly report runner that must run once, a leader election, or a worker pool whose jobs are not rows at all. The two live in different places and never block each other.
From the lesson Row locks, SKIP LOCKED queues, advisory locks

Questions with model answers

What each question tests, the first step of a good answer and the follow-ups to expect.

Mid to senior

When would you choose SERIALIZABLE over REPEATABLE READ, and what trade-off are you accepting?
What it tests

Telling row-level conflicts from read/write dependency tracking, naming the invariants that need the stronger level, and pricing the retries it brings.

First step

Ask what rule the transaction must protect. If two transactions can each read overlapping data, decide from it and then write different rows, REPEATABLE READ lets both commit; only SERIALIZABLE, or an explicit lock that turns the read into a conflict, stops it.
Follow-ups
- The team says 40001 errors are too common under SERIALIZABLE. What do you check first?
- Before switching an endpoint to SERIALIZABLE, how do you measure what it will cost?
- How does SSI track which rows a transaction has read?
From the lesson Isolation in PostgreSQL: RC, RR and SSI
Model answer, Pro. Model answers open with any pass; prices are in the notice on this page.
Opening the model answers with your pass…
Model answers open with any MySmartCoPilot pass.
Pro pass · 1 week₹79$2.99
30 days₹179$5.99
1 year₹1,699$45
One payment; a pass never renews. The price shown is the total you pay: no fees or charges are added at checkout.
Passes can’t be bought right now, so the model answers can’t be opened at the moment. If you have a pass, enter your licence key.
Choose a pass I have a licence key
It also opens every other in-depth part in Learn and every Pro tool. Sales are final, except where the law gives you a right to withdraw, for example in the EU and the UK (refund policy).
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Senior to staff

A SERIALIZABLE transaction is failing with 40001 much more often than expected. How do you investigate?
What it tests

Reading the reason code of a serialization failure, reading SIReadLocks in pg_locks, knowing how predicate locks are promoted to coarser ones, and telling real conflicts from false positives.

First step

Read the DETAIL line of each 40001: its reason code says when SSI cancelled the transaction. Then read pg_locks for SIReadLock rows while the transactions run. A relation-level lock where you expected tuple or page locks means the read set is wider than the query needs, often a sequential scan, and some conflicts may be false positives.
Follow-ups
- You find SIReadLocks at the relation level rather than tuple level. What causes that, and can you narrow them?
- Two batch jobs conflict even though they process disjoint key ranges. How is that possible?
- Would reducing the transaction's read set help, and what is the trade-off?
From the lesson Isolation in PostgreSQL: RC, RR and SSI
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

A queue table jobs holds about 200 live queued rows and 40 million done ones. Workers claim rows with FOR UPDATE SKIP LOCKED, and in the last week the claim latency has climbed from a few milliseconds to about a second. What do you measure, in what order, and what do you change?
What it tests

Reading the claim's plan, choosing an index that leaves done rows out, keeping vacuum ahead of the queue, and deciding what happens to the done rows.

First step

Run the claim once under EXPLAIN (ANALYZE, BUFFERS) inside a transaction you roll back, and read which index it scans, how many rows its filter removes and how many buffers it touches. A claim that returns one row while it touches thousands of buffers is walking past done rows, and the plan shows where.
Follow-ups
- Why does a plain index on jobs(id) still slow down as the done rows pile up, even if the claim takes two?
- What settings would you set on the queue table to make autovacuum work hard enough on it?
- When would you move done rows out to a separate table or partition, and when would you delete them outright?
From the lesson Row locks, SKIP LOCKED queues, advisory locks
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Senior to staff

A service guards its nightly report runner with pg_try_advisory_lock(42). Since the team moved to PgBouncer in transaction mode, some nights the runner refuses to start on any server and other nights two runners run at once. What is going on, and how do you fix it without losing the guard?
What it tests

Knowing the lifetime of a session advisory lock, how a transaction pooler reuses one server connection between clients, and that pg_try_advisory_xact_lock is the pooler-safe variant; detecting the leak and planning a safe migration.

First step

List who holds advisory locks with SELECT * FROM pg_locks WHERE locktype = 'advisory', joined to pg_stat_activity on pid. A key held by a pooled backend that sits idle between transactions, while no runner is working, is the one to explain first.
Follow-ups
- How would you tell, from pg_locks and pg_stat_activity, which pooled connections are carrying leaked locks?
- Why does pg_try_advisory_xact_lock(42) not leak the same way, even under a transaction pooler?
- If the application still needs a lock across several transactions, what is a safe way to carry it under pooling?
From the lesson Row locks, SKIP LOCKED queues, advisory locks
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

An audit team wants every webhook event delivered to a background worker. The on-call wants a lightweight channel and asks "can we just use LISTEN/NOTIFY, with the payload as the JSON event?". Walk through what you would and would not use NOTIFY for in this design, and what you would build around it.
What it tests

The delivery rules of NOTIFY (a payload size limit, sent at commit, folded, not durable), when it has to sit beside a persisted queue, and the pitfalls of listeners that reconnect or drop.

First step

Start from what must never be lost. List the moments where an event could vanish between the webhook arriving and a worker finishing it (a rollback, a listener that is offline, a payload that is too large), and check each delivery rule of NOTIFY against that list before you choose what carries the event.
Follow-ups
- What would happen if the worker stayed on NOTIFY alone, and the listener disconnected for ten minutes?
- A payload of 20 KB is proposed. What rule stops it, and what does the design do instead?
- The release notes of PostgreSQL 19 (still in preparation) include a change to NOTIFY. Why does it matter for an application with many channels?
From the lesson Row locks, SKIP LOCKED queues, advisory locks
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 5 minutes
Mid to senior

A deploy's ALTER TABLE payments ADD COLUMN gst_rate numeric has no lock timeout, and after 40 s, 180 checkout queries wait behind it. pg_blocking_pids() names two holders: a console session idle in transaction for 3 hours and a report with 6 minutes left. What do you do now, and what changes for the next deploy?
What it tests

Telling the holders from the sessions queued behind a migration, choosing which session to stop and with which function while checkout is down, and the settings that keep the next wait to seconds.

First step

Restore checkout first and fix the cause afterwards. Confirm the picture: pg_blocking_pids() for a few checkout sessions should name only the migration, and for the migration the two holders. Then read each holder's state, xact_start and last query in pg_stat_activity before you signal anything.
Follow-ups
- Which session do you signal first, and why that one?
- What do pg_cancel_backend() and pg_terminate_backend() each do to the console session?
- Which settings would have kept this stall to a few seconds, and on which roles?
From the lesson Table locks, lock queues, deadlocks, timeouts
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Mid to senior

The server log shows "deadlock detected" with two process IDs and two transaction IDs. One of your application services reports an error at the same moment. Explain what happened and how you would prevent it.
What it tests

Reading a deadlock report: what the cycle was, which transaction PostgreSQL cancels and when, and what the application changes so that the same cycle cannot form again.

First step

Two transactions each held a lock the other needed, forming a cycle. After deadlock_timeout (1 s by default), PostgreSQL detected the cycle and aborted one transaction with SQLSTATE 40P01.
Follow-ups
- The log shows the queries. How do you tell which rows each was trying to lock?
- Why did the application see an error, not just a retry?
- How do you ensure all code paths update rows in a consistent order?
From the lesson Table locks, lock queues, deadlocks, timeouts
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 4 minutes
Senior to staff

A team asks you to set timeouts for their PostgreSQL 18 database. They have application queries, nightly batch jobs and weekly migrations. Which timeout settings do you recommend, at what levels, and why?
What it tests

Knowing the four main timeout settings, which is appropriate for each workload, and how to apply them per role.

First step

Start from what each workload must never do: a web request must not run for minutes, a migration must not stall traffic while it waits for a lock, and a forgotten transaction must not keep its locks for hours. Match each risk to the setting that bounds that kind of wait, then decide where each value is set so that the batch jobs keep limits of their own.
Follow-ups
- The batch role runs a 3-hour job. How do you prevent statement_timeout from killing it?
- PostgreSQL 17+ adds transaction_timeout. When would you use it instead of the others?
- How do you monitor lock waits without blocking anything?
From the lesson Table locks, lock queues, deadlocks, timeouts
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

A table serves 2,000 reads a second. From 01:00 IST a nightly export holds a transaction open on it for 25 minutes. A migration starting at 01:10 uses a 5 s lock timeout and retries every 30 s for an hour, so checkout stalls for 5 s repeatedly. Redesign the timeout, retries and schedule.
What it tests

Sizing a lock timeout from what users tolerate, bounding retries, finding the blocker before trying, and choosing a time or an action that removes it.

First step

Work out the harm of the current policy: 5 s stalled out of every 35 s is about 14 % of the hour. Then ask what holds the lock: query pg_stat_activity for the export's transaction and pg_locks for who waits for what, before the migration tries again.
Follow-ups
- What timeout would you pick for a page that tolerates a 2 s delay, and why not the longest one that works?
- How does the migration know the table is quiet before it asks for the lock?
- When is cancelling the export the right move, and who decides?
- What do you alert on if the migration gives up?
From the lesson Zero-downtime schema migrations
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

payments has 900 million rows and about 40 million writes an hour. Product wants a foreign key to accounts, and some payments may point at deleted accounts. Plan the change with the lock each step takes, the first thing that can fail, and how you roll back.
What it tests

Splitting a constraint into NOT VALID and VALIDATE, handling existing violations, indexing the referencing column, and reasoning about what a long validation costs.

First step

Write down the steps and the lock of each before touching anything: an index on the referencing column built concurrently, the constraint added NOT VALID, a query for orphan rows, then VALIDATE CONSTRAINT. Say which step could fail and what state the table is left in.
Follow-ups
- Validation finds 12,000 orphan rows. What now, and is the constraint enforcing anything meanwhile?
- Why does the payments column need an index, and when do you build it?
- Validation will run for hours. What else does it affect on the server?
- How does the plan change if payments is partitioned?
From the lesson Zero-downtime schema migrations
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Senior to staff

orders.id is an integer with 1.9 billion of its 2.147 billion values used, about 6 weeks of headroom, on a 600 GB table that never stops taking writes. Other tables reference it. Plan the move to bigint, and say what you do if the limit gets close before you finish.
What it tests

Expand and contract applied to a primary key, sizing disk and replica load for a backfill, ordering the work across referencing tables, and a stopgap for a deadline.

First step

Estimate the cost before choosing: a batched backfill roughly doubles the table file until vacuumed, so you need about 600 GB of spare disk plus WAL for replicas. Then list the tables that reference the key, because each needs its own expand and contract.
Follow-ups
- How do you make the new column the primary key without a long lock?
- What happens to the sequence or identity column that hands out the ids?
- Which tables do you convert first, the referenced one or the referencing ones, and why?
- What stopgap buys time if the deadline is closer than the plan?
From the lesson Zero-downtime schema migrations
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 3 minutes
Mid to senior

An ORM generated ALTER TABLE events ALTER COLUMN source SET NOT NULL for a table of 300 million rows, and the migration tool wraps each file in a transaction. What do you ship on PostgreSQL 17, and what changes on 18?
What it tests

Knowing that SET NOT NULL scans under ACCESS EXCLUSIVE, the CHECK route and the PostgreSQL 18 route, and keeping the tool's transaction from holding locks across steps.

First step

Replace the one statement with several, each in its own migration or transaction. First make sure no row has a null, then add the constraint so that the statement which closes the table has no scan left to do.
Follow-ups
- What do you do when validation fails because some rows are null?
- Which steps need a lock timeout and a retry, and which do not?
- How do you prove the final SET NOT NULL did not scan?
- What stays behind on 17 that you have to clean up?
From the lesson Zero-downtime schema migrations
Model answer, Pro.
In the answer:
- Model answer
- Follow-up answers
- Common mistakes
- Rubric
- Variants
- about 2 minutes

The lessons of this module

Isolation in PostgreSQL: RC, RR and SSI Free lesson, Pro answers: 10 minutes
Row locks, SKIP LOCKED queues, advisory locks Free core, Pro depth: 6 of 20 minutes free
Table locks, lock queues, deadlocks, timeouts Free core, Pro depth: 5 of 15 minutes free
Zero-downtime schema migrations Free core, Pro depth: 5 of 15 minutes free

All PostgreSQL in Depth interview questions · The PostgreSQL in Depth track

Warm-up questions

What isolation levels does PostgreSQL implement, and which one is the default?

When does a PostgreSQL transaction fail with SQLSTATE 40001, and how should the application handle it?

How are a row lock in PostgreSQL and a lock on an advisory key different, and why does each one exist?

Questions with model answers

When would you choose SERIALIZABLE over REPEATABLE READ, and what trade-off are you accepting?

A SERIALIZABLE transaction is failing with 40001 much more often than expected. How do you investigate?

A queue table jobs holds about 200 live queued rows and 40 million done ones. Workers claim rows with FOR UPDATE SKIP LOCKED, and in the last week the claim latency has climbed from a few milliseconds to about a second. What do you measure, in what order, and what do you change?

A service guards its nightly report runner with pg_try_advisory_lock(42). Since the team moved to PgBouncer in transaction mode, some nights the runner refuses to start on any server and other nights two runners run at once. What is going on, and how do you fix it without losing the guard?

The server log shows "deadlock detected" with two process IDs and two transaction IDs. One of your application services reports an error at the same moment. Explain what happened and how you would prevent it.

A team asks you to set timeouts for their PostgreSQL 18 database. They have application queries, nightly batch jobs and weekly migrations. Which timeout settings do you recommend, at what levels, and why?

A table serves 2,000 reads a second. From 01:00 IST a nightly export holds a transaction open on it for 25 minutes. A migration starting at 01:10 uses a 5 s lock timeout and retries every 30 s for an hour, so checkout stalls for 5 s repeatedly. Redesign the timeout, retries and schedule.

payments has 900 million rows and about 40 million writes an hour. Product wants a foreign key to accounts, and some payments may point at deleted accounts. Plan the change with the lock each step takes, the first thing that can fail, and how you roll back.

orders.id is an integer with 1.9 billion of its 2.147 billion values used, about 6 weeks of headroom, on a 600 GB table that never stops taking writes. Other tables reference it. Plan the move to bigint, and say what you do if the limit gets close before you finish.

An ORM generated ALTER TABLE events ALTER COLUMN source SET NOT NULL for a table of 300 million rows, and the migration tool wraps each file in a transaction. What do you ship on PostgreSQL 17, and what changes on 18?