Ожидания получения 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, которое длится доли секунды, почти невозможно. В pg_locks ожиданий закрепления блока в буфере нет.

Из-за таких конфликтов, компаниии выделяют одну реплику, которая не обслуживает запросы, чтобы она только синхронизировалась и не отставала от мастера. Например, OpenAI, помимо 50 реплик, обслуживающих запросы на чтение, использует реплику, не обслуживающую никакие запросы, чтобы можно было на неё быстро переключиться (упомянута в 6 пункте в статье)

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

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

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

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

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

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

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

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

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

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

Скрытый текст

Файловые системы могут иметь специфику и ошибки. Например, Andres Freund пишет: "btrfs assumes that pages do not change while being written out withdirect-io, and corrupts itself if they do"

Расширение

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

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

Скрытый текст

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

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

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

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

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 |          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, 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 |          2 |                2
 t       |    10224 |             2 |          1 |                1

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

Представление 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

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

А автовакуум воркеров не так много и все они могут повиснуть. Хорошо, что в один момент только один 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: 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". Про формулу написано и в документации.

Скрытый текст

В докладе упоминалcя только параметр конфигурации autovacuum_freeze_max_age, хотя агрессивная заморозка начнётся гораздо раньше, через vacuum_freeze_table_age - vacuum_freeze_min_age.

Болеее того, на сборках с 64-разрядным счетчиком транзакций (например, Tantor Postgres SE и SE 1C, Postgres Pro Enterprise), autovacuum_freeze_max_age установлен в 10 миллиардов и агрессивная заморозка этим параметром будет вызываться чрезвычайно редко. А вот на сборках с 32-битным счетчиком, значение параметра по умолчанию всего 200 миллионов и частота возникновения проблемы намного более высока. Могу предположить, что при создании расширения pg_hot_blocks, использовался ванильный 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) c подмораживанием части (20%) блоков.

Скрытый текст

Можно перевести eager как "жадная", "нетерпеливая". Но, как сказала Александра:

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

Сообщество разработчиков PostgreSQL (PGDG) нельзя обвинить в “жадности” или “нетерпеливости” - патчи создаются быстро, терпеливо дорабатываются, обсуждения открыты. Поэтому 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) в PostgreSQL