PostgreSQL interview questions: transactions, plans and locks
Start with the invariant and the rows a query must see or change. Then reason about snapshots, constraints, indexes, locks and transaction lifetime, and verify plans or concurrency behavior on a safe representative database.
Backend and data engineering roles using PostgreSQL. · Practice guide, not an employer rubric. SQL snippets are illustrative and were not run against a database. Confirm behavior against the PostgreSQL major version and schema you operate.
What does a transaction actually see?
At PostgreSQL’s default Read Committed isolation, each statement gets a new snapshot. Two SELECTs inside one transaction may see different committed data.
MVCC lets readers use a snapshot while writers create row versions. Read Committed takes the snapshot at statement start; Repeatable Read holds one transaction snapshot. Serializable adds dependency checks and may abort an execution that cannot be ordered safely.
Being inside BEGIN/COMMIT does not by itself protect a transfer invariant. State the invariant, then use a constraint, row lock or isolation policy that protects it.
SQL · Worked example
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- A concurrent transaction commits here.
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: When does a stable snapshot matter?
Repeatable Read holds a transaction snapshot. Serializable can abort a conflicting execution and requires retrying the full transaction.
Serializable can abort a transaction to prevent a non-serializable outcome. The caller must retry the complete transaction from its first read.
Two requests can each read a roster with two on-call people and remove a different person. Serializable detects the dangerous dependency; retrying must replay the decision under a new snapshot.
Keep effects such as email outside the retryable unit. Commit durable intent and deliver it idempotently. Bound retries and distinguish serialization failures from validation or programming errors.
Python-like pseudocode · Worked example
for attempt in range(max_attempts):
try:
with transaction(isolation='serializable'):
remove_member_and_check_roster()
break
except SerializationFailure:
if attempt + 1 == max_attempts:
raise
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: What must a retry repeat?
Repeat every read and write that informed the decision, from a fresh transaction. Persist non-idempotent effects as intent and deliver them separately.
Start from equality filters, range predicates, ordering and the rows returned. Inspect EXPLAIN and compare estimates with actual work on representative data.
A B-tree on (tenant_id, created_at) can support a tenant equality filter followed by a time range. Column order matters. A sequential scan can cost less when many rows qualify, so an index scan is not the goal by itself.
EXPLAIN ANALYZE executes the statement, and modifying statements really modify unless safely rolled back. It adds measurement overhead. Interpret rows, loops and buffers in context; do not copy plans from tiny fixtures.
SQL · Worked example
CREATE INDEX orders_tenant_created ON orders (tenant_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders WHERE tenant_id = 42
ORDER BY created_at DESC LIMIT 50;
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: What do you check after EXPLAIN?
Compare estimates with actual rows, loops and buffers on representative data. A broad query can make a sequential scan cheaper.
Use NOT NULL, UNIQUE, CHECK, foreign-key or exclusion constraints for invariants PostgreSQL can enforce. Application validation helps users but cannot make a concurrent check-then-write safe.
A unique constraint arbitrates simultaneous attempts to claim a value. CHECK is row-local, not a safe cross-row query. Exclusion constraints can represent some non-overlap rules, such as reservations in the same room at intersecting times.
Before adding a constraint to populated data, find existing violations and plan the rollout. Name constraints so callers can handle expected conflicts without swallowing unrelated errors.
SQL · Worked example
CREATE EXTENSION IF NOT EXISTS btree_gist; -- required for scalar equality in this GiST constraint
ALTER TABLE reservations
ADD CONSTRAINT reservation_no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);
The scalar room equality operator needs the btree_gist extension; confirm the extension is available and installed before adding the constraint. Adapt identifiers and range types to the schema.
Illustrative, not executed.
Follow-up: Where should validation happen?
Validate early for useful feedback, then rely on database constraints to arbitrate concurrent writes and protect all callers.
Lock rows when a decision depends on them staying unchanged until the write. Keep the interval short and acquire multiple rows in consistent order.
SELECT ... FOR UPDATE blocks conflicting updates and locking reads on selected rows; it does not lock every possible future row matching a predicate. Two transfers that lock accounts in opposite orders can deadlock; PostgreSQL aborts one participant.
Advisory locks are cooperative conventions, not enforced for every writer. Prefer a constraint or row lock when it expresses the invariant directly.
SQL · Worked example
BEGIN;
SELECT available FROM inventory WHERE id = 17 FOR UPDATE;
UPDATE inventory SET available = available - 1 WHERE id = 17;
COMMIT;
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: How do you reduce deadlock risk?
Acquire rows in a consistent order and keep transactions short. Still handle deadlock aborts because other paths can form a cycle.
Use a stable cursor and unique tie-breaker instead of skipping an ever-growing OFFSET.
A timestamp alone may not be unique. Carry (created_at, id) in the cursor and match ORDER BY; build an index that begins with equality filters and supports that order.
Keyset pagination reduces skipped work and boundary ambiguity, but separate requests do not share a frozen snapshot. For exports, define a snapshot or high-water mark and decide how updates are handled.
SQL · Worked example
SELECT id, created_at FROM orders
WHERE tenant_id = $1 AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC LIMIT 50;
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: Does keyset pagination give a point-in-time export?
No. It stabilizes cursor boundaries but separate requests need a snapshot or high-water-mark policy for export consistency.
What does VACUUM do, and why may the file stay large?
VACUUM makes dead tuple space reusable and updates visibility information. Ordinary VACUUM usually does not shrink the file back to the OS.
Updates create old row versions. Autovacuum cleans them once no active snapshot needs them; long transactions or replication slots can hold cleanup back. Statistics also guide plan selection, so stale statistics can cause poor choices.
Inspect dead-tuple trends, transaction age and autovacuum before changing thresholds. VACUUM FULL rewrites a table and takes a strong lock, so it is not routine cleanup.
SQL · Worked example
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: What do rising dead tuples suggest?
Inspect churn, autovacuum, long transactions and replication retention before adjusting maintenance settings.
State the invariant, then coordinate two sessions at the exact interleaving that violates it. Check final rows and database errors.
Put a barrier after both sessions read the initial state, then let both issue competing writes. Verify the chosen constraint, lock or isolation level prevents the violation and the application handles the conflict.
Use a disposable database at the target PostgreSQL version. Mocks cannot reproduce MVCC or lock scheduling; never point a reproduction at production or user data.
SQL session sketch · Worked example
A: BEGIN; SELECT count(*) FROM memberships WHERE active;
B: BEGIN; SELECT count(*) FROM memberships WHERE active;
-- Synchronize both sessions before competing writes.
Illustrative example; adapt identifiers and conditions to the actual schema.
Illustrative, not executed.
Follow-up: What makes a concurrency explanation evidence?
Coordinate two sessions at the suspected interleaving on a disposable database, then assert the invariant and expected conflict or retry behavior.
These are Hacker News job posts that mention these technologies, not employee earnings or a measure of what a skill adds to pay. The amounts summarize annualized advertised minimums in approximate USD. Groups can overlap and differ in roles, locations and dates.
Approximate USD using ECB daily reference rates as of 2026-10-09. Original currencies and offer ranges remain on the source listings. These figures cover posts tagged PostgreSQL, rather than every job in the broader technology family.