Модуль: Базы данных · Уровень: Senior

TL;DR#

• PostgreSQL — это процессная (process-per-connection) СУБД, а не потоковая. Родительский процесс postmaster форкает по одному backend-процессу на каждое соединение, плюс набор фоновых служебных процессов.
• Все backend’ы общаются через общую память (shared memory), главная часть которой — shared buffers (кэш страниц 8 КБ). Вытеснение страниц — алгоритм clock-sweep (приближение к LRU).
• Перед записью грязной страницы на диск изменения сначала пишутся в WAL (write-ahead log) — это гарантирует durability и crash recovery. Принцип WAL-before-data, позиция в логе — LSN.
• Checkpoint — точка, до которой все грязные страницы сброшены на диск; ограничивает объём WAL, который нужно проиграть при восстановлении. Регулируется checkpoint_timeout, max_wal_size, checkpoint_completion_target.
• Autovacuum убирает dead tuples (следствие MVCC), предотвращает раздувание (bloat) и transaction ID wraparound, обновляет статистику и visibility map.
• TOAST — механизм хранения значений, не помещающихся в страницу 8 КБ: сжатие + вынос в отдельную таблицу (out-of-line).
• На senior-уровне ждут понимания связи MVCC ↔ vacuum ↔ WAL ↔ checkpoint и умения диагностировать проблемы (I/O-всплески, bloat, wraparound, write amplification).

Теория#

Процессная модель#

PostgreSQL запускает дерево процессов. В отличие от MySQL/Oracle (потоки), здесь на каждое клиентское соединение создаётся отдельный процесс ОС. Это упрощает изоляцию сбоев (краш одного backend не валит сервер), но делает соединения дорогими — отсюда необходимость пулеров (PgBouncer, pgcat) на высоконагруженных системах.

postmaster (главный процесс, слушает порт)
├── backend (клиент 1)
├── backend (клиент 2)
├── background writer
├── walwriter
├── checkpointer
├── autovacuum launcher
│     └── autovacuum worker (форкаются по необходимости)
├── logical replication launcher
├── archiver (если archive_mode=on)
├── wal receiver / wal sender (репликация)
├── startup process (только во время recovery)
└── custom background workers (расширения, parallel workers)

Важно: postmaster использует fork() без exec() — backend наследует уже инициализированную общую память и атрибуты. Соединение «прибивается» к одному процессу на всё время жизни.

Shared buffers (буферный кэш)#

shared_buffers — область общей памяти, кэширующая страницы данных и индексов размером 8 КБ (BLCKSZ, дефолт компиляции). Это центральная разделяемая структура: любой backend читает/пишет данные только через буферный кэш.

Ключевые понятия:

• Buffer descriptor — метаданные буфера: тег страницы (relfilenode, fork, block number), флаги (dirty, valid), usage_count, refcount, lock.
• Pin / unpin — backend «пиннит» буфер на время работы с ним, чтобы его не вытеснили.
• Dirty page — страница, изменённая в памяти, но ещё не записанная на диск. До записи на диск соответствующие WAL-записи уже должны быть на диске (WAL-before-data).

Clock-sweep eviction. Когда нужен свободный буфер, а все заняты, PostgreSQL ищет жертву по кольцу буферов «стрелкой часов» (clock hand):

1. Стрелка проходит по буферам по кругу.
2. Если у буфера usage_count > 0 — уменьшает счётчик на 1 и идёт дальше (даёт «второй шанс»).
3. Если usage_count == 0 и буфер не запинен — это жертва. Если он dirty — сначала записывается на диск (или это уже сделал bgwriter), затем переиспользуется.

При каждом обращении usage_count увеличивается (до потолка BM_MAX_USAGE_COUNT = 5). Это дешёвое приближение LRU без поддержки полноценного списка.

Ring buffer / BAS (Buffer Access Strategy). Для операций, которые читают много данных «одноразово» (seq scan большой таблицы, COPY, VACUUM), PostgreSQL использует маленькое кольцо буферов вместо вытеснения всего горячего кэша — защита от cache trashing.

