Resource Usage / Cost-Based Vacuum Delay

vacuum_cost_delay

• What it does: Sets the amount of time that vacuum processes will sleep when they reach the accumulated cost limit specified by vacuum_cost_limit.
• Why it matters: This parameter is essential for throttling vacuum operations to prevent them from consuming excessive I/O resources and impacting database performance. By introducing delays, vacuum operations are spread out over time, ensuring that regular database operations can proceed without significant contention for I/O resources. This is particularly important for maintaining consistent performance during maintenance operations.
• Ideal value & Best Practice: Default 0 (no delay). For busy production systems, set to 10-20ms to balance vacuum progress with system responsiveness. For systems with significant I/O contention, consider 50-100ms. Adjust based on your workload patterns and vacuum requirements.

vacuum_cost_limit

• What it does: Sets the total cost amount that vacuum processes can accumulate before sleeping for the duration specified by vacuum_cost_delay.
• Why it matters: This parameter works with vacuum_cost_delay to implement the cost-based vacuum delay mechanism. It determines how much work vacuum can do before yielding to other operations. A higher limit allows vacuum to do more work between delays, making it faster but potentially more disruptive. A lower limit makes vacuum less intrusive but slower.
• Ideal value & Best Practice: Default 200 is generally appropriate. For systems where vacuum needs to be more aggressive, increase to 400-600. For environments where minimal disruption is critical, decrease to 100-150. Monitor vacuum progress and system performance when adjusting this value.

vacuum_cost_page_dirty

• What it does: Sets the cost assigned for each page that vacuum dirties (i.e., pages that vacuum must write because it modified them).
• Why it matters: This cost component accounts for the most expensive vacuum operations—those that require actual writing to disk. Dirtying pages involves both reading and writing, making it more resource-intensive than other vacuum operations. Proper costing ensures that write-intensive vacuum work is appropriately throttled.
• Ideal value & Best Practice: Default 20 is reasonable. This should typically be the highest of the page cost parameters since writing dirty pages is the most expensive operation. Increase if you need to be more conservative about write operations during vacuum.

vacuum_cost_page_hit

• What it does: Sets the cost assigned for each page found in the shared buffer cache during vacuum operations.
• Why it matters: This cost accounts for vacuum operations that find needed pages already in memory. Since these operations don't require disk I/O, they're assigned a lower cost. This helps distinguish between memory-based operations (cheap) and disk-based operations (expensive) in the vacuum costing system.
• Ideal value & Best Practice: Default 1 is appropriate. This should be the lowest of the page cost parameters since buffer hits are the least expensive operations. Generally, this value doesn't need adjustment unless you have specific memory characteristics.

vacuum_cost_page_miss

• What it does: Sets the cost assigned for each page that vacuum must read from disk because it wasn't found in the shared buffer cache.
• Why it matters: This cost component accounts for vacuum operations that require reading from disk, which is more expensive than reading from memory but less expensive than writing dirty pages. It helps balance the cost between memory operations and disk I/O operations during vacuum processing.
• Ideal value & Best Practice: Default 10 provides a good balance. This should be between vacuum_cost_page_hit and vacuum_cost_page_dirty in value. Adjust if your storage system has unusual read characteristics compared to write performance.

---

Try pghealth Free Today 🚀

Start your journey toward a healthier PostgreSQL with pghealth.
You can explore all features immediately with a free trial — no installation required.

👉 Start Free Trial