While diving into PostgreSQL's configuration options, two settings caught my attention: fsync and synchronous_commit. At first glance, they might seem like minor toggles, but a closer look reveals their critical role in balancing database performance and data durability. Through research and experimentation, I discovered how these settings can significantly affect not just the performance of your PostgreSQL instance, but also its ability to recover from failures. In this post, I’ll guide you through the inner workings of these settings, their trade-offs, and how to make informed decisions based on your use case.
What is fsync
The fsync parameter in PostgreSQL determines whether all changes to the database are physically written to the disk before a transaction is confirmed. When fsyncis enabled, PostgreSQL issues fsync() system calls (or equivalent methods) to ensure that data is securely flushed to disk. This guarantees that the database cluster can recover to a consistent state in the event of an operating system or hardware crash.
When fsync is disabled, PostgreSQL skips this step, which can dramatically improve performance but introduces significant risks. In the event of a crash or unexpected shutdown, data that was not yet written to disk can be lost or corrupted. Worse, because both the Write-Ahead Log (WAL) and checkpoints are critical for maintaining the consistency of the entire cluster, a crash with fsync disabled could render the entire cluster unusable, requiring a full restore from backups.
What is synchronous_commit
The synchronous_commit determines how PostgreSQL handles transaction commits in relation to the WAL. When enabled, PostgreSQL ensures that the WAL records for a transaction are flushed to durable storage before the transaction is reported as committed. This provides a high level of data safety, ensuring that committed transactions are not lost even in the case of a crash.
When synchronous_commit is disabled, PostgreSQL does not wait for the WAL to be flushed to disk. Instead, it relies on the operating system to handle the flushing asynchronously. This can significantly improve transaction throughput but comes with a trade-off: in the event of a crash, some transactions reported as committed might not actually be saved.
Testing performance with fsync on/off
To benchmark the performance impact, I utilized pgbench with 10 connections and 2 threads.
pgbench -c 10 -j 2 -T 30
With fsync disabled, we observed a performance improvement of approximately 58% in TPS and a latency reduction of about 37% when compared to having it enabled.
Testing performance with synchronous_commit on/off
With synchronous_commit disabled, we observed a performance improvement of approximately 3.5% in TPS and a latency reduction of about 3.4% when compared to having it enabled. When both fsync and synchronous_commit were disabled, there was an additional performance boost, resulting in a 10.7% improvement in TPS and a 6.5% reduction in latency compared to having only synchronous_commit disabled.
Conclusion
These results highlight the need to carefully evaluate the trade-offs between performance and data safety based on the application requirements. For production environments, it's generally recommended to keep fsync enabled and consider disabling synchronous_commit only if the risk of losing recent transactions is acceptable.
However, for high-throughput applications where performance is critical and occasional data loss is tolerable, such as analytics pipelines, caching layers, or systems with external redundancy, relaxing durability guarantees can lead to significant throughput improvements. Ultimately, tuning these settings should be guided by thorough benchmarking and a deep understanding of your workload's tolerance for failure.
Top comments (0)