What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?

1 Answer 1


Those two columns are the result of

SELECT pg_stat_get_live_tuples(c.oid) AS n_live_tup
     , pg_stat_get_dead_tuples(c.oid) AS n_dead_tup
FROM   pg_class c;

Representing the number of live and dead rows (tuples) in the table.
Find those functions in the manual.

Dead rows are deleted rows that will later be reused for new rows from INSERTs or UPDATEs (the space, not the data). Some dead rows (or reserved free space) can be particularly useful for HOT updates (Heap-Only Tuples) that can reuse space in the same data page efficiently. More on H.O.T.:

Dead rows can be removed by VACUUM FULL (or plain VACUUM if it gets lucky) or similar operations on the table, thereby shrinking the physical size accordingly.

Whenever a row is deleted or updated, the old row version becomes invisible to all other transactions starting after the transaction has been committed - with default READ COMMITTED transaction isolation. The row is completely dead as soon as there are no more uncommitted older transactions. That is necessary for PostgreSQL's MVCC model to handle concurrency.

Those are just statistics. You need to enable statistics collection in postgresql.conf if you want them to be updated automatically. track_counts should be on by default, though. Bear in mind that statistics are not updated instantaneously.
More about the statistics collector in the manual here.

  • What is Heap-Only Tuples? When I issue SELECT n_live_tup FROM pg_stat_user_tables WHEN relname = 'mytable'; Why it shows zero? mytable has 6 rows. Mar 12, 2012 at 10:35
  • @MajidAzimi: I added a bit to my answer. Mar 12, 2012 at 10:56
  • 7
    @MajidAzimi If you want to learn a bunch about MVCC and HoT check out the slides from Pavan Deolasee's presentation at PGCon'08
    – dbenhur
    Mar 21, 2012 at 4:51
  • @dbenhur: Nice link! The presentation is very well done and easy to understand. Mar 23, 2012 at 12:12
  • 1
    @AryehLeibTaurog Really? I just clicked it and it brings up the presentation synopsis page with a link to a 1.6MB PowerPoint file.
    – dbenhur
    May 31, 2014 at 5:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.