This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
Find out Live Tuples or Dead Tuples using two different scripts.
Using system function and pg_class:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 |
SELECT relname AS TableName ,n_live_tup AS LiveTuples ,n_dead_tup AS DeadTuples FROM pg_stat_user_tables; |
