Flex2SQL for PostgreSQL: Setting FSYNC Option to Handle Disk I/O for Better Performance or Protection

Using FSYNC when setting up your environment with PostgreSQL provides greater flexibility for developers to either increase performance or enhance data backup capabilities. When using the Mertech driver with PostgreSQL, enabling the FSYNC option can help preserve data in case an operating system or hardware crash were to occur, while disabling it gives the option of faster, albeit less protected performance.

If the FSYNC option is set to “on” in the postgresql.conf file, the PostgreSQL backend will use the fsync()system call in several places to make sure that updates are physically written to disk. This ensures that a database installation will recover to a consistent state after an operating system or hardware crash. However, this operation does slow down PostgreSQL performance because at transaction commit it has to wait for the operating system to flush the write-ahead log. Without fsync, the operating system is allowed to do its best in buffering, sorting, and delaying writes, which can considerably increase performance. However, if the system crashes, the results of the last few committed transactions may be lost in part or whole. In the worst case, unrecoverable data corruption may occur.

For the above performance-related reasons, some administrators always leave it off, some turn it off only for bulk loads where there is a clear restart point if something goes wrong, and some leave it on just to be on the safe side. The default setting is “on” as it is the safest one. However, if you trust your operating system, your hardware, and your utility company or UPS, you might want to disable fsync by setting it to “off”. (Note that this option can only be set at server start). Furthermore, the performance penalty of enabling fsync is considerably less in PostgreSQL version 7.1 and later. If you previously suppressed fsyncs for performance reasons, you may wish to reconsider your choice.