Intermediate LevelPostgreSQL
How does Vacuum Cost Throttling (vacuum_cost_limit) prevent I/O saturation?
❓ Interview Prompt: Explain Vacuum Cost Throttling (`vacuum_cost_limit`, `vacuum_cost_delay`), page hit/miss cost math, and preventing disk I/O spikes.
AutoVACUUM scanning large tables can saturate disk I/O bandwidth. Vacuum Cost Throttling introduces sleep delays when AutoVACUUM exceeds cost limits:
•
'vacuum_cost_page_hit' (Cost 1): Page found in 'shared_buffers'.
•
'vacuum_cost_page_miss' (Cost 2): Page read from disk.
•
'vacuum_cost_limit': When accumulated cost hits threshold, AutoVACUUM sleeps for 'vacuum_cost_delay' (ms).
Code Implementation Example
sql
-- Tune autovacuum cost limits for SSD storage
ALTER SYSTEM SET vacuum_cost_limit = 2000;
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '2ms';
SELECT pg_reload_conf();💡Tuning AutoVACUUM cost limits for modern SSD storage.
🎯 Interview Key Takeaway
Increase vacuum_cost_limit on fast NVMe SSD storage to allow AutoVACUUM to complete faster without stalling.
#vacuum_cost_limit#AutoVACUUM#I/O Throttling#Intermediate
Back to all PostgreSQL Questions→