Postgres n_live_tup Lied to Me, and I Believed It

I once told myself, in writing, that the query planner on my biggest Postgres table was flying blind and that the database was about 16 days from transaction ID wraparound. Both claims were wrong. Both came from reading the wrong columns, and both would have sent me off to do expensive, disruptive maintenance that I did not need.

This is how I caught myself before doing it.

The symptom

I was auditing a PostgreSQL database of a couple of hundred gigabytes that backs one of my side projects. The largest table, about 72 GB, looked wrong in the statistics view:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_analyze
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

For a table I knew contained millions of rows, n_live_tup reported 12,624. Several other big tables showed no last_analyze timestamp at all.

That seemed to add up to a dramatic story. If the database believes a 72 GB table has about twelve thousand rows, then the planner will pick plans suited to a tiny table: nested loops where it should hash join, index scans where it should do a sequential scan. Everything would be slow in a quiet, hard-to-explain way. And since those tables had never been analyzed, it fit. I wrote it up that way.

What I wrongly assumed

I assumed n_live_tup is what the planner uses to estimate how many rows a table has. It is not.

n_live_tup lives in the cumulative statistics system. It is an activity counter, an estimate maintained by autovacuum and analyze that starts counting from whenever the statistics were last reset. After a crash, a statistics reset or a pg_upgrade, it can be absurdly small until something refreshes it. It is meant for autovacuum’s bookkeeping, not for planning.

The planner reads from the system catalogs. The row count estimate is pg_class.reltuples, and the distribution of values lives in pg_statistic, which you see through the pg_stats view. Those are stored in regular catalog tables, and they survive statistics resets. A reset that wipes n_live_tup does not touch them.

How I found out I was wrong

Before queueing up a long maintenance job, I did the cheap sanity check I should have done first: compare the planner’s number with a real count.

SELECT c.relname,
       c.reltuples::bigint AS planner_estimate,
       c.relpages
FROM pg_class c
WHERE c.relname IN ('big_table', 'second_table', 'status_table');

SELECT count(*) FROM big_table;

The results:

The planner’s estimate was within about one percent of reality. The table that n_live_tup said had 12,624 rows had 3.5 million, and the planner knew that. I also checked pg_stats: the main table had column statistics on 24 columns, and a materialized view had them on 35. The planner was well informed. My original diagnosis was simply false.

The lesson: never diagnose planner health from n_live_tup. Check reltuples against count(*), and check pg_stats coverage.

The wraparound scare, measured

My other claim was that transaction ID wraparound was around 16 days away. That number came from an earlier README that I had trusted. Wraparound is a real danger in PostgreSQL: transaction IDs are 32 bits, the database must periodically freeze old rows, and if it cannot, it will stop accepting writes to protect itself. It deserves respect. But “deserves respect” is not the same as “is urgent.”

So I measured the burn rate instead of assuming it. Sampling over 60 seconds with a snapshot function that does not itself consume a transaction ID:

SELECT pg_snapshot_xmax(pg_current_snapshot());  -- run twice, 60s apart
SELECT datname, age(datfrozenxid) FROM pg_database;

The result was 0.9 transaction IDs per second, about 77,760 per day. This database does very little write traffic. The oldest frozen age was 125.6 million. The thresholds that matter:

• aggressive freezing starts at vacuum_freeze_table_age (150 million): about 314 days away
• forced anti-wraparound vacuum starts at autovacuum_freeze_max_age (200 million): about 957 days away, roughly 2.6 years

Zero tables were over the aggressive threshold. The 16-day claim did not reproduce, and I stopped calling it urgent. Even allowing a 2.5 to 3.5 times multiplier for busier periods, it stays months away. I still re-measure the rate occasionally, because the rate is the thing that can change.

One quirk made the freeze age look worse than it was. The database-wide oldest age was pinned by tiny system catalog TOAST tables, a few kilobytes each, not by the giants. But freezing those does not advance the database-level number, because it is the minimum across all relations, and about 28 user tables totalling roughly 155 GB were also above 100 million. Moving that number genuinely means scanning 155 GB. That is exactly why it is correctly deferred to a maintenance window, rather than something to rush because a counter looks big.

What I did apply

Being wrong about the headline did not mean there was nothing useful to do. I applied two safe, online changes.

First, ANALYZE VERBOSE on the five tables that had never been analyzed. It took about 30 seconds in total and only takes a light lock that does not block reads or writes. Now they all have a last_analyze timestamp and autovacuum’s bookkeeping makes sense going forward.

Second, a VACUUM (VERBOSE, ANALYZE) on the only two tables with real bloat. On the status table that removed 114,496 dead tuples, around 8 percent of it, and brought its freeze age from 125.6 million down to 53.2 million.

I left the other tables alone on purpose. They had 0.7, 2.2 and 0 percent dead tuples. A heavy pass would have been busywork. I also skipped a statistics-target increase that someone had drafted, because there was no observed bad plan behind it. Speculative tuning without a bad plan to point at is how you end up with changes nobody can justify later.

The one real finding

The audit did turn up one genuine issue. The status table was the only one with real recurring churn and no per-table autovacuum settings. With default settings, autovacuum triggers at 50 plus 20 percent of the row count. On 3.54 million rows that is 708,327 dead tuples, roughly a month of bloat before autovacuum would ever fire. Its sibling tables were already tuned. This one had been missed.

ALTER TABLE status_table SET (
  autovacuum_vacuum_scale_factor  = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

The new trigger is 177,331 dead tuples for vacuum and 70,962 changes for analyze, about four times tighter. Measured churn was 18 to 21 thousand dead tuples per day, so the table now cleans itself roughly every nine or ten days without my help. Clearing the existing backlog removed another 46,276 dead tuples.

What I check when a plan actually looks wrong

The right way to test planner health is to look at a specific slow query, not at a statistics view. EXPLAIN (ANALYZE) prints the planner’s estimated row count and the actual row count for every node in the plan. When the two disagree by an order of magnitude or more, you have found a genuine estimation problem, and you can then ask whether it is stale statistics, a correlation between columns the planner treats as independent, or a skewed distribution that needs a higher statistics target.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM big_table WHERE created_at > now() - interval '7 days';
-- compare "rows=" (estimate) with "actual ... rows=" on each node

That is the evidence I was missing when I wrote the first, wrong version of this audit. I had a suspicious number in a view and no bad plan to go with it. Without a bad plan, nothing needs fixing.

Lessons

• Know which statistics feed which decisions. n_live_tup is for autovacuum. reltuples and pg_stats are for the planner. They are not interchangeable.
• Cross-check any alarming statistic against a real count. It takes one query.
• Measure the burn rate before you call anything urgent. A timeline you computed beats a timeline you inherited.
• A correction is worth publishing. The most useful thing I wrote down that week was a note saying my earlier note was wrong.
• Do the small, safe work and skip the speculative work. Analyze took 30 seconds and changed nothing dramatic. That is a fine outcome.