Что произойдёт, если индексов много? Какова цена индексов при INSERT/UPDATE?

HOT update и его условия

При UPDATE PostgreSQL пытается использовать Heap Only Tuple (HOT) обновление — новая версия строки пишется в тот же heap page, а индексы не обновляются. HOT возможен только если: изменённые столбцы не входят ни в один индекс, и в heap page есть свободное место.

Если обновляется индексируемый столбец — HOT невозможен. PostgreSQL создаёт новую версию строки и обновляет все затронутые индексы. Старые index tuples помечаются как мёртвые и убираются при следующем VACUUM.

-- Проверить, сколько индексов у таблицы и как они используются
SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC;

-- Найти неиспользуемые индексы (кандидаты на удаление)
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

Что происходит при INSERT с несколькими индексами

При каждом INSERT PostgreSQL обязан обновить все индексы таблицы. Для таблицы с 8 индексами это 8 отдельных B-tree операций записи плюс соответствующие WAL записи для каждой. Стоимость растёт примерно линейно с числом индексов.

Write amplification и WAL

Каждая запись в индекс генерирует WAL. На таблице с 10 индексами один INSERT может генерировать в 5–10 раз больше WAL, чем на таблице без индексов. Это увеличивает:

• Время выполнения INSERT/UPDATE на primary
• Лаг логической и физической репликации
• Нагрузку на диск (особенно на burst write)
• Время bulk load (COPY, pg_restore)

-- При bulk load: удалить индексы до загрузки, восстановить после
-- Это ускоряет загрузку в 3-10 раз для больших таблиц
DROP INDEX CONCURRENTLY idx_orders_status;
COPY orders FROM '/tmp/orders_dump.csv' CSV;
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);
ANALYZE orders;

Bloat от UPDATE

После UPDATE индексируемого столбца старый index tuple остаётся в B-tree до VACUUM. Если autovacuum не успевает — индекс раздувается. Bloated индекс занимает больше места и медленнее сканируется. Проверить можно через расширение pgstattuple:

-- Требует расширения pgstattuple
SELECT * FROM pgstatindex('idx_orders_status');
-- Смотреть: dead_tuple_percent > 10% — сигнал к REINDEX CONCURRENTLY

Подводные камни

• Удалять индекс только по нулевому idx_scan опасно: он может поддерживать PRIMARY KEY, UNIQUE constraint или использоваться редко при аварийных запросах.
• Добавление индекса на high-write таблицу без нагрузочного теста может ухудшить p95/p99 latency INSERT в 2–3 раза.
• HOT update блокируется даже одним индексом по обновляемому столбцу — частые UPDATE полей вроде updated_at или status накапливают dead tuples быстрее при наличии индексов.
• Лаг репликации растёт пропорционально объёму WAL — на write-heavy нагрузке каждый лишний индекс увеличивает лаг на репликах.
• Partial index (WHERE status = 'active') может быть в 10–100 раз меньше полного и почти не влиять на write overhead для строк, не попадающих в предикат.
• Статистика pg_stat_user_indexes сбрасывается при pg_stat_reset() и после рестарта — низкий idx_scan сразу после деплоя не показателен.
• REINDEX CONCURRENTLY на большой таблице создаёт временный индекс рядом — нужно убедиться, что на диске достаточно места (2× размер индекса).
• Индексы на foreign key колонках нужны не для SELECT, а для ускорения DELETE/UPDATE на родительской таблице — удаление такого «неиспользуемого» индекса вызовет full scan дочерней таблицы при каждом DELETE из родительской.

• Считать индекс бесплатным.
• Удалять индексы без проверки constraints.
• Не связывать индексы с WAL и replica lag.

• Просит объяснить HOT update.
• Проверяет метрики pg_stat_user_indexes.
• Уточняет write amplification.