midData platformFree in fullTested in a controlled lab, with the output captured

You want to measure a table's access pattern cleanly after a change. How do you reset just that table's statistics without wiping the cluster?

This looks like a lookup question and it is not. The function name is the easy half; the half being scored is whether you know which statistics you just cleared. PostgreSQL keeps two entirely separate bodies of numbers about a table: cumulative activity counters that record what has happened to it, and the planner's column statistics that describe what is in it. They are refreshed by different commands, they serve different consumers, and mixing them up in an interview suggests you have never used either deliberately. There is also a practical wrinkle that shows up the moment you try this on a busy system, which is worth mentioning before the interviewer finds it. This page covers all three parts.

What the interviewer is scoring

Score points for naming the per-table reset function, for distinguishing it from a database-wide reset and from the per-query subsystem, and for knowing column statistics are refreshed by ANALYZE, never reset.

In short: They're probing whether you know the per-table counter reset exists and, more importantly, that it resets ACTIVITY counters, not the planner's column statistics.

Say this: the 90-second answer

First person, as you would speak it

pg_stat_reset_single_table_counters is the function, and it takes the table's identifier, usually written as a regclass cast of the name.

What it clears is that one table's cumulative activity: sequential scans, index scans, rows inserted, updated and deleted, and the live and dead tuple estimates. Nothing else in the database is touched, which is the point. I want a clean window to measure a change in, not cluster-wide amnesia.

Two things I would distinguish immediately. A plain pg_stat_reset clears counters for the entire current database, which destroys everyone else's baselines at the same time. And pg_stat_statements_reset is a different subsystem altogether, per normalised query rather than per table.

The distinction that actually gets probed is that this resets activity counters, not planner statistics. The distinct estimate, the most-common-values list and the histogram live in the statistics catalog and are refreshed by ANALYZE. No reset function touches them, and ANALYZE zeroes no counter. Two buttons, two purposes.

One caution from doing it for real: counters are accumulated per backend and flushed on a delay, so a read taken immediately afterwards can still show a stray increment from just before.

Hold these beats, not the script

  1. 1Name the per-table function first
  2. 2Not the database-wide reset, not the per-query subsystem
  3. 3Activity counters, never planner statistics
  4. 4Flushes are delayed, so measure a window

How I reason through it

The depth a second question reaches, and where the claim stops.

Treat the reset as the start of a measurement window rather than as proof of zero.

The figures in the statistics views are not written synchronously by each query. Backends accumulate them locally and flush them periodically, so there is a short interval in which activity from before the reset arrives after it. If the measurement matters, take the baseline read a few seconds later, or take two reads and use the difference rather than trusting an absolute value. That is also the correct explanation when an index-scan count is already non-zero on the very next read: it is a flush landing late, not a phantom query.

The other practical caution is that the reset is not transactional and cannot be undone. On a shared cluster somebody else may be mid-measurement on that table, so it is worth saying what you are about to clear.

What I would do if I could not reset at all: capture the counters before and after and subtract. On a busy production system that is strictly better, because it destroys no history and it survives somebody else resetting underneath you.

What I would verify · 3 captured on PostgreSQL 18

The table's activity counters before the reset

Run this
SELECT seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='shipment_manifest'
Captured output
 seq_scan | idx_scan | n_live_tup | n_dead_tup 
----------+----------+------------+------------
       10 |        1 |    6000032 |          0
(1 row)

Copied verbatim from a controlled lab run.

What it shows: The cumulative history we want to clear so a change can be measured against a clean window.

Clearing counters for this one table only

Run this
SELECT pg_stat_reset_single_table_counters('shipment_manifest'::regclass)
Captured output
 pg_stat_reset_single_table_counters 
-------------------------------------
 
(1 row)

Copied verbatim from a controlled lab run.

What it shows: Targeted at one relation. No other table's counters, and nothing cluster-wide, is affected.

Immediately after: scans and tuple estimates cleared, one counter already back at 1

Run this
SELECT seq_scan, idx_scan, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='shipment_manifest'
Captured output
 seq_scan | idx_scan | n_live_tup | n_dead_tup 
----------+----------+------------+------------
        0 |        1 |          0 |          0
(1 row)

Copied verbatim from a controlled lab run.

What it shows: The index-scan figure reads 1 on the very next query because counters are accumulated per backend and flushed on a delay, so an increment from just before the reset lands just after it. This is why the reset starts a measurement window rather than guaranteeing a zero. The planner's column statistics are untouched throughout.

Why this answer scores

The two-buttons framing is what gets remembered. Plenty of candidates name the function; far fewer can say cleanly what it does not affect, and that distinction is the actual content of the question. Volunteering the delayed-flush caveat also signals that you have run this on something busier than a laptop.

What they ask next

You reset, then immediately read, and one counter is already non-zero. Is that a bug?
No. Counters are accumulated per backend and flushed on a delay, so activity from just before the reset can land just after it. Read a few seconds later, or measure the difference between two reads.
Does this affect autovacuum?
Yes, indirectly. Autovacuum reads the dead-tuple estimate you just cleared, so its threshold arithmetic restarts from zero for that table. On a high-churn relation that can delay the next autovacuum.
How do you clear the planner's statistics instead?
You do not clear them, you replace them. ANALYZE recomputes them from a fresh sample. There is no reset for column statistics, and wanting one usually means the real question was about plan caching.

How this answer is usually lost

  • Naming the database-wide reset when asked for a single table.
  • Claiming the reset also refreshes planner statistics.
  • Presenting a read taken immediately after the reset as a guaranteed zero baseline.
  • Forgetting that this is irreversible and shared.

Do not memorise

  • The counter values from any particular run.
  • The full list of columns in the table statistics view.

From the lab to the room

Reset one table's counters on a system with real traffic, then read them straight away and again ten seconds later. The difference between those two reads is the whole caveat, and having seen it makes it a natural thing to mention.

How to reason through it

  • Reach for pg_stat_reset_single_table_counters, which zeroes just that table's counters.
  • Contrast it: pg_stat_reset clears the whole current database's counters; pg_stat_statements_reset is a separate per-query subsystem.
  • Draw the key line: this resets activity counters, not planner column statistics.
  • Note that column statistics are refreshed by ANALYZE, not by any reset function.
  • Mention that counters are flushed with a delay, so a read taken immediately after can still show a stray increment.

What I would verify

  • Take the baseline read a few seconds after the reset, not immediately, so delayed flushes have landed.
  • Prefer before-and-after subtraction over an absolute reading when the system is busy.
  • Confirm nobody else is mid-measurement on that table before clearing it, since the reset is not reversible.
  • Check that planner statistics were untouched if plan behaviour is also in question.

Follow-ups they push on

  • You reset, then immediately read, and one counter is already non-zero. Is that a bug?
  • Does this affect autovacuum?
  • How do you clear the planner's statistics instead?

Worked responses are above, inside the answer.

Concepts tested

pg_stat_reset_single_table_countersCumulative statisticsObservability hygiene

Learn it, run it, then say it

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

2 · Practise it for real

Run it once so the answer describes something you have seen.

Questions that go with this one