Ожидания получения BufferPin включают в себя:

1) Конфликты BufferPin на репликах (recovery conflict). Процесс на реплике startup должен получить блокировку на буфер, чтобы проиграть (replay, apply, накатить журнальную запись) hotcleanup, для этого startup должен дождаться pincount=0. Быстрая очистка происходит часто и процесс startup, если столкнется с блокировкой, полностью приостановит накат. Накатом занимается только он и в один поток. Из-за частых ожиданий BufferPin отставание реплики растёт и кажется, что startup не справляется и кажется, что проблема в том, что он однопоточный. Мониторинг таких ситуаций затруднён, так как в представлении pg_stat_database_conflicts отражаются только конфликты, которые привели к прерыванию запросов на реплике, то есть, когда startup приостанавливал работу более, чем на max_standby_streaming_delay (по умолчению, 30 секунд):

Из-за таких конфликтов, используют реплику, которая не обслуживает запросы, чтобы она не отставала из-за конфликтов и на реплику можно было быстро переключиться.

2) конфликты с автовакуумом при заморозке страниц. Такие конфликты есть и на мастере и на реплике и начинаются через 100 миллионов транзакций (по умолчанию, до 18 версии PostgreSQL). При недолгих тестированиях приложений не отлавливаются, проблемы начинаются при эксплуатации. Это одна из причин, по которой нагрузочные тесты длятся долго. Зная, как работает PostgreSQL, можно использовать недолгие тесты и оптимизировать приложение и параметры экземпляра, используя результаты недолгих тестов для обратной связи при настройке.

Как выглядит проблема

Пример как возникает проблема на мастере был приведён в докладе Александры Бондарь "Как разглядеть невидимое: ищем «горячие данные», виновные в просадке производительности" на PgConf 2026:

который не упомянут в обзоре конференции, хотя это одна из актуальных проблем PostgreSQL. Также доклад Александры - один из докладов, который слушал опытный DBA из статьи "Если ваш админ - самурай или обнять и плакать". После этого доклада задавались отличные вопросы пятью участниками и доклад вызвал интерес. Например, после доклада участник подсказал на ворос участника из банка ПСБ, как он поборол проблему горячего блока с помощью fillfactor.

К сожалению, обработка докладов для "выкладывания" спустя неделю выполняется без понимания ценности докладов и вопросы после докладов безжалостно обрезаются (не только в этом году, но и в предыдущем). Лет 5 назад доклады так безжалостно не обрезались и полезная информация была сохранена. Экономить таким образом место место на диске, я прям даже как то не знаю. Можно было бы хотя бы транскрипт сделать, он много места не занимает. На конференции PG BootCamp так не обрезали, специально проверил: все вопросы после докладов аккуратно сохранили.

Проблема может выглядеть и как описано в статье AvitoTech, хотя в том докладе о причинах не написано и они даже не исследовались. Да и исследовать сложно, так как проблема не мониторится - "идеальные метрики врут". На проблему могут указывать сообщения в диагностическом логе кластера PostgreSQL с текстом "automatic aggressive vacuum of table" или "skipped due to pins". По умолчанию, автоваккум создаёт сообщения, если обрабатывает таблицу дольше log_autovacuum_min_duration (по умолчанию, 10 минут).

Причина долгих ожиданий автовакуума на получение BufferPin в том, что:

1) процесс закрепляет блок дольше обычного, например, для Seq Scan по внешней таблице при соединении методом Nested Loop. При ошибках планировщика и выборе Nested Loop это может быть дольше обычного;

2) очереди ожидания нет и автовакуум обгоняют процессы, которым нужен блок для чтения или даже изменения. Для очистки же блока (автовакуумом или HOTcleanup) нужно, чтобы блок не был закреплён. Автовакуум в обычном режиме пропускает и не очищает закреплённые другими процессами блоки. Ждать автовакуум будет в агрессивном режиме, когда выполняет заморозку. Эта причина описана в докладе Александры:

В докладе описали расширение, которое создали для мониторинга горячих блоков:

В этот раз создали не "потому, что патч прикольнее", а, вероятно, для обучениия новых сотрудников написанию кода. В расширении применены интересные техники, в том числе попеременной записи в две части памяти. Расширение, скорее всего, качественное - в докладе описаны правильные способы снизить накладные расходы при подсчете закреплений. Расширение не отдано в опенсорс (как и probackup3) и в докладе правильно сказано, что перспективы принятия сообществом наработок невысокие, так как нужно менять ядро PostgreSQL добавляя callback (хук) для вызова функции расширения, что вряд ли покрывает ценность собираемой статистики. Но это не единственная причина.

