9 октября 2026Команда Сарнас

PostgreSQL 19: query hints и тонкости настройки max_wal_size

PostgreSQL 19 приносит долгожданный механизм query hints — расширения pg_plan_advice и pg_stash_advice, которые позволяют фиксировать план запроса и управлять им через именованные коллекции советов. Разбираем, как работают теги, почему «подсказки» на деле переопределяют решения планировщика, и заодно проясняем природу параметра max_wal_size, чьё название вводит в заблуждение.

В PostgreSQL 19 появляется механизм подсказок плана запроса (query hints) — модули pg_plan_advice и pg_stash_advice. advice. Отдельно стоит разобрать параметр max_wal_size, который вопреки названию не является ни максимумом, ни размером чего-либо измеримого. Обе темы объединяет одно: и подсказки, и max_wal_size работают не так, как подсказывает их название, и обе требуют понимания механики, чтобы не навредить.

Query hints: как работает pg_plan_advice

Расширение загружается командой LOAD 'pg_plan_advice';. После этого строка рекомендаций задаётся через параметр:

SET pg_plan_advice.advice = 'JOIN_ORDER(c o) NESTED_LOOP_MEMOIZE(o) INDEX_SCAN(o idx_orders_customer)';

С этого момента любой запрос выполняется с учётом advice. Сбросить настройку можно командой RESET pg_plan_advice.advice;.

Строка advice состоит из тегов. Теги сгруппированы по назначению. Метод сканирования задают SEQ_SCAN, INDEX_SCAN, INDEX_ONLY_SCAN, BITMAP_SCAN, DO_NOT_SCAN. Порядок соединения — JOIN_ORDER. Метод соединения — HASH_JOIN, MERGE_JOIN, NESTED_LOOP_PLAIN, NESTED_LOOP_MEMOIZE, NESTED_LOOP_MATERIALIZE. Параллелизм — GATHER, GATHER_MERGE, NO_GATHER.

Как теги адресуют узлы плана

Узлы плана адресуются по алиасам. Алиасы указываются в скобках после тега. INDEX_SCAN(o idx_orders_customer) предписывает для алиаса o использовать индекс idx_orders_customer, а SEQ_SCAN(o) — последовательное сканирование таблицы с алиасом o. JOIN_ORDER(c o oi) перечисляет алиасы в желаемом порядке соединения. Теги методов соединения и параллельных операций тоже принимают алиасы: HASH_JOIN(o), NO_GATHER(c o).

Проверить, что подсказки применились, позволяет EXPLAIN (PLAN_ADVICE): применённые подсказки помечаются как matched, например SEQ_SCAN(o) /* matched */.

Как EXPLAIN генерирует строку advice для фиксации плана

EXPLAIN с опцией PLAN_ADVICE добавляет к обычному плану секцию Generated Plan Advice, в которой перечислены теги, описывающие выбранный планировщиком план. Для запроса с соединением customers и orders эта секция содержит строки JOIN_ORDER(o c), HASH_JOIN(c), SEQ_SCAN(o c) и GATHER_MERGE((c o)).

Полученную строку можно скопировать, изменить и подать обратно через SET pg_plan_advice.advice, чтобы зафиксировать план:

SET pg_plan_advice.advice = 'JOIN_ORDER(o c) HASH_JOIN(c) SEQ_SCAN(o c) GATHER_MERGE((c o))';

После этого EXPLAIN выводит секцию Supplied Plan Advice, где каждая применённая подсказка помечена /* matched */.

Что происходит при сломанном advice

Если advice ссылается на несуществующий алиас или несовместимый тег, запрос не падает полностью: расширение деградирует мягко и выдаёт подробную обратную связь в логах — при условии, что это включено через SET pg_plan_advice.trace_mask=true;.

Если же advice просто неоптимален, не происходит ничего: планировщик Postgres переопределён вашим advice, поэтому нужно быть осторожным, часто запускать EXPLAIN и проверять планы, чтобы не сделать хуже.

pg_stash_advice: именованные коллекции советов

