WAL в PostgreSQL: почему COMMIT ждёт fsync журнала, а не таблицы

UPDATE одной строки записал в журнал 75 байт, COMMIT добавил 34, и клиент получил ответ, пока файл таблицы не тронут: коммит ждёт fdatasync сегмента в pg_wal, около 0,94 мс на диске из опыта. Отсюда потолок около тысячи коммитов в секунду на соединение и выигрыш в 11–13 раз от synchronous_commit = off. Ниже: записи WAL, LSN страницы (каталог данных, раскладка страницы) и цена асинхронного коммита.

DBA / 01koddo

COMMIT ждёт журнал, страница уйдёт позже

Стенд

docker run -d --name dba-wal-write-ahead-log -e POSTGRES_PASSWORD=pg postgres:18
docker exec -it dba-wal-write-ahead-log psql -U postgres

SELECT left(version(), 15) AS version;
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('wal_segment_size','wal_writer_delay','synchronous_commit','wal_sync_method')
ORDER BY name;

     version
-----------------
 PostgreSQL 18.6

        name        |  setting  | unit
--------------------+-----------+------
 synchronous_commit | on        |
 wal_segment_size   | 16777216  | B
 wal_sync_method    | fdatasync |
 wal_writer_delay   | 200       | ms

По умолчанию коммит синхронный, сегмент журнала весит 16 МБ (16 777 216 байт), а ждёт коммит вызова fdatasync. Это тот самый сегмент 0000000100000000000000NN, который мы видели в pg_wal в статье про каталог данных.

LSN: позиция в журнале и цена одной команды

LSN (log sequence number) — 64-битный байтовый адрес в журнале, который psql печатает как 0/17EE2A8: старшие и младшие 32 бита через косую. Каждый DML-запрос сдвигает его на длину своих записей. Снимем адрес до и после каждой команды; первая вставка в таблицу нужна как прогрев, чтобы в замер не попали служебные записи.

CREATE EXTENSION pageinspect;
CREATE TABLE t (id int, name text);
INSERT INTO t VALUES (0, 'warmup');
SELECT pg_current_wal_lsn() AS l0 \gset
INSERT INTO t VALUES (1, 'alpha');
SELECT pg_current_wal_lsn() AS l1 \gset
UPDATE t SET name = 'alpha2' WHERE id = 1;
SELECT pg_current_wal_lsn() AS l2 \gset
SELECT :'l0' AS before, :'l1' AS after_insert, :'l2' AS after_update,
       pg_wal_lsn_diff(:'l1', :'l0') AS insert_bytes,
       pg_wal_lsn_diff(:'l2', :'l1') AS update_bytes;

  before   | after_insert | after_update | insert_bytes | update_bytes
-----------+--------------+--------------+--------------+--------------
 0/17EE238 | 0/17EE2A8    | 0/17EE320    |          112 |          120

INSERT одной строки сдвинул журнал на 112 байт, UPDATE на 120. Обе команды выполнены без BEGIN, поэтому в эти числа входит и запись о коммите. pg_walfile_name_offset('0/17EE2A8') показывает, что адрес лежит в сегменте 000000010000000000000001 со смещением 8 315 560: старшая часть LSN и номер сегмента вычисляются друг из друга, а младшие 24 бита — смещение внутри 16-мегабайтного файла.

Что внутри записей: pg_waldump

Те же 232 байта можно прочитать глазами. pg_waldump лежит в образе рядом с сервером, а сегменты — в PGDATA/pg_wal:

docker exec dba-wal-write-ahead-log bash -c 'cd /var/lib/postgresql/18/docker/pg_wal && \
  pg_waldump -s 0/17EE238 -e 0/17EE320 -r Heap -r Transaction 000000010000000000000001'

rmgr: Heap        len (rec/tot):     65/    65, tx:        756, lsn: 0/017EE238, prev 0/017EE210, desc: INSERT off: 2, flags: 0x00, blkref #0: rel 1663/5/16430 blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        756, lsn: 0/017EE280, prev 0/017EE238, desc: COMMIT 2026-10-27 12:31:42.193830 UTC
rmgr: Heap        len (rec/tot):     75/    75, tx:        757, lsn: 0/017EE2A8, prev 0/017EE280, desc: HOT_UPDATE old_xmax: 757, old_off: 2, old_infobits: [], flags: 0x20, new_xmax: 0, new_off: 3, blkref #0: rel 1663/5/16430 blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        757, lsn: 0/017EE2F8, prev 0/017EE2A8, desc: COMMIT 2026-10-27 12:31:42.195306 UTC

Четыре записи на две команды. Поля одной строки, например третьей:

• rmgr: Heap — менеджер ресурсов, то есть модуль, который умеет воспроизвести запись при восстановлении. Есть Heap, Btree, Transaction, XLOG и другие; ключ -r отбирает записи одного менеджера.
• len (rec/tot): 75/75 — размер самой записи и размер вместе с полными образами страниц. Здесь образов нет, поэтому числа равны; когда они появляются, tot вырастает до 8 КБ.
• tx: 757 — номер транзакции, которая сделала изменение. У INSERT и его COMMIT он общий, 756.
• lsn: 0/017EE2A8 — адрес начала записи.
• prev 0/017EE280 — адрес начала предыдущей записи: так журнал образует цепочку, и по ней же видно, что между нашими записями других не было.
• desc: HOT_UPDATE … и blkref #0: rel 1663/5/16430 blk 0 — что именно изменилось и в какой странице: табличное пространство 1663, база 5, файл 16430, блок 0. Это тот самый блок, который мы разбирали в статье про страницу.

Теперь видно, откуда 112 и 120. Запись в журнале начинается с адреса, кратного 8, поэтому длина каждой округляется вверх: 65 + 34 дают 72 + 40 = 112, а 75 + 34 дают 80 + 40 = 120. В коде за это отвечает строка из ReserveXLogInsertLocation:

	size = MAXALIGN(size);

Источник: xlog.c, строка 1119, тег REL_18_6.

Сводку по типам записей даёт --stats=record: для нашего интервала это одна Heap/INSERT (65 байт), одна Heap/HOT_UPDATE (75) и две Transaction/COMMIT (68), всего 208 байт без выравнивания. Запись о коммите стоит 34 байта и появляется в каждой транзакции, даже если она изменила одну строку.

pd_lsn: страница помнит, какой записью её трогали

SELECT lsn, lower, upper FROM page_header(get_raw_page('t', 0));

    lsn    | lower | upper
-----------+-------+-------
 0/17EE2F8 |    36 |  8072

pd_lsn страницы равен 0/17EE2F8. Это не начало HOT_UPDATE (0/17EE2A8), а его конец, он же начало следующей записи, COMMIT. Так задумано: функция, вставляющая запись, возвращает адрес конца, и именно его вызывающий код записывает в страницу.

 * Returns XLOG pointer to end of record (beginning of next record).
 * This can be used as LSN for data pages affected by the logged action.
 * (LSN is the XLOG point up to which the XLOG must be flushed to disk
 * before the data page can be written out.  This implements the basic
 * WAL rule "write the log before the data".)

Источник: xloginsert.c, XLogInsert, строки 467–471, тег REL_18_6. COMMIT страницу не меняет, поэтому её pd_lsn остался на конце последней записи, которая её касалась.

Второе место, где pd_lsn работает, — запись страницы на диск. Перед этим буферный менеджер сбрасывает журнал до LSN страницы:

	if (buf_state & BM_PERMANENT)
		XLogFlush(recptr);

Источник: bufmgr.c, FlushBuffer, строки 4370–4371, REL_18_6; recptr в этом месте — LSN страницы. В эту строку упирается пунктирная связь на схеме: журнал опережает данные, а не наоборот.

Что делает COMMIT: сбрасывает журнал до своей записи

