Different parameters perform different optimization functions when tuned. It is not a one size fits all. Let's consider fsync, a concept common in most computer systems.
Failing to plan for unforeseen events during read and write operations in Postgresql is equivalent to planing to fail. fsync ensures that the Postgresql cluster can maintain it's state after an operating system or hardware failure.
With fsync parameter turning on, Postgres will ensure that data changes will be written back to disks. So that in case of operating system or hardware failure, the database can
always return to a consistent state. However it will cause additional overhead while doing WAL. It not recommended of setting this parameter to off, instead turning off
synchronous_commit under noncritical circumstance improve the database performance.
What is WAL and synchronous_commit?
WAL
WAL stands for Write-Ahead Logging. What does this mean in Postgresql operation? WAL plays a key role in Postgresql data integrity. It restores database file contents after a crash.
synchronous_commit
Synchronous commit guarantees a transaction is committed and changes on data are written into disk permanently before it is recorded as complete in WAL. This ensures no data will lose under any circumstances. However, it is known that disk access is quite an expensive operation, having synchronous_commit on will cause significant overhead in a transaction time. The risk of having this parameter off is the data loss of
the most recent transactions before power off or hardware failure. Normally under noncritical environment, it is a good way to have it disabled.
Top comments (0)