WAL (Write-Ahead Log) — это журнал всех изменений в БД. До того как PostgreSQL запишет изменение в табличный файл, он пишет его в WAL. Это даёт три ключевых свойства: durability (после COMMIT транзакция переживёт падение), репликация (реплики проигрывают WAL), PITR (Point-In-Time Recovery).

WAL невидим разработчику в обычной работе, но при росте нагрузки он становится узким местом: WAL пишется до того, как ответ на COMMIT уйдёт клиенту — значит, скорость дисковой записи WAL = верхняя граница пропускной способности на запись.

Эта статья — про то, как разработчик уменьшает объём WAL и латентность коммитов, не трогая инфраструктурные настройки.

1. Жизненный цикл одной записи

Когда клиент делает INSERT INTO orders ...; COMMIT;:

  1. PG модифицирует страницу таблицы orders в shared buffers (память).
  2. Записывает запись об изменении в WAL buffer (память).
  3. На COMMIT — fsync-ит WAL buffer на диск (pg_wal/000000010000000000000001).
  4. Только после успешного fsync — отвечает клиенту OK.
  5. Позже (при checkpoint) — изменённая страница orders сбрасывается на диск (table file).

PG-W-001 fsync WAL — синхронная операция в каждом коммите. Это и есть «цена durability». Влиять можно либо реже коммитить (батчинг), либо разрешить async-коммит (synchronous_commit = off, опасно), либо группировать коммиты.

2. Что генерирует WAL

ОперацияРазмер WAL
INSERT одной строки~50–200 байт + длина значений
UPDATE строки (полное переписывание)~50 байт + новая строка целиком
UPDATE с HOT~50 байт + дельта (только изменённые поля, без индексов)
DELETE~50 байт + ID строки
CREATE INDEXвесь индекс целиком
VACUUMсписок освобождённых tuple'ов
TOAST-апдейтесли TOAST-значение не изменилось — оно НЕ пишется в WAL повторно

PG-W-002 UPDATE пишет в WAL новую строку целиком, не дельту — кроме случая HOT (см. §4). Если в строке 30 колонок и обновили 1 из них, в WAL уйдёт вся строка.

PG-W-003 CREATE INDEX без CONCURRENTLY — мощный генератор WAL. На больших таблицах это может удвоить размер WAL за минуты, и реплика лагнёт.

3. Bulk-операции: COPY vs INSERT, batch

PG-W-010 Для массовой вставки — COPY, не цикл INSERT.

-- плохо: 100K транзакций, 100K WAL-fsync, 100K round-trip
for (var row : rows) {
    jdbcTemplate.update("INSERT INTO orders ... VALUES (?, ?, ...)", ...);
}

-- лучше: batch — одна транзакция, меньше WAL-fsync
jdbcTemplate.batchUpdate("INSERT INTO orders ... VALUES (?, ?, ...)", batch);

-- ещё лучше: COPY — минимальный overhead на каждую строку
copyManager.copyIn("COPY orders (col1, col2) FROM STDIN", reader);

Разница на 1M строк может быть 10–100x по времени и в 5–10x по объёму WAL.

PG-W-011 Один большой COMMIT с 10K вставок дешевле 10K маленьких COMMIT. Меньше fsync-ов. Но: длинная транзакция блокирует autovacuum (см. §8). Оптимум — батчи по 1–10K строк.

PG-W-012 Перед массовой загрузкой в пустую таблицу — дроп индексов, загрузка, восстановление.

DROP INDEX ix_orders_customer;
DROP INDEX ix_orders_status_created;
COPY orders FROM '/tmp/data.csv';
CREATE INDEX CONCURRENTLY ix_orders_customer ON orders (customer_id);
CREATE INDEX CONCURRENTLY ix_orders_status_created ON orders (status, created_at);

Каждая вставка с активным индексом пишет в WAL обновление и таблицы, и каждого индекса. Без индексов — только таблицы.

4. HOT и fillfactor — самый недооценённый рычаг

HOT (Heap-Only Tuple) — оптимизация PG для UPDATE: если новая версия строки помещается на ту же страницу И не изменены индексируемые колонки, PG не обновляет индексы и не пишет полные строки в WAL дважды. Просто кладёт новую версию рядом со старой и связывает их chain-ом.

PG-W-020 HOT работает только если на странице есть свободное место для новой версии строки.