В конце транзакции RecordTransactionCommit вставляет запись COMMIT, а потом решает, ждать ли диска:

	if ((wrote_xlog && markXidCommitted &&
		 synchronous_commit > SYNCHRONOUS_COMMIT_OFF) ||
		forceSyncCommit || nrels > 0)
	{
		XLogFlush(XactLastRecEnd);

Источник: xact.c, RecordTransactionCommit, строки 1498–1502, REL_18_6. XactLastRecEnd — конец последней записи этой сессии (его выставляет XLogInsertRecord, строка 1076), то есть 0/17EE320 в нашем опыте. Условие читается так: любой уровень выше off синхронный. Транзакция без WAL-записей не ждёт ничего. Команды вроде DROP TABLE ждут всегда: nrels > 0 не позволяет удалить файлы раньше, чем на диске лежит запись о коммите.

Если synchronous_commit = off, управление идёт в ветку else:

		XLogSetAsyncXactLSN(XactLastRecEnd);

Источник: xact.c, строка 1523. Сессия только сообщает фоновому процессу WAL writer, до какого адреса журнал надо сбросить, и возвращает ответ клиенту. Покажем это тремя указателями журнала: вставки, записи и сброса.

CREATE TABLE a (id int);
INSERT INTO a VALUES (0);
SET synchronous_commit = off;
INSERT INTO a VALUES (1);
SELECT pg_current_wal_insert_lsn() AS i, pg_current_wal_lsn() AS w, pg_current_wal_flush_lsn() AS f;
SELECT pg_sleep(0.7);
SELECT pg_current_wal_insert_lsn() AS i, pg_current_wal_lsn() AS w, pg_current_wal_flush_lsn() AS f;

     i      |     w      |     f
------------+------------+------------
 0/66FAD180 | 0/66FAD118 | 0/66FAD118

     i      |     w      |     f
------------+------------+------------
 0/66FAD180 | 0/66FAD180 | 0/66FAD180

Сразу после асинхронного INSERT запись о нём и коммите (104 байта) есть только в памяти: вставка ушла вперёд, запись и сброс остались позади. Через 0,7 с все три указателя совпали, потому что WAL writer успел. Если процесс упадёт в промежутке, транзакция пропадёт.

Окно потери ограничено. WAL writer просыпается каждые wal_writer_delay (200 мс), но предпочитает писать целыми страницами, поэтому гарантия в комментарии кода такая:

 * We can guarantee that async commits reach disk after at most three
 * wal_writer_delay cycles.

Источник: xlog.c, XLogBackgroundFlush, строки 2956–2957, REL_18_6. При значении по умолчанию это до 600 мс последних коммитов. Целостность при этом не страдает: записи журнала всё равно идут по порядку и сбрасываются префиксом, поэтому после сбоя база встаёт в состояние на какой-то момент 600 мс назад, а не в разорванное. Документация называет тот же предел: три wal_writer_delay.

Групповой коммит: один fsync на нескольких

Для синхронного коммита сессия вызывает XLogFlush. Если сброс уже делает другой процесс, ждать не надо: достаточно, чтобы он дошёл до нужного адреса.

		/*
		 * Try to get the write lock. If we can't get it immediately, wait
		 * until it's released, and recheck if we still need to do the flush
		 * or if the backend that held the lock did it for us already. This
		 * helps to maintain a good rate of group committing when the system
		 * is bottlenecked by the speed of fsyncing.
		 */
		if (!LWLockAcquireOrWait(WALWriteLock, LW_EXCLUSIVE))

Источник: xlog.c, XLogFlush, строки 2847–2854, REL_18_6; строки 2847–2853 — комментарий. Сессия, получившая блокировку, пишет в файл всё, что накопилось в буфере, а не только свою запись, и делает один fdatasync на всех. Считаем это на нагрузке из восьми соединений:

CREATE TABLE gs AS SELECT
  (SELECT sum(fsyncs) FROM pg_stat_io WHERE object = 'wal') AS sy,
  (SELECT xact_commit FROM pg_stat_database WHERE datname = 'postgres') AS cm;

docker exec dba-wal-write-ahead-log pgbench -U postgres -i -s 10 -q postgres
docker exec -e PGOPTIONS="-c synchronous_commit=on" dba-wal-write-ahead-log \
  pgbench -U postgres -c 8 -j 8 -T 15 postgres

SELECT (SELECT xact_commit FROM pg_stat_database WHERE datname = 'postgres') - cm AS commits,
       (SELECT sum(fsyncs) FROM pg_stat_io WHERE object = 'wal') - sy AS wal_fsyncs
FROM gs;

 commits | wal_fsyncs
---------+------------
   34428 |      12846

34 428 коммитов потребовали 12 846 синхронизаций: 2,68 коммита на fsync. Без группового коммита их было бы 34 428. Счётчики pg_stat_io считают и фоновые процессы сервера, поэтому число синхронизаций не совсем точное.

pg_stat_wal и цена fsync на 10 000 вставок

В PostgreSQL 18 из pg_stat_wal убраны wal_write, wal_sync, wal_write_time и wal_sync_time (заметки о выпуске). Записи и синхронизации журнала теперь считает pg_stat_io со значением object = 'wal'; в pg_stat_wal остались wal_records, wal_fpi, wal_bytes и wal_buffers_full. Сравним два способа вставить 10 000 строк в t: десять тысяч отдельных транзакций и одну.

echo "INSERT INTO t VALUES (1, 'alpha');" > ins.sql
docker cp ins.sql dba-wal-write-ahead-log:/tmp/ins.sql
docker exec dba-wal-write-ahead-log pgbench -U postgres -n -t 10000 -f /tmp/ins.sql postgres

number of transactions actually processed: 10000/10000
latency average = 1.113 ms
tps = 898.696584 (without initial connection time)

Перед каждым прогоном сохраняем счётчики в таблицу snap, после прогона берём разность (таблицу создаёт CREATE TABLE snap AS SELECT now() AS ts, wal_records, wal_fpi, wal_bytes, … из запроса ниже, pg_stat_io обновляется с задержкой, поэтому перед чтением ждём 3 секунды):

SELECT w.wal_records - s.wal_records AS wal_records, w.wal_bytes - s.wal_bytes AS wal_bytes, w.wal_fpi - s.wal_fpi AS wal_fpi,
  (SELECT sum(writes) FROM pg_stat_io WHERE object='wal') - s.wr AS wal_write,
  (SELECT sum(fsyncs) FROM pg_stat_io WHERE object='wal') - s.sy AS wal_sync
FROM pg_stat_wal w, snap s;

 wal_records | wal_bytes | wal_fpi | wal_write | wal_sync
-------------+-----------+---------+-----------+----------
       20105 |   1031621 |       5 |     10002 |    10002

То же для INSERT INTO t SELECT g, 'alpha' FROM generate_series(1, 10000) g в одной транзакции дало 10 024 записи, 651 849 байт, 1 образ страницы, 3 записи и 3 синхронизации. Вместе:

Десять тысяч коммитов — десять тысяч записей COMMIT (отсюда вдвое больше wal_records) и десять тысяч синхронизаций, по одной на транзакцию. Одна транзакция на 10 000 строк потратила три синхронизации и заняла 5,8 мс по \timing. Первый прогон дал 898 TPS при задержке 1,113 мс: это стоимость диска, а не процессора. pg_test_fsync на том же томе показывает, что одна 8-килобайтная запись с fdatasync занимает 944 мкс (1 059 операций в секунду): задержка коммита почти целиком состоит из этого вызова.

Измерение: synchronous_commit = on и off

pgbench -i -s 10 создаёт 1 млн строк в pgbench_accounts. Каждый прогон идёт 30 секунд, 8 клиентов и 8 потоков, перед ним CHECKPOINT. Значение synchronous_commit задаём переменной PGOPTIONS соединения pgbench, сервер не перенастраиваем (так же работают ALTER SYSTEM и pg_reload_conf(), но тогда меняется поведение всех сессий).

docker exec dba-wal-write-ahead-log pgbench -U postgres -i -s 10 -q postgres
for mode in on off on off; do
  docker exec dba-wal-write-ahead-log psql -U postgres -qc CHECKPOINT
  docker exec -e PGOPTIONS="-c synchronous_commit=$mode" dba-wal-write-ahead-log \
    pgbench -U postgres -c 8 -j 8 -T 30 postgres
done

Отключение ожидания дало 11–13-кратный рост пропускной способности и снижение задержки на порядок. Разброс между прогонами одного режима — около 4% при on и 11% при off, поэтому честнее писать «около 12 раз», а не одно число. Тест ограничен диском: у хоста, где шёл опыт, fdatasync стоит 0,94 мс, а на сетевом томе в облаке он дороже, и разница будет больше. Уровни remote_write и remote_apply ждут ещё и реплику; без синхронной реплики они ведут себя как on, и их цену можно измерить только при репликации.

Что из этого следует

• Время коммита — это время fsync. Если pg_test_fsync показывает 1 мс, а нагрузка — один писатель, потолок около 1 000 коммитов в секунду. Он поднимается соединениями (групповой коммит дал 2,68 коммита на fsync) и пакетами: 10 000 вставок в одной транзакции стоили 3 синхронизации вместо 10 002.
• Асинхронный коммит теряет хвост, но не базу. Окно — до трёх wal_writer_delay, то есть 600 мс. Для логов кликов и черновиков это нормальная цена за 12-кратный рост; для платежей, выдачи кредитов и всего, о чём клиенту уже сообщили, — нет.
• Переключать можно на уровне транзакции. SET LOCAL synchronous_commit = off внутри одной транзакции не трогает остальные, а критичные операции остаются синхронными.
• Размер WAL измеряется, а не оценивается. pg_current_wal_lsn() до и после операции и pg_wal_lsn_diff дают байты на запрос; помни про выравнивание до 8 байт и про запись COMMIT в 34 байта.
• Не ищи wal_write и wal_sync в pg_stat_wal восемнадцатой версии: они переехали в pg_stat_io.

Чек-лист

1. Снимай pg_current_wal_lsn() до и после операции и считай разность pg_wal_lsn_diff: так видно, сколько байт журнала стоит запрос.
2. Для записи, которая тебя интересует, находи её в сегменте через pg_waldump -s <lsn> -e <lsn> -r <менеджер> и проверяй prev, tx и blkref.
3. Перед synchronous_commit = off перечисли операции, которые нельзя потерять вместе с последними 600 мс: платежи, выдачу кредитов, всё, о чём клиенту уже ответили. Их транзакции оставь синхронными.
4. Измерь fdatasync на том же томе, что и pg_wal, через pg_test_fsync и сравни с TPS своего pgbench: они должны совпасть по порядку величины.
5. Контейнер убирает всё созданное: docker rm -f dba-wal-write-ahead-log.