Debugging Low Cache Hit Ratio in PostgreSQL

Reading a page from RAM costs roughly 100 nanoseconds. Reading the same page from an SSD costs tens to a few hundred microseconds, and from a spinning disk closer to 10 milliseconds - up to 100,000 times slower (Latency Numbers Every Programmer Should Know). An L1 CPU cache reference is around 0.5 ns, well under a nanosecond. The cache hit ratio is the fraction of block reads PostgreSQL served from its in-memory buffer cache instead of going to disk. A low ratio means queries are paying the disk penalty on reads that could have come from memory, which shows up as rising latency and disk I/O.

What the Cache Hit Ratio Actually Measures

PostgreSQL caches table and index pages in a shared memory region called the buffer cache, sized by shared_buffers (default 128MB, common guidance ~25% of RAM on a dedicated server). When a backend needs a page, it first looks in the buffer cache. A hit is served from RAM; a miss is a read request that goes to the operating system, which may serve it from the OS page cache or fall through to physical disk.

The cache hit ratio is blks_hit / (blks_hit + blks_read). The important caveat: pg_stat_database.blks_hit counts only hits in PostgreSQL's own buffer cache, not the OS file system cache (PostgreSQL monitoring docs). Because most systems double-buffer - the same page often sits in both shared_buffers and the kernel page cache - a "low" PostgreSQL ratio does not always mean you are hitting disk. A miss counted here may still be a fast OS-cache read. Treat the ratio as a signal to investigate, not a verdict.

effective_cache_size (default 4GB) is a separate, related setting. It allocates no memory; it is a planner hint describing how much total cache (shared buffers plus OS cache) is available, which biases the planner toward index scans when set higher (docs).

How to Measure the Cache Hit Ratio

For a database-wide ratio, query pg_stat_database:

-- Overall buffer cache hit ratio for the current database
SELECT
  datname,
  blks_hit,
  blks_read,
  round(blks_hit::numeric / nullif(blks_hit + blks_read, 0), 4) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

For a per-table view, including separate heap and index figures, use pg_statio_user_tables:

-- Per-table heap and index hit ratios, worst hit ratio first
SELECT
  relname,
  heap_blks_read,
  heap_blks_hit,
  round(heap_blks_hit::numeric
        / nullif(heap_blks_hit + heap_blks_read, 0), 4) AS heap_hit_ratio,
  round(idx_blks_hit::numeric
        / nullif(idx_blks_hit + idx_blks_read, 0), 4)  AS idx_hit_ratio
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC;

These counters are cumulative since the last pg_stat_reset() or server start, so a cold-start figure looks bad until the cache warms. Reset and re-sample over a representative window to get a meaningful number.

Common Causes and Fixes

A low ratio is a symptom, not a cause. The table maps the usual root causes to the fix that addresses each.

The highest-leverage check is EXPLAIN (ANALYZE, BUFFERS) on your slow queries (explain analyze guide). The Buffers: line reports shared hit versus shared read per node, pinpointing which operation forces the disk reads. A sequential scan over a large table reads every block and evicts useful pages, so converting it to an index scan often raises the ratio more than adding memory.

Inspecting the Cache with pg_buffercache

The pg_buffercache extension exposes the live contents of the buffer cache, letting you see which relations occupy it. Install it once per database:

CREATE EXTENSION pg_buffercache;

The view exposes one row per buffer with columns including bufferid, relfilenode, reldatabase, relblocknumber, isdirty, usagecount, and pinning_backends. To find which relations consume the most buffers, join to pg_class. Use pg_relation_filenode(c.oid) rather than c.relfilenode directly - they differ for shared catalogs and after operations like TRUNCATE that rewrite the file - and restrict the join to the current database or shared objects (reldatabase of 0):

-- Top 10 relations by number of buffers held in shared_buffers
SELECT n.nspname, c.relname, count(*) AS buffers
FROM pg_buffercache b
JOIN pg_class c
  ON b.relfilenode = pg_relation_filenode(c.oid)
 AND b.reldatabase IN (0, (SELECT oid FROM pg_database
                           WHERE datname = current_database()))
JOIN pg_namespace n ON n.oid = c.relnamespace
GROUP BY n.nspname, c.relname
ORDER BY 3 DESC
LIMIT 10;

To see how much of each relation is cached and what share of shared_buffers it occupies (8192-byte blocks):