Если таблица заполнена под завязку (fillfactor=100, дефолт), UPDATE почти никогда не получит HOT — пойдёт на новую страницу, обновит все индексы, в WAL уйдёт новая строка + изменения индексов.

-- таблицы с частыми UPDATE'ами:
CREATE TABLE orders (...) WITH (fillfactor = 90);  -- 10% страницы оставлено под HOT-апдейты

ALTER TABLE orders SET (fillfactor = 80);  -- задним числом, для существующей таблицы
-- + VACUUM FULL orders;  чтобы переразложить таблицу под новый fillfactor

PG-W-021 Для интенсивно обновляемых таблиц — fillfactor = 80–90. Для статичных (read-mostly, например, справочники) — оставь 100. Цена — 10–20% больше места на диске, выигрыш — кратное уменьшение WAL и износа индексов.

PG-W-022 HOT отключается, если UPDATE меняет индексируемую колонку. Даже если у строки была HOT-возможность, UPDATE поля, по которому есть индекс, всегда обновляет индекс. Если поле меняется часто (например, last_seen_at), а индекс по нему слабоселективный — индекс может вообще не нужен, удали его.

Проверка HOT-ratio:

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 > 1000
ORDER BY n_tup_upd DESC LIMIT 10;

Если hot_pct < 50% на горячей таблице — кандидат на снижение fillfactor.

5. TOAST и большие значения

PG-W-030 Большое значение в колонке (text/jsonb/bytea) попадает в TOAST. Если при UPDATE оно не изменилось — TOAST не переписывается, в WAL не идёт.

UPDATE article SET view_count = view_count + 1 WHERE id = ?;
-- view_count маленькое поле, body (text 50KB) в TOAST — НЕ пишется в WAL.

Это ещё один аргумент против «всё в один большой data jsonb»: при обновлении одного маленького ключа PG не знает, что внутри JSONB не изменилось, и пишет всё содержимое в WAL.

PG-W-031 Если в строке есть и часто-обновляемые маленькие поля, и редко-меняющиеся большие — большие должны быть либо в TOAST (длинный text/bytea), либо в отдельной таблице. TOAST это делает автоматически для значений > ~2KB.

6. UNLOGGED таблицы — нулевой WAL

PG-W-040 Для данных, которые можно потерять при crash, — UNLOGGED таблица. Нет WAL, нет реплики, при падении сервера данные обнуляются.

CREATE UNLOGGED TABLE session_cache (
    session_id uuid PRIMARY KEY,
    payload    jsonb NOT NULL,
    expires_at timestamptz NOT NULL
);

Подходит для:

  • Кеши, которые легко прогреть заново.
  • Промежуточные данные ETL (загрузили, обработали, переложили).
  • Очереди задач с TTL, которые не страшно потерять.
  • Тесты, где БД пересоздаётся каждый прогон.

Не подходит для бизнес-данных (даже «не очень важных» — упасть может в самый неудобный момент).

PG-W-041 UNLOGGED → LOGGED через ALTER TABLE переписывает всю таблицу с генерацией полного WAL. Если планируется смена статуса — это разовая операция, не делай в hot path.

7. synchronous_commit — ускорение коммитов

PG-W-050 synchronous_commit = off отключает ожидание fsync WAL — коммит возвращает OK без подтверждения дискa.

Это даёт большой прирост throughput на коммит-heavy нагрузке, но ценой: при крахе сервера в окне 200мс перед падением закоммиченные транзакции могут потеряться. Согласованность БД сохраняется (транзакции либо все пройдут, либо ни одна), но клиент думал что закоммитил, а на самом деле нет.

Применимо для:

  • Аналитики, метрик, логов — где потеря секунды событий не критична.
  • Тестовых окружений.

Не применять для:

  • Финансы, заказы, всё что должно быть «гарантированно записано».

PG-W-051 Можно регулировать на уровне сессии/транзакции:

SET LOCAL synchronous_commit = off;
INSERT INTO metrics_event ...;
COMMIT;

