Benchmark PostgreSQL WAL Sync Methods with pg_test_fsync

You will run pg_test_fsync against the filesystem used by PostgreSQL's pg_wal, compare the reported sync times, and gather evidence for a wal_sync_method choice. Allow around two minutes with the default settings, plus time to repeat the test under representative storage load. This tool writes test data, but it does not change PostgreSQL configuration.

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 database cluster's pg_wal directory. The installed program on this machine is PostgreSQL 16.15, packaged as Ubuntu 16.15-0ubuntu0.24.04.1. The executable is not on this shell's PATH, so use its absolute path or add the directory to your own environment.

$ /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. On a running service, do not guess from a similarly named mount. A benchmark on the wrong disk can produce a neat table that says nothing about the WAL path.

Checkpoint: write down the directory that contains pg_wal. The test file must be created there, or on the same mounted filesystem.

2. Choose a disposable test filename

The -f or --filename option selects the file that receives test writes. If omitted, the program uses pg_test_fsync.out in the current directory. That default is easy to overlook: running from a database directory can leave an unexpected file behind, and an existing path may be overwritten.

Use a clearly named file in the WAL filesystem. Replace /var/lib/postgresql/16/main with the real cluster directory, and check the destination before running:

$ 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

The test commands only inspect the path. If the directory is not readable or writable by your account, stop and ask the database owner for a suitable location or run the benchmark as the service account during an approved maintenance window. Do not use sudo by reflex: changing ownership or permissions is outside this test.

Warning: this is a storage benchmark, not a read-only inspection. It writes and syncs data to TEST_FILE. Never point it at a real WAL file, a tablespace file, or a path whose contents matter.

3. Run a short baseline

The -s or --secs-per-test option sets the seconds spent on each test. The default is 5 seconds and normally completes in under two minutes. A one-second run is useful for checking permissions and output, but its figures are less stable. Use the default for a baseline you intend to discuss.

$ 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 exact rates depend on the filesystem, kernel, storage device, cache state and competing workloads. The colour environment setting makes captured logs predictable; its supported values are always, auto and never. It affects diagnostic colour only, not the benchmark.

Checkpoint: wait for the process to finish and confirm that it returns to the shell without an error. If it cannot open the file, fix the path or permissions before interpreting any result.

4. Read the timing tables

Each method is shown with operations per second and average microseconds per operation. Lower usecs/op, or equivalently higher operations per second, is faster for that test. The first table uses one 8 kB write; the second uses two 8 kB writes. The installed PostgreSQL 16 program also reports comparisons for different open_sync write sizes, a check of fsync on a non-write descriptor, and non-synchronised writes.

Do not select a method from one striking row alone. Look for a result that is consistently good across the one-write and two-write tables, then repeat the run when the storage is under a normal workload. An n/a entry means that method is not available or applicable on this platform; it is not a zero-time winner.

On Linux, the output identifies fdatasync as the default preference outside the general ordering. That is a description of the platform's PostgreSQL preference, not a command to edit a configuration file. pg_test_fsync never changes wal_sync_method for you.

5. Repeat before making a configuration decision

Run several samples at a quiet time and several while the host carries the workload that matters. Keep the command, PostgreSQL version, mount point and observed tables with the results. If the leading methods are close, treat the difference as noise until repeated measurements 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 does not touch PostgreSQL's WAL files, but it does issue real writes and sync calls, so schedule it with care on busy or latency-sensitive storage. Running it requires no database connection and normally needs no elevated privilege once the test path is writable.

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

After recording the output, remove only the test file you deliberately created. Check the variable and path first. The removal is irreversible for that file, so do not substitute a wildcard or a cluster directory.

$ 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 consider a PostgreSQL configuration change. That is a separate, service-affecting operation: follow your distribution's PostgreSQL procedure, record the previous value, and arrange a rollback before editing the server configuration. A faster benchmark result may not improve transaction throughput when WAL is not the limiting resource.