Lesson 31 · Senior PostgreSQL backend engineering

Mock senior PostgreSQL interview pack

A retrieval-first practice pack for turning the senior PostgreSQL lessons into interview-ready answers.

Your win: run a realistic senior PostgreSQL backend mock interview on yourself, score the answers honestly, and turn weak spots into a precise revision list instead of vague stress.

In plain English Plain English: answer out loud before looking at the answer points. The goal is retrieval strength, not just recognition. This page works best when it feels a little uncomfortable.

The format

The answer checklist A strong answer should usually include: (1) the mental model, (2) the key trade-off, and (3) a real backend consequence or repo-grounded example.

Round 1 · Storage and internals

  1. What is MVCC, and why is it better than naive read/write locking for many workloads?
  2. Why does Postgres need VACUUM?
  3. What is bloat, and how does a write-heavy table create it?
  4. What problem does TOAST solve?
  5. How would you explain WAL to a backend engineer?
  6. What is a HOT update, and why do people care about it?
Round 1 · Strong answer points
  1. MVCC. Writers create new row versions so readers keep a consistent snapshot without blocking. That improves concurrency, but it leaves dead tuples behind and moves cleanup cost into vacuum.
  2. VACUUM. MVCC creates dead row versions, so Postgres needs vacuum to reclaim space, maintain visibility metadata, and keep long-term performance from degrading.
  3. Bloat. Bloat is excess dead or wasted storage relative to live data. Heavy updates, deletes, and soft-delete churn create it when cleanup cannot keep pace.
  4. TOAST. TOAST stores oversized values out of line so normal rows stay manageable and the page layout remains practical even with large JSONB or text payloads.
  5. WAL. WAL is the write-ahead log recorded before data files are updated. It is the foundation for durability and crash recovery.
  6. HOT updates. Some updates can avoid additional index churn when indexed columns do not change and page conditions allow it, which reduces write cost on churn-heavy tables.

Round 2 · Planner, indexes, and performance

  1. Why can an index exist but still not be used?
  2. How do you choose between B-tree, GIN, and a composite index for a real query?
  3. What is the planner really doing when it chooses a plan?
  4. Why are row-count misestimates so dangerous?
  5. What is the difference between EXPLAIN and EXPLAIN ANALYZE?
  6. How would you explain a covering or partial index to an interviewer?
Round 2 · Strong answer points
  1. Present but unused index. The planner may believe scanning is cheaper because selectivity is low, stats are stale, predicate shape is mismatched, or a function/type mismatch blocks index use.
  2. Index choice. Start from query shape. B-tree serves scalar equality/range/order, GIN serves multi-value search patterns, and composite indexes serve repeated multi-column access paths in a useful order.
  3. Planner. It estimates row counts and costs for candidate plans, then chooses the cheapest plan according to its model and available statistics.
  4. Misestimates. Bad row-count estimates drive bad scan and join choices, so one wrong assumption can poison the rest of the plan tree.
  5. EXPLAIN vs ANALYZE. EXPLAIN shows the chosen plan and estimated cost without running the query; EXPLAIN ANALYZE executes it and shows actual times, rows, and loops.
  6. Covering / partial indexes. A covering index can reduce heap fetches by storing all needed columns; a partial index narrows maintenance and storage cost by indexing only the subset of rows the important query pattern actually needs.

Round 3 · Concurrency, migrations, and backend workflow

  1. What is the practical difference between Read Committed and Serializable?
  2. Why can naive retries make a database problem worse?
  3. What makes CREATE INDEX CONCURRENTLY operationally valuable?
  4. Why is RLS powerful in this repo’s multi-tenant model?
  5. What is the senior workflow for debugging a slow query in production?
  6. How do you talk about soft deletes as a database design trade-off?
Round 3 · Strong answer points
  1. Read Committed vs Serializable. Read Committed gives each statement a fresh snapshot and allows more anomalies; Serializable gives the strongest correctness but may abort with 40001 and requires retry-aware application design.
  2. Naive retries. Retries can amplify load and extend a degraded situation if they are unbounded or immediate, especially when the database is already under pressure.
  3. CREATE INDEX CONCURRENTLY. It reduces blocking risk during rollout, which makes index creation safer on live systems handling ongoing writes.
  4. RLS in this repo. Tenant isolation is enforced by Postgres itself through resource_path-based policies plus per-connection session state, so a forgotten tenant filter in SQL does not automatically become a data leak.
  5. Slow-query workflow. Find the real SQL through traces or logs, run EXPLAIN ANALYZE, compare estimates to actuals, identify the expensive node, then fix query shape, index strategy, stats, or schema based on evidence.
  6. Soft-delete trade-off. Soft deletes preserve business history and can simplify recovery logic, but they add filtering burden, MVCC churn, and long-term bloat / vacuum cost.

Self-scoring rubric

ScoreMeaning
0I could not explain it without notes.
1I gave fragments, but the answer was incomplete or fuzzy.
2I explained the main idea, but missed the trade-off or backend consequence.
3I gave a strong, clear answer with model + trade-off + backend consequence.
Recovery map — where to go next Missed MVCC / vacuum / WAL? Revisit Lessons 6–9 and 27. Missed index and planner answers? Revisit Lessons 10–17 and 25–26. Missed transaction, lock, or migration answers? Revisit Lessons 18–20 and 28. Missed RLS or repo-grounded debugging answers? Revisit Lessons 21–24 and 29.

Q1. The main rule of this mock pack is…

Retrieval first is what builds durable recall.
What are the three ingredients of a strong senior PostgreSQL answer?
recall, then click to reveal
A clear storage/planner mental model, the important trade-off, and the backend or production consequence.
If you want, I can now run this as a live mock senior PostgreSQL interview and grade you answer by answer, using the same answer rhythm as Lesson 30. Ask me.

Sources. This pack synthesizes the course lessons, especially Lessons 6–17, 18–24, and 25–30, into retrieval practice.