Менее инвазивно было бы реализовать мониторинг методом сэмплинга и код для этого есть в pg_wait_sampling. Но даже и без расширения можно мониторить средствами стандартного расширения pg_buffercache. Проблема организационная: ресурсов разработчиков много, а квалифицированных постановщиков задач мало. Те, что есть, зомбированы идеей "злого" сообщества, не принимающего высококвалифицированно написанные (потому что сложные) патчи. Прична в том, что патчи решают задачи переусложненным способом потому, что "так прикольнее". После доклада Максима Орлова точка зрения сообщества была озвучена, но вряд ли понята. Средний разработчик отличается от опытного тем, что код опытного (senior) прост и понятен всем, а среднего (middle) решает задачу наиболее сложным образом, так как средний разработчик должен показать все свои знания, которые вставляет в свой код.

Для понимания, как выглядит проблема, в докладе Александры был приведён простой пример. В первой сессии создаётся таблица и читается строка в курсоре:

CREATE TABLE t AS SELECT 1 c;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t;
FETCH c;

Блок таблицы при этом закрепляется (pinning_backends=1) процессом, выполняющим транзакцию:

select c.relname, bufferid, usagecount, pinning_backends
 from pg_buffercache b join pg_class c on b.relfilenode =
  pg_relation_filenode(c.oid)
  and c.relname = 't';
 relname | bufferid | usagecount | pinning_backends 
---------+----------+------------+------------------
 t       |    10134 |          2 |                1

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

vacuum (freeze , verbose , skip_locked off) t;
INFO:  aggressively vacuuming "postgres.public.t"

Команда запустилась в агрессивном режиме и подвисла. Запрос к pg_buffercache выдаст уже pinning_backends = 2:

select c.relname, bufferid, usagecount, pinning_backends
 from pg_buffercache b join pg_class c on b.relfilenode =
  pg_relation_filenode(c.oid)
  and c.relname = 't';
 relname | bufferid | usagecount | pinning_backends 
---------+----------+------------+------------------
 t       |    10134 |          2 |                2
 t       |    10224 |          1 |                1

Событие ожидания можно посмотреть запросом, приведённым Александрой в докладе:

SELECT query, wait_event FROM pg_stat_activity WHERE query ILIKE 'vacuum%';
                     query                      | wait_event 
------------------------------------------------+------------
 vacuum (freeze , verbose , skip_locked off) t; | BufferPin

Если в других сессиях закреплять блоки, например, той же самой командой, что и в первой сессии:

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t;
FETCH c;

и если в других сессиях дать те же команды, то все эти сессии будут получать блокировки и пины. И пока все такие команды (или транзакции, если команды даны в транзакциях) не завершатся, то рабочий процесс автовакуума не получит блокировку и будет висеть. Пример результата обращения запроса к pg_buffercache для шести транзакций:

select c.relname, bufferid, relforknumber, usagecount, pinning_backends
  from pg_buffercache b join pg_class c on b.relfilenode =
   pg_relation_filenode(c.oid)
   and c.relname = 't';
 relname | bufferid | relforknumber | usagecount | pinning_backends 
---------+----------+---------------+------------+------------------
 t       |    10134 |             0 |          5 |                6
 t       |    10224 |             2 |          1 |                1

Вторая строка - блок файла слоя _vm (VM fork, файл с картой видимости и заморозки).

А вакуум воркеров не так много и все они могут повиснуть. Хорошо, что в один момент только один autovacuum worker может обрабатывать одну таблицу, поэтому для подвисания всех воркеров нужно, чтобы проблемных таблиц было тоже много, по числу воркеров.

Мониторинг (без патчей)

По текущей базе:

select c.relnamespace::regnamespace, c.relname, bufferid, pinning_backends, usagecount, isdirty, c.relpages
 from pg_buffercache b join pg_class c on b.relfilenode =
  pg_relation_filenode(c.oid)
   and b.reldatabase IN (0, (select oid
    from pg_database where datname = current_database())) and pinning_backends > 1
order by pinning_backends desc
limit 5;
 relnamespace | relname | bufferid | pinning_backends | usagecount | isdirty | relpages 
--------------+---------+----------+------------------+------------+---------+----------
 public       | t       |    10134 |                6 |          5 | t       |        0

Результат запроса можно сохранять, повторять запрос и сравнивать с сохраненными результатами. Если на одном и том же блоке каждый раз  pinning_backends > 1, то этот блок "горячий" и в этом блоке может появиться  проблема: с автовакуумом по истечении 100 миллионов транзакций после создания/усечения таблицы или успешной заморозки, а на реплике в любой момент.

