Scenario
A newly provisioned 32 GB PostgreSQL server has shared_buffers = 128MB (the compile-time default). The cache hit ratio from pg_stat_bgwriter is 72% — far below the 99%+ target for OLTP. Every cache miss hits disk, causing high read latency. The server has plenty of RAM but it is not being used for PostgreSQL caching.
How to Identify
Conditions:
- Cache hit ratio < 95% from
pg_stat_bgwriter or pg_statio_user_tables
shared_buffers = 128MB (or other low default value)buffers_backend rising in pg_stat_bgwriter — backends writing own buffers- High disk read I/O despite queries being “the same” as before
EXPLAIN (BUFFERS) shows large shared blks read counts
Analysis Steps
-- Check current shared_buffers:
SHOW shared_buffers;
-- Cache hit ratio from pg_stat_bgwriter:
SELECT
round(100.0 * buffers_clean /
NULLIF(buffers_clean + buffers_checkpoint + buffers_backend, 0), 2)
AS bgwriter_hit_pct,
buffers_backend,
buffers_alloc
FROM pg_stat_bgwriter;
-- Per-table cache hit ratio:
SELECT schemaname, tablename,
round(100.0 * heap_blks_hit /
NULLIF(heap_blks_hit + heap_blks_read, 0), 1) AS cache_hit_pct,
heap_blks_hit, heap_blks_read
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 20;
-- Tables with low cache_hit_pct = candidates for keeping in shared_buffers
Pitfalls
- The standard recommendation is
shared_buffers = 25% of RAM, but this is a starting point. OLTP workloads with a hot dataset that fits in RAM benefit from larger values (40%).
- Increasing
shared_buffers past the hot dataset size gives diminishing returns — the OS page cache fills the gap.
shared_buffers requires a server restart — it cannot be changed with pg_reload_conf().- On Linux, set
vm.overcommit_memory = 2 and configure huge pages when shared_buffers is large (>8 GB) to avoid allocation failures.
effective_cache_size is not the actual cache — it is a planner hint that should reflect shared_buffers + OS page cache. Setting it too low causes the planner to avoid index scans.
Resolution Approach
Set shared_buffers to 25% of total RAM as a baseline. Set effective_cache_size to 75% of RAM. Restart PostgreSQL. Monitor cache hit ratio — target >99% for OLTP.