Intermediate LevelPostgreSQL
How do work_mem and maintenance_work_mem impact query sorts and index builds?
❓ Interview Prompt: Explain `work_mem` (RAM for in-memory sorts and hash tables per operation), disk spills (`workfiles`), and `maintenance_work_mem` for VACUUM/indexes.
1. 'work_mem':
Sets maximum memory used for query sort operations and hash tables per operation per backend:
•
If a query sort exceeds 'work_mem', Postgres spills temporary sort files to disk ('workfiles'), slowing performance!
•
Warning: 'work_mem' is allocated per sort node, so set conservatively (e.g. 16MB-64MB) to avoid OOM crashes.
2. 'maintenance_work_mem':
Sets memory allocated for maintenance operations ('VACUUM', 'CREATE INDEX', 'ALTER TABLE'). Can be set high (e.g. 512MB-2GB)!
Code Implementation Example
sql
-- Check current memory settings
SHOW work_mem;
SHOW maintenance_work_mem;
-- Increase work_mem for current analytical query session
SET work_mem = '128MB';💡Tuning work_mem for memory-intensive analytical queries.
🎯 Interview Key Takeaway
Set work_mem high enough to prevent disk sort spills while monitoring system RAM to prevent OOM errors.
#work_mem#maintenance_work_mem#Memory Tuning#Intermediate
Back to all PostgreSQL Questions→