You've got a 50 TB table. Walk me through what ANALYZE actually does at that size, does it read every page?
No. ANALYZE reads a fixed-size random sample, not the whole table. At the default default_statistics_target of 100 it reads up to 30,000 blocks and keeps up to 30,000 rows from them, whether the table is 1 GB or 50 TB. Table size does not change how much ANALYZE reads. It changes how well that sample represents the table. This page gives the 90-second answer and the follow-ups that usually come with it.
What the interviewer is scoring
Say first that ANALYZE samples and does not scan the whole table. Name the 300 × default_statistics_target rule. Explain that the sample size stays flat as the table grows.
In short: ANALYZE reads a bounded sample of blocks, not the whole relation. The sample size comes from default_statistics_target or a per-column STATISTICS setting, not from the size of the table.
The spoken answer, backed by captured output
- The full 90-second answer, written first person, the way you would actually say it
- Why: the mechanism, the decision points, and where the claim stops
- Verification steps with output captured verbatim from a controlled PostgreSQL lab run.
- Worked responses to the 3 follow-up probes listed above, plus the traps that lose the point
How to reason through it
- Lead with the headline: ANALYZE reads a bounded sample of blocks, not the whole table.
- Name the rule, the sample targets 300 × default_statistics_target rows (30,000 at the default of 100).
- Explain the two stages: a random sample of blocks, then a reservoir sample of rows inside them.
- State the consequence: the sample size and read cost stay flat as the table grows, but the sample covers a smaller fraction of the table. That hurts n_distinct estimates and coverage of rare values first.
- Give the lever: raise STATISTICS on a skewed column for a bigger sample, at the cost of a slower analyze and a larger statistics catalog.
What I would verify
- Read the VERBOSE output: it states how many pages were scanned, how many rows were sampled, and the resulting row estimate.
- Compare the sampled row count against 300 x the effective target to confirm which target is in force.
- Check for per-column statistics overrides before blaming cluster settings for a slow ANALYZE.
- Treat the resulting row estimate as an estimate, and confirm it against an exact count only when the decision needs one.
Follow-ups they push on
- So is ANALYZE on a 50 TB table cheap or not?
- What if a column has heavy skew and estimates are still wrong?
- Where does the sampled data actually land?
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 same question one step further on: from the sample to the chosen plan.
- 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 the sampled row estimate stops being good enough and you have to measure.