SQL-Ex blog

Стоимость обычного обновления (без HOT)

Без HOT масштабирование обслуживания индексов работает плохо. Рассмотрим таблицу с несколькими индексами:

CREATE EXTENSION IF NOT EXISTS pageinspect;



CREATE TABLE hot_demo (

    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    name text NOT NULL,

    status text NOT NULL DEFAULT 'active',

    score numeric(10,2)

);



CREATE INDEX idx_hot_name ON hot_demo (name);

CREATE INDEX idx_hot_status ON hot_demo (status);



INSERT INTO hot_demo (name, status, score) VALUES

    ('alice', 'active', 95.00),

    ('bob',   'active', 82.50),

    ('carol', 'active', 77.25);

Обновление индексированного столбца name требует значительной фоновой работы:

UPDATE hot_demo SET name = 'BOB' WHERE id = 2;

PostgreSQL должен:

1. Установить t_xmax на старом кортеже в текущий идентификатор транзакции, пометив его как мёртвый


2. Создать новый кортеж


3. Вставить запись в индекс первичного ключа, указывающую на новый ctid


4. Вставить новую запись в idx_hot_name, указывающую на новый ctid


5. Вставить новую запись в idx_hot_status, указывающую на новый ctid

Значения id и status остались идентичными, но их индексы требуют новых записей, потому что физическое расположение кортежа изменилось. Старые указатели в индексах становятся мёртвым грузом, ожидая удаления командой VACUUM.

Как HOT обходит обновление индексов

Записи индекса сопоставляют ключевые значения с физическим расположением кортежа (ctid). Если новый кортеж достижим через старый ctid, ссылки в индексах остаются действительными и не требуют обновления.

ctid — это физический адрес кортежа: (номер_страницы, указатель_строки). Индексы хранят ctid, а не содержимое строк.

PostgreSQL использует HOT-обновление при выполнении двух конкретных условий:

1. Новый кортеж должен поместиться на той же странице, что и старый кортеж.


2. Ни один из обновлённых столбцов не должен быть индексирован.

Если любое из условий не выполнено, происходит «холодное» обновление (cold update) со всеми операциями над индексами. Если выполнены оба — обновление становится HOT.

Демонстрация HOT-обновления

Обновим столбец score, который не входит ни в один индекс:

UPDATE hot_demo SET score = 99.00 WHERE name = 'alice';

Расширение pageinspect показывает HOT-флаги, которые хранятся в t_infomask2:

SELECT

    lp, lp_off, lp_flags, lp_len,

    t_xmin, t_xmax, t_ctid,

    CASE WHEN (t_infomask2 & x'4000'::int) > 0 THEN 'HOT_UPDATED' END AS hot_old,

    CASE WHEN (t_infomask2 & x'8000'::int) > 0 THEN 'HEAP_ONLY' END AS hot_new

FROM heap_page_items(get_raw_page('hot_demo', 0));

Фокус на строки 1 и 5, относящиеся к обновлению alice:

• Указатель строки 1 помечает старый кортеж как мёртвый (t_xmax = 411761). Его t_ctid указывает вперёд на (0,5). Флаг HOT_UPDATED подтверждает, что преемник является heap-only кортежем.


• Указатель строки 5 содержит новый кортеж. Флаг HEAP_ONLY означает, что ни одна запись индекса не указывает прямо на этот кортеж. Он достижим только переходом по цепочке от указателя строки 1.

Индексы остаются нетронутыми. Они по-прежнему указывают на (0,1). При сканировании индекса PostgreSQL читает флаг HOT_UPDATED по адресу (0,1), переходит по t_ctid к (0,5) и возвращает текущую версию строки.

Цепочка ctid

Последовательные HOT-обновления образуют цепочки в пределах одной страницы:

UPDATE hot_demo SET score = 98.00 WHERE name = 'alice';

UPDATE hot_demo SET score = 97.00 WHERE name = 'alice';

Цепочка проходит через lp 1 -> lp 5 -> lp 6 -> lp 7. Промежуточные версии несут оба флага — HOT_UPDATED и HEAP_ONLY; они являются мёртвыми звеньями в середине цепочки.

И вот что делает этот механизм эффективным: индексы по-прежнему имеют ровно одну запись для alice, указывающую на (0,1). Сколько бы раз мы ни обновляли её оценку через HOT, индексы не растут. PostgreSQL проходит по цепочке от (0,1) через мёртвые промежуточные звенья, пока не достигает живого кортежа.

Длинные HOT-цепочки имеют свою цену: каждое сканирование индекса должно пройти по цепочке от начала до конца. Если вы 50 раз HOT-обновите одну и ту же строку между вакуумами, это 50 переходов. Цепочка ограничена одной страницей, поэтому вся работа происходит в памяти, но на высоконагруженных системах это может накапливаться.

Обрезка страницы (Page Pruning)

Когда PostgreSQL обращается к странице, содержащей мёртвые звенья HOT-цепочки, он может выполнить очистку оппортунистически. Никакого VACUUM, никакого фонового процесса, никакого специального планирования. Просто обычный запрос, который случайно обращается к странице и решает, что работа стоит того.

Обрезка (pruning) контролируется двумя проверками:

1. Проверка видимости: pd_prune_xid < RecentGlobalXmin — мёртвые кортежи невидимы для всех выполняющихся транзакций.


2. Проверка стоимости: страница должна быть достаточно заполнена, чтобы её уплотнение стоило затраченных тактов. Порог составляет примерно 10% свободного пространства.

