PostgreSQL internals · MVCC, WAL, vacuum, pgbouncer
Три часа ночи, на дежурном телефоне alert: на проде закончилось место на диске. Ты заходишь по ssh, df -h показывает 98% занято, а du -sh /var/lib/postgresql/data/* выдаёт удивительное: одна pg_wal/ весит 200 ГБ, а таблица homework_submissions, которая на проде должна быть 8 ГБ, физически занимает 60. При этом активных пользователей не прибавилось. Что произошло?
Чтобы ответить, придётся залезть внутрь Postgres: понять, как лежит строка, что такое мёртвая версия, почему WAL может разрастись до сотен гигабайт, и зачем над ним сидит autovacuum. Это самый плотный урок серии: тут лежит то, что Senior разбирает в проде, а Middle обычно знает поверхностно.
Мы не лезем в bgworker-internals, кастомные access methods, тонкости hot-standby query conflicts, репликационные слоты вглубь. Цель скромнее: понимать, почему в логах постоянно autovacuum, что значит txid wraparound и зачем в стеке между приложением и Postgres сидит pgbouncer. После уроков 01 фундаментальной части, 02 про ACID и MVCC поверхностно и 03 про распределённую часть у тебя есть язык, чтобы говорить о внутренностях.
Главная мысль урока: MVCC дёшево раздаёт снимки, и за эту дешевизну приходится платить мусором. Все десять разделов про одно: что это за плата (мёртвые tuple’ы, WAL, переполнение коннектов), кто за неё убирает (vacuum, checkpoint, pgbouncer) и где это ломается на проде.
К концу занятия ты сможешь:
- объяснить, как лежит одна строка в Postgres: 8K-страница, line pointer, tuple header с xmin/xmax;
- проследить, что физически происходит при INSERT, UPDATE и DELETE, и почему UPDATE никогда не меняет строку на месте;
- разобраться с autovacuum: его пороги, метрики, что чинит, чего не чинит, и когда вместо него зовут
pg_repack; - прочитать
pg_stat_replicationиpg_stat_wal, понять, почему диск подpg_walпухнет; - объяснить, что такое txid wraparound, почему БД переходит в read-only, и как этого избежать;
- выбрать режим pgbouncer (session, transaction, statement), зная, какие фичи Postgres из-за этого отвалятся.
0. Карта внутренностей за 5 минут
Postgres устроен как кластер процессов под одним postmaster. Никаких потоков, всё это отдельные UNIX-процессы, общающиеся через shared memory.
┌──────────────────────────────┐
client ──┐ │ postmaster │
client ──┤───▶ │ fork() ──> backend per conn │
client ──┘ └──────────────────────────────┘
│
▼
┌──────────────────────────────┐
│ shared memory │
│ shared_buffers (page cache) │
│ WAL buffers, lock tables │
│ procarray, ClogBuffers │
└──────────────────────────────┘
▲
┌────────────┬────┴───────┬────────────┬──────────────┐
│ │ │ │ │
walwriter checkpointer bgwriter autovacuum stats-collector
launcher (PG14+: cumulative stats)Что делает каждый:
| Процесс | Зачем |
|---|---|
postmaster | слушает порт, форкается на каждое соединение, мониторит детей |
backend (postgres: user db ...) | один на клиента, выполняет SQL |
| walwriter | пишет WAL-буфер на диск между fsync-ами |
| checkpointer | проводит контрольные точки: сбрасывает грязные страницы на диск |
| bgwriter | заранее выталкивает грязные страницы, разгружая backend |
autovacuum launcher | запускает воркеров, которые делают VACUUM и ANALYZE |
| autovacuum worker | конкретный VACUUM на конкретной таблице |
| walreceiver (на реплике) | принимает поток WAL от primary |
| walsender (на primary) | отдаёт WAL подключённой реплике или CDC-клиенту |
Эти процессы видишь в ps auxf | grep postgres. На каждом проде надо хотя бы раз посмотреть глазами, чтобы карта в голове стала своей.
Где что лежит на диске
$PGDATA/
├── base/ -- собственно данные
│ └── <db_oid>/
│ ├── <relation_oid> -- основной fork таблицы или индекса
│ ├── <relation_oid>_fsm -- free space map
│ ├── <relation_oid>_vm -- visibility map
│ └── <relation_oid>.1, .2 ... -- сегменты по 1 ГБ
├── pg_wal/ -- WAL-сегменты по 16 МБ
├── pg_xact/ (раньше pg_clog) -- статусы транзакций (commit/abort/in-progress)
├── pg_multixact/ -- многотранзакционные блокировки
├── pg_subtrans/ -- подтранзакции (SAVEPOINT)
├── postgresql.conf -- основной конфиг
├── pg_hba.conf -- авторизация
└── global/ -- catalog, общий для всех базКогда что-то идёт не так, смотреть начинаешь сюда. du -sh pg_wal показывает, не разрослись ли журналы. du -sh base/<oid> показывает физический размер БД, который часто сильно больше “логического”: разница это и есть bloat.
1. Страница 8K и tuple
Любые данные в Postgres лежат в страницах. Дефолтный размер страницы 8 КБ, задан на этапе сборки (./configure --with-blocksize=8), менять можно только пересборкой. На диске таблица это последовательность таких страниц, плюс файлы карт (_fsm и _vm) с метаданными.
Анатомия страницы
┌─────────────────────────────────────────────────────────────┐
│ PageHeaderData (24 байта) │
├─────────────────────────────────────────────────────────────┤
│ ItemIdData[]: line pointers (по 4 байта на строку) │
│ │ lp_off, lp_len, lp_flags │
│ ▼ │
│ │
│ СВОБОДНОЕ МЕСТО (растёт навстречу) │
│ │
│ ▲ │
│ │ tuples (заголовок + сами байты значений) │
├─────────────────────────────────────────────────────────────┤
│ special area (для индексов; в heap, как правило, пусто) │
└─────────────────────────────────────────────────────────────┘Сверху страница заполняется указателями (line pointers), снизу растут сами tuples. Между ними свободное место. Когда они встречаются, страница считается полной. Это и есть единица I/O: Postgres всегда читает и пишет целыми 8-КБ страницами, не отдельными строками.
Tuple header
Каждая строка хранит ~23 байта служебного заголовка перед самими данными:
// упрощённо, из htup_details.h
typedef struct HeapTupleHeaderData {
TransactionId t_xmin; // транзакция, создавшая версию
TransactionId t_xmax; // транзакция, удалившая или обновившая
CommandId t_cid; // или t_xvac, для VACUUM FULL
ItemPointerData t_ctid; // указатель на эту же tuple или на новую версию
uint16 t_infomask2;
uint16 t_infomask; // флаги: HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED, ...
uint8 t_hoff; // длина заголовка
bits8 t_bits[]; // битмап NULL-полей, если есть
// ... здесь начинаются сами данные
} HeapTupleHeaderData;Ключевые поля:
t_xmin, id транзакции, которая создала эту версию;t_xmax, id транзакции, которая её удалила (0, если живая);t_ctid, ссылка на саму себя или на следующую версию (для UPDATE);t_infomask, флаги: HEAP_HASNULL, HEAP_HASVARWIDTH, HEAP_XMIN_COMMITTED, HEAP_XMAX_COMMITTED и пр.
Имея только эти поля, любой backend по своему snapshot может решить: видна мне эта строка или нет. Это и есть основа MVCC.
Что значит “tuple”
В Postgres-сообществе слова “row” и “tuple” путают на каждом шагу. Аккуратно:
- row, логическая сущность, “одна строка таблицы” с точки зрения пользователя;
- tuple, физическая версия этой строки в storage. Одной row могут соответствовать несколько tuple’ов одновременно, если разные транзакции видят разные версии.
UPDATE не “меняет row”, а создаёт новый tuple и помечает старый. Эта разница ключевая: на неё опирается всё остальное в этом уроке.
Размер строки и почему SELECT * дорог
Заголовок 23 байта + битмап NULL + сам payload + выравнивание до 8 байт. На таблицу со 100 байт полезных данных получается ~140 байт реальных. На таблицу с одним boolean, всё равно 28+ байт.
Отдельно дорого, когда поле большое (text, jsonb, bytea): если строка не помещается в 8K, то либо она компрессируется, либо большие колонки выносятся в TOAST-таблицу (раздел 6). Поэтому SELECT * по таблице с тяжёлым jsonb это всегда трогание TOAST и десятки лишних страниц на каждую строку.
Visibility map и free space map
Рядом с основным файлом таблицы (16384) лежат два спутника: 16384_vm и 16384_fsm.
- Visibility Map (
_vm): по биту на страницу, “все ли tuple’ы на этой странице видны всем”. Если бит выставлен, planner может пропустить страницу при сканировании или сделать Index Only Scan без обращения к heap (см. урок 03). VM обнуляется при любом изменении страницы и снова выставляется при VACUUM. - Free Space Map (
_fsm): для каждой страницы примерное количество свободного места, чтобы при INSERT быстро найти подходящую.
Их размер невелик (десятки килобайт на гигабайтную таблицу), но они радикально ускоряют сканы и вставку. После аварии (kill -9, рестарт без чистого shutdown) они могут оказаться рассинхронизированы; чинят командой VACUUM с флагом (DISABLE_PAGE_SKIPPING).
2. MVCC от лица tuple
Теперь, когда есть язык tuple-ов, можно перепроходить MVCC по-настоящему, а не как в уроке 02. Сценарий: один пользователь делает UPDATE, второй читает в той же таблице параллельно. Что физически происходит на диске?
Транзакция и её снимок
Каждая транзакция получает snapshot: компактную запись о том, кто сейчас активен, начиная с какой xid и какие транзакции идут параллельно. Снимок берётся при первом запросе в Read Committed (на каждый запрос свой) или в начале блока в Repeatable Read.
сделать
txid snapshot
──── ────────────────
100 committed
101 committed
102 committed
103 in progress ──┐
104 in progress │ xip = [103, 104]
105 in progress ──┘
106 -- наш txid
107 -- next txidИмея этот snapshot, проверка видимости tuple-а сводится к ответу на:
xminуже committed на момент снимка?xmaxлибо 0 (живая), либо aborted, либо ещё не committed на момент снимка?
Если оба да, tuple виден текущей транзакции. Иначе пропускаем.
Где Postgres смотрит, committed ли xid? В pg_xact/: для каждой xid там 2 бита, IN_PROGRESS / COMMITTED / ABORTED. Этот файл маленький и почти всегда влезает в кэш, поэтому проверка дешёвая.
INSERT, UPDATE, DELETE на уровне tuple
-- t0: создаём строку
INSERT INTO h (id, state) VALUES (42, 'pending');В heap появляется tuple с t_xmin = 200 (наш txid), t_xmax = 0, t_ctid = (page, line) (указывает сам на себя).
-- t1: меняем state
UPDATE h SET state = 'review' WHERE id = 42;Происходит два действия:
- Старая tuple помечается:
t_xmax = 201,t_ctidостаётся, потом будет переписан на новую. - Создаётся новая tuple на той же или соседней странице:
t_xmin = 201,t_xmax = 0,state = 'review'. t_ctidстарой переписывается на адрес новой.
Получается цепочка версий: old -> new. Старая ещё видна транзакциям, у которых snapshot до 201; новая видна тем, у кого snapshot >= 201 (если 201 уже committed).
-- t2: удаляем
DELETE FROM h WHERE id = 42;Просто проставляется t_xmax = 202. Никаких новых tuple, никакого освобождения места. Старая версия остаётся, пока её не увидит VACUUM.
Прогон выше показывает ту же сцену, но руками. В режиме “обычный” каждый UPDATE добавляет tuple и свой line pointer, индексы растут. Переключи на “HOT” и пройди шаги заново, увидишь, как старый line pointer становится REDIRECT, а новые версии живут на той же странице без индексных записей. VACUUM в конце убирает оба случая до пустой страницы; раздел 3 и раздел 4 про то, как это работает в проде.
Cmin, cmax и команда внутри транзакции
Поле t_cid хранит CommandId: порядковый номер команды внутри одной транзакции. Это нужно, чтобы внутри одной транзакции команда #2 видела результат команды #1, но не наоборот.
BEGIN;
INSERT INTO h VALUES (...); -- cmd 0
SELECT * FROM h WHERE ...; -- cmd 1: видит свою же вставку
UPDATE h SET state = ...; -- cmd 2: видит и обновляет
SELECT * FROM h; -- cmd 3: видит уже обновлённое
COMMIT;В реальности t_cid используется как cmin или cmax в зависимости от роли (создатель или удалитель). Это техническая деталь, но именно она объясняет, почему SELECT после UPDATE в той же транзакции видит изменение.
Snapshot isolation и сериализуемость
В уроке 02 разбирали уровни изоляции; теперь видно, как они реализованы. Read Committed = новый snapshot на каждый запрос. Repeatable Read = один snapshot на всю транзакцию (Postgres называет его snapshot isolation). Serializable = snapshot isolation + механизм SSI (Serializable Snapshot Isolation), который ловит конфликты “опасных” read-write зависимостей и откатывает одну из транзакций.
Снимки в Postgres дешёвые: чтения не блокируют ничего, никаких MVCC-локов на чтение нет. Это и есть та самая “readers don’t block writers, writers don’t block readers”: на физическом уровне читатель смотрит на t_xmin / t_xmax, не трогая блокировок.
Цена этой архитектуры одна: каждый UPDATE и DELETE рождает мёртвую версию. Раздел 4 про то, как от неё избавляться.
3. HOT updates и fill factor
Без оптимизаций каждый UPDATE стоит так: одна новая tuple в heap плюс обновление всех индексов на эту строку. Если у тебя на таблице 6 индексов, UPDATE одного поля стоит 7 операций записи (heap + 6 индексов).
Postgres сильно умнее. Если выполнены два условия:
- ни одна индексируемая колонка не изменилась;
- новая версия влезает на ту же страницу, что и старая,
UPDATE выполняется как HOT update: индексы не трогаются вообще. Линия пойнтер старой версии становится REDIRECT и указывает на новый, а индексные записи продолжают смотреть на старый, через который попадают на актуальную версию.
До UPDATE:
ItemId[1] ─▶ tuple₁ (xmin=100, xmax=0, ctid=(0,1))
indexes ──▶ ItemId[1]
После UPDATE с HOT:
ItemId[1] ─▶ REDIRECT ─▶ ItemId[2]
ItemId[2] ─▶ tuple₂ (xmin=200, xmax=0, ctid=(0,2))
tuple₁ (xmin=100, xmax=200, ctid=(0,2))
indexes ──▶ ItemId[1] -- ничего не поменялосьКогда VACUUM придёт, он схлопнет HOT-цепочку: tuple₁ удалится, ItemId[1] получит данные от tuple₂, и индексы продолжат указывать корректно. Никакого REINDEX, никаких сломанных индексов.
Как проверить, идёт ли HOT
В pg_stat_user_tables есть колонки n_tup_upd (всего UPDATE) и n_tup_hot_upd (из них HOT). Хорошее соотношение, 80%+ 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-ratio < 30%, у тебя проблема: либо UPDATE-ы массово меняют индексируемые колонки (надо подумать о схеме), либо страницы плотно набиты и места под HOT нет (надо снижать fillfactor).
Fillfactor
Fillfactor, это параметр таблицы или индекса: процент страницы, до которого Postgres готов заполнять её при INSERT. Default для heap 100 (заполняем полностью), для B-tree 90. Снижая до 80 или 70, ты резервируешь свободное место под будущие HOT-updates.
ALTER TABLE homework_submissions SET (fillfactor = 80);
-- применится к новым страницам, к старым -- только после VACUUM FULL или CLUSTERКогда стоит снижать? Если таблица в основном append-only (логи, события), fillfactor 100 нормален: ничто никогда не обновляется, место под HOT не нужно. Если таблица update-heavy (статусы, счётчики, last_seen), fillfactor 70-85 даёт +20-30% к скорости UPDATE за счёт HOT.
Антипаттерн: индекс по часто меняющемуся полю
CREATE INDEX ON users (last_seen) ломает HOT для каждого UPDATE поля last_seen. А last_seen меняется на каждый запрос. Лучше либо не индексировать (если выбирают по нему редко), либо вынести last_seen в отдельную таблицу user_activity, обновляемую асинхронно. Или вообще считать last_seen в Redis (см. урок 05) и сбрасывать раз в N минут.
4. Dead tuples, bloat, autovacuum
Помнишь те 60 ГБ на месте 8-гигабайтной таблицы из вступления? Это и есть bloat. Постоянный поток UPDATE-ов и DELETE-ов оставляет за собой мёртвые версии: tuple’ы, у которых t_xmax ссылается на committed-транзакцию, и ни одна живая транзакция их уже не увидит. Физически они лежат в heap, занимают место. От них таблица пухнет.
Что такое bloat и как его измерить
Bloat это разница между “сколько данных реально нужно” и “сколько физически занято”. Источники:
- мёртвые tuple’ы, оставшиеся после UPDATE и DELETE;
- старые версии в HOT-цепочках, не схлопнутые VACUUM-ом;
- свободное место на странице, которое не используется при следующих вставках (из-за выравниваний, fillfactor);
- удалённые индексы и индексы с большой “разреженностью” в листьях.
Точно мерить bloat умеет расширение pgstattuple:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('homework_submissions');
-- table_len, tuple_count, tuple_len, dead_tuple_count,
-- dead_tuple_len, free_space, ...
SELECT * FROM pgstatindex('homework_submissions_pkey');
-- index_size, internal_pages, leaf_pages, empty_pages, ...Быстро и без блокировок, через pg_stat_user_tables:
SELECT relname,
n_live_tup,
n_dead_tup,
round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
last_autovacuum,
last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;Норма для OLTP, dead_pct < 10-20%. Если стабильно > 30%, autovacuum не справляется.
Что делает VACUUM
VACUUM table (или autovacuum) выполняет:
- Сканирует страницы таблицы (только те, что менялись с прошлого VACUUM, благодаря Visibility Map).
- На каждой странице находит tuple’ы с
t_xmax, видимым как уже невозможный (committed и старше OldestXmin). - Помечает их line pointer как
LP_DEAD. - Удаляет соответствующие записи из всех индексов.
- Помечает line pointer как
LP_UNUSED, освобождая место для следующей вставки на этой же странице. - Обновляет Visibility Map и Free Space Map.
- Обновляет
pg_class.relfrozenxid(см. раздел 8 про wraparound).
Что VACUUM НЕ делает: не возвращает место ОС. Если в конце таблицы появились пустые страницы, они отдадутся системе через truncate, но дырки внутри файла останутся. Таблица в 60 ГБ после VACUUM может остаться в 60 ГБ, даже если данных в ней на 8 ГБ.
VACUUM FULL и pg_repack
Чтобы сжать файл по-настоящему, нужен VACUUM FULL, который переписывает таблицу заново. Он держит ACCESS EXCLUSIVE лок: ни SELECT, ни INSERT не пройдут, пока он не закончит. Для 100-гигабайтной таблицы это часы простоя.
На проде вместо VACUUM FULL используют pg_repack: он делает то же самое, но без долгой блокировки. Под капотом он копирует данные в shadow-таблицу через триггеры, держа ACCESS EXCLUSIVE только в самом конце, для свопа, на доли секунды.
# на сервере с pg_repack
pg_repack -d batschool -t public.homework_submissionsПодвох: нужен PRIMARY KEY или UNIQUE NOT NULL, нужно столько же свободного места на диске, сколько весит таблица. Поэтому борьба с bloat это не разовая акция, а гигиена: лучше настраивать autovacuum, чем раз в полгода поднимать pg_repack ночью.
Autovacuum: пороги и тюнинг
Autovacuum запускается раз в autovacuum_naptime (default 1 минута), читает статистику и решает, на какие таблицы запустить VACUUM или ANALYZE.
Формулы порогов:
threshold_vacuum = autovacuum_vacuum_threshold -- default 50
+ autovacuum_vacuum_scale_factor -- default 0.2 (= 20%)
* reltuples
threshold_analyze = autovacuum_analyze_threshold -- default 50
+ autovacuum_analyze_scale_factor -- default 0.1 (= 10%)
* reltuplesСтандартные дефолты разумны для маленьких таблиц. Для большой таблицы они катастрофичны: на таблице в 100M строк autovacuum запустится только после 20M мёртвых tuple’ов, и сам по себе пройдёт за часы. Решение, тюнить per-table:
ALTER TABLE homework_submissions SET (
autovacuum_vacuum_scale_factor = 0.02, -- запускать после 2%
autovacuum_vacuum_threshold = 10000,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2, -- меньше пауза = быстрее
autovacuum_vacuum_cost_limit = 2000 -- больше работы за раунд
);Глобальные параметры в postgresql.conf:
autovacuum_max_workers = 5 -- больше воркеров параллельно
autovacuum_vacuum_cost_limit = 2000 -- общий бюджет на всех (если -1, локальный с каждой таблицы)
maintenance_work_mem = '1GB' -- память на один VACUUM-воркерmaintenance_work_mem важный: чем он больше, тем больше мёртвых tuple-id’ов воркер держит в памяти за один проход. Маленький maintenance_work_mem заставляет VACUUM проходить таблицу несколько раз.
Сигналы, что autovacuum проигрывает
n_dead_tupстабильно растёт быстрее, чем уменьшается;- в логах
autovacuum: VACUUM ... runtime: ... hours; pg_stat_activityпоказывает две-три долго работающие автовакуум-сессии;pg_stat_user_tables.last_autovacuumотстаёт на сутки и больше;- метрика времени запросов на таблице тихо ползёт вверх.
Когда autovacuum проигрывает, обычно лечат:
- Тюнят per-table параметры (выше).
- Увеличивают
maintenance_work_mem. - Снижают
autovacuum_vacuum_cost_delay(паузу между порциями работы). - Иногда добавляют расписание ручного
VACUUM (PARALLEL 4)ночью (PG13+).
Длинные транзакции, главный убийца vacuum
VACUUM может удалить tuple, только если её t_xmax старше OldestXmin, то есть старше самой старой ещё активной транзакции. Если у тебя где-то висит транзакция, открытая 6 часов назад (забытый BEGIN, idle-in-transaction в коде, отвалившийся batch-job), все мёртвые tuple’ы за эти 6 часов не могут быть удалены. Они физически остаются в таблице, и autovacuum проходит вхолостую.
Найти и убить:
SELECT pid,
state,
now() - xact_start AS xact_age,
query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'active')
AND xact_start IS NOT NULL
ORDER BY xact_age DESC;
-- если нашёл древнюю idle-in-transaction:
SELECT pg_terminate_backend(<pid>);В коде ставят таймауты:
SET idle_in_transaction_session_timeout = '5min';
SET statement_timeout = '60s';
SET lock_timeout = '5s';idle_in_transaction_session_timeout автоматически убивает соединение, висящее в idle in transaction дольше N. Если ты Senior, тебе должно быть стыдно за прод без этого таймаута.
дальше после входа
Урок целиком открыт всем, кто вошёл. Войди по своему email, чтобы читать дальше.
Вход пока по приглашению. Если хочешь учиться, напиши Vassiliy.