Базы данных/Продвинутый ~ 60 мин

PostgreSQL: архитектура и MVCC

PostgreSQL кажется простым: приложение отправляет SQL, база возвращает строки. Но под этим интерфейсом живёт много механизмов: отдельный backend process на соединение, общая память, WAL, чекпоинты, фоновые процессы, MVCC, autovacuum, статистика и планировщик.

Для Go backend-разработчика это не академическая деталь. От понимания внутренней модели зависит, почему UPDATE раздувает таблицу, почему длинная транзакция ломает vacuum, почему SELECT может видеть старую версию строки, почему EXPLAIN показывает Heap Fetches, и почему база может быть медленной даже без очевидных lock'ов.

Go service
   │ SQL over TCP / Unix socket
   ▼
PostgreSQL backend process
   │
   ├─ shared buffers
   ├─ WAL buffers -> WAL files
   ├─ locks / snapshots / transaction state
   └─ heap tables + indexes + TOAST

PostgreSQL 18 - текущая major-версия документации на момент написания. Большинство идей ниже стабильны много лет, но детали EXPLAIN, vacuum и настройки могут развиваться.

Архитектура PostgreSQL

Process model

PostgreSQL использует process-based architecture. Главный процесс postmaster принимает соединения и создаёт отдельный server process для клиента. Часто его называют backend process.

postmaster
  ├─ backend for connection #1
  ├─ backend for connection #2
  ├─ checkpointer
  ├─ background writer
  ├─ walwriter
  ├─ autovacuum launcher
  ├─ autovacuum workers
  ├─ stats collector / cumulative stats
  └─ optional workers: logical replication, parallel query, extensions

Следствия для приложения:

каждое соединение не бесплатно;
max_connections=1000 обычно хуже, чем pooler + разумное число backend'ов;
память вроде work_mem выделяется не "на сервер", а на операции внутри запросов и может умножаться на число активных соединений;
долгий запрос занимает отдельный backend process, держит snapshot, locks и ресурсы.

В Go это означает: database/sql или pgxpool должны иметь лимиты. Если каждый pod держит по 100 соединений, а pod'ов 30, PostgreSQL увидит 3000 потенциальных backend'ов. Для production часто используют PgBouncer или другой connection pooler перед PostgreSQL.

Shared memory и caches

PostgreSQL хранит общие структуры в shared memory. Самая известная - shared_buffers: кэш страниц таблиц и индексов внутри PostgreSQL.

query
  │
  ├─ ищет страницу в shared_buffers
  │    ├─ hit  -> читает из памяти PostgreSQL
  │    └─ miss -> читает с диска / OS cache -> кладёт в shared_buffers
  │
  └─ меняет страницу -> page becomes dirty

Важно не путать:

shared_buffers - кэш страниц внутри PostgreSQL;
page cache операционной системы - кэш файлов на уровне OS;
work_mem - память на sort/hash/materialize операции внутри запроса;
maintenance_work_mem - память для VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY;
temp_buffers - session-local buffers для temporary tables.

Официальная документация даёт practical starting point для выделенного DB-сервера: shared_buffers часто начинают примерно с 25% RAM, но редко имеет смысл уходить сильно выше 40%, потому что PostgreSQL всё равно полагается на OS cache.

Для разработчика важнее не запомнить магическое число, а понимать trade-off:

маленький shared_buffers -> больше чтений через OS/disk;
слишком большой shared_buffers -> больше давление на memory и checkpoint/WAL поведение;
большой work_mem при сотнях соединений -> риск OOM;
маленький work_mem -> sort/hash spill на диск.

WAL: журнал перед данными

WAL - Write-Ahead Log. Идея: перед тем как изменённая data page считается durable, PostgreSQL записывает описание изменения в WAL.

UPDATE users SET name = 'Ann' WHERE id = 10;

изменить страницу в shared buffers
записать WAL record
на COMMIT гарантировать WAL до нужного LSN
позже dirty page попадёт в data file

WAL нужен для:

crash recovery: после падения PostgreSQL проигрывает WAL и доводит data files до согласованного состояния;
replication: standby получает WAL и применяет изменения;
PITR: base backup + archived WAL позволяют восстановиться на момент времени;
logical decoding: изменения можно декодировать в логические события.

