**Impact of Memory Spills:**

Sort in memory (work_mem = 500MB):

- Data: 500MB

- Time: 2 seconds

Sort spills to disk (work_mem = 50MB):

- Data: 500MB

- Write to temp: 500MB (10 seconds)

- Read from temp: 500MB (10 seconds)

- Time: 22 seconds (11x slower)

Hash join in memory (work_mem = 200MB):

- Build hash table: 200MB

- Time: 1 second

Hash join spills to disk (work_mem = 50MB):

- Build partial hash tables

- Write to temp: 200MB (4 seconds)

- Multiple passes

- Time: 15 seconds (15x slower)

**Cache Hit Ratio Impact:**

95% hit ratio:

- 1000 queries

- 950 from memory (100ns each) = 95μs

- 50 from disk (10ms each) = 500ms

- Total: 500ms

80% hit ratio:

- 1000 queries

- 800 from memory = 80μs

- 200 from disk = 2000ms

- Total: 2000ms (4x slower)

**Memory Configuration Trade-offs:**

Large shared_buffers (40GB):

- Pros: High cache hit ratio, less disk I/O

- Cons: Less memory for work_mem, longer checkpoint times

Large work_mem (500MB):

- Pros: Fewer spills, faster sorts/joins

- Cons: High memory usage with many connections

Balance:

- OLTP: Larger shared_buffers, smaller work_mem

- Analytics: Moderate shared_buffers, larger work_mem

**Concurrency Impact:**

Low concurrency (10 connections):