Полезно, если в одном сервисе есть и критичные данные (по умолчанию on), и метрики (явно off для batch'ей).

8. Длинные транзакции — тихий убийца

PG-W-060 Открытая долгая транзакция блокирует освобождение WAL и работу autovacuum.

PostgreSQL не может удалить WAL-файлы, в которых есть данные, нужные хоть какой-то открытой транзакции (для MVCC). Открытая транзакция от часа держит часы WAL — диск заполняется до отказа, кластер встаёт.

То же с VACUUM: он не может почистить мёртвые tuple'ы, видимые открытой транзакцией → таблица распухает (bloat) → запросы медленнее.

PG-W-061 Не делай долгие транзакции в коде. Типичные ошибки:

  • Spring @Transactional на методе, который ходит во внешний HTTP / в Kafka / в S3 (десятки секунд = открытая транзакция).
  • Долгий debug в IDE с открытой PgAdmin-транзакцией.
  • Batch jobs без коммитов внутри.

Правило: транзакция — секунды, не минуты. Если нужно долго — режь на куски, коммить чаще.

PG-W-062 Мониторинг долгих транзакций:

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

Поставь алёрт на xact_age > 5 min — это всегда баг или брошенная сессия.

9. Replication slot — ещё один тихий убийца WAL

PG-W-070 Висящий replication slot не даёт PG удалить WAL. Если реплика отвалилась или logical-consumer не читает — WAL копится бесконечно, диск заполняется.

SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS lag_bytes
FROM pg_replication_slots
ORDER BY lag_bytes DESC;

Алёрт на lag_bytes > 10GB или active = false дольше часа — почти всегда сломанный consumer / реплика.

PG-W-071 max_slot_wal_keep_size (PG13+) ограничивает запас WAL для слота. Если слот отстал больше — PG удаляет WAL, слот ломается, но кластер не падает.

10. Мониторинг WAL

PG-W-080 Базовые метрики:

-- размер pg_wal/
SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();

-- скорость генерации WAL
SELECT now(), pg_current_wal_lsn();
-- замерь дважды через минуту, посчитай разницу

-- статистика записи
SELECT * FROM pg_stat_wal;
-- wal_records, wal_bytes, wal_buffers_full, wal_write, wal_sync, wal_write_time, wal_sync_time

PG-W-081 Что мониторить в проде:

  • pg_wal/ size — алёрт на > N GB.
  • WAL throughput (bytes/sec) — рост указывает на новую нагрузку.
  • wal_buffers_full — растёт → увеличить wal_buffers.
  • HOT-ratio в pg_stat_user_tables < 50% на горячих таблицах.
  • Долгие транзакции > 5 мин.
  • Replication slot lag > N GB.

11. Чек-лист «тормозит запись»

  1. Это write-нагрузка? Если read — WAL ни при чём, см. Чтение EXPLAIN.
  2. synchronous_commit = on (дефолт)? — да, это правильно для бизнеса. Если нет — починить, если транзакции критичные.
  3. Bulk-вставки идут через COPY или batch? Не цикл INSERT по одной.
  4. Индексы на горячей таблице — все нужны? Каждый индекс = +WAL на каждую вставку.
  5. HOT-ratio > 80% на интенсивно-обновляемых таблицах? Если нет — снизить fillfactor до 80–90.
  6. Долгие транзакции > 5 мин? Если есть — найти и починить, autovacuum/WAL заблокированы.
  7. Replication slot лагает? Если да — починить consumer или временно дропнуть слот.
  8. Большие JSONB или text часто обновляются целиком? Разделить на маленькие колонки или вынести в отдельную таблицу.

Чек-лист на ревью кода

  • [ ] Spring @Transactional — только вокруг короткой DB-логики, не вокруг HTTP/Kafka/S3.
  • [ ] Bulk-импорты — через COPY (или JDBC batchUpdate), не цикл одиночных INSERT.
  • [ ] Длинные транзакции (> 5 сек) — режутся на куски с промежуточными COMMIT-ами.
  • [ ] Кеши и временные данные — UNLOGGED таблицы, не обычные.
  • [ ] Часто-обновляемые таблицы — fillfactor = 80–90 в DDL.
  • [ ] Не вешать индексы на колонки, которые обновляются почти на каждом UPDATE и редко используются в WHERE — это ломает HOT.
  • [ ] Не складывать в один data jsonb и горячие, и тяжёлые поля — UPDATE одного байта пишет всё в WAL.
  • [ ] Метрики/логи — рассмотреть synchronous_commit = off на уровне сессии для batch'а.

Связанные

  • Composite-индексы — каждый индекс = +WAL на каждую запись.
  • JSONB — полное переписывание при апдейте.
  • Антипаттерны.