Как поле updated_at может ломать HOT update?

Что такое HOT update?

HOT (Heap-Only Tuple) update — это оптимизация в PostgreSQL, которая позволяет обновлять строки без необходимости обновлять индексы. Это возможно, если обновляемые столбцы не входят ни в один индекс. В таком случае PostgreSQL может просто создать новую версию строки в той же странице (page) и указать на неё через указатель в старой версии, не трогая индексы.

Как updated_at ломает HOT update?

Если поле updated_at индексировано (что часто бывает для сортировки или фильтрации по времени последнего изменения), то при каждом обновлении записи это поле меняется. Поскольку оно входит в индекс, PostgreSQL не может применить HOT update — ему приходится обновлять индекс, что требует дополнительных операций ввода-вывода и может привести к фрагментации.

Пример

-- Создаём таблицу с индексом на updated_at
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_updated_at ON users (updated_at);

-- Обновление записи
UPDATE users SET name = 'Alice', updated_at = NOW() WHERE id = 1;
-- Здесь updated_at меняется, индекс обновляется, HOT update не работает

Как избежать?

• Не индексировать updated_at, если это не критично для производительности запросов.
• Использовать частичные индексы или индексы на выражения, если нужно только для определённых условий.
• Рассмотреть возможность использования триггеров для обновления updated_at только при изменении других индексированных полей.

Вывод

Индексирование поля updated_at может препятствовать применению HOT update, что снижает производительность операций обновления. Стоит избегать индексации этого поля, если оно обновляется часто и не требуется для быстрого поиска или сортировки.