Summarize the Buffer Cache with pg_buffercache_summary

Summarize the Buffer Cache with pg_buffercache_summary

PostgreSQL 16 added a shortcut to a question DBAs have always had to answer the expensive way. Before that release, checking how full the shared buffer cache was — how many buffers were dirty, how many pinned, how hot the clock-sweep usage counts were running — meant scanning pg_buffercache, one row per 8KB buffer, and aggregating the result yourself. pg_buffercache_summary() collapses that scan into a single row: how many buffers are used, unused, dirty, and pinned, plus the average clock-sweep usage count across the whole cache — computed inside the function instead of pulled buffer-by-buffer into a client and summed by hand.

Purpose and Overview

The pg_buffercache contrib module exposes what is actually sitting in PostgreSQL's shared buffer cache right now — not a sampled estimate, not a log-derived approximation, but the live state of every buffer slot. It ships as an optional extension rather than a core feature, so it has to be enabled per database with CREATE EXTENSION pg_buffercache; before any of its views or functions are queryable.

That's a meaningfully different kind of data than most of what a DBA reads out of PostgreSQL's monitoring surface. The cumulative statistics system — pg_stat_user_tables, pg_stat_database, and the rest of the pg_stat_* family — accumulates counters over time and flushes them to shared memory at intervals, so what you read is always slightly behind actual activity. pg_buffercache sits outside that system entirely. It reads the buffer manager's own bookkeeping directly, which is why its module documentation describes it as a way of "examining what's happening in the shared buffer cache in real time" rather than a history of what happened.

The tradeoff is cost. The full pg_buffercache view returns one row per buffer — on a server with shared_buffers set to several gigabytes, that's hundreds of thousands of rows to scan and aggregate just to answer a question like "how full is the cache right now?" pg_buffercache_summary() answers that exact question in one row: buffers_used, buffers_unused, buffers_dirty, buffers_pinned, and usagecount_avg, computed server-side. Per PostgreSQL's own module reference, it provides "similar and more detailed information" to the full view but is "significantly cheaper" — the right tool when a health check, not a per-relation breakdown, is what's needed. Access to it is restricted by default to superusers and members of the built-in pg_monitor role; GRANT can extend that to a dedicated monitoring account.

Sample Code

1SELECT
2    buffers_used,
3    buffers_unused,
4    buffers_dirty,
5    buffers_pinned,
6    round(usagecount_avg::numeric, 2) AS usagecount_avg
7FROM
8    pg_buffercache_summary();

Sized and percentage variant, for a report-ready read of the same row:

 1SELECT
 2    buffers_used,
 3    buffers_unused,
 4    pg_size_pretty(buffers_used::bigint * 8192)                AS used_size,
 5    pg_size_pretty(buffers_dirty::bigint * 8192)                AS dirty_size,
 6    round(100.0 * buffers_dirty  / NULLIF(buffers_used, 0), 2)  AS pct_dirty,
 7    round(100.0 * buffers_pinned / NULLIF(buffers_used, 0), 2)  AS pct_pinned,
 8    round(usagecount_avg::numeric, 2)                            AS usagecount_avg
 9FROM
10    pg_buffercache_summary();

Notes: pg_buffercache_summary() requires PostgreSQL 16 or later — on earlier releases the function simply doesn't exist, even with the extension installed, and the fallback is aggregating the full pg_buffercache view yourself. 8192 is PostgreSQL's standard 8KB block size, used here only to convert a buffer count into bytes for pg_size_pretty(); a cluster built with a non-default block size needs that multiplier adjusted to match.

Code Breakdown

The base query reads the five summary columns as-is; the sized variant adds derived byte counts and percentages so the output reads like a status report instead of a raw column dump.

buffers_used, buffers_unused, and buffers_dirty

buffers_used and buffers_unused split shared_buffers into buffers currently holding data and buffers that are still empty — on a server that hasn't been running long enough to fill its cache, buffers_unused can be a large share of the total, which is normal and not a sign of anything wrong. buffers_dirty counts buffers holding a modified page that hasn't yet been written back to disk by the background writer or a checkpoint. A buffers_dirty count that keeps climbing relative to buffers_used is the direct, real-time version of the same signal a DBA usually chases through checkpoint logs after the fact.

