Monitor PostgreSQL Table Cache Hit Ratio with SQL

calendar May 14, 2026 / · 4 min read · pg_statio_user_tables cache performance shared_buffers administration  ·
Share on: linkedin copy

Measuring PostgreSQL Table Cache Efficiency with pg_statio_user_tables

PostgreSQL keeps frequently accessed data pages in shared_buffers to avoid hitting disk. When a backend reads a data block, PostgreSQL first checks shared_buffers; if the block is there, it counts as a heap hit. If it is not, PostgreSQL reads from disk and counts it as a heap read. The ratio of hits to total reads — the table cache hit ratio — is a primary indicator of how well your shared_buffers setting matches your working data set.

The pg_statio_user_tables view tracks these heap block reads and hits per table since the last statistics reset. A healthy cluster-wide ratio sits at or above 0.99 (99%). Ratios below 0.90 are a strong signal that shared_buffers is too small for the active working set and that a significant fraction of page reads are going to disk.

Sample Code

 1-- Cluster-wide table cache hit ratio
 2SELECT
 3    SUM(heap_blks_read)  AS heap_blks_read,
 4    SUM(heap_blks_hit)   AS heap_blks_hit,
 5    SUM(heap_blks_hit) /
 6        GREATEST(
 7            SUM(heap_blks_hit) + SUM(heap_blks_read),
 8            1
 9        )::float         AS cache_hit_ratio
10FROM pg_statio_user_tables;

Notes: Available on all supported PostgreSQL versions. Statistics accumulate since the last pg_stat_reset() call or server restart. Run as any role with access to pg_statio_user_tables; superuser not required.

Code Breakdown

  • pg_statio_user_tables — one row per user table; tracks block-level I/O counts for heap, TOAST, and index activity
  • heap_blks_read — number of disk block reads from the table's heap (cache miss)
  • heap_blks_hit — number of block reads served from shared_buffers (cache hit)
  • SUM(...) — aggregates across all user tables for a cluster-wide view
  • GREATEST(..., 1) — guards against division by zero when statistics have just been reset or no tables have been accessed
  • ::float — ensures floating-point division; without this, integer division truncates to 0 or 1

Key Points

  • Target ratio ≥ 0.99; below 0.90 warrants investigating shared_buffers sizing
  • A new cluster or a cluster after a pg_stat_reset() will show a low ratio until a representative workload has run — allow time for the buffer cache to warm up before drawing conclusions
  • The ratio reflects cumulative activity since the last reset, not current load; a recently started cluster may show artificially low ratios
  • A high cluster-wide ratio does not guarantee all tables are well-cached; always check the per-table breakdown to identify cold outliers

Insights and Explanations

The cluster-wide ratio masks individual table performance. Use the per-table query to find specific tables with low cache hit ratios:

 1SELECT
 2    schemaname,
 3    relname                          AS table_name,
 4    heap_blks_read,
 5    heap_blks_hit,
 6    CASE heap_blks_hit + heap_blks_read
 7        WHEN 0 THEN NULL
 8        ELSE ROUND(
 9            heap_blks_hit::numeric /
10            (heap_blks_hit + heap_blks_read),
11            4
12        )
13    END                              AS cache_hit_ratio
14FROM pg_statio_user_tables
15WHERE heap_blks_hit + heap_blks_read > 0
16ORDER BY cache_hit_ratio ASC NULLS LAST
17LIMIT 20;

Tables with low per-table ratios are candidates for investigation. Common causes:

  • Large tables exceeding shared_buffers — full sequential scans on tables larger than shared_buffers will always show low ratios; these do not benefit from increasing the buffer cache and should instead be addressed with partial indexes or partitioning
  • Infrequently accessed tables — tables accessed rarely will accumulate high read counts relative to hits because their pages are evicted between accesses
  • Recent data load — a freshly loaded table has never been in cache; its ratio rises as queries warm the buffer

To compare table cache hit ratio alongside index cache hit ratio for a complete I/O picture:

 1SELECT
 2    t.schemaname,
 3    t.relname                        AS table_name,
 4    CASE t.heap_blks_hit + t.heap_blks_read
 5        WHEN 0 THEN NULL
 6        ELSE ROUND(t.heap_blks_hit::numeric /
 7             (t.heap_blks_hit + t.heap_blks_read), 4)
 8    END                              AS table_cache_ratio,
 9    CASE i.idx_blks_hit + i.idx_blks_read
10        WHEN 0 THEN NULL
11        ELSE ROUND(i.idx_blks_hit::numeric /
12             (i.idx_blks_hit + i.idx_blks_read), 4)
13    END                              AS index_cache_ratio
14FROM pg_statio_user_tables  t
15JOIN pg_statio_user_indexes i USING (schemaname, relname)
16WHERE t.heap_blks_hit + t.heap_blks_read > 0
17GROUP BY t.schemaname, t.relname,
18         t.heap_blks_hit, t.heap_blks_read,
19         i.idx_blks_hit,  i.idx_blks_read
20ORDER BY table_cache_ratio ASC NULLS LAST
21LIMIT 20;

If both table and index cache ratios are low for the same table, increasing shared_buffers is the primary remedy. The standard starting point for a dedicated PostgreSQL server is 25% of total RAM, with diminishing returns above 8 GB on most workloads.

Additional Considerations

  • Permissions: pg_statio_user_tables is accessible to all roles by default; no superuser required
  • Statistics reset: SELECT pg_stat_reset() resets all pg_stat_* and pg_statio_* counters; after a reset, wait for a full workload cycle before evaluating ratios
  • TOAST tables: pg_statio_user_tables tracks the main heap only; TOAST heap I/O appears in separate toast_blks_read and toast_blks_hit columns in the same view
  • OS page cache: heap block reads counted by PostgreSQL reflect misses from shared_buffers, but Linux may serve them from the OS page cache — actual disk I/O may be lower than heap_blks_read implies on systems with large RAM

References

Posts in this series