pg_test_fsync - determine fastest wal_sync_method for PostgreSQL

pg_test_sync is intended to give you a reasonable idea of what the fastest wal_sync_method is on your specific system, as well as supplying diagnostic information in the event of an identified I/O problem. However, differences shown by pg_test_fsync might not make any significant difference in real database throughput, especially since many database servers are not speed-limited by their transaction logs. pg_test_fsync reports average file sync operation time in microseconds for each wal_sync_method, which can also be used to inform efforts to optimize the value of commit_delay.

The default wal_sync_method in Linux is fdatasync.

Choose a commit_delay to half of the time reported by pg_test_fsync.

For database servers, set swappiness to 0. Use sysctl to set it interactively as quoted above, or better still, set it in the /etc/sysctl.conf file to persist when the server is restarted.