find total Live Tuples and Dead Tuples

This is one of the very important post for all PostgreSQL Database Professionals.

In this post, I am sharing a small, but very powerful script to know about the Live Tuples (Rows) and Dead Tuples (Rows) of the PostgreSQL Object like: Tables and Indexes.

If you don’t know about the MVCC (Multi Version Concurrency Control), Please visit this article.

PostgreSQL is based on MVCC Architecture.

In MVCC Architecture, When you update or delete any row, Internally It creates the new row and mark old row as unused.

This kind of data, we call as Dead Tuples or Dead Rows. (We can also say like, This is an internal fragmentation).

Periodically, We should find dead rows of the object and we should remove it using VACUUM techniques of PostgreSQL.
By this way, we can increase the overall performance of PostgreSQL Database Server.

PostgreSQL: Short note on VACUUM, VACUUM FULL and ANALYZE

Find out Live Tuples or Dead Tuples using two different scripts.

Using system function and pg_class:
SELECT 
 relname AS ObjectName
 ,pg_stat_get_live_tuples(c.oid) AS LiveTuples
 ,pg_stat_get_dead_tuples(c.oid) AS DeadTuples
FROM pg_class c;

Using pg_stat_user_tables:
SELECT 
 relname AS TableName
 ,n_live_tup AS LiveTuples
 ,n_dead_tup AS DeadTuples
FROM pg_stat_user_tables;

All database dead Tubles calculated which tables having more than 5000 dead tubles:
\c db2
select count(*) from pg_stat_all_tables where n_dead_tup > 5000;

\c db1
select count(*) from pg_stat_all_tables where n_dead_tup > 5000;

\c db5
select count(*) from pg_stat_all_tables where n_dead_tup > 5000;

\c db4
select count(*) from pg_stat_all_tables where n_dead_tup > 5000;

\c db3
select count(*) from pg_stat_all_tables where n_dead_tup > 5000;

\c chennaidb
select count(*) from pg_stat_all_tables where n_dead_tup > 5000;