BufferPin conflicts in PostgreSQL: how to find and fix

BufferPin Conflicts in PostgreSQL: Diagnosis and Optimization for Replicas and Autovacuum

BufferPin conflicts when pinning buffers—one of the hidden but critical performance issues in PostgreSQL, especially on replicas and during page freezing. They don't show up in standard monitoring metrics, masquerade as "hangs" or "unexplained lag," and require a deep understanding of the DBMS internals for diagnosis. This article breaks down how they arise, their symptoms, detection methods, and practical ways to reduce their impact.

Why BufferPin Conflicts Are Dangerous and Hard to Spot

The main challenge is that BufferPin waits aren't recorded as locks in pg_locks and don't always show up in conflict statistics. The process waiting for a pin to be released simply hangs in the wait_event = 'BufferPin' state, without triggering alerts. This hits especially hard on replicas:

• The startup process when replaying WAL records (for example, HOT cleanup) needs exclusive access to the buffer—that is, it must wait for pincount = 0.
• If a long-running query (for example, Seq Scan) is executing on the replica, it holds the pin on the block.
• Startup completely stalls until the block is released.
• Since WAL replay happens in a single thread, a delay on one block causes lag to build up across the entire replica.

A similar situation occurs with autovacuum on the primary and replicas during page freezing (freeze). Autovacuum skips pinned blocks in normal mode, but in aggressive mode (during freeze)—it waits. If there are many such blocks, the workers hang, a cleanup backlog accumulates, and this can lead to transaction ID wraparound.

Hidden symptoms:

• Replica lag growth without any visible load.
• Log messages like: automatic aggressive vacuum of table... or skipped due to pins.
• No waits visible in pg_locks, but BufferPin present in pg_stat_activity.wait_event.

How to Reproduce and Diagnose the Problem

A simple example is enough to understand the mechanism. Create a table and pin a row using a cursor in a transaction:

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

Now check the buffer state via 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';

The result will show pinning_backends = 1—our transaction is holding the pin.

Run an aggressive VACUUM in a second session:

vacuum (freeze, verbose, skip_locked off) t;

VACUUM will hang. A repeat query to pg_buffercache will show pinning_backends = 2. Check the wait:

SELECT query, wait_event FROM pg_stat_activity WHERE query ILIKE 'vacuum%';
-- wait_event = 'BufferPin'

If you open a few more sessions with similar cursors, pinning_backends will keep growing. VACUUM won't proceed until all transactions complete.

Practical Monitoring and Prevention Methods

Standard tools don't show the problem in real time. But you can build effective monitoring based on pg_buffercache and pg_stat_activity.

Basic query for detecting hot blocks:

SELECT 
  n.nspname AS schema_name,
  c.relname AS table_name,
  b.bufferid,
  b.pinning_backends,
  b.usagecount,
  b.isdirty,
  c.relpages
FROM pg_buffercache b 
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
  AND b.pinning_backends > 1
ORDER BY b.pinning_backends DESC
LIMIT 10;

What to check regularly:

• Tables with high pinning_backends—prime candidates for optimization.
• Presence of long-running transactions (pg_stat_activity.xact_start).
• Waiting processes with wait_event = 'BufferPin'.

Ways to reduce conflicts:

• Optimizing fillfactor—if the table has few rows but gets frequent updates, lowering fillfactor spreads rows across multiple blocks, reducing contention for a single buffer.
• Dedicated replica—a replica without user queries avoids pin conflicts entirely, ensuring stable synchronization. This is how they do it, for example, at OpenAI.
• Tuning max_standby_streaming_delay—increasing the value gives queries on the replica more time to run, but raises the risk of lag.
• Limiting transaction duration—implement timeouts and automatic termination of hanging sessions.
• Preemptive cleanup before freeze—run manual VACUUM FREEZE during periods of low load.

Extensions and Alternatives for In-Depth Analysis

Alexandra Bondar's talk mentioned a custom extension for monitoring hot blocks. It uses callback hooks and alternating writes to memory to minimize overhead. However, such solutions are rarely accepted by the community—they require core modifications and address very niche issues.

Alternatives without core changes:

• pg_wait_sampling—collects wait statistics, including BufferPin, via sampling.
• pg_buffercache + cron—periodic data collection and comparison against previous states.
• Autovacuum logging—set log_autovacuum_min_duration = 0 to capture all cases of skipped blocks.

Example logging setup:

ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();

After this, logs will include entries like:

LOG:  skipping block N of relation "public.t" --- block is pinned

This makes it possible to identify problematic tables even without active monitoring.

Key Takeaways

• BufferPin conflicts are an invisible cause of replica lag growth and autovacuum hangs.
• Diagnosis is only possible via pg_buffercache and pg_stat_activity.wait_event.
• Fillfactor and dedicated replicas are the most effective ways to reduce contention.
• Autovacuum logging with a zero threshold helps spot problematic tables in production.
• Standard metrics (pg_stat_database_conflicts) miss most cases—custom monitoring is essential.

— Editorial Team