What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables

Question

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

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

Dead rows are deleted rows that will later be cleaned up by vacuuming the table or the disk space may be reused by HOT (Heap-Only Tuples) updates. (More on H.O.T. in the release notes for PostgreSQL 8.3 where this feature was introduced.)

When ever you delete or update a row, the old row version becomes invisible - a dead row. 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.