Features
Cache behavior in PostgreSQL is managed through parameters and functions like pg_prewarm, letting you warm, monitor, or clear shared buffers for faster queries.
PostgreSQL stores recently accessed data pages in shared_buffers and the OS page cache. Proper tuning reduces disk I/O, lowers latency, and keeps throughput stable during traffic spikes.
A query first checks shared_buffers for the needed block. A hit serves it from RAM; a miss reads it from disk then caches it. The operating-system page cache supplies a second layer, further reducing disk reads.
shared_buffers sets PostgreSQL’s buffer pool size. effective_cache_size tells the planner how much OS cache is available. work_mem and maintenance_work_mem size in-memory sorts, hashes, and maintenance tasks.
Use the pg_prewarm extension to load tables or indexes into shared_buffers after a restart or maintenance.
SELECT pg_prewarm('products', mode := 'buffer');
Enable track_io_timing and run EXPLAIN (ANALYZE, BUFFERS) to view shared hits and disk reads. Check pg_stat_database and pg_statio_user_tables for global and per-table hit ratios.
Run DISCARD ALL to drop session-level caches (plans, temp tables). PostgreSQL cannot fully flush shared_buffers; restart PostgreSQL or the host OS for a hard reset.
Allocate 25–40% of server RAM to shared_buffers, leaving the rest for the OS cache. Prewarm critical tables after maintenance. Tune checkpoint_timeout and max_wal_size to prevent cache churn from frequent checkpoints.
CHECKPOINT;
SELECT pg_prewarm('products');
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM products WHERE price < 50;
Over-allocating shared_buffers (more than half of RAM) starves the OS cache, leading to swap and slowdowns. Ignoring effective_cache_size causes the planner to prefer sequential scans even when indexes fit in RAM.
Official docs: shared_buffers, pg_prewarm, EXPLAIN BUFFERS, pg_stat_statements.
No. PostgreSQL caches data pages, not full result sets. Proper indexing and memory tuning are required for repeated query speed.
Start around 25% of server RAM, then benchmark. Increase gradually while watching OS swap and cache hit ratios.
PostgreSQL cannot explicitly flush shared_buffers. A restart of PostgreSQL or the host OS is the only reliable method.
.avif){kind=logo}