pg_create_advice_stash('production_fixes') создаёт именованную коллекцию советов — stash. Коллекция — это контейнер, а набор — одна конкретная строка подсказок внутри неё; в одной коллекции можно хранить много таких наборов. Чтобы применить коллекцию к запросам, задают её имя через параметр pg_stash_advice.stash_name — это включает набор советов в базе, сессии или для конкретного запроса.

Область применения можно ограничить:

на уровне базы — ALTER DATABASE mydb SET pg_stash_advice.stash_name = 'production_fixes';
на уровне роли — ALTER ROLE reporting_user SET pg_stash_advice.stash_name = 'reporting_tuning';
по идентификатору запроса — SELECT pg_set_stashed_advice('my_stash', <query_id>, '...');

Query ID одинаков у запросов одной формы: литеральные значения не влияют, поэтому WHERE id = 1 и WHERE id = 99999 дают один и тот же query ID. Этот же идентификатор использует pg_stat_statements для группировки статистики. Получить его можно из EXPLAIN (VERBOSE, COSTS OFF, PLAN_ADVICE). Пример привязки совета к конкретному запросу:

SELECT pg_set_stashed_advice(
    'production_fixes',
    9122549731181782750,
    'JOIN_ORDER(c o) NESTED_LOOP_MEMOIZE(o) INDEX_SCAN(o idx_orders_customer) NO_GATHER(c o)'
);

Когда hints не нужны

Авторы предупреждают, что hints «вероятно, никогда не понадобятся»: планировщик обычно прав, а плохие решения планировщика считаются настоящими багами и быстро исправляются — поэтому сообщество долго сопротивлялось добавлению hints. В большинстве случаев лучше исправить ошибку приложения, обновить статистику или добавить подходящий индекс.

Advice оправдан, когда код запроса нельзя изменить: внешнее приложение, foreign data wrapper или непрозрачная функция. Планировщик не видит внутренностей функций и предполагает, что булевы функции совпадают с 33% строк.

Пример: функция is_flagged_order отмечает 50 заказов из 1 миллиона, но планировщик считает, что совпадёт 333K, и строит hash joins по полным таблицам order_items (3 миллиона строк) и customers (100 000 строк). С plan advice можно переключиться на nested loops, выполняющие 50 точечных index lookups вместо полного сканирования таблиц, — это работает примерно в 2 раза быстрее.

max_wal_size: что это на самом деле

max_wal_size — это не размер каталога pg_wal и не жёсткий предел. Это триггер контрольной точки, выраженный в байтах.

В конце контрольной точки сервер удаляет сегменты старше нового redo pointer, но часть из них не удаляет, а перерабатывает сегмент в будущие сегменты. Порядок такой: сначала вычисляется номер сегмента по RedoRecPtr, затем KeepLogSeg корректирует его с учётом удержаний — сегментов, которые нельзя удалять или перерабатывать, потому что они ещё нужны для архивации, слотов репликации, суммаризации WAL или как самые свежие wal_keep_size мегабайт плюс один файл. После этого номер уменьшается на единицу и передаётся в RemoveOldXlogFiles.

Сколько сегментов переработать, а сколько удалить, решает XLOGfileslop: он берёт границы minSegNo и maxSegNo от lastredoptr, оценивает расстояние как (1.0 + CheckPointCompletionTarget) * CheckPointDistanceEstimate с добавкой 10% и зажимает recycleSegNo между этими границами. Поэтому ниже предела система перерабатывает достаточно файлов под оценку до следующей контрольной точки, а остальное удаляет. При превышении max_wal_size из-за кратковременного пика ненужные сегменты удаляются, пока система не вернётся под лимит.

Независимо от max_wal_size всегда удерживаются самые свежие wal_keep_size мегабайт WAL плюс один файл, а также сегменты, нужные для архивации, слотов репликации и суммаризации WAL. При включённой архивации старые сегменты не могут быть удалены или переработаны, пока не заархивированы; при отставании архивации они накапливаются в pg_wal. Медленный или сбойный standby с replication slot даёт такой же эффект.

max_wal_size мягкий и в другую сторону: понижение не освобождает место сразу — уже предвыделенные сегменты перед точкой вставки контрольной точкой не пересматриваются.

Как max_wal_size запускает checkpoint

