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:
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;
relname AS TableName
,n_live_tup AS LiveTuples
,n_dead_tup AS DeadTuples
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.