Lesson 30 · Senior PostgreSQL backend engineering
Senior PostgreSQL interview questions
High-signal backend and internals questions, with the best answer shape for each.
Your win: answer common senior PostgreSQL interview questions in a way that sounds like an engineer who has debugged real systems, not just memorised definitions.
In plain English
Plain English: do not memorise the wording exactly. Memorise the shape of a strong answer: the mental model, the trade-off, and the backend consequence.
The problem: answers that stop at the definition
Many answers fail because they stop too early. “The planner chose a bad plan” is not wrong, but it is incomplete. A stronger answer says what the planner believed, what trade-off was in play, and what kind of production symptom followed from that belief.
The answer recipe
Start with the storage or planner mental model. Then state the trade-off. Finish with the production consequence. That pattern sounds much stronger than a definition alone.
Question bank
| Question | Best answer shape |
|---|---|
| Why is an index present but not used? | Because the planner thinks scanning is cheaper: selectivity is low, stats are stale, the predicate shape does not match, or a function/type mismatch blocks index use. |
| What is MVCC and what problem does it solve? | Writers create new row versions so readers keep a consistent snapshot without blocking. The trade-off is dead tuples and vacuum work. |
| What is the difference between VACUUM and ANALYZE? | VACUUM reclaims dead-tuple space and visibility info; ANALYZE refreshes planner statistics. Both help, but in different ways. |
| When would you choose a partial index? | When an important query repeatedly targets a small, meaningful subset of rows and indexing the whole table would waste space and write cost. |
| Why can a planner misestimate hurt so much? | Because wrong row-count estimates cause wrong scan and join choices, so cost-based planning starts from bad assumptions. |
| What makes CREATE INDEX CONCURRENTLY valuable? | It reduces blocking risk during rollout, which makes schema changes safer on busy systems. |
| Why do soft deletes have performance consequences? | They preserve logical history but add MVCC churn, dead tuples, wider filtering needs, and long-term bloat pressure. |
| What is the senior debugging loop for a slow query? | Find the real SQL, run EXPLAIN ANALYZE, compare estimates to actuals, identify the expensive node, then choose the fix in query, index, stats, or schema. |
| Why is RLS powerful for multi-tenancy? | Because tenant filtering is enforced by Postgres itself, not just by application convention, so a forgotten WHERE clause does not automatically become a data leak. |
| What is the difference between a B-tree and GIN in practice? | B-tree serves scalar equality/range/order well; GIN serves multi-value search patterns like arrays, JSONB, and trigram/full-text style workloads. |
| When do you need SERIALIZABLE retries? | When the strongest correctness guarantee is worth conflict retries; the application must treat SQLSTATE 40001 as part of the design. |
| How do you explain HOT updates simply? | Some updates can avoid extra index churn if indexed columns do not change and page conditions allow it, which makes heavy-update workloads cheaper than they might otherwise be. |
Anchor — what strong answers sound like here
The strongest answers connect PostgreSQL internals back to this repo’s realities: soft deletes, RLS, hand-written SQL, tracing, migrations, and pgx transaction helpers.
Read this next
Use the official docs as your answer backbone
High-trust interview prep comes from the official PostgreSQL docs and the course’s repo-grounded references, not random trivia lists.
Check yourself (from memory)
Q1. A strong senior Postgres answer usually includes…
That structure makes answers sound like lived engineering, not memorised glossary entries.
What are the three parts of a strong senior PostgreSQL answer?
recall, then click to reveal
(1) the storage/planner mental model, (2) the trade-off, and (3) the production consequence.
When you are ready, do not just reread this page — go to the mock interview pack and answer out loud. Ask me if you want me to run it live and grade the structure of each answer.
Sources. PostgreSQL Documentation; Senior backend playbook.