Служебные поля версии

У каждого кортежа в заголовке есть xmin (xid транзакции, создавшей версию), xmax (xid транзакции, удалившей или заменившей её; 0, если версия актуальна), ctid (физический адрес: страница, смещение), плюс cmin/ cmax для видимости внутри собственной транзакции. Их можно прямо посмотреть: SELECT xmin, xmax, ctid, * FROM accounts; - отличная демонстрация на собесе.

Снапшот

Снапшот состоит из трёх частей: xmin (все транзакции старше уже завершены), xmax (всё новее ещё не начиналось) и xip, список активных транзакций на момент снимка. Версия видна, если её создатель закоммичен и не входит в «активные или будущие», и при этом её xmax либо нулевой, либо принадлежит транзакции, которая для нас ещё не завершилась.

Почему из этого следует отсутствие грязного чтения

У незакоммиченной версии xmin принадлежит активной транзакции - правило видимости отбрасывает её у всех, кроме самой этой транзакции. Показать грязные данные в PostgreSQL физически нечем, поэтому READ UNCOMMITTED совпадает с READ COMMITTED.

Чем платим

• Мёртвые версии копятся в таблице → нужен VACUUM, возможен bloat.
• Раздувание индексов: каждая новая версия строки (кроме HOT) добавляет записи во все индексы.
• count(*) не может быть мгновенным: видимость строки зависит от снапшота, поэтому приходится сканировать (спасает частично index-only scan через visibility map).
• Долгая транзакция вредит всей базе, удерживая горизонт очистки.

Пошагово

1. Движок находит текущую версию строки и берёт на неё блокировку.
2. Формирует новую версию целиком - со всеми колонками, даже неизменёнными.
3. Пишет её в ту же страницу, если есть место, иначе в другую.
4. Старой версии проставляет xmax = мой xid и связывает её ctid с новой.
5. Добавляет записи во все индексы, если это не HOT-обновление.
6. Пишет всё это в WAL (а после контрольной точки - ещё и полную страницу целиком).

Отсюда неочевидные следствия: обновление одного boolean в строке с jsonb на 500 КБ создаёт новую версию строки (правда, неизменённые TOAST-чанки переиспользуются); таблица, где 1000 раз в секунду инкрементируется счётчик, растёт со скоростью 1000 мёртвых версий в секунду; ROLLBACK ничего не откатывает физически - записанные версии просто остаются мусором.

HOT (Heap-Only Tuple)

Если новая версия помещается в ту же страницу и ни одна проиндексированная колонка не изменилась, PostgreSQL не трогает индексы вообще: индексная запись продолжает указывать на старую версию, а внутри страницы выстраивается HOT-цепочка, по которой движок доходит до актуальной. Так получается меньше записи в WAL, индексы не пухнут, а очистка цепочки может произойти прямо при обращении к странице, без полноценного VACUUM. С PG 16 HOT не ломается и при изменении колонок, на которых есть только BRIN-индекс: обновится лишь его сводка.

Что делает VACUUM

• Помечает место мёртвых версий свободным для повторного использования (файл при этом не уменьшается).
• Удаляет соответствующие записи из всех индексов.
• Обновляет visibility map, битовую карту «в этой странице все версии видны всем», без которой index-only scan теряет смысл: ему приходится ходить в таблицу.
• Замораживает старые кортежи, отодвигая угрозу transaction ID wraparound.
• Попутно (через autovacuum) запускается ANALYZE, обновляющий статистику.

Обычный VACUUM берёт SHARE UPDATE EXCLUSIVE и не мешает SELECT/INSERT/UPDATE. VACUUM FULL устроен совсем иначе: переписывает таблицу в новый файл, возвращает место ОС, но берёт ACCESS EXCLUSIVE, то есть таблица недоступна полностью. На проде вместо него используют pg_repack.

Почему возникает bloat

1. Долгая транзакция или idle in transaction удерживает горизонт, и VACUUM не имеет права удалять новые мёртвые версии.
2. Забытый replication slot держит xmin бессрочно. Причина самая коварная: реплики уже может не быть, а слот остался.
3. Дефолтный autovacuum_vacuum_scale_factor = 0.2 на большой таблице: уборка начнётся, когда мёртвых накопится 20 %.
4. Autovacuum душат настройками (cost_delay, мало воркеров) - он не успевает за нагрузкой.
5. Массовые UPDATE/DELETE одной транзакцией.

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC LIMIT 20;

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);  -- чистить чаще
ALTER TABLE orders SET (fillfactor = 85);                        -- больше HOT

Правило упреждающей записи

