SLRU, the small caches beside the pool

An SLRU is one of a handful of small fixed-size caches PostgreSQL keeps for data that is not table data: transaction commit status, multixact offsets and members, subtransaction parents, commit timestamps, notify queues and predicate locks. Each has its own pages and its own replacement policy, and none is part of shared buffers. They are sized in pages, historically not configurable, and small enough that a workload can miss in one constantly while the buffer pool reports a perfect hit ratio.

Small, invisible, and occasionally the whole problem

The reason these matter is proportion. Shared buffers is measured in gigabytes; an SLRU cache was measured in tens of pages for most of PostgreSQL’s history. Any workload whose transaction status lookups scatter across a wide range of transaction ids, which is what a long-running transaction plus heavy short traffic produces, can exceed one of them and start reading from disk on a code path that was designed assuming a hit.

The symptom does not look like I/O. It shows as a lightweight lock wait event named after the cache, arriving on queries that have no obvious reason to be slow, with unremarkable buffer statistics and nothing in the plan. Operators who have not met this look at the query, the index and the table, and find nothing wrong with any of them, because nothing is.

Two workloads reach it most often. Heavy subtransaction use, typically from a framework that wraps each statement in a savepoint, overflows the subtransaction cache. Heavy row locking through foreign keys pushes the multixact caches, which is the same workload that eventually raises multixact age. From PostgreSQL 17 each cache has its own size setting, which turns this from a rebuild-the-server problem into a restart.

Listing the caches and what they are doing

One view reports every cache, and the names are worth reading closely.

SELECT name, blks_zeroed, blks_hit, blks_read, blks_written
FROM pg_stat_slru ORDER BY name;

      name       | blks_zeroed | blks_hit | blks_read | blks_written 
-----------------+-------------+----------+-----------+--------------
 CommitTs        |           0 |        0 |         0 |            0
 MultiXactMember |           1 |        1 |         0 |            0
 MultiXactOffset |           0 |        1 |         0 |            0
 Notify          |           0 |        0 |         0 |            0
 other           |           0 |        0 |         0 |            0
 Serial          |           0 |        0 |         0 |            0
 Subtrans        |           0 |        1 |         0 |            0
 Xact            |           0 |       71 |         0 |            0
(8 rows)

That is 14.24. The identical query on 18.6 returns eight rows again, and seven of the names are different: commit_timestamp, multixact_member, multixact_offset, notify, serializable, subtransaction and transaction, with only other unchanged. PostgreSQL 17 renamed them, and an alert matching the old strings stops matching after that upgrade without erroring, which is the quietest kind of breakage.

The number to watch is blks_read climbing on a cache whose blks_hit is large, since that is a working set larger than the cache. blks_zeroed counts pages initialised rather than read, and it grows normally as the transaction counter advances.

What to do when one of them misses

The two workloads above are the ones worth ruling in or out first, and the long-transaction half of it is bound up with how far the transaction id counter has been allowed to spread, which transaction id wraparound covers. If the wait event pointing here came from a sampling dashboard, the wait event types explains why a lightweight lock class means the answer is internal rather than in another session’s query.