**PostgreSQL Configuration:**

-- View current settings

SHOW shared_buffers;

SHOW work_mem;

SHOW maintenance_work_mem;

SHOW effective_cache_size;

-- Set in postgresql.conf

shared_buffers = 16GB

work_mem = 50MB

maintenance_work_mem = 2GB

effective_cache_size = 40GB

-- Set for current session

SET work_mem = '500MB';

-- Set for specific query

BEGIN;

SET LOCAL work_mem = '1GB';

SELECT ... -- uses 1GB work_mem

COMMIT;

-- work_mem reverts to default

**Monitor Cache Hit Ratio:**

-- Overall cache hit ratio

SELECT

sum(heap_blks_read) as heap_read,

sum(heap_blks_hit) as heap_hit,

sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio

FROM pg_statio_user_tables;

-- Per-table cache hit ratio

SELECT

schemaname,

tablename,

heap_blks_read,

heap_blks_hit,

round(heap_blks_hit::numeric / (heap_blks_hit + heap_blks_read), 4) as hit_ratio

FROM pg_statio_user_tables

WHERE heap_blks_read > 0

ORDER BY hit_ratio ASC

LIMIT 10;

-- Index cache hit ratio

SELECT

schemaname,