COMMIT обычно ждёт fsync WAL, а не немедленную запись всех изменённых table pages. Поэтому PostgreSQL может быть быстрым: он пишет последовательный журнал, а грязные страницы сбрасывает позже.

Что полезно знать в backend:

много мелких commits создают много WAL и fsync pressure;
bulk load иногда ускоряют через batching, COPY, временные таблицы;
synchronous_commit=off уменьшает latency, но допускает потерю последних committed transactions при crash;
большие UPDATE/DELETE пишут много WAL и создают dead tuples;
replication lag часто означает, что standby не успевает получить или применить WAL.

Checkpoints и background writer

Если WAL растёт бесконечно, recovery после crash станет долгим. Поэтому PostgreSQL периодически делает checkpoint: точку, до которой все dirty pages должны быть записаны в data files.

WAL stream:  ... changes ... | checkpoint | ... new changes ...
                             ^
                             recovery can start from here

Важные процессы:

Процесс	Роль
`checkpointer`	Организует checkpoint и fsync dirty pages.
`background writer`	Плавно сбрасывает dirty pages, чтобы backend'и реже сами ждали запись.
`walwriter`	Пишет WAL buffers в WAL files.

Плохой признак - checkpoint spikes: latency резко растёт, потому что система вынуждена быстро записать много грязных страниц. На это влияют checkpoint_timeout, max_wal_size, checkpoint_completion_target, скорость диска и объём write workload.

Для Go-сервиса это проявляется как "иногда запросы к базе внезапно становятся медленными". Причина может быть не в конкретном SQL, а в I/O фоне: checkpoint, autovacuum, backup, WAL archiving.

Storage layout

Heap, pages и tuple

Обычная таблица в PostgreSQL хранится как heap: набор страниц, обычно по 8 KB. Строка внутри страницы называется tuple.

table heap
  page 0
    tuple: id=1, xmin=100, xmax=0
    tuple: id=2, xmin=101, xmax=0
  page 1
    tuple: id=3, xmin=102, xmax=0

Индекс не хранит всю строку. Обычно он хранит key + pointer на heap tuple. Этот pointer называется TID/ctid: (block number, item offset).

SELECT ctid, xmin, xmax, id, email
FROM users
WHERE id = 42;

ctid, xmin, xmax - системные колонки. Их не используют как бизнес-API, но для понимания MVCC они полезны.

MVCC и vacuum

MVCC: несколько версий одной строки

MVCC - Multi-Version Concurrency Control. PostgreSQL не перезаписывает строку "на месте" в логическом смысле. UPDATE создаёт новую версию tuple, а старая остаётся в heap, пока может быть видна старым transactions.

T1: UPDATE accounts SET balance = 90 WHERE id = 1;

old tuple: id=1, balance=100, xmin=10, xmax=20
new tuple: id=1, balance=90,  xmin=20, xmax=0

xmin - transaction id, который создал tuple.
xmax - transaction id, который удалил или заменил tuple. Если xmax=0, версия не удалена.

Видимость версии зависит от snapshot:

transaction видит tuple, если создавшая transaction committed и не находится "в будущем" относительно snapshot;
transaction не видит tuple, если tuple удалён committed transaction, которая видима в snapshot;
in-progress transactions обычно не видны другим transactions;
свои изменения transaction видит сама.

Это позволяет readers не блокировать writers, а writers не блокировать обычных readers:

T1: BEGIN;
T1: SELECT balance FROM accounts WHERE id = 1; -- видит 100

T2: UPDATE accounts SET balance = 90 WHERE id = 1;
T2: COMMIT;

T1: SELECT balance FROM accounts WHERE id = 1;
-- Read Committed: новый statement увидит 90
-- Repeatable Read: transaction продолжит видеть 100

Snapshots и isolation levels

Snapshot - это представление о том, какие transaction id уже committed, какие ещё active, и где граница "будущего".

В READ COMMITTED PostgreSQL создаёт новый snapshot на каждый statement. Поэтому два SELECT внутри одной transaction могут увидеть разные данные.

В REPEATABLE READ snapshot фиксируется на начало transaction. Повторный SELECT видит ту же картину, но возможны serialization errors при конфликтующих writes.

