Write-Ahead Log / Checkpoints

checkpoint_completion_target

• What it does: Sets the target completion time for checkpoints as a fraction of the checkpoint interval, controlling how aggressively dirty buffers are written to disk during each checkpoint.
• Why it matters: This parameter is crucial for smoothing out I/O workload and preventing checkpoint spikes. A higher value spreads checkpoint I/O over a longer period, making writes more gradual and reducing the impact on concurrent database operations. A lower value makes checkpoints more aggressive, potentially causing I/O bursts that can affect query performance.
• Ideal value & Best Practice: Default 0.9 is recommended for most workloads. This spreads the checkpoint I/O over 90% of the checkpoint interval. For systems with consistent I/O capacity, 0.5-0.8 can provide a good balance. Avoid very low values (below 0.3) as they can cause I/O spikes.

checkpoint_flush_after

• What it does: Controls how frequently the checkpoint process forces OS cache flushes by specifying the number of pages after which writes are flushed to persistent storage.
• Why it matters: This parameter helps manage the writeback behavior during checkpoints, balancing between performance and durability. More frequent flushing ensures data is persisted to disk sooner but may impact checkpoint performance. Less frequent flushing improves performance but increases the window for potential data loss.
• Ideal value & Best Practice: Default 256KB (32 pages of 8KB). For systems with battery-backed write cache or reliable storage, you can increase to 512KB or 1MB for better performance. For systems without write protection, consider keeping the default or lower values for better durability.

checkpoint_timeout

• What it does: Sets the maximum time between automatic WAL checkpoints, ensuring that checkpoints occur at regular intervals regardless of WAL volume.
• Why it matters: This parameter provides time-based checkpoint triggering, which is essential for ensuring that recovery time remains bounded. Without time-based checkpoints, a database with low write activity might not checkpoint for extended periods, leading to potentially long recovery times after a crash.
• Ideal value & Best Practice: Default 5min is reasonable for most systems. Increase to 15-30min for write-intensive workloads to reduce checkpoint frequency. Decrease to 1-2min for systems requiring faster recovery times. Balance between recovery time objectives and checkpoint overhead.

checkpoint_warning

• What it does: Sets the threshold for warning messages when checkpoints occur too frequently due to WAL volume rather than time.
• Why it matters: This parameter helps identify when checkpoints are happening more frequently than expected due to high write activity, which can indicate that max_wal_size needs adjustment or that there's unusually high write volume that should be investigated.
• Ideal value & Best Practice: Default 30s. Set to 60s for most production systems. If you see frequent warnings, consider increasing max_wal_size or investigating the source of high write activity. Set to 0 to disable warnings if they become too noisy.

max_wal_size

• What it does: Sets the soft maximum size of WAL files that can accumulate before triggering a checkpoint.
• Why it matters: This is one of the most important checkpoint parameters. It controls how much WAL data can be generated between checkpoints, directly affecting both recovery time and I/O patterns. A larger value reduces checkpoint frequency but increases potential recovery time. A smaller value increases checkpoint frequency but may impact write performance.
• Ideal value & Best Practice: Default 1GB is often too small for production. Set to 4-8GB for moderate write workloads, 16-32GB for write-intensive systems. Calculate based on your checkpoint_timeout and write rate: max_wal_size = (write_rate_per_second * checkpoint_timeout) * 2.

min_wal_size

• What it does: Sets the minimum amount of WAL disk space that PostgreSQL will attempt to maintain, preventing unnecessary shrinking of WAL space after periods of high activity.
• Why it matters: This parameter helps avoid frequent allocation and deallocation of WAL space. After a period of high write activity, PostgreSQL will keep at least this much WAL space allocated, making it ready for future write bursts without needing to immediately allocate more space.
• Ideal value & Best Practice: Default 80MB. Set to 10-25% of max_wal_size. For example, with max_wal_size = 8GB, set min_wal_size = 1-2GB. This provides a good balance between space efficiency and readiness for write bursts.

---

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