PostgreSQL: Script to find total Live Tuples and Dead Tuples (Row) of a Table

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.

What is Multi Version Concurrency Control (MVCC)

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:

Using pg_stat_user_tables:

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of