What is the meaning of n_live_tup and n_dead_tup in pg_stat_user_tables or pgstattuple?
Stack Exchange Network Stack Exchange network consists of 177 Q&A communities including Stack Overflow , the largest, most trusted online community for developers to #, The pg_stat_all_tables view shows one row for each table in the current database (including TOAST tables) to display statistics about accesses to that specific table.
The pg_stat_user_tables and pg_stat_sys_tables views contain the same information, but filtered to only show user and system tables respectively.
In Greenplum Database 6, the pg_stat_*_tables views display access statistics for.
, SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables ; I can see the last vacuum time matching the time I ran the VACUUM command.
I also ran the below command to see if there any dead tuples, SELECT relname, n_dead_tup FROM pg_stat_user_tables; n_dead_tup count for Employee table is 0.
Still after all these above commands if I run,, > select relname, n_live_tup, n_dead_tup , last_vacuum, last_autovacuum > from pg_stat_user_tables where schemaname = ‘lobby’ and relname = > ‘lobby_player’ order by n_live_tup desc;, postgres_pg_stat_tables.
3(1) # Linux manual page #version psql (PostgreSQL) 10.
3 #table pg_stat_activity 000 pg_stat_activity datid 001 pg_stat_activity datname 002 pg_stat_activity pid 003 pg_stat_activity usesysid 004 pg_stat_activity usename 005 pg_stat_activity application_name 006 pg_stat_activity client_addr 007 pg_stat_activity client_hostname 008 pg_stat_activity client_port 009.
, Whenever a record is deleted, it does not create an extra space in the system.
PostgreSQL rather creates what is called a “dead tuple”.
If there is no more dependency on those tuples by the running transactions, PostgreSQL cleans it up using a process called VACUUM.
, pg_stat_user_tables : Same as pg_stat_all_tables, except that only user tables are shown.
pg_stat_xact_all_tables: Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views).
The columns for numbers of live and dead rows and vacuum and analyze.
, select relname, n_live_tup, n_dead_tup from pg_stat_user_tables group by 1, 2, 3 order by 2, 3 desc.
Let#s break the columns down: relname = the name of the table in question.
n_live_tup = the approximate number of live rows.
n_dead_tup = the approximate number of dead rows.
Now let#s see it in practice.
, You should not have to worry about vac’ing in your application.
Instead, you should have the autovac process configured on your server (in postgresql.
conf), and the server takes takes of VACCUM and ANALYZE processes based on its own internal statistics.
You can configure how often it should run, and what the threshold variables are for it to process.