DEV Community

Dimas Adiputro
Dimas Adiputro

Posted on

Configure pgstattuple

pgstattuple is part of PostgreSQL's contrib modules—a collection of officially supported but optional extensions.
1) download and install the rpm from postgresql official website - https://download.postgresql.org/pub/repos/yum/15/redhat/rhel-8.10-x86_64/

sudo yum install postgresql<version>-contrib
Enter fullscreen mode Exit fullscreen mode

2) upload the rpm using sudo / root and

sudo rpm -ivh postgresql<version>-contrib
Enter fullscreen mode Exit fullscreen mode

3) login to psql

CREATE EXTENSION pgstattuple;
Enter fullscreen mode Exit fullscreen mode

4) to check the dead tuple table use below command

SELECT * FROM pgstattuple('joe.this_my_table_name');
Enter fullscreen mode Exit fullscreen mode

5) below is the result.

mydb=# SELECT * FROM pgstattuple('joe.this_my_table_name');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
      8192 |           5 |       463 |          5.65 |                0 |              0 |                  0 |       7656 |        93.46
(1 row)
Enter fullscreen mode Exit fullscreen mode

Description :
table_len: Total size of the table on disk in bytes.
tuple_count: Number of live (visible) tuples.
tuple_len: Total length of live tuples in bytes.
tuple_percent: Percentage of space occupied by live tuples.
dead_tuple_count: Number of dead tuples (not yet vacuumed).
dead_tuple_len: Total length of dead tuples in bytes.
dead_tuple_percent: Percentage of space occupied by dead tuples. This is a direct indicator of bloat due to dead rows.
free_space: Total free space available within allocated pages in bytes (usable for future INSERTs/UPDATEs without extending the table).
free_percent: Percentage of total table space that is free.

Top comments (0)