This article is half-done without your Comment! *** Please share your thoughts via Comment ***
This is very important task for PostgreSQL DBA to check the fragmentation level of Table.
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.
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:
|
1 |
CREATE EXTENSION pgstattuple; |
CREATE TABLE tbl_ItemTransactions
|
1 2 3 4 5 |
( TranID SERIAL ,TransactionDate TIMESTAMPTZ ,TransactionName TEXT ); |
|
1 2 3 4 |
INSERT INTO tbl_ItemTransactions (TransactionDate, TransactionName) SELECT x, 'dbrnd' FROM generate_series('2014-01-01 00:00:00'::timestamptz, '2016-08-01 00:00:00'::timestamptz,'2 seconds'::interval) a(x); |
|
1 |
SELECT *FROM pgstattuple('public.tbl_itemtransactions'); |
|
1 2 3 4 5 6 7 8 9 |
table_len | 2125627392 tuple_count | 40737601 tuple_len | 1873929646 tuple_percent | 88.16 dead_tuple_count | 45 dead_tuple_len | 7787 dead_tuple_percent | 0.88 free_space | 9923 free_percent | 1.96 |
