How to check fragmentation of tables and indexes in PostgreSQL?

#postgres #database #devops

Create a new table named cities using the following command:

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    latitude NUMERIC,
    longitude NUMERIC
);

postgres=# \d public.cities
                                     Table "public.cities"
  Column   |          Type          | Collation | Nullable |              Default               
-----------+------------------------+-----------+----------+------------------------------------
 id        | integer                |           | not null | nextval('cities_id_seq'::regclass)
 name      | character varying(100) |           |          | 
 latitude  | numeric                |           |          | 
 longitude | numeric                |           |          | 
Indexes:
    "cities_pkey" PRIMARY KEY, btree (id)

Populate the table with the city data using the following SQL INSERT statements:

INSERT INTO cities (name, latitude, longitude) VALUES ('Lisbon', 38.724874, -9.139604);
INSERT INTO cities (name, latitude, longitude) VALUES ('Porto', 41.158389, -8.629163);
INSERT INTO cities (name, latitude, longitude) VALUES ('Sintra', 38.800306, -9.379136);
INSERT INTO cities (name, latitude, longitude) VALUES ('Obidos', 39.362068, -9.157140);
INSERT INTO cities (name, latitude, longitude) VALUES ('Coimbra', 40.211491, -8.429200);
INSERT INTO cities (name, latitude, longitude) VALUES ('Covilha', 40.282650, -7.503260);
INSERT INTO cities (name, latitude, longitude) VALUES ('Fatima', 39.617207, -8.652142);

To check the fragmentation of table(s) and indexe(s) in Postgres, you can use the built-in pgstattuple extension.

postgres=# create extension pgstattuple;
CREATE EXTENSION
postgres=#

To analyze a table for fragmentation, you can use the following command:

SELECT * FROM pgstattuple('table_name');

This will return a set of statistics about the table, including the number of live and dead rows, the number of pages, and the amount of free space on each page. You can use this information to identify any fragmentation issues that may be impacting performance.

postgres=# SELECT * FROM pgstattuple('cities');
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
      8192 |           7 |       370 |          4.52 |                0 |              0 |                  0 |       7744 |        94.53
(1 row)

To analyze an index for fragmentation, you can use the following command:

SELECT * FROM pgstatindex('index_name');

This will return a set of statistics about the index, including the number of pages, the number of distinct values, and the size of the index. You can use this information to identify any fragmentation issues that may be impacting query performance.

postgres=# SELECT * FROM pgstatindex('cities_pkey');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation 
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          0 |      16384 |             1 |              0 |          1 |           0 |             0 |             1.77 |                  0
(1 row)

postgres=#