A query became slow after a deploy. Show a safe production triage that separates estimate error, execution or I/O cost, lock waits, and a cleanup horizon. Then explain what VACUUM can reclaim, how freezing protects availability, and how you would prove physical bloat.
This prompt is deliberately oversized: it asks for a triage sequence and then for the vacuum mechanics behind one of its branches. Trying to cover both at once produces a shapeless answer, so the strongest response takes the triage first, says clearly which branch it is parking, and offers to continue. Within the triage itself, the scored decision is the order. Reaching for an index or an autovacuum setting before establishing causality is the common failure, and it is expensive because both are changes to a system nobody has finished diagnosing. There is also a safety point that has to be said out loud, because the obvious diagnostic tool executes the statement it is measuring. This page covers the ordering, the safety, and the mechanics behind the branch.
What the interviewer is scoring
Weak answer: “add an index,” “autovacuum is on,” cancels an unknown backend, drops a required slot, or treats the dead-tuple estimate as a measurement.
In short: Read the plan without guessing from one field. Prove a horizon holder before acting, and distinguish reusable space, file shrink, XID safety, exact inspection, and planner statistics.
The spoken answer and the reasoning behind it
- The full 90-second answer, written first person, the way you would actually say it
- How I reason through it: the mechanism, the decision points, and where the claim stops
- Verification steps with a written summary of what a controlled lab run showed, labelled as a summary rather than a transcript.
- Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point
Card required. Cancel before day 7 and you are not charged.
How to reason through it
- Preserve query text/parameters, plan-affecting settings, baseline, and safety constraints; remember that EXPLAIN ANALYZE executes the statement.
- Find the first material estimated-versus-actual row divergence; separately inspect loops, time, buffers, temporary I/O, spills, and wait events.
- Prove the specific cleanup horizon: a backend's snapshot, a replication slot position, or a prepared transaction; use a holder-specific and owner-approved remediation.
- Explain that MVCC updates/deletes leave old row versions; ordinary VACUUM removes versions no snapshot needs and usually makes space reusable inside the relation rather than shrinking its file.
- Explain that VACUUM FULL rewrites and shrinks a relation under ACCESS EXCLUSIVE, while freezing protects old row versions from 32-bit ID wraparound and is an availability safeguard.
- Choose the exact bloat measurement only when a full-relation read is acceptable; otherwise use the approximate form and say which results are approximate.
- Keep cumulative activity counters separate from planner statistics: a per-table counter reset clears activity, while ANALYZE refreshes planner statistics.
What I would verify
- Capture the plan-affecting settings and the parameters before changing anything, so there is a baseline to compare against.
- Use a plan-only explain first when executing the statement would not be safe.
- Locate the first node where estimated and actual rows separate, reading upward from the leaves.
- Name the specific horizon holder before proposing any vacuum change, and check whether you are allowed to remove it.
- Separate a dead-tuple estimate from a physical measurement before calling anything bloat.
Follow-ups they push on
- Why not just run EXPLAIN ANALYZE straight away?
- Dead tuples are climbing and autovacuum is running. What is your next move?
- How would you prove the bloat rather than assert it?
The probes are open. Pro carries the spoken answer, the reasoning behind it, and the verification steps, including a worked response to each of these.
Concepts tested
Learn it, run it, then say it
Three steps, in order. Nothing here is a detour.
1 · Learn the mechanism
Understand it before you try to say it.
2 · Practise it for real
Run it once so the answer describes something you have seen.
3 · Rehearse the next question
Keep going while the mechanism is fresh.
Questions that go with this one
- senior · ProTake me from that sample to a query plan, what does ANALYZE compute, and how does the planner use it?The estimate half of this triage, taken all the way down to the statistics.
- senior · Pron_live_tup and n_dead_tup are estimates. If I need the real number of tuples in a table, what do you reach for?Where a bloat measurement turns into a production decision.
- senior · ProA customer says “the database is down.” In five minutes, how do you separate a broken connection path, connection saturation, wrong-node routing, lock waits, and a query-specific failure—and what do you communicate before you know root cause?The next question down: once you know it is one query, how do you triage it safely?
- senior · ProOn PostgreSQL 18, add a defaulted column and create or rebuild an index on a busy 2 TB table. Name the table and index locks, distinguish scans from rewrites, choose the online path, and define abort and cleanup conditions.The other side of a schema change: what it did to the plans afterwards.