Триггер по объёму WAL использует не само значение max_wal_size, а производное число CheckPointSegments, вычисляемое в CalculateCheckpointSegments:

target = (double) ConvertToXSegs(max_wal_size_mb, wal_segment_size) /
    (1.0 + CheckPointCompletionTarget);

/* round down */
CheckPointSegments = (int) target;

if (CheckPointSegments < 1)
    CheckPointSegments = 1;

То есть триггер срабатывает не на самом max_wal_size, а на max_wal_size / (1 + checkpoint_completion_target), округлённом вниз до целых сегментов. Изменение max_wal_size или checkpoint_completion_target пересчитывает CheckPointSegments.

Функция XLogCheckpointNeeded решает, пора ли запускать checkpoint: она сравнивает номер текущего сегмента WAL с номером сегмента, содержащего RedoRecPtr, и сообщает, что checkpoint нужен, когда разница достигает CheckPointSegments - 1. Именно этот сигнал заставляет сервер начать checkpoint по объёму WAL.

Отдельно существует величина distance — WAL, записанный между двумя последними запусками checkpoint; он сохраняется в PrevCheckPointDistance. А estimate (CheckPointDistanceEstimate) — сглаженная оценка: она мгновенно поднимается до nbytes, если nbytes больше, иначе снижается как 0.90 * CheckPointDistanceEstimate + 0.10 * nbytes. Эта оценка используется не для запуска checkpoint, а в XLOGfileslop для решения о числе удерживаемых сегментов. При checkpoint, вызванном по max_wal_size, оценка должна сходиться к CheckpointSegments * wal_segment_size.

Формула и пороги

Целевое расстояние в сегментах вычисляется как max_wal_size_mb, переведённый в сегменты, делённый на (1.0 + CheckPointCompletionTarget), с округлением вниз; если получается меньше 1, устанавливается 1.

При checkpoint XLOGfileslop вычисляет minSegNo и maxSegNo как lastredoptr / wal_segment_size плюс ConvertToXSegs от min_wal_size_mb и max_wal_size_mb соответственно, минус 1. Затем distance = (1.0 + CheckPointCompletionTarget) * CheckPointDistanceEstimate, увеличивается на 10%, и recycleSegNo = ceil((lastredoptr + distance) / wal_segment_size). Полученное значение ограничивается снизу minSegNo и сверху maxSegNo — это и есть наибольший сегмент, который следует преаллоцировать.

Цена «недобора»

Реальная цена платится при занижении max_wal_size, а не при превышении. Механизм — full_page_writes: первое изменение любой страницы после контрольной точки записывает в WAL всю страницу целиком (8 кБ). При 1GB этот workload запускал checkpoint каждые 22–28 секунд, поэтому каждая горячая страница пересоздавалась так часто.

Документация объясняет причину FPI: запись страницы во время сбоя ОС может быть частичной, и обычных данных WAL не хватит для восстановления страницы; полный образ гарантирует корректное восстановление, но увеличивает объём WAL. Там же указан способ снижения затрат: увеличение интервалов между checkpoint.

Реальная цена при занижении — рост wal_fpi и объёма WAL. Единственная цена завышения — возможное время восстановления после сбоя: recovery воспроизводит WAL от redo pointer последней завершённой контрольной точки, и если сбой попадёт ближе к концу следующей, это будет всё расстояние триггера плюс всё, записанное во время работы checkpoint, — полный max_wal_size.

Как сравнивали и что получилось

Замеры проводились на workload pgbench с четырьмя клиентами против базы scale-50, в 512 MB shared_buffers, по три минуты каждый прогон, при checkpoint_timeout в 15min, на версии 18.6. Сравнивались два значения max_wal_size.

Параметр	`max_wal_size = 1GB`	`max_wal_size = 8GB`
tps	3,700	4,131
WAL written	3815 MB	1080 MB
checkpoints (requested)	7	0
wal_fpi	447,963	91,335

При 1GB чекпоинт запускался каждые 22–28 секунд, и лог семь раз сообщал checkpoints are occurring too frequently (23 seconds apart). Показатель pg_stat_wal.wal_fpi при 1GB составлял 10% всех WAL-записей и, при выключенном wal_compression, около 90% байт. Чекпоинтер записал 263,737 буферов за эти три минуты и ни одного в другом прогоне.