Практика выбора размера: классическая рекомендация — shared_buffers ≈ 25% RAM (но не «чем больше, тем лучше» — ОС-кэш тоже кэширует файлы, и слишком большой shared_buffers усиливает работу checkpointer и удваивает кэширование).

SHOW shared_buffers;

-- Что сейчас в кэше (расширение pg_buffercache)
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT c.relname,
       count(*)                               AS buffers,
       pg_size_pretty(count(*) * 8192)        AS size,
       round(100.0 * count(*) FILTER (WHERE b.isdirty) / count(*), 1) AS dirty_pct
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY c.relname
ORDER BY buffers DESC
LIMIT 10;

-- Оценка hit ratio (должен быть высоким, обычно >99% на OLTP)
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

WAL (Write-Ahead Log)#

WAL — журнал предзаписи. Фундаментальное правило: запись об изменении попадает в WAL и флашится на диск раньше, чем изменённая страница данных. Это даёт durability (D в ACID) и возможность crash recovery: после сбоя достаточно проиграть WAL от последнего чекпойнта.

LSN (Log Sequence Number) — монотонно растущий 64-битный адрес байта в потоке WAL, форматируется как XXXXXXXX/XXXXXXXX. Каждая страница хранит pd_lsn — LSN последней WAL-записи, которая её изменила. Перед сбросом страницы на диск PostgreSQL гарантирует, что WAL до pd_lsn уже на диске.

Жизненный цикл:

1. Backend меняет страницу в shared buffers → формирует WAL-запись в wal_buffers.
2. При COMMIT WAL флашится на диск через fsync (если synchronous_commit=on) — только после этого клиенту возвращается успех.
3. Грязная страница данных может остаться в памяти и попасть на диск позже (bgwriter/checkpointer).

fsync — обязателен для durability. fsync=off ускоряет, но при крахе/потере питания возможна порча кластера (не просто потеря последних транзакций!). Никогда не выключайте на проде.

synchronous_commit — тонкая настройка durability vs latency:

full_page_writes. Защита от partial page writes (torn pages): если ОС/диск пишет страницу 8 КБ не атомарно и происходит сбой посреди записи, на диске останется «полустраница». Поэтому при первом изменении страницы после чекпойнта PostgreSQL пишет в WAL её полный образ (FPI — full page image). При recovery такая страница восстанавливается целиком, а не накатывается дельтой на возможно битую страницу.

Цена — раздувание WAL: сразу после чекпойнта объём WAL резко растёт за счёт FPI. Это одна из причин, почему слишком частые чекпойнты вредны (см. ниже). Выключать full_page_writes можно только если ФС/железо гарантирует атомарную запись блока (например, ZFS с подходящим recordsize, или СХД с защитой) — иначе риск молчаливой порчи.

-- Текущая позиция вставки в WAL
SELECT pg_current_wal_lsn();

-- Сколько WAL сгенерировано между двумя точками (в байтах)
SELECT pg_wal_lsn_diff('0/16D6358', '0/16C0000');

-- Объём WAL по записям/типам (PG 14+)
SELECT * FROM pg_stat_wal;

Checkpoint (контрольная точка)#

Checkpoint гарантирует, что все грязные страницы до определённого LSN записаны на диск. После чекпойнта WAL до этой точки больше не нужен для recovery (но может удерживаться для архива/репликации/replication slots).

Зачем нужен: ограничивает время восстановления (нужно проиграть WAL только от последнего чекпойнта) и позволяет переиспользовать/удалять старые WAL-сегменты.

Триггеры чекпойнта:

• по времени — checkpoint_timeout (дефолт 5 мин);
• по объёму WAL — когда объём WAL приближается к max_wal_size (дефолт 1 ГБ);
• вручную — CHECKPOINT;
• при pg_start_backup, выключении сервера и т. п.

