Эффективно ли использовать индекс на таблице с частыми обновлениями
Что проверяют
Понимаете ли вы, что индекс это сделка, а не бесплатное ускорение: чтение дешевеет, запись и обслуживание дорожают. Ответ «да, индексы это хорошо» и ответ «на таких таблицах индексы вредны» одинаково плохи, ждут условия, при которых верно то и другое.
Для питониста вопрос ещё и прикладной: индексы в проекте обычно создаёт не человек, а миграция ORM, и появляются они пачками под каждый внешний ключ и unique, а не по замеру.
Как отвечать
Начните с механики. Индекс это отдельная структура, которую нужно поддерживать при вставке, удалении и при изменении входящих в неё колонок.
Дальше уточнение, которого обычно ждут: дорожает не любое обновление, а обновление проиндексированных колонок. В PostgreSQL есть HOT-обновление, когда новая версия строки кладётся на ту же страницу и индексы не трогаются, но работает оно, только если изменённых колонок нет ни в одном индексе и на странице осталось место. Индекс на горячую колонку (счётчик, статус, updated_at) эту оптимизацию отключает и разгоняет раздувание.
Затем обратная сторона: UPDATE и DELETE сами ищут строки по условию, и тут индекс помогает. Точечное обновление по ключу без индекса превращается в полный проход по таблице, а на большой таблице это дороже любой поддержки дерева.
Закончите практикой: оставлять индексы под реальные запросы (pg_stat_user_indexes показывает неиспользуемые), брать частичные индексы вместо полных, снижать fillfactor на часто обновляемых таблицах, создавать индексы через CREATE INDEX CONCURRENTLY.
Где спотыкаются
Индексируют каждую колонку, встречающуюся в фильтрах, и получают таблицу, где индексы весят больше данных. Каждая вставка после этого пишет в десяток деревьев, а планировщик всё равно берёт один или два из них.
Услышав про накладные расходы, снимают нужный индекс с большой таблицы. Запись становится дешевле, зато UPDATE по одной строке начинает читать её целиком, и общая нагрузка растёт.
Забывают про раздувание и autovacuum. Старые версии строк остаются в индексе, дерево распухает, выигрыш постепенно съедается, и без REINDEX CONCURRENTLY картина не вернётся сама.