Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

check Table Fragmentation using pgstattuple module

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 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 EXTENSION pgstattuple;
Create one sample table:
CREATE TABLE tbl_ItemTransactions

 (
     TranID SERIAL
     ,TransactionDate TIMESTAMPTZ
     ,TransactionName TEXT
 );
Insert few millions of data:
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);
Check the tuple information using pgstattuple:
SELECT *FROM pgstattuple('public.tbl_itemtransactions');

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

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL