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?

Zionism

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.

Embryotomy

Be cautious about using REINDEX on big indexes as write locks are obtained on the parent table. One strategy to achieve the same result on a live site is to build an index concurrently on the same table and columns but with a different name, and then dropping the original index and renaming the new one. This procedure, while much longer, won’t require any long running locks on the live tables. devcenter.heroku.com/articles/… – Tolman

@RohitTaneja PostgreSQL v12 has REINDEX CONCURRENTLY which makes that easier. – Embryotomy

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.

Evincive