Индексы помогают или мешают при вставке данных?

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

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

Как отвечать

Короткий ответ: мешают. Индекс это отдельная структура, чаще B-дерево, и при INSERT база обязана добавить запись в каждый индекс таблицы. Таблица с пятью индексами означает шесть структур для записи вместо одной, и каждая пишет свою долю в журнал. Когда страница B-дерева заполнена, она делится, это ещё работа.

Детали, которые показывают понимание:

• уникальный индекс: при вставке проверяется отсутствие дубля. Это цена, зато дубли не проскочат, и без такого индекса не работает INSERT ... ON CONFLICT в PostgreSQL;
• внешние ключи: при вставке база ищет родительскую строку по первичному ключу. Здесь индекс как раз помогает, без него проверка была бы полным просмотром;
• случайные ключи: UUID v4 в первичном ключе раскидывает вставки по всему дереву, особенно больно в InnoDB, где таблица кластеризована по первичному ключу. Последовательные id или UUID v7 дописываются в конец;
• массовая загрузка: данные грузят через COPY или пачками в одной транзакции, а на очень больших объёмах индексы удаляют и создают заново после загрузки. Команды «выключить индекс» в PostgreSQL нет.

Для QA это практично: если тест на запись тормозит, посмотрите, сколько индексов на таблице и какие реально используются (в PostgreSQL pg_stat_user_indexes). Миграцию, которая создаёт индекс на большой таблице, проверяйте на блокировки: обычный CREATE INDEX блокирует запись, CREATE INDEX CONCURRENTLY нет.

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

Отвечают «помогают, потому что потом быстрее искать». Вопрос про саму вставку, и на ней индексы стоят ресурсов.

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

Считают, что индекс замедляет только INSERT. UPDATE индексированной колонки и DELETE тоже обновляют индексы.