n_dead_tup vs dead_tuple_count in postgresql?

Question

I initially thought n_dead_tup and dead_tuple_count in PostgreSQL give the same counts. But they seem to be not. I do not quite understand what exactly is difference.

Following are my observations:

1. Created a table with 10k rows.
2. Updated all the 10k rows. Now I have 10k dead tuples.

SELECT dead_tuple_count FROM public.pgstattuple('public.vacuum_test');

 dead_tuple_count 
------------------
            10002

select * from pg_stat_get_dead_tuples('18466');

 pg_stat_get_dead_tuples 
-------------------------
                   10002

3. I did vacuum full on the table. As expected dead_tuple_count is 0.

SELECT dead_tuple_count FROM public.pgstattuple('public.vacuum_test');

 dead_tuple_count 
------------------
                0

But n_dead_tup from pg_stat_all_tables i.e pg_stat_get_dead_tuples('18466') is still 10002:

select * from pg_stat_get_dead_tuples('18466');

 pg_stat_get_dead_tuples 
-------------------------
                   10002

I repeated this process several times and observed that number of updated tuples is getting added to the stat n_dead_tup after every update.

So what exactly is VACUUM doing here? And what is the difference between n_dead_tup and dead_tuple_count?

Answer 1

pgstattuple scans the tables and calculates real-time results. It can be quite slow for a big table, but produces accurate results.

Access to the pg_stat views, directly or via functions like pg_stat_get_dead_tuples, uses the most recent data collected by ANALYZE. So it can be out of date, especially if you just made big changes. However, it's very fast to access.

If you ANALYZE the table, the stats will match again, or close. They'll often not be exactly the same because the stats from ANALYZE are just estimates.

BTW, it's time to upgrade from 8.4 to something current.

Answer 2

VACUUM FULL is a little buggy in that it doesn't reset those statistics counters. An ordinary VACUUM would do so.