PostgreSQL Buffer Pin Waits: The Idle Cursor That Froze Autovacuum

The alarm said the orders table had crossed thirty percent estimated bloat, which was strange, because autovacuum had been "on it" since 09:00. It was now 14:00. pg_stat_activity told the real story in two rows: an autovacuum worker on orders with wait_event_type Pin and wait_event BufferPin, state active, five hours old; and an analytics session, state idle in transaction, six hours old, running a BI extract through a cursor. We terminated the analytics session. The vacuum finished in four minutes. No deadlock detector had fired, no lock_timeout had fired, pg_locks showed nothing between the two sessions — because a buffer pin is none of those things, and the tooling that watches locks is structurally blind to it.

This is the wait event that took me the longest to build a mental model for, because everything you know about lock debugging quietly does not apply. Here is what a buffer pin actually is, why a lazy vacuum ends up waiting for one, how to find the pinner when the catalogs will not tell you, and the two settings that keep it from recurring.

What is a buffer pin, and why don’t your lock tools see it?

A buffer pin is a refcount on a shared buffer, taken by any backend that is physically touching the page — reading a tuple, following an index pointer — and released as soon as the touch is done. It is lighter than a lock by design: no deadlock detection, no queue, no timeout, no entry in pg_locks, just an atomic count in the buffer header that says "N backends are looking at this page right now." That design is why a pinned page cannot be evicted, and it is also why the standard incident reflexes fail: lock_timeout does not govern pins, statement_timeout on the waiter does not help (the autovacuum worker is not executing a statement you configured), and SELECT * FROM pg_locks shows a clean, innocent database while a vacuum rots. The visibility you do get is pg_stat_activity’s wait columns: wait_event_type Pin with wait_event BufferPin means "this backend is waiting for some buffer’s pin count to drain," and that row is often the only announcement you will ever get. The full taxonomy of wait events and how to read them is in the wait events debugging notes; BufferPin deserves its own chapter because it is the one that does not behave like the others.

Why was autovacuum waiting on a pin at all?

Because removing dead tuples from a page requires the page to be completely untouched: before a lazy vacuum can clean a heap page, it takes what is called a cleanup lock, which waits until the pin count drops to exactly one — vacuum’s own pin. Every other pin on that page must drain first, and a pin normally lives for microseconds. The exception is a cursor. A portal with an open cursor holds its current heap page pinned between fetches, and it holds it for as long as the transaction sits idle — in our case, six hours of a BI tool that had fetched a batch, gone quiet, and left the transaction and the cursor open. The page it happened to sit on was a hot region of orders that vacuum needed to clean, so the worker waited, and waited, and since autovacuum’s cost-based delay already stretches a big-table vacuum across hours, the wait blended into normal slowness until the bloat alarm made it visible. One idle session, one pinned page, one frozen vacuum: the failure needs no bad luck beyond overlap.

The query that finds this shape is the one I now run before blaming autovacuum itself for a slow vacuum — list the waiting workers, then list the sessions whose transactions are old enough to be suspects:

SELECT pid,
       backend_type,
       wait_event_type,
       wait_event,
       state,
       now() - xact_start AS xact_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event = 'BufferPin'
   OR backend_type = 'autovacuum worker'
   OR (state = 'idle in transaction' AND xact_start < now() - interval '15 minutes')
ORDER BY xact_start NULLS LAST;

The join between "who waits" and "who pins" is circumstantial — PostgreSQL does not record which buffer a waiter wants or which backend holds which pin — but in practice an old idle-in-transaction session whose workload touches the same table is the pinner almost every time, and terminating it is both the diagnosis and the cure. The broader menagerie of idle-in-transaction damage is covered in the idle-in-transaction notes; the cursor variant is the same disease with a pinned page as the symptom.

How do you confirm the story instead of guessing?

You watch the vacuum move after the kill, and you inspect the table’s buffer footprint while it happens. The confirmation loop after terminating our analytics session was immediate: the worker’s wait_event cleared, pg_stat_progress_vacuum showed heap_blks_scanned climbing, and four minutes later dead tuples on orders went to zero. For the table-side view, pg_buffercache will show you exactly how much of the table currently lives in shared buffers and how dirty it is — useful for gauging how hot the contested region is, and for confirming that the "one page" in question sits in the middle of the table’s cached working set:

CREATE EXTENSION IF NOT EXISTS pg_buffercache;

SELECT count(*)                        AS buffers,
       count(*) FILTER (WHERE isdirty) AS dirty,
       max(usagecount)                 AS hottest_usagecount
FROM pg_buffercache b
JOIN pg_class c ON c.oid = pg_filenode_relation(b.reltablespace, b.relfilenode)
WHERE c.relname = 'orders';

Attribution tooling beyond this is thin, and it is worth being honest about that in the runbook: there is no pg_bufferpins view, the pin count lives in buffer headers you can see only in aggregate, and the practical diagnosis is the correlation above — BufferPin waiter plus old idle transaction plus table overlap. If your memory budgeting from the memory tuning guide keeps the hot tables resident, expect the contested page to be cached, which is exactly why the cursor’s pin mattered: the page was in constant use, so nobody’s natural activity was going to flush the pin holder out. Only ending the transaction releases it.

How do you keep the incident from repeating?

You bound idle transactions globally, and you fix the cursor pattern that created the pin. The global bound is idle_in_transaction_session_timeout — we set fifteen minutes for the analytics role and an hour cluster-wide — which converts any future repeat from a silent five-hour stall into a logged, terminated session with an application error somebody actually sees. The pattern fix is on the client side: BI extracts should either fetch the cursor to completion and commit, or use a WITH HOLD cursor and materialize, but never open a cursor and idle; we also gave the extract role statement_timeout so a stuck export fails loudly instead of pinning indefinitely. On the vacuum side, the lesson from the timing is that autovacuum’s throttling made the stall invisible — a worker that normally finishes orders in forty minutes had five hours of runway before anyone asked questions. Progress monitoring closes that: an alert on a vacuum whose heap_blks_scanned has not advanced in ten minutes, cross-referenced with any BufferPin waiter, pages on the condition itself rather than on the bloat it eventually causes. Pins will always exist — they are how PostgreSQL reads anything — the discipline is making sure nothing holds one across a coffee break.

Watching pin waits with MonPG

BufferPin is a rare wait right up until it is your whole afternoon, and the session that causes it looks idle and harmless in every dashboard that only graphs active queries. MonPG monitors PostgreSQL in production today, and its PostgreSQL monitoring tracks session states, wait events, and autovacuum progress on one timeline, so the idle-in-transaction session and the stalled vacuum worker share a screen and an alert instead of meeting for the first time in a bloat incident. Bound the idle transactions, watch the vacuums that stop moving, and the next BufferPin is a footnote.

Related documentation

• PostgreSQL: routine vacuuming