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

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

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

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

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

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

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

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

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

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

Пример, как описывают этот случай: https://www.postgresql.org/message-id/flat/1059371874.2807306.1706727919318%40mail.yahoo.com.

Симптомы для реплики хорошо описаны в статье (если не использовали что-то нестандартное типа zfs, симптомы такие же) при загрузке больших объемов, в какой-то момент, реплика начинает очень медленно применять WAL и лаг растёт, причину диагностировать не удаётся, техподдержки нет, поэтому списывают на неизвестный баг:

Расширение

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

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

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

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

Пока курсор работает со строкой в блоке, блок закреплён. Если курсор закончит выбирать строки в блоке и перейдёт в другой блок, то закрепление снимется с блока и будет закреплён следующий блок. На реплике процесс startup может так следовать блок за блоком за курсором на реплике.

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

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

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

select locktype, relation::regclass, virtualtransaction, mode, granted, waitstart
 from pg_locks where relation::regclass::text='t'::text 
  OR granted = false;
 locktype | relation | virtualtransaction |           mode           | granted | waitstart 
----------+----------+--------------------+--------------------------+---------+-----------
 relation | t        | 11/4               | AccessShareLock          | t       | 
 relation | t        | 6/6                | ShareUpdateExclusiveLock | t       | 

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

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 , 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: N s,

frozen: N pages from table,

visibility map: X pages set all-visible, Y pages set all-frozen (Z 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 проблемой (от PGDG)

Сообщество разработчиков 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 и блоки с таким битом при вакуумировании в режиме заморозки не считывались. И если вакуум периодически обрабатывал таблицу в режиме заморозки, пока она росла и накапливала строки, то после того, как таблица дорастала до террабайт, заморозка на этой таблице была не такой долгой, так как большая часть блоков уже  была заморожена. Однако, если в таблице массово менялось, вставлялось или удалялось большое число строк, то проблема возникала снова.

Для закрытия проблемы с таблицами с преимущественными вставками строк, в 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 автовакуумом и причины, по которой реплика может отставать от мастера. Приведены методы мониторинга проблемы и описаны пути решения.