В SERIALIZABLE PostgreSQL использует Serializable Snapshot Isolation и может отменить transaction с ошибкой сериализации, если параллельное исполнение нельзя представить как последовательное. Приложение обязано retry'ить такие transaction.

Для Go-кода:

tx, err := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
})

Если вы выбираете высокий уровень изоляции, обработайте serialization_failure и повторите бизнес-операцию целиком. Нельзя просто повторить последний SQL: вся логика читала старый snapshot.

Dead tuples и bloat

MVCC даёт concurrency, но создаёт мусор. После UPDATE или DELETE старая версия строки остаётся в таблице. Когда никакая active transaction уже не может её видеть, версия становится dead tuple.

UPDATE x 100000 times
  -> 100000 old tuple versions
  -> heap grows
  -> indexes may grow
  -> queries read more pages

Bloat - лишнее место в таблицах и индексах, занятое старыми версиями, пустыми страницами и фрагментацией. Он опасен не только размером на диске:

больше страниц надо читать;
хуже cache hit ratio;
autovacuum делает больше работы;
index scan может ходить по мёртвым index entries;
backup и replication получают больше данных.

Типичная причина bloat в приложении - частые updates одних и тех же строк: counters, updated_at, статусные поля, JSONB-документы.

VACUUM и autovacuum

VACUUM делает несколько важных вещей:

удаляет dead tuple versions, которые больше никому не видны;
помечает место как доступное для reuse;
чистит связанные index entries;
обновляет visibility map;
помогает защититься от transaction ID wraparound;
вместе с ANALYZE обновляет статистику для планировщика.

Обычный VACUUM обычно не возвращает место операционной системе. Он делает место переиспользуемым внутри таблицы. VACUUM FULL переписывает таблицу, возвращает место OS, но требует ACCESS EXCLUSIVE lock и может быть очень тяжёлым.

Autovacuum состоит из launcher и workers. Он автоматически запускает VACUUM/ANALYZE, когда таблица достаточно изменилась. В production autovacuum почти никогда нельзя просто выключать: если он не справляется, его настраивают.

Симптомы проблем:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Особенно опасны long-running transactions:

SELECT pid, now() - xact_start AS age, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY age DESC;

Если transaction держит старый snapshot, vacuum не может убрать tuple versions, которые потенциально ей видны. В Go это часто выглядит как забытый tx.Rollback(), долгий cursor, открытая transaction вокруг внешнего HTTP-вызова или миграция, которая держит lock дольше ожидаемого.

Freeze и transaction ID wraparound

Transaction ID в PostgreSQL конечен. Чтобы старые row versions не стали "будущими" после wraparound, PostgreSQL замораживает очень старые tuple versions. Frozen rows считаются видимыми для всех будущих transactions.

Этим занимается vacuum. Если таблицы долго не vacuum'ятся, PostgreSQL вынужден запускать aggressive vacuum, а в худшем случае может начать защищать кластер от wraparound очень неприятными способами.

Практический вывод:

не отключать autovacuum;
следить за возрастом database/table XID;
не держать долгие transactions без необходимости;
помнить, что append-only таблицам тоже нужен vacuum для freeze, даже если dead tuples мало.

Incident symptoms: MVCC, vacuum, WAL, bloat

В production PostgreSQL редко сообщает "у вас проблема с MVCC". Симптомы приходят как рост latency, диска, replication lag или странные планы. Полезно связывать наблюдение с механизмом:

Симптом	Возможная причина	Где смотреть	Первый безопасный шаг
Таблица быстро растёт после частых `UPDATE`	Dead tuples и heap/index bloat	`pg_stat_user_tables.n_dead_tup`, размер relation/index	Найти hot update pattern, проверить autovacuum, убрать лишние индексы
`VACUUM` идёт, но место на диске не возвращается	Обычный vacuum переиспользует место внутри relation	`pg_relation_size`, `pg_total_relation_size`	Не запускать сразу `VACUUM FULL`; оценить `pg_repack`/перепаковку в окно
Autovacuum не чистит старые версии	Long-running transaction держит старый snapshot	`pg_stat_activity.xact_start`, `backend_xmin`	Завершить/починить долгую transaction, потом дать vacuum догнать
Внезапный рост replication lag	Standby не успевает получить или применить WAL	`pg_stat_replication`, WAL archive, I/O metrics	Снизить write burst, проверить disk/network, не удалять WAL вручную
Диск быстро заполняется `pg_wal`	Checkpoint/archive/replica не успевают	`pg_stat_bgwriter`, archive status, replication slots	Проверить slots/archive, освободить место безопасно, устранить источник WAL
Периодические latency spikes	Checkpoint пишет много dirty pages	`pg_stat_bgwriter.checkpoints_req`, I/O latency	Настроить checkpoint pacing, `max_wal_size`, workload batching
Index-only scan делает много `Heap Fetches`	Visibility map не all-visible из-за writes/vacuum lag	`EXPLAIN (ANALYZE, BUFFERS)`, vacuum stats	Проверить write churn и autovacuum, не обещать index-only как гарантию
Запросы внезапно выбрали seq scan	Статистика устарела после bulk changes	`EXPLAIN`, `pg_stat_user_tables.last_analyze`	`ANALYZE`, затем разбираться с data skew и stats target

Senior review heuristic: перед "добавим индекс" или "поднимем timeout" сначала спросите, что происходит с версиями строк, WAL и vacuum. Многие DB-инциденты выглядят как slow query, но начинаются с долгой transaction, write amplification или фонового I/O.

Visibility map и index-only scan

PostgreSQL indexes обычно не знают, видима ли tuple текущей transaction. Поэтому обычный index scan идёт в index, потом в heap, чтобы проверить tuple visibility.

Visibility map отмечает heap pages, где все tuples видимы всем transactions. Если page all-visible, index-only scan может не читать heap page.

Index Only Scan
  ├─ index содержит нужные columns
  └─ visibility map говорит: heap page all-visible

В EXPLAIN (ANALYZE, BUFFERS) у index-only scan смотрите Heap Fetches. Если их много, запрос формально index-only, но всё равно часто ходит в heap. Причина может быть в активных updates, слабом vacuum или недавно изменённых страницах.

HOT updates

HOT - Heap-Only Tuple optimization. Если UPDATE не меняет indexed columns и на той же heap page есть место для новой версии, PostgreSQL может не создавать новые index entries.

-- index on email
UPDATE users SET last_seen_at = now() WHERE id = 42;
-- если last_seen_at не indexed, есть шанс на HOT update

Почему это важно:

меньше index bloat;
дешевле update;
меньше WAL;
быстрее vacuum.

Как помочь HOT:

не индексировать каждую колонку "на всякий случай";
держать часто меняющиеся поля вне лишних индексов;
иногда использовать fillfactor ниже 100, чтобы оставить место на странице;
не хранить большие mutable JSONB-документы в одной горячей строке без причины.

ALTER TABLE users SET (fillfactor = 90);

fillfactor не чинит всё. Он обменивает плотность хранения на шанс обновляться внутри той же страницы.

TOAST: большие значения отдельно

PostgreSQL page обычно 8 KB, но строка может содержать большой text, bytea, jsonb. Для этого есть TOAST - The Oversized-Attribute Storage Technique.

Если значение слишком большое, PostgreSQL может:

сжать его;
вынести кусками в отдельную TOAST-таблицу;
оставить в основной строке pointer.

orders heap tuple
  id
  status
  payload pointer -> pg_toast.pg_toast_...

Практические последствия:

SELECT * по таблице с большим jsonb может случайно тащить огромные TOAST-значения;
обновление большого поля создаёт новые версии и может сильно раздувать TOAST;
индексировать весь JSONB через GIN удобно, но дорого по write amplification;
для API лучше выбирать только нужные columns.

-- лучше
SELECT id, status, created_at FROM orders WHERE user_id = $1;

-- осторожно
SELECT * FROM orders WHERE user_id = $1;

Как читать EXPLAIN с учётом MVCC

EXPLAIN показывает план. EXPLAIN ANALYZE выполняет запрос и показывает фактические timings/rows. BUFFERS добавляет чтение страниц.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email
FROM users
WHERE email = 'a@example.com';

Минимальный checklist:

Поле	Что спросить
`cost`	Что планировщик ожидал?
`rows`	Насколько estimate отличается от actual?
`actual time`	Где реально ушло время?
`loops`	Узел выполнялся один раз или много?
`Buffers: shared hit/read`	Данные были в cache или читались с диска?
`Heap Fetches`	Index-only scan действительно обошёл heap?
`Rows Removed by Filter`	Не читаем ли слишком много лишнего?

Пример плохого сигнала:

Index Only Scan using users_email_idx on users
  Heap Fetches: 500000

Индекс есть, но heap всё равно читается. Причина может быть в visibility map: страницы не all-visible из-за недавних writes или неуспевающего vacuum.

Другой пример:

Seq Scan on events
  Rows Removed by Filter: 9999000

Это не всегда плохо: если фильтр выбирает половину таблицы, seq scan нормален. Но если фактически нужно 100 строк из 10 млн, нужно смотреть индекс, статистику, тип predicate и selectivity.

Типичные production-ошибки

Transaction вокруг внешнего мира

tx, _ := db.BeginTx(ctx, nil)
// SELECT ...
// HTTP request to another service
// UPDATE ...
tx.Commit()

Пока Go ждёт внешний сервис, PostgreSQL держит transaction, snapshot и, возможно, locks. Лучше сначала собрать внешние данные, затем открыть короткую transaction и быстро зафиксировать изменения.

Частый UPDATE счётчиков

UPDATE posts SET views = views + 1 WHERE id = $1;

Для горячего поста это создаёт постоянные row versions и lock contention. Иногда лучше buffered aggregation, Redis counter с периодическим flush, append-only events или отдельная sharded counter table.

Большой JSONB как изменяемая сущность

UPDATE documents
SET payload = jsonb_set(payload, '{status}', '"done"')
WHERE id = $1;

Логически меняется одно поле, физически может переписываться большая строка/TOAST-значение и GIN index. Для горячих полей часто лучше вынести columns отдельно.

Забытый rollback

tx, err := db.BeginTx(ctx, nil)
if err != nil { return err }
defer tx.Rollback()

// ...
return tx.Commit()

defer tx.Rollback() после успешного commit вернёт ошибку, которую обычно игнорируют, зато в ошибочных ветках transaction будет закрыта.

Что помнить на собеседовании

PostgreSQL readers и writers не блокируют друг друга в обычном чтении благодаря MVCC.
UPDATE создаёт новую версию строки, старая версия очищается позже vacuum'ом.
xmin и xmax - transaction ids создания и удаления/замены tuple version.
Snapshot определяет, какие tuple versions видимы transaction.
READ COMMITTED делает snapshot на statement, REPEATABLE READ - на transaction.
Длинные transactions мешают vacuum и могут вызвать bloat.
VACUUM обычно переиспользует место, но не возвращает его OS; VACUUM FULL переписывает таблицу и требует тяжёлый lock.
WAL обеспечивает crash recovery, replication и PITR.
Checkpoint сбрасывает dirty pages и ограничивает объём WAL, который нужен для recovery.
HOT update возможен, когда не меняются indexed columns и есть место на heap page.
TOAST выносит большие значения из основной строки, но не делает их бесплатными.
EXPLAIN (ANALYZE, BUFFERS) надо читать вместе с actual rows, loops, buffers и visibility effects.

Практика

Выполните несколько UPDATE одной строки и посмотрите, как растут dead tuples в pg_stat_user_tables.
Откройте длинную transaction и проверьте, как она мешает vacuum очищать старые версии.
Сравните EXPLAIN (ANALYZE, BUFFERS) до и после VACUUM ANALYZE.
Найдите таблицу, где частые updates indexed columns мешают HOT updates.

Интерактивная практика

Quiz+10 XP

Что чаще всего происходит при UPDATE строки в PostgreSQL?

Создаётся новая версия строки, а старая позже очищается vacuum
Старая строка всегда переписывается на месте без следов
Все readers блокируются до COMMIT
WAL не используется, если включён autovacuum

Predict+15 XP

Что выведет этот код?

WITH isolation(level_name) AS (
    VALUES ('read committed'), ('repeatable read')
)
SELECT CASE
    WHEN level_name = 'repeatable read' THEN 'transaction'
    ELSE 'statement'
END AS snapshot_scope
FROM isolation;

Полезные официальные разделы

PostgreSQLMVCCWALvacuumautovacuumTOAST