Когда приложение делает INSERT или UPDATE, данные не сразу летят на диск. PostgreSQL сначала записывает изменение в специальный файл — WAL. Именно он обеспечивает надёжность: если сервер упадёт, база восстановится по этому журналу. Но WAL — не просто «страховка»: от его размера зависит скорость записи, расход диска и то, насколько быстро работает репликация.
Разберём, что происходит внутри и какие решения разработчика напрямую влияют на объём журнала.
Как PostgreSQL записывает данные
Представьте записную книжку, в которую шеф-повар пишет каждое изменение рецепта до того, как применить его на кухне. Если кухня сгорит — книжка поможет восстановить всё с последней записи. WAL — это такая книжка для базы данных.
Когда вы выполняете COMMIT, происходит следующее:
INSERT INTO orders ...; COMMIT;
1. PostgreSQL изменяет страницу в памяти (shared buffers).
2. Записывает изменение в WAL-буфер (тоже память).
3. На COMMIT — сбрасывает WAL-буфер на диск (fsync).
4. Только после fsync отвечает клиенту «OK».
5. Позже, на контрольной точке (checkpoint), изменённая страница
сбрасывается на диск отдельно.
Ключевой момент: fsync происходит на каждый COMMIT. Это синхронная операция — пока диск не подтвердил запись, база не отвечает. Скорость этого fsync и есть верхняя граница скорости записи.
Сколько WAL генерирует каждая операция
Вот где многих удивляет поведение UPDATE. В большинстве баз данных обновление пишет только изменившееся поле. PostgreSQL пишет новую строку целиком.
Если в строке 30 колонок и вы обновили одну — в WAL уйдут все 30. Это связано с тем, как устроен MVCC: каждое обновление — это новый физический экземпляр строки, а не патч поверх старого.
Примерный размер записей в WAL:
| Операция | Что пишется в WAL |
|---|---|
INSERT одной строки | ~50–200 байт + значения колонок |
UPDATE (обычный) | ~50 байт + новая строка целиком |
UPDATE с HOT | ~50 байт + только изменившиеся поля |
DELETE | ~50 байт + идентификатор строки |
CREATE INDEX без CONCURRENTLY | весь индекс целиком |
VACUUM | список освобождённых версий строк |
Обратите внимание на UPDATE с HOT — это особый режим, который мы разберём ниже. Он кардинально меняет картину для часто обновляемых таблиц.
HOT и fillfactor: главный рычаг для update-нагрузки
Обычный UPDATE создаёт новую версию строки — и если индексированные колонки не изменились, PostgreSQL всё равно обновляет индексы. Это двойная работа: и WAL больше, и индексы замедляются.
HOT (Heap-Only Tuple) — оптимизация, при которой новая версия строки ссылается на старую прямо внутри страницы, без участия индексов. Условия:
- Новая версия строки помещается на ту же страницу памяти.
- Изменение не затронуло ни одну индексируемую колонку.
Если оба условия выполнены — PostgreSQL не трогает индексы и пишет в WAL только дельту. Это может сократить WAL в несколько раз.
Проблема: при fillfactor = 100 (значение по умолчанию) страница заполнена под завязку, и новой версии строки нет места рядом со старой. HOT не срабатывает.
Решение — fillfactor 80–90: оставить 10–20% страницы свободными специально для обновлений.
-- при создании таблицы
CREATE TABLE orders (...) WITH (fillfactor = 90);
-- для существующей таблицы
ALTER TABLE orders SET (fillfactor = 80);
VACUUM FULL orders; -- перераскладывает данные с новым fillfactor
Проверить, как часто срабатывает HOT:
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. Цена — 10–20% дополнительного места на диске, выигрыш — кратное снижение WAL и нагрузки на индексы.
Важное ограничение: HOT не работает, если UPDATE меняет колонку, по которой есть индекс. Если такая колонка меняется часто, а индекс слабоселективный — рассмотрите удаление этого индекса.
COPY вместо INSERT: 10–100x быстрее при массовой загрузке
Допустим, нужно загрузить миллион строк. Интуитивно пишут цикл из INSERT:
INSERT INTO orders (id, customer_id, total) VALUES (...);
-- повторяется миллион раз
Каждый INSERT — отдельная транзакция, отдельный fsync, отдельный сетевой round-trip. Миллион строк = миллион fsync. Это очень медленно.
Намного эффективнее: объединить строки в пакет внутри одной транзакции. Ещё эффективнее — использовать COPY, специально созданный для массовой загрузки:
COPY orders (id, customer_id, total) FROM STDIN;
COPY передаёт данные одним потоком, генерирует минимальный WAL и обходится одним fsync на всю загрузку. На миллионе строк разница — 10–100 раз по времени и 5–10 раз по объёму WAL.
Как использовать COPY из кода:
import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
void copyInsert(Connection conn, String csvData) throws Exception {
CopyManager cm = new CopyManager((BaseConnection) conn);
try (Reader reader = new StringReader(csvData)) {
cm.copyIn("COPY orders (id, customer_id, total) FROM STDIN WITH CSV", reader);
}
}
import (
"context"
"github.com/jackc/pgx/v5"
"github.com/jackc/pgx/v5/pgxpool"
)
// pgx.CopyFrom транслируется в COPY STDIN
func bulkInsert(ctx context.Context, pool *pgxpool.Pool, orders []Order) (int64, error) {
rows := pgx.CopyFromSlice(len(orders), func(i int) ([]any, error) {
return []any{orders[i].ID, orders[i].CustomerID, orders[i].Total}, nil
})
return pool.CopyFrom(ctx,
pgx.Identifier{"orders"},
[]string{"id", "customer_id", "total"},
rows,
)
}
import { Pool } from 'pg';
import { from as copyFrom } from 'pg-copy-streams';
import { Readable } from 'stream';
import { pipeline } from 'stream/promises';
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
async function copyInsert(csvData: string): Promise<void> {
const client = await pool.connect();
try {
const stream = client.query(
copyFrom('COPY orders (id, customer_id, total) FROM STDIN WITH CSV'),
);
await pipeline(Readable.from([csvData]), stream);
} finally {
client.release();
}
}
import asyncpg
async def bulk_insert(pool: asyncpg.Pool, orders: list[dict]) -> None:
records = [(o["id"], o["customer_id"], o["total"]) for o in orders]
async with pool.acquire() as conn:
await conn.copy_records_to_table(
"orders",
records=records,
columns=["id", "customer_id", "total"],
)
Если COPY по каким-то причинам не подходит — хотя бы объединяйте строки в пакеты по 1–10 тысяч штук внутри одной транзакции. Один большой COMMIT с 10 000 вставок дешевле 10 000 маленьких.
Ещё один приём при массовой загрузке: удалить индексы до загрузки, загрузить, восстановить индексы. Каждая вставка с активным индексом пишет в WAL не только строку таблицы, но и обновление каждого индекса.
DROP INDEX ix_orders_customer;
COPY orders FROM '/tmp/data.csv';
CREATE INDEX CONCURRENTLY ix_orders_customer ON orders (customer_id);
UNLOGGED таблицы: нулевой WAL для временных данных
Некоторые данные не требуют надёжности. Кеш сессий, промежуточные результаты ETL, очереди задач с коротким временем жизни — если сервер упадёт, их несложно восстановить.
Для таких данных PostgreSQL предлагает UNLOGGED-таблицы: изменения в них вообще не пишутся в WAL.
CREATE UNLOGGED TABLE session_cache (
session_id uuid PRIMARY KEY,
payload jsonb NOT NULL,
expires_at timestamptz NOT NULL
);
Обратная сторона: такие таблицы не реплицируются, и при аварийном завершении PostgreSQL их содержимое обнуляется. Для бизнес-данных — неприемлемо. Для кешей и временных таблиц — отличный выбор.
TOAST: почему большие поля не всегда дорого
PostgreSQL автоматически хранит большие значения (длинные тексты, JSON) в отдельной TOAST-таблице. Важная деталь: если при UPDATE большое значение не изменилось — оно не переписывается в WAL.
-- обновляем только счётчик просмотров
UPDATE article SET view_count = view_count + 1 WHERE id = ?;
-- поле body (50 КБ текста) хранится в TOAST и НЕ пишется в WAL
Это аргумент против хранения всего подряд в одном большом jsonb-поле: если обновить один маленький ключ внутри JSONB, PostgreSQL не знает, что остальное не изменилось, и записывает весь объект в WAL.
synchronous_commit: скорость vs надёжность
По умолчанию PostgreSQL ждёт подтверждения от диска перед каждым COMMIT. Это гарантирует, что данные не потеряются даже при аварии.
Параметр synchronous_commit = off убирает это ожидание: база отвечает «OK» немедленно, а данные попадают на диск с задержкой около 200 мс. Это заметно увеличивает пропускную способность записи.
Цена: если база упадёт в эти 200 мс — последние закоммиченные транзакции могут потеряться. Согласованность базы при этом не нарушается, теряются только данные этого окна.
-- включить только для конкретной операции
SET LOCAL synchronous_commit = off;
INSERT INTO metrics_event ...;
COMMIT;
Применимо для метрик, аналитики, логов. Никогда — для финансовых операций и заказов.
Длинные транзакции: тихий убийца
PostgreSQL не может удалить WAL-файлы, пока они нужны хотя бы одной открытой транзакции. Если транзакция висит час — WAL за этот час никуда не денется, диск заполняется.
Та же открытая транзакция мешает VACUUM: он не может убрать старые версии строк, которые ещё видит эта транзакция. Таблицы разбухают.
Типичные причины длинных транзакций:
- транзакция охватывает сетевой вызов (HTTP-запрос к другому сервису, запись в S3, отправка в очередь);
- разработчик открыл транзакцию в консоли и забыл закрыть;
- пакетная задача обрабатывает миллион строк без промежуточных коммитов.
Посмотреть текущие долгие транзакции:
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: WAL может копиться бесконечно
Replication slot — это механизм, который гарантирует, что PostgreSQL не удалит WAL до тех пор, пока подписчик (реплика или логический потребитель) его не прочитает. Это удобно, но опасно.
Если реплика отвалилась или логический потребитель остановился — 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 > 10 ГБ или active = false дольше часа.
В PostgreSQL 13+ есть параметр max_slot_wal_keep_size: если слот отстал больше заданного предела, PostgreSQL удаляет WAL (слот сломается, но кластер не упадёт).
Мониторинг WAL
Несколько запросов, которые стоит добавить в мониторинг:
-- объём директории pg_wal/
SELECT pg_size_pretty(sum(size)) FROM pg_ls_waldir();
-- статистика записи в WAL
SELECT * FROM pg_stat_wal;
-- wal_records, wal_bytes, wal_buffers_full, wal_write_time, wal_sync_time
На что обращать внимание:
- размер
pg_wal/— алерт при превышении порога; - рост
wal_bytes— признак новой нагрузки; - рост
wal_buffers_full— сигнал увеличить параметрwal_buffers; - HOT-ratio ниже 50% на горячих таблицах;
- транзакции дольше 5 минут;
- отставание replication slot.
Коротко
- WAL — журнал изменений, который пишется до ответа на
COMMIT. Скоростьfsyncопределяет максимальную скорость записи. UPDATEпишет в WAL новую строку целиком, не только изменившееся поле — если не срабатывает HOT.- HOT сокращает WAL до дельты: нужно свободное место на странице и отсутствие изменений в индексируемых колонках. Включается через
fillfactor 80–90. COPYбыстрее цикла изINSERTв 10–100 раз по времени и в 5–10 раз по WAL.UNLOGGED-таблицы вообще не пишут WAL — для кешей и временных данных это нормально.- Большие TOAST-поля не переписываются в WAL, если не изменились.
synchronous_commit = offувеличивает пропускную способность, но данные за 200 мс могут потеряться при аварии. Только для некритичных данных.- Длинные транзакции удерживают WAL и мешают VACUUM. Транзакция — секунды, не минуты.
- Висящий replication slot копит WAL бесконечно. Нужен алерт на отставание.
Что почитать дальше
- VACUUM и bloat — связь с HOT и долгими транзакциями.
- Уровни изоляции —
idle_in_transaction_session_timeoutпротив долгих транзакций. - Индексы — лишние индексы увеличивают WAL.
- Миграции —
CREATE INDEX CONCURRENTLYи WAL.