-- Cached size per relation and percentage of the relation in cache
SELECT
  c.relname,
  pg_size_pretty(count(*) * 8192) AS buffered,
  round(100.0 * count(*) /
        (SELECT setting FROM pg_settings
         WHERE name = 'shared_buffers')::integer, 1) AS buffers_percent,
  round(100.0 * count(*) * 8192 / pg_relation_size(c.oid), 1) AS percent_of_relation
FROM pg_class c
JOIN pg_buffercache b
  ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_database d ON b.reldatabase = d.oid AND d.datname = current_database()
WHERE pg_relation_size(c.oid) > 0
GROUP BY c.oid, c.relname
ORDER BY 3 DESC
LIMIT 10;

If a single bloated or rarely-queried table dominates the buffers, that is your target. Querying pg_buffercache scans all of shared_buffers, so run it sparingly on busy systems.

Root-Cause Analysis with Pulse

Mapping a falling cache hit ratio to the specific query, table, or index driving it usually means correlating pg_stat_statements, pg_statio_user_tables, EXPLAIN plans, and pg_buffercache snapshots by hand, often after the incident has passed. Pulse automates that loop. It continuously tracks the hit ratio and disk-read rate across heap, index, and TOAST blocks, detects when reads start rising, and traces the regression to the queries and relations responsible - a sequential scan from a dropped index, an oversized table evicting hot pages, or a working set that has outgrown shared_buffers. It then recommends a concrete change (add this index, raise shared_buffers, vacuum this table) for a human to approve, rather than acting blindly.

For prevention, connecting your database to Pulse's proactive monitoring catches the slow drift - growing bloat, an index that stopped being used, a working set creeping past RAM - before the ratio degrades into user-visible latency. See database monitoring best practices for the broader signal set worth tracking.

Frequently Asked Questions

Q: What is a good PostgreSQL cache hit ratio?
A: For OLTP workloads where the working set fits in memory, a buffer cache hit ratio above 0.99 is typical and a number consistently below ~0.95 is worth investigating. Analytics workloads that scan large tables legitimately run lower. The ratio is a relative signal, not a hard target, because it ignores the OS page cache.

Q: Why is my cache hit ratio low even though queries feel fast?
A: pg_stat_database.blks_hit counts only PostgreSQL buffer cache hits, not the operating system page cache. A page missing from shared_buffers is often still served quickly from the kernel cache, so a "low" ratio can coexist with good latency. Confirm with actual query timings and disk I/O metrics before tuning.

Q: Does increasing shared_buffers always raise the cache hit ratio?
A: Not always. Raising shared_buffers helps when the working set is larger than the current cache but fits in RAM. PostgreSQL also relies on the OS cache, so allocating more than ~40% of RAM rarely helps and can starve the kernel cache. If the cause is a sequential scan or bloat, adding memory only masks it.

Q: How do I see which tables are in the PostgreSQL buffer cache?
A: Install the pg_buffercache extension and aggregate its rows by relation, joining to pg_class on pg_relation_filenode(c.oid). This shows how many buffers each table and index holds and what fraction of shared_buffers they consume.

Q: Why is the cache hit ratio low right after a restart?
A: The buffer cache starts empty after a restart or crash recovery, so every early read is a miss until the cache warms with your working set. The cumulative counters make this look worse than steady state. Reset stats with pg_stat_reset() and re-sample, or warm the cache with pg_prewarm.

Q: What is the difference between shared_buffers and effective_cache_size?
A: shared_buffers allocates actual RAM for PostgreSQL's buffer cache. effective_cache_size allocates nothing - it is a planner hint estimating total available cache (shared buffers plus OS cache) so the planner can decide between index and sequential scans. Set effective_cache_size to roughly 50-75% of system RAM.

Q: Can unused indexes lower the cache hit ratio?
A: Yes, indirectly. Every index page that gets read competes for space in shared_buffers. Maintaining and occasionally reading large indexes that no query uses evicts pages your hot queries need. Check pg_stat_user_indexes.idx_scan and drop indexes that are never scanned.

Related Reading

• PostgreSQL Performance Tuning: Configuration and query-level levers for a faster database.
• EXPLAIN ANALYZE in PostgreSQL: Read query plans and the BUFFERS output to find disk reads.
• Creating Indexes in PostgreSQL: Add the right index to turn sequential scans into cache-friendly index scans.
• Debugging Slow PostgreSQL Queries: Find and fix the queries driving disk activity.
• PostgreSQL Connection Pooling with PgBouncer: Reduce per-connection memory pressure that competes with the cache.
• Database Monitoring Best Practices: Which metrics to track so a falling hit ratio surfaces early.