Скрытая цена MVCC в PostgreSQL: мёртвые строки и борьба с раздуванием

Скрытая цена MVCC в PostgreSQL: мёртвые строки и борьба с раздуванием

Анатомия многоверсионности: почему UPDATE создает новую строку

Архитектура управления конкурентным доступом с помощью многоверсионности (Multi-Version Concurrency Control, MVCC) является основой СУБД PostgreSQL. Главная задача MVCC — обеспечить высокую параллельность, при которой операции чтения не блокируют запись, а запись не препятствует чтению. Каждая транзакция видит согласованный снимок данных (snapshot), актуальный на момент ее начала.

За отсутствие блокировок чтения PostgreSQL платит физической нагрузкой на пространство хранения таблиц. В PostgreSQL операция UPDATE не перезаписывает данные на месте. Система создает в файле таблицы новую физическую версию строки (tuple), а старую помечает как устаревшую в заголовке xmax.

Пока старая версия видна хотя бы одной активной транзакции, она остается на диске. Когда транзакции завершаются, версия превращается в «мертвый кортеж» (dead tuple). Накопление миллионов таких мертвых строк приводит к физическому раздуванию (bloat) таблиц и вторичных индексов, снижая скорость выполнения запросов.

Скрытый механический след: физическое раздувание и рост WAL

Издержки MVCC критичны на таблицах с высокой частотой обновлений (write-heavy workload). При постоянном обновлении статусов или счетчиков страница таблицы размером 8 КБ быстро заполняется устаревшими версиями.

При новой версии строки в куче она получает новый физический адрес (TID). По умолчанию все вторичные индексы таблицы (по полям email, status, created_at) должны получить новую запись, указывающую на этот адрес.

Радим Марек (Radim Marek) провел эксперимент: при обновлении 100 000 неиндексируемых полей в таблице без вторичных индексов система сгенерировала 36 МБ логов предзаписи (WAL) и 302 510 записей WAL. На идентичной таблице с первичным ключом и четырьмя вторичными индексами объем WAL вырос до 69 МБ, а количество записей WAL превысило 709 000.

Изменение одного второстепенного поля при множестве индексов создает высокую нагрузку на дисковый ввод-вывод, забивает бинарные логи и замедляет репликацию на ведомые узлы.

Спасительный механизм HOT Updates и почему он ломается

Для смягчения раздувания индексов инженеры PostgreSQL разработали оптимизацию Heap-Only Tuples (HOT Updates).

Если новая версия строки помещается на ту же страницу кучи (8 КБ), где лежала старая, и UPDATE не затрагивает столбцы вторичных индексов, PostgreSQL не создает новые записи во вторичных индексах. В заголовке старой строки создается указатель на новую версию внутри страницы. Вторичные индексы ссылаются на старый адрес, а внутренний обход страницы идет по цепочке.

Механизм HOT зависит от двух условий:

1. Неизменяемость индексируемых колонок: если UPDATE меняет столбец из индекса, HOT не применяется.
2. Свободное место на странице: если страница заполнена на 100%, новая версия уходит на другую страницу, что ломает HOT.

Для запаса места используется параметр fillfactor (по умолчанию 100). Установка fillfactor = 70 резервирует 30% объема под будущие HOT-версии. Однако полезная плотность хранения данных снижается, а объемы чтения при последовательном сканировании всей таблицы вырастают.

Заблокированная очистка: как долгие транзакции останавливают VACUUM

Фоновый процесс autovacuum сканирует страницы, удаляет мертвые кортежи и помечает освободившееся пространство в карте свободного пространства (Free Space Map).

Однако autovacuum бессилен при долгих транзакциях. Граница очистки определяет самый старый снимок данных, который читает активная сессия. Забытая сессия в режиме REPEATABLE READ создает барьер.

В статистике такие строки обозначаются как «dead but not removable». Таблица раздувается, autovacuum совершает бессмысленные циклы сканирования, а производительность базы падает.

Различают два типа очистки:

• Обычный VACUUM (и autovacuum): освобождает место внутри страниц для новых записей. Он не возвращает место операционной системе и не уменьшает физический размер файла на диске.
• VACUUM FULL: полностью перестраивает таблицу в новый файл, возвращая место ОС. Операция захватывает эксклюзивную блокировку (AccessExclusiveLock), блокируя читателей и писателей.

Стратегия эксплуатации и практический чек-лист базы данных

Для предотвращения деградации PostgreSQL рекомендуются следующие шаги:

1. Отслеживание HOT-обновлений: проверять представление pg_stat_all_tables. Соотношение n_tup_hot_upd к n_tup_upd показывает долю HOT-операций. При низком проценте проверить индексы и fillfactor.
2. Контроль долгих сессий: настроить разрыв зависших транзакций через idle_in_transaction_session_timeout и statement_timeout.
3. Персональная настройка autovacuum: для гигантских таблиц задавать индивидуальные параметры autovacuum_vacuum_scale_factor и autovacuum_vacuum_threshold.
4. Контроль зацикливания Transaction ID (wraparound): следить за заморозкой старых строк (freezing) во избежание блокировки записи.

SQL-запрос для аудита интенсивно изменяемых таблиц:

SELECT relname, n_tup_upd, n_tup_hot_upd, n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY n_dead_tup DESC;

Архитектурный контекст: сравнение с подходом других СУБД

Многоверсионность реализуется в разных СУБД по-разному. Сравнение подходов показывает осознанность компромиссов PostgreSQL:

• PostgreSQL (Heap storage): хранит все версии строк в файлах кучи. Плюс — быстрое создание версий. Минус — необходимость уборки мертвых строк и раздувание индексов.
• MySQL InnoDB / Oracle (Undo logs): хранят в таблице только последнюю версию строки, вытесняя старые в сегменты откатов (Undo Logs). Плюс — нет мертвых строк в таблице. Минус — нагрузка на очистку Undo и замедление долгих чтений.
• SQL Server (Version Store): использует tempdb для хранения версий строк при snapshot isolation.
• LSM-движки и etcd (Compaction): записывают изменения последовательно, убирая старые версии во время уплотнения (compaction).

Каждая СУБД выбирает, когда платить за изолированность транзакций. Понимание этих механизмов позволяет инженерам PostgreSQL грамотно проектировать схемы данных и поддерживать высокую производительность системы.