Любое изменение сначала описывается записью в WAL, и только потом может быть сброшено в файл данных. Страницы меняются в shared_buffers и остаются «грязными»; COMMIT ждёт fsync журнала, а не страниц. Если сервер упадёт, при старте PostgreSQL проиграет WAL с последней контрольной точки и восстановит все закоммиченные изменения.

Почему это ещё и быстро

В WAL пишут последовательно, в один файл. Изменённые страницы сбрасываются случайной записью по всему файлу таблицы. Разница на порядок даже на SSD и на два порядка на HDD. Плюс несколько транзакций объединяют свои fsync в один (group commit). Так что WAL отвечает и за надёжность, и за производительность записи.

Checkpoint

Контрольная точка сбрасывает все грязные страницы на диск и записывает в журнал отметку «до этого места всё гарантированно в файлах данных». Она определяет, откуда начнётся восстановление. Настраивается через checkpoint_timeout и max_wal_size: чем реже контрольные точки, тем меньше нагрузка на диск в обычном режиме, но тем дольше recovery после аварии и больше объём WAL. checkpoint_completion_target размазывает запись во времени, чтобы не было всплеска I/O. Отдельно запомни full_page_writes: первая модификация страницы после контрольной точки пишет в WAL всю страницу. Так база защищается от частично записанных страниц, и отсюда же пики объёма WAL сразу после checkpoint.

Связь с репликацией и бэкапами

Физическая репликация буквально передаёт поток WAL на реплику, где он проигрывается. Логическая декодирует тот же WAL в логические изменения строк (wal_level = logical). PITR строится на базовой копии и архиве WAL и позволяет восстановиться на любой момент времени. Один механизм закрывает три задачи, и это хороший способ показать системное понимание.

Строка в PostgreSQL не может пересекать границу страницы, а страница - 8 КБ. Как только версия строки перестаёт влезать примерно в 2 КБ, включается TOAST: движок берёт самые крупные колонки переменной длины и сначала пытается их сжать (по умолчанию pglz, с PG 14 доступен lz4). Если этого мало - выносит значение в таблицу pg_toast.pg_toast_NNNN, нарезая на чанки примерно по 2 КБ, а в самой строке оставляет указатель. Одно TOAST-значение ограничено 1 ГБ.

Почему это важно знать на практике

• Ленивое чтение. SELECT id, status по таблице с гигантским jsonb не читает TOAST вообще, а SELECT * вытащит и распакует всё. Это одна из самых конкретных причин не писать «звёздочку».
• Место. pg_relation_size не включает TOAST, а pg_total_relation_size включает. В расхождении этих цифр обычно и кроется ответ на «куда делось место».
• Обновления. Новая версия строки создаётся всегда, но неизменённые TOAST-чанки переиспользуются, а не копируются.
• Стратегии хранения задаются на колонку: EXTENDED (сжимать и выносить, дефолт), EXTERNAL (только выносить - быстрее substring()), MAIN (сжимать, выносить в крайнем случае), PLAIN (ничего).

pg_stat_activity - здесь и сейчас

SELECT pid, usename, state,
       now() - xact_start  AS tx_age,
       now() - query_start AS query_age,
       wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 90)
FROM   pg_stat_activity
WHERE  backend_type = 'client backend' AND state <> 'idle'
ORDER  BY tx_age DESC NULLS LAST;

Что смотреть: state = 'active' с большим query_age выдаёт тормозящие запросы, state = 'idle in transaction' показывает забытые транзакции, которые блокируют VACUUM, а непустой blocked_by означает цепочки блокировок; wait_event_type подскажет природу ожидания (Lock, IO, LWLock, Client). На аварийный случай есть pg_cancel_backend (отменить запрос) и pg_terminate_backend (убить соединение).

pg_stat_statements - накопленная картина

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows / GREATEST(calls, 1)          AS rows_per_call,
       shared_blks_read, shared_blks_hit,
       left(query, 100)
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

Расширение требует shared_preload_libraries = 'pg_stat_statements' и рестарта. Оно нормализует запросы: константы заменяются на $1, поэтому миллион однотипных запросов схлопывается в одну строку с большим calls.

Как читать

• Сортировать по total_exec_time, а не по среднему: запрос на 8 мс, вызываемый 40 тыс. раз в минуту, вреднее запроса на 3 секунды раз в час.
• Огромный calls при крошечном mean - почти наверняка N+1 из ORM.
• Большой rows_per_call значит, что запрос тащит в приложение лишнее.
• Если shared_blks_read сравним с hit, данные не помещаются в кэш и чтение идёт с диска; повод посмотреть индексы или shared_buffers.
• Большой stddev_exec_time - план нестабилен: разные параметры дают разные планы.
• pg_stat_statements_reset() перед экспериментом, чтобы видеть эффект изменения чисто.