Databases use memory for two main purposes: caching data pages (buffer pool) and executing operations (work memory). Memory is 100-1000x faster than disk. When operations don't fit in memory, they spill to disk, causing massive slowdowns.

Buffer pool: Caches frequently accessed data pages. High cache hit ratio (>90%) means most reads come from memory, not disk.

Work memory: Used for sorts, hashes, joins. If operation exceeds work_mem, it spills to temp files on disk (100x slower).

Key principle: Keep hot data in memory, avoid disk I/O at all costs.

**Buffer Pool (Shared Buffers):**

The buffer pool caches data pages in memory. When you query a table, PostgreSQL:

1. Checks if page is in buffer pool (cache hit)

2. If not, reads from disk (cache miss)

3. Stores page in buffer pool for future use

Cache hit ratio = (cache hits) / (cache hits + cache misses)

Target: >95% for OLTP, >90% for analytics

Example:

- 1000 queries

- 950 cache hits (data in memory)

- 50 cache misses (read from disk)

- Hit ratio: 950/1000 = 95%

Impact of cache misses:

- Memory read: 100 nanoseconds

- SSD read: 100 microseconds (1000x slower)

- HDD read: 10 milliseconds (100,000x slower)

**PostgreSQL Memory Configuration:**

1. **shared_buffers** (Buffer Pool)

- Caches data pages

- Default: 128MB (way too small)

- Recommended: 25% of RAM (up to 40% for dedicated DB server)

- Example: 64GB RAM → 16GB shared_buffers

2. **work_mem** (Per-Operation Memory)

- Used for sorts, hashes, joins

- Allocated per operation (not per query)

- Default: 4MB (often too small)

- Recommended: Depends on workload

- Example: Complex query with 5 sorts → uses 5 × work_mem

3. **maintenance_work_mem** (Maintenance Operations)

- Used for VACUUM, CREATE INDEX, ALTER TABLE

- Default: 64MB

- Recommended: 1-2GB for large tables

- Higher = faster index creation

4. **effective_cache_size** (Hint to Planner)

- Tells optimizer how much memory is available (DB + OS cache)

- Doesn't allocate memory, just affects planning