Строка завершённого checkpoint выглядит так:

checkpoint complete: wrote 36828 buffers (56.2%), ... 0 WAL file(s) added, 0 removed, 33 recycled; write=22.929 s, sync=0.150 s, total=23.108 s; ... distance=540676 kB, estimate=540681 kB; ...

Поле wrote 36828 buffers (56.2%) показывает, сколько буферов было записано и какую долю от общего числа это составило. Часть 0 WAL file(s) added, 0 removed, 33 recycled означает, что ни один сегмент не добавлен и ни один не удалён, а 33 сегмента переработаны. Переработка ниже max_wal_size система перерабатывает достаточно файлов, чтобы покрыть оценённую потребность до следующего checkpoint, а остальные удаляет.

Рост WAL в 3.5 раза при том же наборе транзакций объясняется тем же full_page_writes. Падение tps на 10% сопровождается записью 263 737 буферов чекпоинтером. Соотношение нелинейно: трёхминутный прогон при 8GB всё ещё начинался с объёма ре-имиджинга одной контрольной точки, который пятнадцатиминутный цикл оплачивает лишь однажды.

Что цифры не показывают

Измерение не отражает поведение при archive_mode: старые сегменты не могут быть удалены или переработаны, пока не заархивированы, и при отставании архивации накапливаются в pg_wal.

Не отражает оно и поведение на standby: в режиме восстановления выполняются restartpoints, и из-за ограничений на их выполнение max_wal_size часто превышается во время восстановления на объём до одного цикла checkpoint.

Не отражено поведение при аварийном завершении: увеличение параметра может увеличить время, необходимое для crash recovery.

Наконец, не отражено поведение при разных wal_sync_method: этот параметр определяет, как PostgreSQL просит ядро сбросить обновления WAL на диск. При open_datasync или open_sync запись в XLogWrite гарантирует синхронизацию, а issue_xlog_fsync ничего не делает; при fdatasync, fsync или fsync_writethrough запись перемещает WAL в кэш ядра, а issue_xlog_fsync синхронизирует их.

Внутреннее устройство WAL: вставка и flush

XLogInsertRecord резервирует место, вызывая ReserveXLogInsertLocation, которая под спинлоком читает CurrBytePos (конец зарезервированного WAL) и PrevBytePos (начало предыдущей записи), увеличивает CurrBytePos на выровненный размер записи и сдвигает PrevBytePos на старое значение CurrBytePos. Это критичная по производительности часть XLogInsert, которая должна быть сериализована между бэкендами.

Вставка защищена небольшим фиксированным набором WAL insertion locks: чтобы вставить запись, нужно держать один из них. Каждый такой lock содержит LWLock, атомарный insertingAt (докуда дошла вставка) и lastImportantAt (LSN последней важной записи). Значение insertingAt читается при flush, чтобы дождаться только тех вставок, которые затрагивают записываемые буферы.

XLogFlush обеспечивает durable-запись до commit: он берёт WALWriteLock, при необходимости спит CommitDelay для группового коммита, вызывает WaitXLogInsertionsToFinish, затем XLogWrite с WriteRqst.Write=Flush=insertpos, и после этого проверяет, что LogwrtResult.Flush >= record, иначе выдаёт ошибку. Документация подтверждает: обычно WAL-буферы записываются и сбрасываются по запросу XLogFlush, который делается в основном на commit транзакции, чтобы гарантировать сброс записей транзакции на постоянное хранилище.

Внутреннее устройство WAL: checkpoint и recycling