Spread checkpoints. Если бы checkpointer писал все грязные страницы разом, получился бы I/O-шторм и провал latency. Поэтому запись «размазывается» во времени: checkpoint_completion_target (дефолт 0.9) означает, что чекпойнт должен завершить запись грязных страниц за 90% интервала до следующего ожидаемого чекпойнта.

Диагностика частых чекпойнтов — частый кейс на собеседовании: если checkpoints_req (по объёму) » checkpoints_timed, значит max_wal_size мал и чекпойнты вызываются объёмом WAL → растёт write amplification.

-- PG <17
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time,
       buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;

-- PG 17+: статистика чекпойнтов вынесена в отдельное представление
SELECT num_timed, num_requested, write_time, sync_time, buffers_written
FROM pg_stat_checkpointer;

MVCC и autovacuum#

PostgreSQL реализует MVCC: UPDATE не перезаписывает строку на месте, а создаёт новую версию (tuple) и помечает старую как удалённую (xmax). DELETE помечает строку мёртвой. Старые версии (dead tuples) остаются физически в странице, пока на них может смотреть какая-то транзакция (по её snapshot).

Каждый tuple хранит xmin (транзакция-создатель) и xmax (транзакция-удалитель). Видимость определяется сравнением с снапшотом транзакции и transaction ID (XID).

Зачем нужен VACUUM:

1. Освобождает место от dead tuples — иначе таблица/индекс раздуваются (bloat): размер на диске растёт, кэш-эффективность падает, scans медленнее.
2. Защита от XID wraparound. XID 32-битный (~4 млрд). При переполнении старые транзакции «оказались бы в будущем» и данные стали бы невидимыми. VACUUM «замораживает» (freeze) старые tuple, помечая их как заведомо видимые (FrozenTransactionId), и продвигает relfrozenxid. Если этого не делать, при приближении к лимиту PostgreSQL переходит в защитный режим, а в крайнем случае останавливает приём запросов.
3. Обновляет visibility map — что критично для index-only scans и для пропуска «всех видимых» страниц при последующих vacuum.
4. ANALYZE обновляет статистику планировщика.

Различия:

• VACUUM — убирает dead tuples, не возвращает место ОС (только делает доступным для повторного использования внутри таблицы).
• VACUUM FULL — переписывает таблицу целиком, возвращает место ОС, но берёт ACCESS EXCLUSIVE lock (блокирует таблицу) — на проде опасно; альтернатива pg_repack.
• FREEZE — агрессивная заморозка.

Autovacuum — фоновая автоматизация. Launcher по статистике (pg_stat_user_tables.n_dead_tup) решает, что пора:

порог = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples

То есть для таблицы из 100 млн строк при дефолтном scale_factor = 0.2 autovacuum сработает только после ~20 млн мёртвых строк — это слишком поздно для больших таблиц. Типичная senior-практика — снижать scale_factor или ставить per-table настройки:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_vacuum_cost_limit   = 2000,   -- агрессивнее на горячих таблицах
  autovacuum_vacuum_cost_delay   = 2
);

Ключевые глобальные параметры: autovacuum_max_workers, autovacuum_naptime, autovacuum_vacuum_cost_delay / cost_limit (throttling I/O), autovacuum_freeze_max_age (форсирует anti-wraparound vacuum).

-- Кому давно не делали vacuum и кто раздут
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, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Контроль wraparound: сколько XID осталось до forced vacuum
SELECT c.relname,
       age(c.relfrozenxid) AS xid_age,
       2147483648 - age(c.relfrozenxid) AS xids_left
FROM pg_class c
WHERE c.relkind = 'r'
ORDER BY xid_age DESC
LIMIT 20;

TOAST (The Oversized-Attribute Storage Technique)#

Страница PostgreSQL — 8 КБ, и tuple не может пересекать границу страницы. Значит большие значения (длинные text, bytea, jsonb) нужно как-то хранить. Это делает TOAST.

