N_live_tup value is less than actual row count in a table

From: Teja Jakkidi <teja(dot)jakkidi05(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: N_live_tup value is less than actual row count in a table
Date: 2022-06-10 20:23:26
Message-ID: D93AF3EA-7668-4FFD-87F4-211982B76C17@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello PGSQL Admin group,

I am noticing this wierd behavior and not sure if this is how Postgres actually works.

I have a test table with 100000 rows and I deleted 20000 rows. So the current rows are 80000 and dead tuples should be 20000. After autovacuum completed, the dead tuples were gone.
When querying the pg_stat_all_tables, it returned n_live_tup value as 63945. Why is it less than actual row count 80000?

I read some articles and it is suggested to analyze manually to bring that n_live_tup number to actual row count. And it did work. After manual analyze, the n_live_tup is showing as 80000.
I am having hard time understanding why is this behavior and if there is something internally that is happening which I am missing. Please help me understand.

Thanks,
Teja.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2022-06-10 20:46:58 Re: N_live_tup value is less than actual row count in a table
Previous Message Stephen Frost 2022-06-10 16:33:25 Re: pg_basebackup and pg_receivewal timing, missing WAL files