PostgreSQL: How to check Table Fragmentation using pgstattuple module

This is very important task for PostgreSQL DBA to check the fragmentation level of Table.postgresql fragmentation

Please do not forget about that the PostgreSQL is based on MVCC architecture. Which is good in one way, but bad in another way.

Using below article, You must read about MVCC and also access other related articles.

What is Multi Version Concurrency Control (MVCC)

PostgreSQL: Short note on VACUUM, VACUUM FULL and ANALYZE

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

PostgreSQL provides pgstattuple module to get all tuples information of a Table. You can find information like live_tuple, dead_tuple, free_space and other.

Using this information you can find fragmentation of table which you can remove using VACUUM / VACUUM FULL command.

You must install the pgstattuple to find tuples related information. You can also use pgstatindex() to find information related to indexes.

Load pgstattuple module:

Create one sample table:
CREATE TABLE tbl_ItemTransactions
Insert few millions of data:
Check the tuple information using pgstattuple:
The Result:
Anvesh Patel

Leave a Reply

2 Comments on "PostgreSQL: How to check Table Fragmentation using pgstattuple module"

Notify of
avatar
Sort by:   newest | oldest | most voted
kashi
Guest

is this new feature ? I am using 9.3 I could not create extension.

wpDiscuz