Порог — TOAST_TUPLE_THRESHOLD (~2 КБ): если строка не влезает, PostgreSQL применяет к toast-able колонкам:

1. Компрессию значения «на месте» (in-line).
2. Если всё ещё велико — выносит значение в отдельную TOAST-таблицу (pg_toast.pg_toast_<oid>), разбивая на чанки (~2 КБ), а в основной строке оставляет указатель (TOAST pointer) — это и есть out-of-line хранение.

Стратегии хранения колонки (ALTER TABLE ... ALTER COLUMN ... SET STORAGE ...):

Компрессия. Исторически — PGLZ. С PG 14 доступен LZ4 (default_toast_compression = lz4) — быстрее и часто лучше по ratio; задаётся глобально или per-column.

Важные следствия для производительности:

• TOAST-значения читаются лениво: если в запросе колонка не выбрана, чанки не читаются. Поэтому SELECT * по таблице с большими jsonb может быть кратно дороже, чем выбор нужных колонок.
• Обновление не-toast колонок в строке с большим toast-значением обычно не переписывает TOAST-чанки (они шарятся по указателю) — это экономит I/O.
• TOAST-таблица имеет собственный индекс и тоже подлежит VACUUM и bloat.

-- Storage стратегия колонок
SELECT attname, atttypid::regtype,
       CASE attstorage WHEN 'p' THEN 'plain' WHEN 'e' THEN 'external'
                       WHEN 'm' THEN 'main'  WHEN 'x' THEN 'extended' END AS storage
FROM pg_attribute
WHERE attrelid = 'my_table'::regclass AND attnum > 0 AND NOT attisdropped;

-- Найти TOAST-таблицу и её размер
SELECT c.relname,
       pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS toast_size,
       pg_size_pretty(pg_relation_size(c.oid))           AS main_size
FROM pg_class c
WHERE c.relname = 'my_table';

ALTER TABLE my_table ALTER COLUMN payload SET STORAGE EXTERNAL;
ALTER TABLE my_table ALTER COLUMN payload SET COMPRESSION lz4;  -- PG 14+

Связь с Go-приложением#

Процессная модель и дороговизна соединений напрямую влияют на конфигурацию пула в Go.

// database/sql: ограничиваем число backend-процессов на сервере
db, err := sql.Open("pgx", dsn) // драйвер jackc/pgx через stdlib
if err != nil {
    return err
}
// На каждое открытое соединение PostgreSQL держит отдельный backend-процесс.
// MaxOpenConns не должен превышать разумную долю max_connections сервера
// (а лучше — ходить через PgBouncer и держать пул небольшим).
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(20)             // равно MaxOpen, чтобы избежать постоянного reopen
db.SetConnMaxIdleTime(5 * time.Minute)
db.SetConnMaxLifetime(30 * time.Minute) // переоткрытие, чтобы не копить долгоживущие backend'ы

// pgx/v5 нативный пул — даёт доступ к фишкам PostgreSQL (LISTEN/NOTIFY, COPY, типы)
import "github.com/jackc/pgx/v5/pgxpool"

cfg, _ := pgxpool.ParseConfig(dsn)
cfg.MaxConns = 20
cfg.MinConns = 2
cfg.MaxConnLifetime = 30 * time.Minute
pool, _ := pgxpool.NewWithConfig(ctx, cfg)
defer pool.Close()

// Долго не закрытая транзакция в Go (забыли Rollback/Commit) удерживает snapshot
// и блокирует autovacuum от очистки dead tuples во ВСЕЙ базе → bloat.
// Всегда defer tx.Rollback() и короткие транзакции.

Практический мостик: «idle in transaction» соединение из Go-приложения — частая первопричина bloat и роста размера базы, потому что оно держит xmin и не даёт vacuum собрать мёртвые версии.

Подводные камни / gotchas#

