Что такое Index Fragmentatation в SQL Server/PostgreSQL и как ее исправить (Rebuild vs Reorganize)?
Фрагментация индексов (Index Fragmentation) — это явление, при котором логический порядок страниц в индексе перестаёт соответствовать их физическому расположению на диске, либо страницы заполнены не полностью. Это приводит к увеличению количества операций ввода-вывода при выполнении запросов и, как следствие, к деградации производительности базы данных.
## Причины фрагментации
Фрагментация возникает из-за операций INSERT, UPDATE и DELETE над таблицами. Когда в индексную страницу добавляются новые данные и она переполняется, происходит **page split** — страница делится на две, и физический порядок нарушается. Со временем таких разрывов накапливается всё больше.
## Виды фрагментации
— **Внешняя (логическая) фрагментация** — логический порядок страниц не совпадает с физическим. SQL Server измеряет её через `avg_fragmentation_in_percent` в `sys.dm_db_index_physical_stats`.
— **Внутренняя фрагментация** — страницы заполнены не полностью (низкий `avg_page_space_used_in_percent`), что ведёт к избыточному чтению.
## Диагностика в SQL Server
sql
SELECT index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(‘dbo.MyTable’), NULL, NULL, ‘LIMITED’);
## Диагностика в PostgreSQL
PostgreSQL не имеет встроенного аналога, но можно использовать расширение `pgstattuple`:
sql
SELECT * FROM pgstattuple(‘my_index’);
Также косвенным признаком служит большое расхождение между `pg_relation_size` и реальным количеством строк.
## Rebuild vs Reorganize
### REBUILD (перестройка индекса)
— Полностью пересоздаёт индекс с нуля.
— Устраняет как внешнюю, так и внутреннюю фрагментацию.
— В SQL Server: `ALTER INDEX idx_name ON table REBUILD;`
— В PostgreSQL аналог — `REINDEX INDEX idx_name;`
— **Плюсы:** максимальная эффективность, обновляет статистику.
— **Минусы:** требует больше ресурсов, блокирует таблицу (в SQL Server есть опция `ONLINE = ON` для Enterprise Edition).
— **Когда использовать:** фрагментация > 30%.
### REORGANIZE (реорганизация индекса)
— Дефрагментирует существующие страницы без полного пересоздания.
— В SQL Server: `ALTER INDEX idx_name ON table REORGANIZE;`
— В PostgreSQL прямого аналога нет; частично роль выполняет `VACUUM` (убирает мёртвые строки и освобождает место).
— **Плюсы:** онлайн-операция, не блокирует таблицу, потребляет меньше ресурсов.
— **Минусы:** не устраняет внутреннюю фрагментацию, не обновляет статистику автоматически.
— **Когда использовать:** фрагментация 10–30%.
## Рекомендации
| Фрагментация | Действие |
|—|—|
| 30% | REBUILD |
В PostgreSQL регулярный `AUTOVACUUM` и периодический `REINDEX CONCURRENTLY` (без блокировок) — основной инструмент борьбы с фрагментацией. Правильная настройка `fillfactor` при создании индекса также снижает частоту page split.