Cache Efficiency Analysis

Measure Database-Wide Cache Hit Ratio

Start with global hit ratio to confirm shared buffers alignment. Aim for ≥95% in OLTP systems; values below this threshold typically point to undersized buffers or missing indexes.

SELECT
    SUM(shared_blks_hit) AS total_cache_hits,
    SUM(shared_blks_read) AS total_disk_reads,
    SUM(shared_blks_hit + shared_blks_read) AS total_blocks_accessed,
    ROUND((SUM(shared_blks_hit)::numeric /
           NULLIF(SUM(shared_blks_hit + shared_blks_read), 0) * 100)::numeric, 2) AS cache_hit_ratio_pct
FROM pg_stat_insights;

Identify Queries with Poor Cache Performance

Highlight queries that miss cache frequently or drive heavy disk reads. Triage by frequency and bytes to decide whether to tune or cache.

SELECT query,
       calls,
       shared_blks_hit,
       shared_blks_read,
       (shared_blks_hit::numeric /
        NULLIF(shared_blks_hit + shared_blks_read, 0)) AS hit_ratio
  FROM pg_stat_insights
 WHERE shared_blks_read > 0
 ORDER BY hit_ratio ASC, shared_blks_read DESC
 LIMIT 20;

Detect Relation-Level Hotspots

SELECT relid::regclass AS relation,
       SUM(shared_blks_read) AS disk_reads,
       SUM(shared_blks_hit) AS cache_hits,
       SUM(shared_blks_read) * 8 / 1024 AS read_mb
  FROM pg_stat_insights_relation
 GROUP BY relid
 ORDER BY disk_reads DESC
 LIMIT 15;

Hot relations may require dedicated indexes, partitioning, or prewarming strategies to keep data resident in shared buffers.

Monitor Autovacuum & Checkpoint Impact

SELECT datname,
       relname,
       autovacuum_count,
       vacuum_count,
       n_dead_tup
  FROM pg_stat_user_tables
 ORDER BY autovacuum_count DESC
 LIMIT 20;

psql -c "SELECT checkpoint_write_time, buffers_checkpoint, buffers_clean, buffers_backend FROM pg_stat_bgwriter;"

Apply Remediation