• Process-per-connection дорог. Тысячи коротких соединений из Go без пулера → тысячи fork’ов и память на backend. Решение: PgBouncer (transaction pooling) + небольшой пул в приложении.
• fsync=off ≠ просто «быстрее». При краше это порча кластера, а не потеря последних транзакций. Для durability vs latency используйте synchronous_commit, а не fsync.
• Слишком частые чекпойнты (мелкий max_wal_size) → лавина FPI после каждого чекпойнта (full_page_writes) → write amplification и всплески I/O. Смотрите соотношение checkpoints_req/checkpoints_timed.
• shared_buffers «побольше» не панацея. Double caching с ОС-кэшем, дольше чекпойнты. Тюнинг — по hit ratio и нагрузке, не «по памяти».
• Autovacuum «не успевает» на больших таблицах из-за дефолтного scale_factor=0.2. Ставьте per-table агрессивные настройки.
• Long-running / idle-in-transaction транзакции держат xmin → autovacuum не может удалить dead tuples → bloat по всей базе. Мониторьте pg_stat_activity (state='idle in transaction', xact_start).
• XID wraparound — катастрофа: при достижении лимита сервер уходит в read-only / останавливается. Мониторьте age(relfrozenxid).
• VACUUM не отдаёт место ОС. Для реального возврата — pg_repack (online) или VACUUM FULL (с эксклюзивной блокировкой).
• SELECT * по таблице с большими TOAST-значениями читает все чанки, даже если значение не нужно. Выбирайте только необходимые колонки.
• Index bloat — индексы тоже раздуваются; REINDEX CONCURRENTLY (PG 12+) перестраивает без долгой блокировки.
• HOT updates (Heap-Only Tuple) работают только если меняемые колонки не входят в индекс и на странице есть место (fillfactor); иначе каждый UPDATE трогает все индексы.

Вопросы на собеседовании#

В: Чем процессная модель PostgreSQL отличается от потоковой и какие у неё последствия для проектирования сервиса? О: Один процесс ОС на соединение (fork от postmaster). Плюсы: изоляция сбоев, простота. Минусы: соединения дороги по памяти и CPU на fork → нужны пулеры (PgBouncer/pgxpool), небольшой max_connections, короткие транзакции. В Go это ограничивает MaxOpenConns и делает обязательным контроль времени жизни соединений.

В: Что такое clock-sweep и почему не используют точный LRU? О: Это приближение LRU без поддержки дорогого списка: «стрелка» обходит буферы по кольцу, у каждого есть usage_count (второй шанс). При нехватке буфера счётчик уменьшается; буфер с usage_count=0 и без pin становится жертвой. Точный LRU потребовал бы блокировок и перестроения списка на каждом обращении — слишком дорого при конкурентном доступе многих backend’ов.

В: Объясните принцип WAL-before-data и роль LSN. О: Изменение сначала фиксируется в WAL и флашится на диск, и только потом (возможно, гораздо позже) грязная страница попадает на диск. LSN — адрес в потоке WAL; страница хранит pd_lsn, и перед её сбросом гарантируется, что WAL до этого LSN на диске. Это даёт durability и crash recovery: после краха проигрываем WAL от последнего чекпойнта.

В: Зачем нужен full_page_writes и какова его цена? О: Защита от torn pages (частичная запись 8 КБ при сбое). При первом изменении страницы после чекпойнта в WAL пишется её полный образ (FPI), чтобы при recovery восстановить страницу целиком. Цена — раздувание WAL сразу после чекпойнтов; усиливается при частых чекпойнтах.

В: Чем synchronous_commit=off отличается от fsync=off? О: synchronous_commit=off не дожидается fsync на COMMIT — при краше теряются последние транзакции, но кластер целостен. fsync=off вообще не гарантирует попадание данных/WAL на диск — при краше возможна порча всего кластера. Первое — допустимый компромис latency/durability, второе — почти всегда недопустимо на проде.

