Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 8 down vote accepted

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.
You can find those functions in the manual.

Dead rows are deleted rows that will later be reused for new rows from INSERTs or UPDATEs. Some dead rows (or reserved free space) can be particularly useful for HOT (Heap-Only Tuples) updates that can reuse space in the same data page efficiently. More on H.O.T.:
- PostgreSQL Initial Database Size
- Release notes for PostgreSQL 8.3 where this feature was introduced.

Or dead rows may be cleaned up by VACUUM FULL (or plain VACUUM if it gets lucky) or similar operations on the table, which can shrink the physical table accordingly.

When ever you delete or update a row, the old row version becomes invisible to all transactions starting after that - a dead row as soon as there are no more running older transactions. That is necessary for PostgreSQL's MVCC model that handles 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. Also, bear in mind that statistics are not updated instantaneously. Read more about that in the manual.

share|improve this answer
    
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. –  Majid Azimi Mar 12 '12 at 10:35
    
@MajidAzimi: I added a bit to my answer. –  Erwin Brandstetter Mar 12 '12 at 10:56
2  
@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 '12 at 4:51
    
@dbenhur: Nice link! The presentation is very well done and easy to understand. –  Erwin Brandstetter Mar 23 '12 at 12:12
    
@dbenhur The link seems to be dead now. –  Aryeh Leib Taurog May 29 at 8:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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