PostgreSQL Serializable Interview Questions: Write Skew, Serialization Failures, and Retries

Two on-call operators each see that another operator is available, then both leave duty. Their transactions update different rows, so a same-row write conflict never has to occur. The shared rule—someone must remain on duty—has still been broken.

Strong PostgreSQL Serializable interview questions ask you to trace that dependency and explain recovery. Serializable execution protects the history of committed transactions, but the application must express its business decision inside the transaction and rerun that decision when an attempt fails.

Evidence boundary: PostgreSQL 18 official documentation was checked September 21, 2026. Official facts are labeled and linked. The duty-roster scenario, retry design, and interview follow-ups are original exercises; no candidate reports are used. SQL was not executed against PostgreSQL. A local Python model checks the scenario's business logic, not PostgreSQL's Serializable Snapshot Isolation implementation.

What does Serializable guarantee?

Official fact: PostgreSQL Serializable aims to make committed concurrent transactions equivalent to some serial execution. It builds on snapshot behavior and monitors dependencies that could produce an invalid serial history. An attempt can fail and require retry. PostgreSQL Repeatable Read prevents phantom reads but still permits serialization anomalies. Transaction isolation

Interview interpretation: “Serializable” does not mean the server physically runs every transaction one at a time. It means the committed result must admit an ordering in which each transaction could have run alone.

That guarantee cannot invent a missing business rule. If the application unconditionally turns every operator off duty, serial execution can still produce an empty roster. The transaction must check whether removing the operator would preserve coverage.

Nor should you say every user request succeeds. Rejecting a database attempt can prevent the invalid combined outcome. The application decides whether to retry, return a business rejection after fresh reads, or report temporary contention after its retry budget is exhausted.

How can write skew occur when different rows are updated?

Original scenario: Table duty contains one row each for operators A and B in shift 7. Both have on_duty = true. Each leave-duty transaction counts active operators and proceeds only if the count exceeds one.

Interleaving step	What each transaction knows or changes
A reads the active count	A sees two and decides leaving is allowed.
B reads before A commits	B also sees two and makes the same decision.
A updates A's row	A sets its own status to false.
B updates B's row	B sets its own status to false.
Both changes become durable	The combined state has zero active operators.

This is the unsafe schedule being examined, not a transcript of a PostgreSQL run. The writes target different rows, but each decision depends on data changed by the other transaction.

Try both serial orders. If A completes first, B's subsequent count is one, so B must remain. If B completes first, A must remain. Zero active operators is inconsistent with both valid serial orders.

A local Python fixture evaluated those two serial orders and the overlapping-snapshot decision model. Both serial orders retain one operator; the unsafe combined decisions retain zero. That validates the counterexample's logic without claiming to simulate the database's conflict detector.

What would the two-session SQL exercise contain?

Create the fixture in a disposable test database, with a primary key on (shift_id, operator_id) and Boolean on_duty. Seed A and B as active. Then use two independent connections and pause both after their count query.

An original transaction outline for A is:

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) AS active_count
FROM duty
WHERE shift_id = 7 AND on_duty;
-- Application proceeds only if active_count > 1.
UPDATE duty
SET on_duty = false
WHERE shift_id = 7 AND operator_id = 'A';
COMMIT;

B uses the same outline with operator ID B. The comment is an application branch that must actually be implemented; the SQL block alone does not conditionally suppress the update.

Expected interpretation from the documented contract: With both decisions inside overlapping Serializable transactions, the invalid combined history cannot successfully commit as shown. A serialization failure must be handled. Do not promise which transaction fails or which statement reports the failure for every possible interleaving.

For a Repeatable Read comparison, restore the initial fixture and rerun the overlapping reads before either update commits. Keep that comparison separate from a run where one transaction finishes before the other's first read; those schedules test different conditions.

The acceptance condition is coverage after successful commits, not a particular error-message sentence. Record the actual isolation setting, statement ordering, commit outcomes, and final rows when running the exercise yourself.

Are predicate locks the same as blocking range locks?

Official fact: PostgreSQL uses predicate-lock information for Serializable dependency tracking. Its SIReadLock entries do not block ordinary readers or writers as an exclusion lock would. The monitoring can retain information beyond a transaction's completion while overlapping work finishes. Serializable dependency monitoring

Design inference: A dependency cycle is a different explanation from “B waited for A's row lock.” In the duty example, the dependency is that A reads a population B changes, and B reads a population A changes.

Official fact: PostgreSQL documents consistency enforcement using Serializable transactions across the relevant writes and reads. Mixing in other isolation levels requires separate reasoning about protection. Application-level consistency

Audit every writer that can change the roster: leave requests, administrative edits, shift deletion, and bulk imports. Making only the web endpoint Serializable does not prove that an unrelated maintenance path respects the invariant.

For explicit coordination, identify the common object or key all participants must protect. Locking only each operator's own row does not obviously coordinate their shared count.

Why must a retry repeat the entire decision?

Official fact: PostgreSQL assigns 40001 to serialization failures and requires retrying the complete transaction, including the logic that selects statements and values. It cannot safely replay arbitrary application decisions automatically. Serialization failure handling

Original retry failure: B receives 40001, opens another transaction, and retries only UPDATE duty SET on_duty = false WHERE shift_id = 7 AND operator_id = 'B'. It has preserved the earlier decision based on a count of two. The new transaction can therefore execute the wrong business action even though its SQL is valid.

The retry must count again. If A has successfully left, B now sees one and declines the leave request. That is successful recovery of the business operation, even though B's requested state change is refused.

