Почему индексы замедляют вставку и обновление данных

Что проверяют

Понимаете ли вы, что индекс это отдельная структура, которую база обязана держать согласованной с таблицей, и умеете ли считать цену записи, а не только выигрыш на чтении. Для работы с данными это прежде всего вопрос про массовую загрузку.

Как отвечать

Вставка строки это не одна запись, а одна плюс по записи в каждый индекс. Отсюда всё остальное:

• лишние обходы структуры: в каждом B-дереве надо найти место и вставить ключ, а заполненную страницу расщепить;
• случайный порядок ключа: значение вроде uuid или хеша попадает в произвольную страницу, кэш не помогает и растёт число обращений к диску, тогда как монотонный ключ пишет в конец и почти бесплатен;
• журнал: изменения индексов тоже пишутся в WAL, объём записи растёт кратно числу индексов;
• проверка уникальности: уникальный индекс требует чтения перед вставкой;
• обновление: в PostgreSQL это новая версия строки, и если менялась проиндексированная колонка или на странице нет места, оптимизация HOT не срабатывает, и обновить приходится все индексы, даже те, чьи колонки не трогали.

Практический вывод: перед разовой массовой загрузкой индексы снимают и строят заново после, на живой таблице берут create index concurrently, дублирующие и неиспользуемые индексы удаляют по системной статистике, а витрины наполняют пачками через copy, а не построчно.

Где спотыкаются

Говорят, что индекс перестраивается на каждой вставке. Дерево целиком не перестраивается, дорого лишь расщепление страницы, и такое преувеличение сразу показывает, что человек внутрь не заглядывал.

Второе: меряют эффект на маленькой таблице. Пока индекс целиком лежит в кэше, разница почти незаметна, и проявляется она только когда структура перестаёт помещаться в память.

Третье: забывают про обратную сторону. Индекс по колонке, по которой идёт поиск обновляемых или удаляемых строк, эти операции ускоряет, поэтому «снести все индексы ради записи» на живой нагрузке часто делает хуже.