Resource Usage / Asynchronous Behavior

backend_flush_after

• What it does: Controls how frequently the backend writes dirty buffers to disk by specifying the number of pages after which writes are flushed.
• Why it matters: This parameter helps balance between write performance and data durability. More frequent flushing reduces the risk of data loss but can impact performance by increasing I/O operations. Less frequent flushing improves performance but increases the window for potential data loss in case of a crash.
• Ideal value & Best Practice: Default 0 (disabled). For systems with battery-backed write cache, set to 0. For systems without write cache, consider values like 32 or 64 pages (256-512KB) to reduce I/O overhead. Adjust based on your durability requirements and storage performance.

effective_io_concurrency

• What it does: Sets the estimated number of simultaneous I/O requests your storage system can handle efficiently, helping the planner make better decisions about parallel I/O operations.
• Why it matters: This parameter significantly affects the planner's ability to leverage modern storage systems that can handle multiple concurrent I/O operations. Proper setting enables better use of SSDs, RAID arrays, and other high-performance storage systems by allowing PostgreSQL to issue multiple I/O requests simultaneously.
• Ideal value & Best Practice: For SSDs/NVMe: 200-300. For RAID arrays: 2-4 per spindle. For single HDD: 1. Start with conservative values and increase while monitoring I/O wait times and system performance.

io_combine_limit

• What it does: Sets the maximum size for combining adjacent I/O requests, optimizing read and write operations by reducing the number of smaller I/O calls.
• Why it matters: This parameter helps optimize I/O patterns by combining smaller adjacent I/O requests into larger, more efficient operations. This reduces overhead and improves throughput, especially for workloads with many small I/O operations.
• Ideal value & Best Practice: Default 0 (system default). For most modern storage, leave at default. For specialized storage with optimal large I/O sizes, set to match the storage's preferred I/O size (e.g., 8192 for 8KB operations).

maintenance_io_concurrency

• What it does: Sets the I/O concurrency setting specifically for maintenance operations like VACUUM, CREATE INDEX, and bulk loading.
• Why it matters: Maintenance operations often have different I/O patterns than regular queries. This parameter allows tuning I/O concurrency specifically for these operations without affecting normal query performance. This is particularly important for large maintenance jobs that need to coordinate with production workload.
• Ideal value & Best Practice: Default -1 (same as effective_io_concurrency). Set to a value 2-4 times higher than effective_io_concurrency for maintenance windows when you want to accelerate maintenance operations. Reduce during normal operation to avoid impacting production workload.

max_parallel_maintenance_workers

• What it does: Sets the maximum number of parallel worker processes that can be used for a single maintenance operation like CREATE INDEX or VACUUM.
• Why it matters: This parameter controls how aggressively PostgreSQL parallelizes maintenance operations, which can significantly reduce the time required for index creation, vacuuming, and other maintenance tasks. Proper tuning balances maintenance speed with system resource usage.
• Ideal value & Best Practice: Default 2. Set to 50-75% of available CPU cores for maintenance-heavy systems. For example, with 16 cores, use 8-12. Consider available memory and I/O capacity when increasing this value.

max_parallel_workers

• What it does: Sets the global maximum number of parallel worker processes that can be active simultaneously across the entire database cluster.
• Why it matters: This parameter controls the total parallelism capacity of your PostgreSQL instance. It must be at least as large as the largest max_parallel_workers_per_gather setting you plan to use, and it affects all types of parallel operations including queries and maintenance.
• Ideal value & Best Practice: Default 8. Set to approximately 50-75% of total CPU cores. For example, with 32 cores, set to 16-24. Ensure adequate memory is available for parallel workers.

max_parallel_workers_per_gather

• What it does: Sets the maximum number of parallel worker processes that can be used for a single Gather or Gather Merge node in a query plan.
• Why it matters: This parameter directly controls the degree of parallelism for individual queries. It determines how many worker processes can be used to accelerate a single query execution, significantly affecting query performance for parallelizable operations.
• Ideal value & Best Practice: Default 2. Set to 25-50% of total CPU cores. For example, with 16 cores, use 4-8. Adjust based on your typical query workload and available resources.

max_worker_processes

• What it does: Sets the maximum number of background worker processes that can be running simultaneously, including parallel workers, autovacuum workers, and extension background workers.
• Why it matters: This parameter controls the total capacity for background processing in PostgreSQL. It must be large enough to accommodate all types of background workers including parallel query workers, autovacuum workers, and any extension-specific workers.
• Ideal value & Best Practice: Default 8. Set to at least the sum of max_parallel_workers and autovacuum_max_workers, plus overhead for other background processes. For busy systems, set to max_parallel_workers + autovacuum_max_workers + 4.

parallel_leader_participation

• What it does: Controls whether the leader process (the original backend) also participates in executing the parallel query plan or only coordinates the worker processes.
• Why it matters: When enabled, the leader process helps execute the query alongside worker processes, which can improve performance for some queries. When disabled, the leader only coordinates, which can be better for queries where the leader's participation would create a bottleneck.
• Ideal value & Best Practice: Default on is recommended for most workloads. Set to off if you notice the leader process becoming a bottleneck in parallel queries, particularly for queries with expensive aggregation or sorting operations.

---

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