Understanding fsync in PostgreSQL: Balancing Performance and Durability

What is fsync?

When a transaction is committed, fsync is a parameter that tells PostgreSQL whether it must wait for the operating system to flush the write-ahead log (WAL) to disk or only keep the transaction in the WAL buffer to be written later in an optimal manner. The trade-off here is between performance and durability.

Official documentation:

If this option is on, the PostgreSQL server will try to make sure that updates are physically written to disk, by issuing fsync() system calls or various equivalent methods (see wal_sync_method). This ensures that the database cluster can recover to a consistent state after an operating system or hardware crash.

fsync is off

Transactions are kept in the WAL buffer and written to disk later, typically during a checkpoint.

Improves performance but risks data loss and corruption in case of a crash.

When fsync is off, the WAL files persists at checkpoins

fsync is on

Every transaction in the WAL buffer is immediately flushed to disk upon commit.

Ensures maximum durability but with a performance cost.

When fsync is on the WAL persists for every transaction

The Trade-off: Performance vs. Durability

• Performance: If fsync is set to true, each transaction must be written to the WAL on disk, which can slow down transaction processing.
• Durability: If the system crashes, all transactions in the WAL buffer will be lost if fsync is set to false. However, if fsync is enabled, even in the event of a crash, transactions are safely stored on disk.

Conclusion

For most scenarios, enabling fsync provides the safest and most reliable operation. However, in trusted environments or during bulk data operations, disabling fsync may offer performance benefits.