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.

Answer 2

With PostgreSQL index defragmentation should generally be handled automatically by the Autovacuum daemon. If you don't use the autovacuum daemon, or if it isn't able to keep up, you can always reindex problematic indexes.

Determining which indexes may be badly fragmented isn't particularly straight forward and it's discussed at length in this blog post and in this PostgreSQL wiki article.