Interactive guide · 4 chapters · ~15 min read

PostgreSQL Interview & Revision

Postgres interviews tend to probe MVCC, isolation levels, and index selection — this section condenses those questions into revision notes, a self-check quiz, and a cheat sheet for the night before.

Mangesh MandlikLast updated: 1 November 2026

01 · Q&A

Interview Questions

The questions that come up again and again, with the shape of a strong answer.

"What happens when you run a SELECT?"

Parser validates syntax and object references → planner picks the lowest-cost plan (seq scan vs index scan, join order) → executor runs it and streams rows back.

"Explain MVCC."

Every row version carries xmin/xmax. Readers see a consistent snapshot without locking; writers create new versions instead of overwriting; vacuum reclaims old versions once no transaction can see them.

"What's the difference between a clustered and non-clustered index?"

Postgres doesn't have true clustered indexes like SQL Server — CLUSTER physically reorders a table by an index once, but new rows aren't kept in that order automatically. Every Postgres index is effectively non-clustered/secondary.

"When would you choose a sequential scan over an index scan?"

When the query needs a large fraction of the table — above a rough heuristic of 5–10% of rows (the planner decides from cost estimates, not a fixed threshold), random-access index lookups plus heap fetches cost more than one linear pass.

"How does Postgres handle a deadlock?"

A background detector periodically looks for lock-wait cycles; when found, it aborts one transaction (the victim) with a deadlock detected error so the other can proceed.

"What's the difference between Read Committed and Repeatable Read?"

Read Committed takes a fresh snapshot per statement (allows non-repeatable reads); Repeatable Read takes one snapshot for the whole transaction (prevents non-repeatable and phantom reads, but write conflicts can still abort it).

02 · Quick recall

Revision Notes

Key takeaways

ACID = Atomicity, Consistency, Isolation, Durability.
MVCC = every tuple has xmin/xmax; readers never block writers.
B-Tree index lookup = O(log n); sequential scan = O(n).
Isolation levels, weakest to strongest: Read Committed → Repeatable Read → Serializable.
Autovacuum reclaims dead tuples left behind by UPDATE/DELETE.
WAL (Write-Ahead Log) is what makes durability and replication possible.
Replication is asynchronous by default; synchronous_commit trades latency for zero data loss.
Partitioning splits one logical table into many physical ones for scale and manageability.

03 · Self-check

Quiz

Answer these before checking the Revision Notes above — if you can explain the "why" for each, you're interview-ready.

1. Why doesn't a SELECT ever block on a concurrent UPDATE to the same row in Postgres?

2. A table has 15 indexes and write latency has crept up over months. What's your first hypothesis and how would you confirm it?

3. Why can a sequential scan sometimes be faster than using an available index?

4. What's the practical difference between periodic full snapshots and Postgres' WAL-based replication?

5. Two transactions deadlock. What does Postgres do, and what should your application do in response?

6. Why does a long-running transaction hurt autovacuum, even on tables it never touches directly?

04 · Reference

Cheat Sheet

sqlThe six queries worth memorizing — they cover 90% of day-to-day Postgres diagnosis.

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

-- See current activity / long transactions
SELECT pid, xact_start, state, query FROM pg_stat_activity
WHERE state != 'idle' ORDER BY xact_start ASC;

-- Unused indexes
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- Replication lag on primary
SELECT client_addr, state, sent_lsn, replay_lsn,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- Dead tuple ratio
SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup::numeric / greatest(n_live_tup,1), 3) AS ratio
FROM pg_stat_user_tables ORDER BY ratio DESC;

-- Plan a query with real timing
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;