Чекпоинтер определяет необходимость checkpoint, проверяя ненулевое слово флагов в разделяемой памяти: если ckpt_flags не ноль, он ставит do_checkpoint = true и chkpt_or_rstpt_requested = true. Дополнительно он форсирует checkpoint по времени: если elapsed_secs >= CheckPointTimeout, ставится do_checkpoint = true и добавляется флаг CHECKPOINT_CAUSE_TIME. Затем под спинлоком ckpt_lck он забирает накопленные флаги, обнуляет их, увеличивает счётчик начатых ckpt_started++ и будит ожидающих через ConditionVariableBroadcast(&CheckpointerShmem->start_cv). Если установлен CHECKPOINT_END_OF_RECOVERY, режим restartpoint отменяется, иначе режим определяется через RecoveryInProgress(). По завершении он освобождает все smgr-объекты вызовом smgrdestroyall(), публикует завершение через ckpt_done = ckpt_started и будит done_cv.

Bgwriter взаимодействует с чекпоинтером косвенно: при FirstCallSinceLastCheckpoint() он тоже вызывает smgrdestroyall(), а также периодически пишет xl_running_xacts через LogStandbySnapshot(), если активна standby-информация и нет recovery, прошёл интервал LOG_SNAPSHOT_INTERVAL_MS и last_snapshot_lsn <= GetLastImportantRecPtr().

RemoveOldXlogFiles решает судьбу сегмента так: вычисляет recycleSegNo = XLOGfileslop(lastredoptr) и endlogSegNo из endptr, строит имя последнего сохраняемого сегмента через XLogFileName, и для каждого файла, прошедшего проверку имени, сравнивает по алфавиту имя без части с timeline. Если XLogArchiveCheckDone подтверждает завершение архивации, он обновляет lastRemovedSegNo в разделяемой памяти и вызывает RemoveXlogFile. Внутри RemoveXlogFile сегмент рециклируется, только если одновременно включён wal_recycle, endlogSegNo <= recycleSegNo, активен InstallXLogFileSegmentActive, тип файла обычный и успешен вызов InstallXLogFileSegment. Тогда увеличивается счётчик ckpt_segs_recycled++ и endlogSegNo. Иначе файл удаляется.

Значения по умолчанию

checkpoint_timeout — 5 минут, checkpoint_completion_target — 0.9, max_wal_size — 1 ГБ, min_wal_size — 80 МБ.

checkpoint_timeout задаёт максимальное время между автоматическими контрольными точками, а checkpoint_completion_target — долю этого интервала, за которую checkpoint должна завершиться, распределяя нагрузку ввода-вывода. max_wal_size — мягкий предел роста WAL при автоматических checkpoint. min_wal_size задаёт минимум WAL, который всегда перерабатывается для будущего использования, даже если система простаивает.

Количество WAL-сегментов в pg_wal зависит от min_wal_size, max_wal_size и объёма WAL, сгенерированного в предыдущих циклах checkpoint. wal_buffers автонастраивается примерно в 3% от shared_buffers, с максимумом в один сегмент WAL и минимумом в 8 блоков; значение -1 означает запрос на автонастройку. wal_keep_size независимо от max_wal_size сохраняет самые свежие wal_keep_size мегабайт WAL-файлов плюс один дополнительный файл.

Что из этого следует на практике

Подсказки плана — инструмент для случаев, когда код запроса изменить нельзя, а планировщик ошибается из-за невидимых ему внутренностей функций. Строку advice удобно получать из EXPLAIN (PLAN_ADVICE), копировать и подавать обратно через SET pg_plan_advice.advice. Сломанный advice не роняет запрос, а деградирует мягко с обратной связью в логах при включённом trace_mask; неоптимальный advice не даёт никаких сигналов, поэтому планы нужно регулярно проверять через EXPLAIN. Именованные коллекции советов позволяют привязывать их к базе, роли или конкретному query ID, который одинаков для запросов одной формы и совпадает с идентификатором в pg_stat_statements.

max_wal_size — не максимум и не размер каталога, а триггер checkpoint в байтах. Триггер срабатывает не на самом значении, а на max_wal_size / (1 + checkpoint_completion_target), округлённом вниз до целых сегментов. Реальная цена платится при занижении: частые checkpoint заставляют full_page_writes переписывать горячие страницы целиком, что в замере дало рост WAL в 3.5 раза и падение tps на 10%. Единственная цена завышения — возможное время восстановления после сбоя. Понижение параметра не освобождает место сразу, а архивные сегменты, слоты репликации и суммаризация WAL удерживают файлы независимо от значения.