В: Как диагностировать и лечить слишком частые чекпойнты? О: Сравнить checkpoints_req и checkpoints_timed (pg_stat_bgwriter / pg_stat_checkpointer в PG17+). Если преобладают req — чекпойнты вызываются объёмом WAL → увеличить max_wal_size, поднять checkpoint_timeout, держать checkpoint_completion_target ~0.9 для размазывания I/O. Это снизит количество FPI и write amplification.

В: Почему растёт размер таблицы, хотя строк столько же? Что делать? О: Bloat из-за MVCC: UPDATE/DELETE плодят dead tuples, которые autovacuum не успевает/не может собрать (например, из-за long-running транзакции, удерживающей xmin, или слабых autovacuum-настроек). Диагностика: n_dead_tup, pg_stat_activity на idle-in-transaction. Лечение: устранить долгие транзакции, поднять агрессивность autovacuum per-table, при необходимости pg_repack.

В: Что такое XID wraparound и как его не допустить? О: XID 32-битный; при переполнении старые транзакции «попадают в будущее», данные становятся невидимыми. VACUUM замораживает старые tuple (relfrozenxid продвигается). Мониторим age(relfrozenxid); autovacuum_freeze_max_age форсирует anti-wraparound vacuum. При приближении к лимиту сервер уходит в защитный режим вплоть до остановки записи.

В: Как PostgreSQL хранит большой jsonb и как это влияет на запросы? О: Через TOAST: сначала компрессия (PGLZ/LZ4), затем при необходимости вынос в отдельную TOAST-таблицу чанками по ~2 КБ с указателем в основной строке (out-of-line). TOAST читается лениво — если колонка не в SELECT, чанки не читаются; поэтому SELECT * дорог. Стратегию хранения (EXTENDED/EXTERNAL/MAIN) и компрессию можно настроить per-column.

В: Что такое HOT update и почему он важен? О: Heap-Only Tuple — оптимизация UPDATE, когда новая версия строки помещается на ту же страницу и изменённые колонки не входят в индексы. Тогда индексы не обновляются (новая версия достижима через указатель в старой), что резко снижает write amplification и нагрузку на vacuum. Управляется наличием места (fillfactor) и составом индексов.

На что копают на senior+#

• Системная связность. Умение объяснить цепочку: MVCC порождает dead tuples → нужен vacuum → vacuum продвигает relfrozenxid и обновляет visibility map → visibility map ускоряет index-only scans и сам vacuum. Кандидат должен видеть систему, а не отдельные параметры.
• Write amplification. Понимание, как full_page_writes + частые чекпойнты + не-HOT updates умножают объём записи; как fillfactor, max_wal_size, LZ4-компрессия WAL (wal_compression) это снижают.
• Диагностика на проде. Не «прочитать доку», а назвать конкретные представления: pg_stat_bgwriter/pg_stat_checkpointer, pg_stat_activity (idle in transaction, longest xact), pg_stat_user_tables (dead tuples), pg_buffercache, age(relfrozenxid), pg_stat_wal.
• Репликация и WAL. Как replication slots удерживают WAL (риск заполнения диска при отставшей реплике), synchronous_standby_names, физическая vs логическая репликация, влияние hot_standby_feedback на vacuum на primary.
• Пулинг под процессную модель. Различие session/transaction/statement pooling в PgBouncer, почему prepared statements ломаются при transaction pooling, как pgx это обходит, расчёт пула под max_connections.
• Тонкости TOAST. Деградация при больших jsonb, SET STORAGE, выбор LZ4 vs PGLZ, vacuum/bloat самой TOAST-таблицы, влияние на logical decoding.
• Аварийные сценарии. Поведение при приближении к wraparound, восстановление после краха (startup process, redo), PITR через archive + base backup, что произойдёт при checkpoint во время backup.
• Trade-off durability/latency под конкретный бизнес-кейс: где допустим synchronous_commit=off, где нужны синхронные реплики, как это влияет на RPO/RTO.