Write-Ahead Log / Settings

commit_delay

• What it does: Sets a delay in microseconds between transaction commit and flushing WAL to disk, allowing multiple commits to be grouped together.
• Why it matters: This parameter can improve throughput for high-concurrency write workloads by grouping multiple commit operations into a single I/O operation. However, it increases the window for potential data loss in case of a crash. The delay only applies when there are at least commit_siblings concurrent transactions.
• Ideal value & Best Practice: Default 0 (no delay). For high-throughput OLTP systems with many concurrent commits, consider 10000-20000 microseconds (10-20ms). Test thoroughly and ensure adequate replication or backup strategies are in place to mitigate the increased risk of data loss.

commit_siblings

• What it does: Sets the minimum number of concurrent open transactions required before commit_delay is applied.
• Why it matters: This parameter ensures that commit_delay only takes effect when there's sufficient concurrent activity to benefit from grouping commits. This prevents unnecessary delays when the system is not busy, maintaining responsiveness during low-concurrency periods.
• Ideal value & Best Practice: Default 5. Increase to 10-20 for very busy systems with high concurrency. Decrease to 3-5 for systems with moderate concurrency. Adjust based on your typical transaction concurrency patterns.

fsync

• What it does: Controls whether PostgreSQL forces all database updates to be physically written to disk using the fsync() system call.
• Why it matters: This is a critical durability parameter. When enabled, it ensures that the database can recover to a consistent state after an operating system or hardware crash. Disabling fsync improves performance but risks data corruption and makes crash recovery impossible.
• Ideal value & Best Practice: Default on is essential for production systems. Only disable for non-critical data or during bulk loading operations where performance is paramount and data loss is acceptable. Never disable in production without understanding the severe risks.

full_page_writes

• What it does: Controls whether full database pages are written to WAL when first modified after a checkpoint.
• Why it matters: This parameter protects against partial-page writes that can occur during operating system crashes. While it increases WAL volume, it's essential for ensuring that recovery can complete successfully after a crash. Partial-page writes cannot be repaired using only row-level WAL records.
• Ideal value & Best Practice: Default on is strongly recommended. Only disable if you have storage hardware with guaranteed atomic writes (e.g., certain enterprise storage arrays with battery-backed cache). The performance impact is usually worth the safety guarantee.

synchronous_commit

• What it does: Controls the synchronization level for transaction commits, balancing between durability and performance.
• Why it matters: This parameter offers a spectrum of durability guarantees. Higher levels (on, remote_write, remote_apply) provide stronger durability but impact performance. Lower levels (off, local) improve performance but increase the risk of data loss. This is one of the most important parameters for tuning write performance versus durability.
• Ideal value & Best Practice: Default on provides good durability for most applications. Use remote_write or remote_apply for synchronous replication setups. Consider off for non-critical data or bulk operations where performance is more important than immediate durability.

wal_buffers

• What it does: Sets the amount of shared memory used for buffering WAL data before it's written to disk.
• Why it matters: This buffer reduces the number of disk writes by grouping WAL records. Larger values can improve performance for write-intensive workloads by reducing the frequency of WAL writes. However, very large values provide diminishing returns and waste shared memory.
• Ideal value & Best Practice: Default -1 (auto-tuned). Manual setting: 16-64MB for most systems. For very write-intensive workloads, consider 128MB. Ensure it's at least 16MB regardless of auto-tuning results.

wal_compression

• What it does: Enables compression of full-page images in WAL records, reducing WAL volume.
• Why it matters: This can significantly reduce WAL generation for workloads that modify large data pages, reducing storage requirements and I/O load. However, it adds CPU overhead for compression and decompression during normal operation and recovery.
• Ideal value & Best Practice: Default off. Enable if you have CPU capacity to spare and want to reduce WAL storage requirements. Particularly beneficial for workloads with large TOAST values or extensive full-page writes.

wal_init_zero

• What it does: Controls whether new WAL files are pre-filled with zeros before use.
• Why it matters: Pre-zeroing WAL files can prevent information leakage from previously deleted files and ensure consistent performance characteristics. However, it adds overhead during WAL file creation and may be unnecessary on modern filesystems that support efficient hole punching.
• Ideal value & Best Practice: Default on for security. Set to off if you need maximum performance and are not concerned about information leakage. Modern filesystems often handle this efficiently regardless of the setting.

wal_level

• What it does: Sets the amount of information written to WAL, controlling which features are available.
• Why it matters: This fundamental parameter determines what WAL-based features you can use. Higher levels enable logical replication, archive recovery, and other advanced features but increase WAL volume. Lower levels reduce WAL volume but disable important functionality.
• Ideal value & Best Practice: Default replica is appropriate for most production systems. Use logical if you need logical replication. Never use minimal in production as it disables critical crash recovery safety features.

wal_log_hints

• What it does: Controls whether full pages are written to WAL for hint bit updates.
• Why it matters: Hint bits are internal optimization markers that can be set on database pages. When enabled, this ensures that hint bit changes are WAL-logged, which is necessary for certain replication scenarios and provides additional safety at the cost of increased WAL volume.
• Ideal value & Best Practice: Default off. Enable if using certain replication setups or if you want extra safety for hint bit consistency. For most standalone databases, the default is sufficient.

wal_recycle

• What it does: Controls whether WAL files are recycled by renaming rather than recreated.
• Why it matters: File recycling can be faster than creating new files, especially on filesystems with slow metadata operations. However, it may cause issues on certain storage systems or with certain monitoring tools.
• Ideal value & Best Practice: Default on is recommended. Disable only if you experience issues with WAL file recycling on your specific storage system.

wal_skip_threshold

• What it does: Sets the minimum size for new files where WAL logging is skipped in favor of immediate fsync.
• Why it matters: This optimization can improve performance for large bulk operations by avoiding WAL overhead for files that are larger than the threshold. The files are still crash-safe because they're immediately synced to disk.
• Ideal value & Best Practice: Default 2MB. Increase to 8-16MB for systems with large bulk loading operations. Decrease if you have limited disk I/O capacity.

wal_sync_method

• What it does: Selects the method used for forcing WAL updates to disk.
• Why it matters: Different synchronization methods have varying performance characteristics and reliability across different operating systems and storage systems. This allows tuning for your specific platform.
• Ideal value & Best Practice: Default is platform-appropriate. Generally should not be changed unless you have specific knowledge that another method performs better on your system. Test thoroughly before changing from default.

wal_writer_delay

• What it does: Sets the time between WAL flush operations performed by the background WAL writer.
• Why it matters: This controls how frequently the WAL writer wakes up to flush buffered WAL data to disk. Shorter delays make WAL writes more frequent but may increase overhead. Longer delays reduce overhead but may increase the amount of data lost in a crash.
• Ideal value & Best Practice: Default 200ms is reasonable. Adjust based on your durability requirements and performance characteristics. For high-durability needs, consider 100ms. For better performance, consider 500ms.

wal_writer_flush_after

• What it does: Sets the amount of WAL data that triggers a flush operation by the WAL writer.
• Why it matters: This parameter works with wal_writer_delay to control WAL flushing behavior. It ensures that large amounts of WAL data don't accumulate without being flushed, even if the time delay hasn't been reached.
• Ideal value & Best Practice: Default 1MB provides a good balance. Increase to 2-4MB for write-intensive systems with good I/O capacity. Decrease to 512KB for systems requiring more frequent flushing for durability.

---

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