shared_buffers and the OS page cache · PostgreSQL

PostgreSQL does not manage its own I/O the way some databases do. It reads through the filesystem, which means every page it reads is cached twice: once in shared_buffers, and once in the operating system’s page cache.

That design decision is the reason shared_buffers sizing advice is different from the “give the database 80% of RAM” rule that applies elsewhere.

The two levels

query  ->  shared_buffers  ->  OS page cache  ->  storage

A page not in shared_buffers is a read in EXPLAIN (BUFFERS) output — but that read may be served entirely from the page cache, at memory speed, without touching storage at all.

This is why buffer “hit ratios” are less informative than they look. A 90% hit ratio on a cluster whose misses are all served by the page cache is fine. A 99% hit ratio on a cluster whose 1% of misses are cold storage reads may not be.

track_io_timing distinguishes them, at the clock cost discussed in lesson X-02:

ALTER SYSTEM SET track_io_timing = on;

SELECT relname,
       heap_blks_read, heap_blks_hit,
       round(100.0 * heap_blks_hit
             / nullif(heap_blks_hit + heap_blks_read, 0), 2) AS hit_pct
  FROM pg_statio_user_tables
 ORDER BY heap_blks_read DESC
 LIMIT 10;

What the pool is actually holding

Read-only / Safepg_buffercache after a large sequential scan— Captured on 18.6. shared_buffers is 128 MB, so 16,384 buffers.

$ psql -U postgres -c "SELECT c.relname, count(*) AS buffers, pg_size_pretty(count(*)*8192::bigint) AS size, round(100.0*count(*)/(SELECT setting::numeric FROM pg_settings WHERE name='shared_buffers'),1) AS pct_of_pool, round(avg(b.usagecount),2) AS avg_usage FROM pg_buffercache b JOIN pg_class c ON c.relfilenode=b.relfilenode GROUP BY c.relname ORDER BY count(*) DESC LIMIT 8"

         relname          | buffers |  size  | pct_of_pool | avg_usage
--------------------------+---------+--------+-------------+-----------
ordersx                  |   16093 | 126 MB |        98.2 |      0.23
pg_operator              |      11 | 88 kB  |         0.1 |      2.91
pg_amop                  |       7 | 56 kB  |         0.0 |      3.86
pg_amproc_fam_proc_index |       6 | 48 kB  |         0.0 |      2.67
pg_index                 |       6 | 48 kB  |         0.0 |      4.17
...

Read-only / Safethe pool as a whole— Captured on 18.6 at the same moment.

$ psql -U postgres -c "SELECT count(*) FILTER (WHERE relfilenode IS NOT NULL) AS used, count(*) FILTER (WHERE relfilenode IS NULL) AS free, count(*) FILTER (WHERE isdirty) AS dirty, count(*) AS total FROM pg_buffercache"

  used | free | dirty | total
16384 |    0 |    31 | 16384

Read the avg_usage column against the pct_of_pool column, because together they tell a story a size figure alone cannot.

ordersx holds 98.2% of the pool at an average usage count of 0.23. A usage count near zero means those pages were read once and are first in line for eviction. Meanwhile the catalogue entries — a few kilobytes each — show usage counts of 3 to 4, the maximum. Those are genuinely hot.

This is a large sequential scan having evicted the working set. The scan’s own pages will themselves be evicted shortly, and everything that was warm is now cold. Nothing is wrong, exactly, but a workload that does this repeatedly gets no benefit from its buffer pool at all.

What PostgreSQL already does about this, and when it is not enough

PostgreSQL uses a ring buffer for large sequential scans: a scan whose estimated size exceeds a quarter of shared_buffers is confined to a small circular set of buffers rather than being allowed to evict the whole pool. The same applies to VACUUM, bounded by vacuum_buffer_usage_limit, which defaults to 2 MB.

So the eviction above was bounded — it happened because the scan was large relative to a small pool on a test cluster, not because the protection is absent.

Where it is still not enough:

Many medium scans. Each is individually under the threshold and collectively they churn the pool.
Index scans over a large fraction of a table. These are not sequential scans and do not use a ring buffer.
A pool too small for the working set, where everything competes.

pg_prewarm can load a specific relation deliberately, which is useful after a restart when a known-hot table would otherwise be read in slowly under production load:

CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT pg_prewarm('orders');
SELECT pg_prewarm('orders_pkey');

It is a warm-up tool, not a pinning mechanism: the pages are subject to eviction like any others the moment the workload starts.

Sizing

The conventional starting point is 25% of system memory, and it is a starting point rather than an answer.

The reasoning behind not going much higher, on a system that has the memory:

Double buffering. Every page in shared_buffers is probably also in the page cache. Memory given to shared_buffers is memory taken from the page cache, and past a point you are simply moving the same pages between two caches.

Checkpoint cost. A larger pool holds more dirty pages, so each checkpoint writes more at once. Part XII covers the smoothing settings that address this.

