Benchmark PostgreSQL WAL Sync Methods with pg_test_fsync

pg_test_fsync answers a question you should ask before touching wal_sync_method: which sync call is actually fastest on this disk, right now.

Run it against the filesystem behind pg_wal, compare the reported sync times, and you have real evidence instead of a guess copied from a forum post about different hardware. The default run takes about two minutes; budget more time to repeat it under representative load. It writes test data, but it never changes PostgreSQL's configuration itself.

1. Check the installed tool and the target filesystem

You need PostgreSQL's client or server utilities package and write access to a test file on the same filesystem as the cluster's pg_wal directory. This machine has PostgreSQL 16.15, packaged as Ubuntu 16.15-0ubuntu0.24.04.1, and the binary is not on this shell's PATH, so use the full path or add its directory yourself.

$ /usr/lib/postgresql/16/bin/pg_test_fsync --version
pg_test_fsync (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
$ /usr/lib/postgresql/16/bin/pg_test_fsync --help
Usage: pg_test_fsync [-f FILENAME] [-s SECS-PER-TEST]

Find the cluster's data directory using your normal PostgreSQL administration method rather than guessing from a similarly named mount. A benchmark on the wrong disk produces a lovely, useless table.

2. Choose a disposable test filename

-f or --filename picks the file that receives the test writes. Skip it and the program defaults to pg_test_fsync.out in the current directory, which is exactly how people leave a stray file sitting in a database directory or silently overwrite something already there.

Name your own file, put it in the WAL filesystem, and check the destination before you run anything. Replace /var/lib/postgresql/16/main with your actual cluster directory:

$ TEST_FILE=/var/lib/postgresql/16/main/pg_test_fsync-check.out
$ test -d "$(dirname "$TEST_FILE")/pg_wal" && echo "pg_wal directory found"
pg_wal directory found
$ test ! -e "$TEST_FILE" && echo "test file does not already exist"
test file does not already exist

Those test commands only look at the path, nothing more. If the directory is not readable or writable by your account, stop and ask the database owner for a suitable location, or run it as the service account during an approved window. Do not reach for sudo on reflex; changing ownership or permissions is not part of this test.

3. Run a short baseline

-s or --secs-per-test sets how long each test runs, five seconds by default, and the whole thing normally finishes in under two minutes. A one-second run is fine for checking permissions and output shape, but its numbers wobble more. Use the default for a baseline you plan to actually act on.

$ PG_COLOR=never /usr/lib/postgresql/16/bin/pg_test_fsync \
    --filename "$TEST_FILE" --secs-per-test 5
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.

Compare file sync methods using one 8kB write:
        open_datasync                 ... ops/sec  ... usecs/op
        fdatasync                     ... ops/sec  ... usecs/op
        fsync                         ... ops/sec  ... usecs/op
        fsync_writethrough                       n/a
        open_sync                     ... ops/sec  ... usecs/op

The actual numbers depend on filesystem, kernel, storage device, cache state and whatever else is hammering the disk at the time, so treat this run as one sample, not a fixed target. PG_COLOR=never just keeps captured logs predictable (the other values are always and auto); it changes diagnostic colour only, nothing about the benchmark itself.

4. Read the timing tables

Each method gets operations per second and average microseconds per operation. Lower usecs/op (or higher ops/sec, same thing said backwards) wins for that test. The first table covers one 8 kB write, the second two 8 kB writes, and the installed PostgreSQL 16 build also reports different open_sync write sizes, an fsync check on a non-write descriptor, and non-synchronised writes.

Do not crown a winner off one striking row. Look for a method that is consistently good across both tables, and rerun the test while the storage is under normal load. An n/a entry means that method is not available on this platform, not that it won by doing nothing.

On Linux, the output names fdatasync as the default preference, separate from the general ranking. That is a description of the platform's PostgreSQL preference, not an instruction to go and edit a config file: pg_test_fsync never touches wal_sync_method itself.

5. Repeat before making a configuration decision

Sample it at a quiet time, then again while the host is carrying its normal workload. Keep the command, PostgreSQL version, mount point and the tables you got alongside the results. If the leading methods are close, that is noise until repeated runs actually separate them:

$ for run in 1 2 3; do
    echo "--- run $run ---"
    PG_COLOR=never /usr/lib/postgresql/16/bin/pg_test_fsync \
      --filename "$TEST_FILE" --secs-per-test 5
  done

This loop reuses the same disposable path. It never touches PostgreSQL's real WAL files, but it does issue real writes and sync calls, so schedule it carefully on busy or latency-sensitive storage. No database connection needed, and usually no elevated privilege once the test path is writable.

6. Clean up and keep the result separate from PostgreSQL settings

Once you have recorded the output, remove only the test file you deliberately created, and check the variable before you do. This is irreversible for that one file, so no wildcards and no cluster directories:

$ printf 'test file: %s\n' "$TEST_FILE"
test file: /var/lib/postgresql/16/main/pg_test_fsync-check.out
$ test -f "$TEST_FILE" && rm -- "$TEST_FILE"
$ test ! -e "$TEST_FILE" && echo "test file removed"
test file removed

Only after comparing repeated measurements should you even think about changing PostgreSQL configuration, and that is a separate, service-affecting change: follow your distribution's procedure, record the old value, and have a rollback ready before you edit the server config. A faster benchmark number does not automatically mean faster transactions if WAL was never the bottleneck.