How to find out fragmented indexes and defragment them in PostgreSQL?

Question

I've found how we can solve this problem in SQL Server here - but how can i do it in PostgreSQL?

Answer 1

Normally you don't have to worry about that at all.

However, if there has been a mass delete or update, or the sustained change rate was so high that autovacuum couldn't keep up, you may end up with a badly bloated index.

The tool to determine that id the pgstattuple extension:

CREATE EXTENSION pgstattuple;

Then you can examine index bloat like this:

SELECT * FROM pgstatindex('spatial_ref_sys_pkey');

-[ RECORD 1 ]------+-------
version            | 2
tree_level         | 1
index_size         | 196608
root_block_no      | 3
internal_pages     | 1
leaf_pages         | 22
empty_pages        | 0
deleted_pages      | 0
avg_leaf_density   | 64.48
leaf_fragmentation | 13.64

This index is in excellent shape (never used): It has only 14% bloat.

Mind that indexes are by default created with a fillfactor of 90, that is, index blocks are not filled to more than 90% by INSERT.

It is hard to say when an index is bloated, but if leaf_fragmentation exceeds 50-60, it's not so pretty.

To reorganize an index, use REINDEX.