seniorData platformPro answerTested in a controlled lab, with the output captured

n_live_tup and n_dead_tup are estimates. If I need the real number of tuples in a table, what do you reach for?

The question contains a deliberate ambiguity and the best answers notice it. Real number of tuples can mean two different things: how many rows are visible right now, which count(*) answers exactly, or how much physical material is in the relation including dead versions and free space, which count(*) cannot see at all. Candidates who pick one meaning and run tend to give a half answer. The interviewer is also checking that you know why the statistics views cannot be trusted for this, and that you know the cheaper approximate tool and what it trades away. This page covers separating the two readings quickly, then choosing the instrument that matches the decision.

What the interviewer is scoring

Score points for separating exact live rows from exact physical inspection, for naming pgstattuple_approx as the cheap option, and for knowing the cost trade of a full heap scan.

In short: They're checking that you know the stats-view counters and the catalog row estimate are estimates, and that exact physical truth, plus the dead-tuple and free-space breakdown, comes from the pgstattuple extension.

ProFrom a real interview loop

The spoken answer, backed by captured output

The question, the intro, what the interviewer is scoring, the reasoning outline, what you would verify and the follow-up probes are all open above. Pro unlocks the spoken answer to “n_live_tup and n_dead_tup are estimates. If I need the real number of tuples in a table, what do you reach for?”, the reasoning behind it, and the verification that backs 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 output captured verbatim from a controlled PostgreSQL 18 lab run.
  • 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.

Compare plans

How to reason through it

  • Split the question first: exact visible rows is one thing, physical tuple and space accounting is another.
  • Say plainly that the catalog row estimate and the live/dead counters are estimates maintained by ANALYZE and autovacuum.
  • Reach for the pgstattuple extension for exact tuple count, dead tuple count, and free space.
  • Offer pgstattuple_approx when a full scan is too expensive; it leans on the visibility map.
  • Note that count(*) gives exact live rows but no dead or free-space breakdown, and still costs a scan.

What I would verify

  • Decide what the number is for; a rebuild decision and a dashboard justify different instruments.
  • Compare the estimate against the measurement, because disagreement between them is itself a finding.
  • Check how much of the table is all-visible before assuming the approximate tool will be cheap.
  • Label approximate results as approximate wherever they are reported.

Follow-ups they push on

  • Why is the catalog row estimate sometimes exactly right?
  • The dead-tuple count is huge but pgstattuple says there are almost none. What happened?
  • When would you not run pgstattuple at all?

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

pgstattuplereltuples estimateBloat measurement

Learn it, run it, then say it

Three steps, in order. Nothing here is a detour.

Questions that go with this one