Explain the difference between shared_buffers and work_mem. How does each affect query performance?

shared_buffers is the buffer pool that caches data pages in memory. It's shared across all connections. High shared_buffers increases cache hit ratio, reducing disk I/O. Target: 25% of RAM. work_mem is per-operation memory for sorts, hashes, and joins. Each operation (not query) gets work_mem. Complex query with 5 sorts uses 5 × work_mem. If operation exceeds work_mem, it spills to disk (temp files), causing 10-100x slowdown. shared_buffers affects read performance (cache hits), work_mem affects operation performance (avoiding spills). Both critical but serve different purposes.

Why does a hash join spill to disk when work_mem is too small? Explain the batching process.

Hash join builds a hash table on the smaller table. If hash table size exceeds work_mem, PostgreSQL cannot fit it in memory. It splits the data into batches: Batch 1 fits in work_mem, remaining batches written to temp files. Process: (1) Build hash table for Batch 1 in memory, (2) Probe with matching rows from larger table, (3) Write Batch 1 results, (4) Load Batch 2 from temp files, (5) Repeat for all batches. Each batch requires disk I/O (write + read). If hash table is 200MB and work_mem is 4MB, need 50 batches. Each batch adds disk I/O overhead. Disk is 1000x slower than memory, so 50 batches makes query 50x slower. Solution: increase work_mem to fit entire hash table (200MB + overhead = 250MB work_mem).

How do you calculate the maximum memory usage for work_mem with concurrent connections? Why is this important?

Max work_mem usage = max_connections × avg_operations_per_query × work_mem. Example: 1000 connections, 10 operations per query (5 sorts + 5 hash joins), work_mem = 100MB. Max usage = 1000 × 10 × 100MB = 1TB. If server has 64GB RAM, this exceeds available memory by 15x. Result: memory exhaustion, swapping, OOM killer. This is critical because work_mem is per-operation, not per-query. Complex queries use multiple × work_mem. With high concurrency, total usage can exceed RAM. Solutions: (1) Connection pooling to reduce active connections (1000 → 100), (2) Lower work_mem globally (100MB → 10MB), (3) Use SET work_mem for specific large queries only. Rule: max work_mem usage should be <50% of RAM.

EXPLAIN ANALYZE shows: Hash Batches: 25, Memory Usage: 4MB, Disk Usage: 96MB. The query takes 10 minutes. How do you fix this?

-- Diagnosis: Hash join spilling to disk

-- Hash table size: 4MB (in memory) + 96MB (on disk) = 100MB

-- work_mem: 4MB (too small)

-- Batches: 25 (should be 1)

-- Each batch requires disk I/O

-- Check current work_mem

SHOW work_mem;

-- Result: 4MB (default, too small)

-- Calculate required work_mem

-- Hash table: 100MB

-- Add 50% overhead: 100MB × 1.5 = 150MB

-- Option 1: Set for current session

SET work_mem = '150MB';

-- Run query again

EXPLAIN ANALYZE

SELECT o.order_id, c.name, o.total

FROM orders o

JOIN customers c ON o.customer_id = c.id;

-- Verify fix:

-- Hash Batches: 1 (no spill)

-- Memory Usage: 100MB

-- No Disk Usage

-- Time: 10 seconds (60x faster)

-- Option 2: Set globally (if many queries need it)

-- Edit postgresql.conf:

-- work_mem = 150MB

-- Restart PostgreSQL

-- Option 3: Set per-query (safest for mixed workload)

BEGIN;

SET LOCAL work_mem = '150MB';

SELECT ... -- large join

COMMIT;

-- work_mem reverts to default after transaction