Configuring Memory Settings
1. Setting shared_buffers
shared_buffers = 8GB # ~25% of RAM (Linux); requires restart2. Setting work_mem
work_mem = 64MB # per sort/hash node; per query may use manyWarning: Total memory = work_mem × N(parallel) × hash/sort nodes × connections. Easy to OOM.
3. Setting maintenance_work_mem
maintenance_work_mem = 1GB # for VACUUM, CREATE INDEX, ALTER TABLE4. Setting effective_cache_size
effective_cache_size = 24GB # ~50-75% of RAM; planner hint, not allocation5. Setting temp_buffers
temp_buffers = 8MB # per session, for TEMP tables6. Setting wal_buffers (WAL buffer size)
wal_buffers = 16MB # -1 = auto (1/32 of shared_buffers)7. Understanding Memory Context
| Region | Purpose |
|---|---|
| shared_buffers | Cache of disk pages |
| work_mem (private) | Sort / hash spills |
| maintenance_work_mem | Bulk admin operations |
| wal_buffers | Pending WAL before flush |
| OS page cache | Effective cache (Linux) |
8. Monitoring Memory Usage
SELECT * FROM pg_stat_bgwriter;
SELECT name, setting, unit FROM pg_settings
WHERE name IN ('shared_buffers','work_mem','maintenance_work_mem','effective_cache_size');9. Setting max_stack_depth
max_stack_depth = 7MB # must be < ulimit -s10. Setting huge_pages
huge_pages = try # try | on | off
# Linux: sysctl vm.nr_hugepages = ceil(shared_buffers / 2MB)