Keep stable request inputs separate from database-derived decisions. The requested operator and shift remain the same across attempts. The active count, permission to leave, and any values computed from database state must be recalculated.

An original retry protocol is:

1. Start a fresh Serializable transaction on a usable connection.
2. Read the state and evaluate the business rule inside that transaction.
3. Apply the allowed changes and attempt commit.
4. On 40001, roll back and discard results from that attempt.
5. If the retry budget remains, delay with jitter and restart from the reads.
6. Return the committed result or an explicit exhausted-budget response.

The error handler must include commit. A transaction body returning normally does not prove the attempt committed. A savepoint around the final update also does not refresh the whole transaction's original decision.

Which errors belong in the retry policy?

Official fact: PostgreSQL recommends classifying errors by SQLSTATE rather than localized message text. 40001, 40P01, and 23505 represent different conditions. PostgreSQL error codes

Observed condition	Interview response
40001: serialization failure	Retry the whole transaction within the application budget.
40P01: deadlock detected	Consider a whole-transaction retry and investigate lock ordering.
23505: unique violation	Inspect the violated constraint and operation semantics before deciding to retry.
Connection lost around commit	Reconcile the operation outcome; do not assume a confirmed rollback.

Official fact: PostgreSQL's retry guidance treats some uniqueness and exclusion failures as potentially transient, but warns that these codes can also represent persistent errors. Retry itself does not guarantee eventual success. Retry classification

Original example: A duplicate operation ID might mean a previous request already completed and its result should be returned. A duplicate username may mean the requested name is unavailable. Repeatedly submitting either insert without interpreting the constraint can be the wrong recovery policy.

Use bounded attempts and an overall deadline. Track attempts per logical operation, not only per HTTP request, so nested client and server retry loops do not multiply work unexpectedly.

How do side effects change the design?

Original failure analysis: Sending a “you are off duty” notification before commit can expose a decision that later aborts. Sending it inside every retry can produce duplicates. The transaction's rollback cannot recall an external message.

One proposed design writes a notification intent into an outbox in the same transaction as the approved roster change. A separate dispatcher sends committed intents using a stable event identity and a retry-safe destination contract. The outbox coordinates persistence; it does not make delivery inherently exactly once.

An uncertain commit is a different case from a received serialization error. If the connection disappears after a commit request, the client may not know whether the change became durable. Use a stable operation ID stored transactionally with the result, then reconcile through the authoritative database before deciding how to continue.

Distinguish the user's intent from an attempt ID. A new database attempt belongs to the same leave request. Creating a new operation identity for every retry defeats deduplication precisely when the outcome is uncertain.

When is READ ONLY DEFERRABLE useful?

Official fact: DEFERRABLE has an effect when combined with SERIALIZABLE and READ ONLY. PostgreSQL may wait while obtaining a suitable snapshot, after which the transaction can avoid the usual Serializable monitoring burden and serialization-failure risk. This mode suits long reports or backups. SET TRANSACTION

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
SELECT shift_id, COUNT(*)
FROM duty
WHERE on_duty
GROUP BY shift_id;
COMMIT;

Interview application: A long consistency report may accept startup waiting to reduce the chance of repeating expensive work. That tradeoff differs from a latency-sensitive leave-duty request that must write.

Do not describe deferrable as a general option that makes read-write transactions stop failing. Also separate snapshot consistency from freshness: the report describes its snapshot, not every change made after it began.

How would you diagnose frequent serialization failures?

Original investigation plan: Group failures by transaction type, accessed business keys, duration, and number of attempts. Inspect broad reads, long transactions, external waits inside transaction boundaries, and concentrated updates to the same business scope.

Reducing transaction duration can reduce overlap, but shortening code is not enough if it moves the invariant check outside the transaction. Preserve the decision boundary while removing unrelated work.

Official fact: PostgreSQL constraints provide independent integrity mechanisms. A CHECK constraint is not a general cross-row assertion mechanism; uniqueness and exclusion constraints address different supported relationships. Constraints

Design recommendation: Use an appropriate constraint when it directly expresses the rule, and retain Serializable reasoning for decisions that span reads and writes. Do not replace the roster invariant with a row-local Boolean check and call the problem solved.

Evaluate success rate, tail latency, and retry amplification together. A system that eventually succeeds after many attempts may preserve correctness while failing its service target. Under persistent contention, reconsider the transaction shape or introduce deliberate coordination for the hot scope.

Practice the invariant before the isolation label

The Serializable follow-ups are original extensions, not assertions that the source interviews required PostgreSQL.

Practice question	Serializable follow-up
Compare Locks and MVCC in a Versioned Store	Distinguish a stable snapshot from a serializable committed history.
Simulate Transaction Lock Scheduling	Trace read/write dependencies separately from blocking-lock waits.
Design a bank account ledger	Put the ledger invariant and decision reads inside one retryable unit.
Object-Oriented Design: Hotel Room Booking System	Identify the booking invariant and whether a constraint can express it.
Repair and Extend a Transaction Reward Processor	Separate stable operation identity from recalculated reward decisions.

Continue with the PostgreSQL interview guide. Explain the invariant, show an unsafe schedule, then trace a failed attempt through fresh reads to a valid committed outcome.

Sources and Further Reading

• PostgreSQL 18: Transaction isolation
• PostgreSQL 18: Application-level consistency
• PostgreSQL 18: Serialization failure handling
• PostgreSQL 18: Error codes
• PostgreSQL 18: SET TRANSACTION
• PostgreSQL 18: Constraints