Чтобы принудительно выполнить обрезку на демо-таблице, выполним VACUUM:

VACUUM hot_demo;

После этого структура страницы меняется:

• Указатель строки 1 становится LP_REDIRECT (lp_flags = 2) и начинает указывать прямо на указатель строки 7.


• Указатели строк 2, 5 и 6 становятся LP_UNUSED (lp_flags = 0).


• Указатель строки 7 уплотняется (compacted). Страница дефрагментируется, упаковывая выжившие кортежи в конец страницы.

LP_REDIRECT

PostgreSQL сохраняет перенаправление (redirect) по адресу указателя строки 1, потому что индексы по-прежнему ссылаются на (0,1). Освобождение этого указателя оставило бы висячие ссылки (dangling pointers). Перенаправление занимает только сам слот указателя строки (четыре байта) и сохраняется до тех пор, пока будущий VACUUM не перепишет записи индексов, чтобы они указывали непосредственно на (0,7). Только тогда указатель строки 1 может стать LP_UNUSED.

Когда HOT не работает

Обновление индексированного столбца name не может быть HOT-обновлением:

UPDATE hot_demo SET name = 'ALICE' WHERE name = 'alice';

Здесь новый кортеж попал на другой указатель строки, старый кортест был помечен как мёртвый, и PostgreSQL пришлось вставить новые записи во все индексы, потому что изменился физический адрес (ctid).

Второй распространённый сценарий отказа — нехватка места. Даже если вы обновляете только неиндексированные столбцы, полностью заполненная страница не оставляет PostgreSQL выбора, кроме как поместить новый кортеж на другую страницу. А обновление между страницами всегда означает обслуживание индексов, поскольку ctid меняется.

fillfactor: создание пространства для HOT

HOT-обновлениям нужно свободное пространство на странице. По умолчанию PostgreSQL заполняет страницы с fillfactor = 100, упаковывая их полностью при вставках. Первое обновление на полной странице вынуждено размещать новый кортеж в другом месте, и HOT-обновление становится невозможным.

Уменьшение fillfactor резервирует пространство специально для обновлений:

CREATE TABLE hot_ff_demo (

    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

    val text,

    counter integer DEFAULT 0

) WITH (fillfactor = 80);

fillfactor = 80 оставляет 20% страницы пустыми при вставках, давая HOT-цепочкам пространство для роста.

Правильный выбор fillfactor зависит от вашей рабочей нагрузки:

• Нагрузки с преимущественным чтением или только вставками: оставляйте 100.


• Частые обновления неиндексированных столбцов: 80–90.


• Широкие строки с высокой частотой изменений: 50–70.

Измерение эффективности HOT

SELECT

    relname,

    n_tup_upd,

    n_tup_hot_upd,

    round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct

FROM pg_stat_user_tables

WHERE n_tup_upd > 0

ORDER BY n_tup_upd DESC;

Снижающийся процент HOT на таблице с частыми обновлениями — сигнал для исследования. Обычные подозреваемые:

• Страницы заполнены (fillfactor слишком высок, или VACUUM не успевает освобождать место).


• Обновляются индексированные столбцы (ORM часто обновляют updated_at вместе с другими полями).


• Слишком много индексов (каждый индекс добавляет столбец в условие «ни один индексированный столбец не должен меняться»).

pd_prune_xid

После HOT-обновления (или любого обновления, создавшего мёртвый кортеж) поле заголовка страницы pd_prune_xid заполняется значением t_xmax самого старого мёртвого кортежа на странице, ожидающего обрезки.

В следующий раз, когда какой-либо фоновый процесс обратится к этой странице, он сравнит pd_prune_xid с RecentGlobalXmin (самый старый snapshot xmin среди всех активных транзакций, слотов репликации и подготовленных транзакций в кластере). Если мёртвые кортежи гарантированно невидимы для всех и страница достаточно заполнена, чтобы её уплотнение стоило усилий, срабатывает обрезка.

Длительные транзакции — враг обрезки страниц. Если транзакция началась час назад и всё ещё открыта, RecentGlobalXmin остаётся привязанным к её снимку. Никакие кортежи, созданные после этого момента, не могут быть обрезаны, независимо от того, насколько они мертвы.

HOT в одном абзаце

Когда PostgreSQL получает обновление, он проверяет две вещи: есть ли место на той же странице и все ли изменённые столбцы не индексированы? Если да на оба вопроса:

• Новый кортеж размещается на той же странице с флагом HEAP_ONLY.


• Старая версия помечается как HOT_UPDATED, с t_ctid, указывающим на новую.


• Устанавливается pd_prune_xid, сигнализируя о наличии мёртвых версий для очистки.


• Ни одна запись индекса не создаётся и не изменяется.

Позже, когда какой-либо фоновый процесс читает страницу, он проверяет: невидимы ли мёртвые версии для всех и достаточно ли страница заполнена, чтобы её уплотнение стоило усилий? Когда оба условия выполнены, срабатывает обрезка: промежуточные звенья цепочки становятся LP_UNUSED, исходный указатель строки превращается в LP_REDIRECT, который по-прежнему удовлетворяет все ссылки индексов, а страница дефрагментируется. Освобождённое пространство готово для следующего HOT-обновления.

При правильно подобранном fillfactor этот цикл может работать вечно, и VACUUM остаётся в значительной степени не у дел.