Buffer Pool, Caches & Memory Optimization

-- PostgreSQL: shared_buffers
-- The main memory cache for table and index data pages

-- Check current setting:
SHOW shared_buffers;
-- Default: 128MB (way too low for production!)

-- Recommended: 25% of total RAM
-- 16GB RAM server → shared_buffers = 4GB
-- 64GB RAM server → shared_buffers = 16GB
-- Don't exceed 40% — OS needs file cache too

-- Check buffer usage:
SELECT
    c.relname AS table_name,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_size,
    COUNT(*) AS buffers_cached,
  
...

-- work_mem: memory per sort/hash operation per query

-- Check current setting:
SHOW work_mem;
-- Default: 4MB

-- Each sort, hash join, or hash aggregate gets this much memory
-- If data exceeds work_mem → spills to disk (SLOW)

-- EXPLAIN shows when this happens:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders ORDER BY total DESC;
-- Sort Method: external merge Disk: 52480kB  ← BAD (disk spill!)
-- Sort Method: quicksort Memory: 25kB        ← GOOD (in memory)

-- Set per-session for heavy que
...

-- maintenance_work_mem: memory for maintenance operations
-- Used by: VACUUM, CREATE INDEX, ALTER TABLE ADD FK

SHOW maintenance_work_mem;
-- Default: 64MB (too low!)

-- Recommended: 1GB-2GB (only one maintenance op at a time)
-- Set globally since maintenance ops are infrequent:
-- maintenance_work_mem = 1GB

-- Impact on CREATE INDEX:
SET maintenance_work_mem = '64MB';
CREATE INDEX idx_test1 ON big_table (column1);
-- Time: 45 seconds

SET maintenance_work_mem = '1GB';
CREATE INDEX idx_test2
...

-- Monitor memory usage and I/O patterns

-- 1. Check if you need more shared_buffers:
-- If cache hit ratio < 99%, consider increasing
SELECT
    datname,
    blks_hit,
    blks_read,
    ROUND(blks_hit::decimal / NULLIF(blks_hit + blks_read, 0) * 100, 2)
        AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- 2. Check if work_mem is causing disk spills:
-- Look for "external merge" or "external sort" in EXPLAIN output
-- Also check pg_stat_statements for high tem
...

-- TUNING CHECKLIST for a 32GB RAM server

-- postgresql.conf recommended settings:

-- Memory:
-- shared_buffers = 8GB            # 25% of RAM
-- effective_cache_size = 24GB     # 75% of RAM
-- work_mem = 32MB                 # conservative global
-- maintenance_work_mem = 2GB      # for VACUUM, CREATE INDEX

-- WAL:
-- wal_buffers = 64MB              # 1/32 of shared_buffers
-- checkpoint_completion_target = 0.9  # spread checkpoint I/O
-- max_wal_size = 4GB              # before forced checkp
...