Второе место, где ещё можно мониторить проблему: проверять диагностический журнал кластера в поисках сообщений автовакуума. В сообщениях будет большое значение после "elapsed" (общее время вакуумирования одной таблицы). В elapsed включаются все ожидания, в том числе  ожидания получения BufferPin, поэтому в диагностическом логе такие события будут присутствовать, если будет превышение значения log_autovacuum_min_duration (по умолчанию, 10 минут). Пример сообщения после выполнения заморозки командой vacuum (freeze), , если завершить транзакции:

vacuum (freeze , verbose , skip_locked off) t;
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 1 remain, 0 are dead but not yet removable
removable cutoff: 580671, which was 0 XIDs old when operation ended
new relfrozenxid: 580671, which is 1 XIDs ahead of previous value
frozen: 1 pages from table (100.00% of total) had 1 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 33 hits, 0 reads, 6 dirtied
WAL usage: 6 records, 6 full page images, 2139 bytes, 0 buffers full
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 3001.59 s

В команде можно обратить внимание на значения после:

elapsed: 3001.59 s,

frozen: 1 pages from table,

visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)

Борьба с ожиданием блокировок BufferPin

После выполнения заморозки, блок отмечается в карте заморозки и повторно не замораживается, если в блоке не будет изменений.

Если горизонт базы удерживается, то заморозка не может выполниться и команда vacuum (freeze) блокироваться на получении PufferPin не будет.

Установка значения параметра конфигурации transaction_timeout прерывает сессию:

set transaction_timeout = '5s';
vacuum (freeze , verbose , skip_locked off, disable_page_skipping ) t;
INFO:  aggressively vacuuming "postgres.public.t"
FATAL:  terminating connection due to transaction timeout
CONTEXT:  while scanning block 0 of relation "public.t"
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Параметр конфигурации statement_timeout прерывает вакуумирование таблицы:

set statement_timeout = '3s';
vacuum (freeze , verbose , skip_locked off, disable_page_skipping ) t;
INFO:  aggressively vacuuming "postgres.public.t"
ERROR:  canceling statement due to statement timeout
CONTEXT:  while scanning block 0 of relation "public.t"

Что на реплике?

Если на реплике дать команды закрепления блока в буфере:

BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t;
FETCH c;

а на мастере выполнится вакуумирование с заморозкой, автоматически или вручную:

vacuum (freeze , verbose) t;

эта команда на мастер успешно выполнится, так как на мастере блокировок нет, то на реплике через 30 секунд в соответствии с параметром конфигурации max_standby_streaming_delay, установленном на реплике, то в диагностическом логе появится запись:

FATAL:  terminating connection due to conflict with recovery
DETAIL:  User was holding shared buffer pin for too long.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.

а в транзакции на реплике, на любую команду выдастся ошибка:

postgres=*# select 1;
FATAL:  terminating connection due to conflict with recovery
DETAIL:  User was holding shared buffer pin for too long.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

Мониторинг задержек применения журнальных записей на репликах

Мониторить задержки в работе процесса startup можно по столбцу replay запроса, который нужно выполнить на мастере:

select application_name, state, sync_state,
 (pg_current_wal_lsn() - sent_lsn) as network,
 (sent_lsn - write_lsn) as write,
 (write_lsn - flush_lsn) as flush,
 (flush_lsn - replay_lsn) as replay,
 (pg_current_wal_lsn() - replay_lsn) as lag
from pg_stat_replication;

 application_name |   state   | sync_state | network | write | flush | replay |  lag  
------------------+-----------+------------+---------+-------+-------+--------+-------
 walreceiver      | streaming | async      |       0 |     0 |     0 |  16640 | 16640

Удобно то, что запрос на мастере выдаёт данные по всем репликам.

В примере, пока 30s не истекли replay будет больше нуля, lag показывает суммарное отставание реплики от мастера.

Правильная борьба c проблемой (когда борется сообщество)

Сообщество разработчиков PostgreSQL (PGDG) добавило в алгоритм вакуумирования энергичную заморозку в дополнение к агрессивной.

При обычном вакуумировании:

1) в блоках очищаются версии строк, вышедшие за горизонт базы данных.

2) если в блоке нет старых версий строк (старые версии были вычищены предыдущим вакуумированием), то помечает такой блок в карте видимости (файл vm таблицы) битом all_visible.