The OS is good at this. The page cache uses whatever is free, adapts instantly, and needs no restart to resize.

When a larger pool genuinely helps:

The working set fits. If the hot data is 40 GB and the server has 128 GB, a 48 GB pool keeps it all resident with no page cache round trip.
Write-heavy workloads, where a page updated many times before being written benefits from staying in the pool.
Cloud storage with real latency, where a page cache miss is expensive enough to justify avoiding it.

Measure rather than assume:

-- how much of each table is currently resident
SELECT c.relname,
       pg_size_pretty(count(*) * 8192::bigint)              AS cached,
       pg_size_pretty(pg_relation_size(c.oid))              AS total,
       round(100.0 * count(*) * 8192 / nullif(pg_relation_size(c.oid), 0), 1) AS pct_cached
  FROM pg_buffercache b
  JOIN pg_class c ON c.relfilenode = b.relfilenode
 GROUP BY c.oid, c.relname
 ORDER BY count(*) DESC
 LIMIT 20;

A hot table at 100% cached needs no more pool. One at 30% cached, being read constantly, is the argument for a larger one.

pg_buffercache scans the whole pool

It takes a lock on the buffer mapping and examines every buffer. On a 64 GB pool that is 8 million entries, and it is not something to put on a dashboard refreshing every ten seconds.

PostgreSQL provides a cheaper summary view for routine use:

SELECT * FROM pg_buffercache_summary();

which returns the used, unused, dirty and pinned counts plus the average usage count without producing a row per buffer.

Use the summary for monitoring and the full view for investigation.

Clock sweep, usage counts, and why the numbers stay small

PostgreSQL does not use LRU. It uses a clock sweep, and the usagecount column is what makes it work.

Each buffer has a counter, capped at 5. Touching a buffer increments it. When a buffer is needed, the sweep advances through the pool: a buffer with a non-zero count has it decremented and is skipped; the first buffer found at zero is evicted.

Three consequences.

Eviction cost is bounded but not constant. A pool where everything has a high count requires several sweeps to find a victim, and that work is done by whichever backend needed the buffer. This is why an undersized pool under pressure shows up as CPU in the backends rather than as I/O.

A usage count near zero is diagnostic, not alarming. It means the page was read once and not touched again — exactly what a sequential scan produces, and exactly the capture at the top of this lesson. Pages with counts of 4 and 5 are the working set.

The distribution is more informative than the mean, and there is a purpose-built function for it that does not materialise a row per buffer:

SELECT * FROM pg_buffercache_usage_counts();

Read-only / Safethe usage-count distribution of a warm, busy pool— EXECUTION VALIDATED on 18.6 on a replication primary under pgbench load. shared_buffers is 128 MB, so 16,384 buffers.

$ CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT * FROM pg_buffercache_usage_counts();

 usage_count | buffers | dirty | pinned
-------------+---------+-------+--------
         0 |    2043 |     0 |      0
         1 |    3915 |    51 |      0
         2 |    3285 |     2 |      0
         3 |    2481 |     2 |      0
         4 |    3118 |     4 |      0
         5 |    1542 |    46 |      0

The counts sum to 16,384: the whole pool, in one row per usage count, without materialising a row per buffer. This is a healthy shape — mass spread across the middle, meaning most buffers have been touched more than once and the pool is doing real work.

Contrast it with the sequential-scan capture earlier in this lesson, where one table occupied 98.2% of the pool at an average usage count of 0.23. That is the opposite shape: almost everything read once and never touched again.

A pool concentrated at 0 and 1 is being churned and is giving you little. A pool with substantial mass at 4 and 5 is holding a genuine working set. A pool where nearly everything is at 5 is too small — every page is hot and they are evicting each other.

That last shape is the honest signal that shared_buffers should be larger, and it is a far better argument than any percentage of RAM.

What to take from this

Two caches: shared_buffers and the OS page cache. A shared buffer miss is often not a disk read.
Hit ratio alone is weakly informative. track_io_timing separates cache misses from storage reads.
Measured: one table at 98.2% of the pool with avg_usage 0.23 — a scan that evicted the working set.
Ring buffers bound large sequential scans and vacuum already.
25% of RAM is a starting point. pg_buffercache showing a hot table partly cached is the argument for more.
The usage count distribution is the honest sizing signal. Everything at 5 means the pool is too small.

Cross-course references

Linux for Production Sysadmins — Part XL (Memory Performance) covers the page cache from the operating system’s side, including why a large cache is not a leak.
Docker & Containers — Part XV (Resource controls) covers how a memory limit accounts for page cache, which changes the sizing arithmetic in a container.
Observability for Production Sysadmins — Part LIX (Database observability) covers the cache hit ratio and why it is a weaker signal than it looks.

The two levels

What the pool is actually holding

Sizing

What to take from this

Cross-course references

Quiz