Роздування (bloat) - індекс займає значно більше місця, ніж потрібно для живих даних. Причина - MVCC: UPDATE і DELETE залишають мертві версії рядків, а записи індексу, що на них вказували, звільняються вакуумом не завжди повністю. Сторінки індексу стають напівпорожніми.
Наслідки: більший індекс гірше вміщається в пам'ять, більше читань з диска, повільніші запити й бекапи.
Особливо схильні:
- індекси на колонках, які часто змінюються;
- таблиці-черги, де рядки постійно вставляють і видаляють;
- індекси на монотонно зростаючих значеннях з видаленням старих рядків (ліва частина дерева порожніє).
Як оцінити: розширення pgstattuple (pgstatindex('orders_created_idx') показує щільність листків) або запити-оцінки з PostgreSQL Wiki. Простий сигнал - індекс помітно більший за свіжостворений аналог.
Як виправити без простою:
REINDEX INDEX CONCURRENTLY orders_created_idx; -- PostgreSQL 12+
REINDEX TABLE CONCURRENTLY orders;
CONCURRENTLY будує новий індекс поруч і підміняє старий, не блокуючи запис. Звичайний REINDEX блокує запис у таблицю на весь час перебудови.
Підводні камені: як і з CREATE INDEX CONCURRENTLY, команду не можна виконати в транзакції, а при збої може лишитися недійсний індекс (_ccnew), який треба видалити вручну.
Профілактика:
- Налаштований autovacuum для таблиць з частими змінами.
fillfactorменше 100 для таблиць з частими оновленнями - щоб нові версії рядків вміщалися на ту саму сторінку (HOT-оновлення не чіпають індекси взагалі).- Не індексувати колонки, які часто змінюються, без потреби.