Быстрая очистка не может очистить все версии, она должна оставить как минимум две версии строки, так как выполняется в транзакции и не знает, откатится или зафиксируется ли транзакция, в которой, вставленная в блок версия строки, вызвала быструю очистку блока (HOT cleanup). К блокам, отмеченным как all_visible, следующее вакуумирование (в версиях PostgreSQL до 18) не обращалось, они исключались из сканирования. Если до следующего вакуумировании блок изменится, то процесс, изменивший блок, уберет бит all_visible.

3) Если автовакуум долго не обрабатывал таблицу в режиме заморозки, цикл автовакуума он запустится в агрессивном режиме (freeze).

Формула: age(pg_class.relfrozenxid) >  vacuum_freeze_table_age - vacuum_freeze_min_age  или еслиmxid_age(pg_class.relminmxid) > vacuum_multixact_freeze_table_age - vacuum_freeze_min_age

Формула приведена на 280 слайде курса PT-16 "Настройка производительности PostgreSQL". Про формулу написано и в документации.

В агрессивном режиме автовакуум ждёт получения блокировки и не пропускает заблокированные (pincount>0). Если таких блоков много, то автовакуум затыкается на большом числе блоков и долго обрабатывает таблицу. При этом автовакуум (autovacuum worker) удерживает горизонт базы и другие процессы, выполняющие вакуум и процессы автовакуума не могут очищать старые версии строк.

До 18 версии проблема сглаживалась тем, что заморозив блок, вакуум отмечал его в карте заморозки битом all_frozen и блоки с таким битом при вакуумировании в режиме заморозки не считывались. И если вакуум периодически обрабатывал таблицу в режиме заморозки, пока она росла и накапливала строки, то после того, как таблица дорастала до террабайт, заморозка на этой таблице была не такой долгой, так как большая часть блоков уже  была заморожена. Однако, если в таблице массово менялось, вставлялось или удалялось большое число строк, то проблема возникала снова.

Карта заморозки была встроена в карту видимости в версии 9.6 и, после встройки, проблема долгой заморозки на больших таблицах, по большей части, была устранена. После этого актуальность перехода с 32-битного на 64-битный xid существенно уменьшилась.

С 9.6 до 18 версии алгоритм автовакуума дорабатывался, доделывали косяки, о которых не догаались. Например, при вставке строк, старые версии не порождались, но блоки со вставленными версиями всё равно надо морозить. Из-за этой необходимости автовакуум, как только запускался режим заморозки, мог работать долго. Для закрытия проблемы с таблицами с преимущественными вставками строк, в 13 версии добавили параметры autovacuum_vacuum_insert_scale_factor и для чётности autovacuum_vacuum_insert_threshold. То есть только через 5 лет.

4) И вот через 5 лет добавили параметр vacuum_max_eager_freeze_failure_rate и энергичную заморозку. Была полная, агрессивная, обычная заморозки. И теперь между обычной и "агрессивной" добавилась "энергичная" (eager). Можно перевести eager как "жадная", а некоторые переводят как "нетерпеливая". Как сказала Александра:

жадность это не всегда хорошо, хорошо - это открытость

PGDG нельзя обвинить в жадности - всё в открытом доступе, в нетерпеливости тоже - патчи создаются энергично, терпеливо дорабатываются, поэтому в PostgreSQL eager - это "энергично" :)

Автовакуум будет при обычном, не агрессивном сканировании, будет сканировать таблицы с all_visible, но не all_frozen битами. То есть вероятность, что оба бита будут установлены на 18 версии повышается. До 18 версии у большей части блоков был установлен бит all_visible, но не all_frozen. Алгорим "умный": чтобы не морозить всё за одно вакуумирование, а растянуть на несколько вакуумирований, алгоритм морозит только 20% от all_visible блоков за одно вакуумирование. Заморозка растягивается на 5 проходов автовакуума. Параметр vacuum_max_eager_freeze_failure_rate = 0.03 (3%) означает, что если на 3% от общего числа блоков в таблице в процессе сканирования 20% блоков из карты видимости с битом all_visible, не сможет быть установлен бит all_frozen, то прекратить сканировать блоки all_visible. Причина, по которой не может быть выставлен all_frozen в том, что блок закреплён каким-то другим процессом (pincount>0), то есть из блока читаются строки. Если бы строки менялись, то процесс убрал бы бит all_visible. Автовакууму нужно эксклюзивно закрепить блок, чтобы на каждой строке в этом блоке установить признак заморозки, только после этого он сможет установить бит в карте заморозки.

Заключение

В статье детально рассмотрена проблема ожидания BufferPin автовакуумом и причины, по которой реплика может отставать от мастера. Приведены методы мониторинга проблемы и описаны пути решения.