Как найти фрагментированные индексы и дефрагментировать их в PostgreSQL?

Question

Я нашел, как мы можем решить эту проблему в SQL Server здесь - но как я могу сделать это в PostgreSQL?

Answer 1

Обычно вам не нужно беспокоиться об этом вообще.

Однако, если произошло массовое удаление или обновление, или устойчивая частота изменений была настолько высока, что автоочистка не выдерживала, вы можете получить сильно раздутый индекс.

Инструмент для определения этого идентификатора pgstattuple расширение:

CREATE EXTENSION pgstattuple;

Затем вы можете проверить индекс наворотов, как это:

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

Этот индекс находится в отличной форме (никогда не использовал): он имеет только 14% раздувания.

Имейте в виду, что индексы по умолчанию создаются с fillfactor из 90, то есть блоки индекса не заполнены более чем на 90% INSERT,

Трудно сказать, когда индекс раздут, но если leaf_fragmentation превышает 50-60, это не так красиво.

Чтобы реорганизовать индекс, используйте REINDEX,

Answer 2

С PostgreSQL дефрагментация индекса обычно должна автоматически выполняться демоном Autovacuum. Если вы не используете демон autovacuum или не можете его поддерживать, вы всегда можете переиндексировать проблемные индексы.

Определить, какие индексы могут быть сильно фрагментированы, не очень просто, и об этом подробно говорится в этом сообщении в блоге и в этой статье на вики PostgreSQL.