usagecount_avg and the Clock-Sweep Algorithm

PostgreSQL decides which buffer to evict for new data using a clock-sweep algorithm: every buffer carries a usagecount that increments (up to a cap) each time it's accessed and decrements as the sweep passes over it looking for a victim. The full pg_buffercache view exposes this per buffer as usagecount smallint, and PostgreSQL's own pg_buffercache_usage_counts() sample output shows the distribution running from 0 through 5. usagecount_avg is that same clock-sweep signal collapsed into one number — averaged only across buffers_used (empty slots don't have a usage count to average in). A low average means buffers are being evicted almost as soon as they're touched, the profile of a cache that's too small for its working set; a high average clustered near the cap means most of what's cached is being hit repeatedly.

Deriving Sizes and Percentages from Raw Buffer Counts

pg_size_pretty(buffers_used::bigint * 8192) turns a buffer count into a human-readable byte size — casting to bigint first avoids integer overflow on a very large cache before the multiplication runs. NULLIF(buffers_used, 0) guards the percentage expressions against a division-by-zero error on the edge case where buffers_used is genuinely zero, such as immediately after server start before any query has populated the cache.

Key Buffer Cache Concepts

shared_buffers and the Buffer Pool

shared_buffers is the memory area pg_buffercache_summary() is reporting on — PostgreSQL's dedicated in-memory pool for cached table and index pages, sized once at startup since it's a static parameter that requires a restart to change. The community default is a conservative 128 MB; AWS's guidance for Amazon RDS for PostgreSQL derives its default from DBInstanceClassMemory/32768 and recommends allocating roughly 30–35% of total memory for community PostgreSQL and RDS, since both still lean on the OS page cache underneath. Amazon Aurora PostgreSQL uses a different formula and a notably higher allocation, because it doesn't depend on OS-level caching the way self-managed PostgreSQL and RDS do.

Dirty Pages and the Background Writer

A dirty page, per that same AWS guidance, is any page in shared_buffers that a write operation has modified but that hasn't yet been flushed to permanent storage. The background writer process handles this incrementally, flushing dirty pages when the pool of clean buffers is running low, and checkpoints periodically force every outstanding dirty page to disk to establish a crash-recovery restore point. buffers_dirty from the summary function is a direct read of how much work is currently queued for that process.

Pinned Buffers and Why They Resist Eviction

A pinned buffer is one a backend process is actively using right now — pinning_backends in the full view tracks how many. Pinned buffers can't be evicted or reused; PostgreSQL's own documentation notes that its buffer-eviction testing function returns false specifically "if it couldn't be evicted because it was pinned." That eviction function, pg_buffercache_evict(), is restricted to superusers and intended for developer testing, not production remediation — buffers_pinned in the summary output is a number to observe and factor into diagnosis, not one to try to force down directly.

Why the Summary Function Skips Buffer Manager Locks

Neither pg_buffercache_summary() nor the full view acquires buffer manager locks to build its result, which is what keeps either one cheap to run against a live server. pganalyze notes that since PostgreSQL 10 reduced the locking pg_buffercache needs to run, querying it has been "generally safe... without impacting the regular workload" in production. The cost of that design is that the result can show minor inaccuracies under concurrent buffer activity — acceptable for a health check, not something to treat as a transactionally consistent snapshot.

Practical Applications

Because it's cheap enough to run repeatedly without a second thought, pg_buffercache_summary() fits naturally into both reactive triage and scheduled monitoring.

First Response During a Slow-Query Investigation

When a query that used to be fast suddenly isn't, checking whether the buffer cache's overall shape has shifted is a faster first move than reaching straight for the heavier per-relation query. pganalyze built its buffer cache tracking feature specifically to "pinpoint whether a slow query occurred because the cache contents changed" — an unrelated workload pushing a hot table out of cache is a common, otherwise invisible cause of a sudden regression. If the summary row shows buffers_dirty or the used/unused ratio has moved sharply, that's the cue to drop down to the full pg_buffercache view, joined to pg_class, to see exactly which tables are occupying the cache.

Sizing shared_buffers Before a Configuration Change

Before increasing shared_buffers — a change that requires a restart to apply — checking buffers_used against buffers_unused over a representative period shows whether the current pool is actually running full or has headroom. AWS's worked example for sizing shared_buffers walks through exactly this kind of before/after comparison; pairing it with pg_buffercache_summary()'s usagecount_avg adds a second signal — a cache that's full but has a low average usage count is churning rather than genuinely undersized.

Tracking Cache Behavior Over Time

A single snapshot only shows the cache's current state, not its trend. pganalyze's buffer cache statistics feature runs this query on a schedule — every 10 minutes — and rolls the results up per table and per index, so a table that's "always in memory" is visually distinguishable from one that's "only in memory while its query workload requires it." The same pattern is reproducible with any scheduler by inserting the summary row into a logging table at a fixed interval.

Catching Dirty-Page Buildup Before a Checkpoint Storm

A pct_dirty figure that's trending upward across successive checks, rather than holding steady, is the earliest available signal that the background writer is falling behind write volume — well before it shows up as checkpoint-related I/O spikes. Catching that trend from pg_buffercache_summary() gives more lead time than waiting for it to surface in checkpoint timing logs.

Version Compatibility

The pg_buffercache extension and its underlying view predate the summary function by a wide margin, but its production-safety profile changed materially along the way. In PostgreSQL 10, the module was changed to run with fewer buffer manager locks specifically to make it "less disruptive when run on production systems" — the change pganalyze's own recommendation to query it in production leans on.

pg_buffercache_summary() and its companion aggregate function pg_buffercache_usage_counts() — which returns the buffer count broken down by each individual usage-count value rather than a single average — were both added in PostgreSQL 16, released 2023-09-14. Neither exists on PostgreSQL 15 or earlier; on those versions, the only path to the same information is scanning pg_buffercache directly and aggregating with COUNT(), SUM(), and AVG() in the query itself, exactly the cost the newer functions were added to avoid.

PostgreSQL 17, released 2024-09-26, extended the module further with pg_buffercache_evict(), pg_buffercache_evict_relation(), and pg_buffercache_evict_all() — functions that let a superuser force specific buffers out of the pool. These are documented as intended for developer testing, restricted to superusers, and unrelated to reading the summary data itself; a monitoring role granted access to pg_buffercache_summary() via pg_monitor membership does not gain eviction rights alongside it.

Best Practices

• Run pg_buffercache_summary() before the full view, not instead of it — it tells you whether a deeper per-relation scan is worth the cost; it doesn't replace that scan when the answer is yes.
• Grant access through pg_monitor, not superuser — a dedicated monitoring role with pg_monitor membership covers pg_buffercache_summary() and pg_buffercache_usage_counts() without handing out a broader privilege than the task needs.
• Log the summary row on a schedule, not only during incidents — a single reading has no baseline to compare against; a trend of pct_dirty or usagecount_avg over days is what actually flags a developing problem.
• Treat buffers_pinned as a signal, not a target — it reflects real concurrent activity; don't reach for pg_buffercache_evict() in production to reduce it.
• Re-check shared_buffers sizing after any major workload change — a used/unused ratio that looked fine at launch can drift once table sizes and query patterns change months later.
• Confirm the PostgreSQL major version before scripting against these functions — pg_buffercache_summary() and pg_buffercache_usage_counts() fail outright on PostgreSQL 15 and earlier; a monitoring script meant to run across a mixed-version fleet needs a version check or a pg_buffercache-view fallback query.

References

• PostgreSQL Documentation — pg_buffercache — column and function reference for the view, pg_buffercache_summary(), pg_buffercache_usage_counts(), and the eviction functions.
• PostgreSQL Documentation — The Cumulative Statistics System — the pg_stat_* monitoring views, useful as the contrast case for why pg_buffercache reads live state instead of accumulated counters.
• pganalyze — Tracking Postgres Buffer Cache Statistics — how buffer cache tracking is used in practice to correlate slow queries with cache content changes over time.
• AWS Database Blog — Determining the Optimal Value for shared_buffers Using pg_buffercache — sizing guidance and defaults across community PostgreSQL, Amazon RDS, and Amazon Aurora PostgreSQL.