WAL и как разработчик влияет на него

Опирается на правила: PG-W-001 … PG-W-081 из PostgreSQL Style Guide → раздел WAL и как разработчик влияет на него.

Важно знать

• WAL — журнал всех изменений. Даёт durability, репликацию, PITR.
• fsync WAL в каждом COMMIT — цена durability.
• UPDATE пишет НОВУЮ СТРОКУ ЦЕЛИКОМ, не дельту (кроме HOT).
• COPY вместо INSERT цикла — 10-100x по времени, 5-10x по WAL.
• HOT (Heap-Only Tuple) — главный рычаг: fillfactor 80-90 на write-heavy.
• HOT отключается если UPDATE меняет индексируемую колонку.
• TOAST не переписывается если не изменился (аргумент против data jsonb).
• UNLOGGED таблицы — нулевой WAL для сессий/кешей.
• synchronous_commit = off для аналитики, не для money.
• Долгие TX блокируют освобождение WAL и autovacuum.
• Висящий replication slot — WAL копится бесконечно.

WAL пишется до ответа на COMMIT — скорость fsync = верхняя граница throughput на запись.

Жизненный цикл записи

PG-W-001:

INSERT INTO orders ...; COMMIT;

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

fsync WAL — синхронная операция в каждом COMMIT.

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

PG-W-002..003:

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

UPDATE пишет в WAL новую строку целиком, не дельту (кроме HOT). Если в строке 30 колонок и обновили 1 — в WAL уйдёт вся строка.

CREATE INDEX без CONCURRENTLY — мощный генератор WAL.

Bulk-операции

PG-W-010..012:

-- ✗ — 100K транзакций, 100K fsync, 100K round-trip
for (var row : rows) jdbcTemplate.update("INSERT INTO orders ...");

-- лучше — batch
jdbcTemplate.batchUpdate("INSERT INTO orders ...", batch);

-- ещё лучше — COPY
copyManager.copyIn("COPY orders FROM STDIN", reader);

Разница на 1M строк — 10-100x по времени, 5-10x по WAL.

Один большой COMMIT с 10K вставок дешевле 10K маленьких. Но длинная TX блокирует autovacuum. Оптимум — батчи по 1-10K строк.

Перед массовой загрузкой

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 обновление таблицы И каждого индекса.

HOT и fillfactor

PG-W-020..022: главный рычаг.

HOT — если новая версия строки помещается на ту же страницу И не изменены индексируемые колонки, PG не обновляет индексы и не пишет полные строки в WAL дважды.

Работает только при свободном месте на странице. На fillfactor=100 (default) почти никогда не получит HOT.

CREATE TABLE orders (...) WITH (fillfactor = 90);   -- 10% под HOT
ALTER TABLE orders SET (fillfactor = 80);
-- + VACUUM FULL orders;   чтобы переразложить

На write-heavy — fillfactor 80-90. Для статичных (справочники) — 100. Цена 10-20% места, выигрыш кратный по WAL.

HOT отключается если UPDATE меняет индексируемую колонку. Если поле меняется часто и индекс слабоселективный — удали индекс.

Проверка 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.

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

PG-W-030..031:

Большое значение в TOAST. Если при UPDATE не изменилось — НЕ переписывается, в WAL не идёт.

UPDATE article SET view_count = view_count + 1 WHERE id = ?;
-- body (text 50KB) в TOAST — НЕ в WAL

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

UNLOGGED таблицы

PG-W-040..041: нулевой WAL.

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

Нет WAL, нет реплики, при падении данные обнуляются.

Подходит:

• Кеши, которые легко прогреть.
• Промежуточные ETL.
• Очереди задач с TTL.
• Тесты.

Не подходит для бизнес-данных.

UNLOGGED → LOGGED через ALTER TABLE переписывает всю таблицу с полным WAL. Разовая операция.

synchronous_commit

PG-W-050..051:

synchronous_commit = off — коммит возвращает OK без подтверждения дискa.

Большой прирост throughput, ценой: при крахе в окне 200ms закоммиченные транзакции могут потеряться. Согласованность БД сохраняется.

Применимо: аналитика, метрики, логи, тесты. Не для: финансы, заказы.

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

Длинные транзакции

PG-W-060..062: тихий убийца.

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

То же с VACUUM — не может почистить tuples, видимые открытой TX → bloat.

Типичные ошибки:

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

Транзакция — секунды, не минуты.

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.

Replication slot

PG-W-070..071: ещё один тихий убийца.

Если реплика отвалилась или 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 > часа.

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

Мониторинг

PG-W-080..081:

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

-- скорость генерации
SELECT now(), pg_current_wal_lsn();

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

Что мониторить:

• pg_wal/ size — алёрт на N GB.
• WAL throughput rate — рост = новая нагрузка.
• wal_buffers_full растёт → увеличить wal_buffers.
• HOT-ratio < 50% на горячих.
• Long TX > 5 min.
• Replication slot lag > N GB.

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

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

Что запрещено

Антипаттерн	Правило	Что взамен
Цикл INSERT для bulk	PG-W-010	COPY или batch
fillfactor 100 на write-heavy	PG-W-021	80-90
synchronous_commit = off для money	PG-W-050	оставить on
@Transactional вокруг HTTP/Kafka	PG-W-061	TX вне
Игнорировать xact_age > 5 min	PG-W-062	алёрт
Висящий replication slot без алёрта	PG-W-070	мониторинг lag
Bulk-загрузка с индексами	PG-W-012	drop + load + create
Indexable колонка часто обновляется	PG-W-022	удалить индекс или денормализовать

Куда дальше

• PG → WAL — нормативные формулировки.
• VACUUM — bloat от disabled HOT.
• Spring @Transactional — длительность TX.
• Backup — WAL archive для PITR.
• Репликация — replication slots.
• Уровни изоляции — idle_in_transaction_session_timeout.
• Composite-индексы — лишние индексы = больше WAL.
• Миграции — CREATE INDEX CONCURRENTLY.