Що таке HOT-оновлення і чому зайвий індекс сповільнює UPDATE?

Через MVCC UPDATE у PostgreSQL створює нову версію рядка. Звичайно нова версія лягає в інше місце таблиці, і кожен індекс таблиці отримує новий запис, що на неї вказує, - навіть індекси на колонках, які не змінювалися.

HOT (Heap-Only Tuple) - оптимізація, коли оновлення не чіпає індекси взагалі. Нова версія кладеться на ту саму сторінку, а стара посилається на неї ланцюжком. Індекси продовжують вказувати на стару позицію й знаходять нову через ланцюжок.

Умови HOT:

1. Жодна індексована колонка не змінилася.
2. На тій самій сторінці є вільне місце для нової версії.

Чому зайвий індекс шкодить UPDATE: індекс на колонці updated_at чи views_count, яку змінює майже кожне оновлення, робить HOT неможливим для всіх таких оновлень. Кожен UPDATE ... SET views_count = views_count + 1 тепер пише в усі індекси таблиці й більше роздуває їх.

Як збільшити частку HOT:

• Не індексувати «гарячі» колонки, які часто змінюються, без явної потреби.
• fillfactor менше 100 - залишати на сторінках вільне місце для нових версій:

ALTER TABLE counters SET (fillfactor = 80);

(діє для нових сторінок; для наявних - після VACUUM FULL чи перебудови).

• Винести часто змінювані лічильники в окрему вузьку таблицю.

Як виміряти:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_percent
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC;

Низький відсоток HOT на таблиці з багатьма оновленнями - сигнал переглянути індекси й fillfactor.

Докладніше в документації: Heap-Only Tuples (HOT)