DEV Community

Cover image for Faster SSDs for PostgreSQL: From Read I/O to WAL Waits
Franck Pachot
Franck Pachot

Posted on Edited on

Faster SSDs for PostgreSQL: From Read I/O to WAL Waits

The baseline in the previous post reached 50,648 NOPM, with most active-client observations at IO:DataFileRead. To understand the next bottleneck, I first increased the storage budget, then checked the next boundary in the commit path: acknowledgment from another availability zone. The objective was to distinguish device capacity from waiting higher in the path.

The 95,000-warehouse dataset, 512 workers, 15-minute ramp-up, and 45-minute measurement remained. The primary still had 64 vCPUs, 512 GiB RAM, and 128 GiB of shared buffers.

What changed on the primary

I changed storage only on the primary because this step targeted the benchmark's foreground read I/O (IO:DataFileRead). In this HammerDB TPROC-C run, all transactions, including their SELECT statements, used the primary endpoint. The workload did not offload reads to replicas. Doing so would require changing the tested execution and checking its consistency assumptions.

I migrated the existing data to an Ultra disk, which supports up to 400K IOPS and 10 GB/s throughput at low latency. The IO limits are then determined by the non-bursting E64bds_v5 with NVMe: 294,800 IOPS and 7,800 MB/s throughput.

Original primary (previous post) Higher-I/O primary (this post)
VM E64ds_v5 E64bds_v5, NVMe
CPU / memory 64 vCPUs / 512 GiB 64 vCPUs / 512 GiB
Data disk Premium SSD v2 Ultra Disk
Capacity 20,480 GiB, or 20 TiB 20,480 GiB, or 20 TiB
Provisioned IOPS 80,000 294,800
Provisioned bandwidth 1,200 MB/s 7,800 MB/s
Filesystem ext4 ext4

Both data and WAL were on that remote disk.

The two replicas retained their Premium SSD v2 disks at 80,000 IOPS / 1,200 MB/s, as my goal was only to accelerate the reads on the primary. Each benchmark used the same-zone D64ds_v5 client with 64 vCPUs / 256 GiB. The standbys still performed I/O to receive, flush, and replay WAL. Their storage could affect synchronous commit. The local-commit diagnostic below separates their acknowledgments from the primary's own synchronization.

Keep in mind that the usable budget depends on both the disk and the VM. Ultra Disks support up to 400,000 IOPS / 10,000 MB/s, but the E64bds_v5 NVMe configuration lists 294,800 IOPS / 7,800 MB/s for Ultra Disk and Premium SSD v2 without bursting. That's sufficient for my test, as long as I don't encounter a bottleneck. Ultra Disk has been generally available since August 2019 and can be used with self-managed PostgreSQL on Azure VMs. It's now also generally available for Azure Database for PostgreSQL (the community PostgreSQL running as a managed service).

Premium SSD v2: waiting for reads

The first result covered in the previous post in this series was 50,648 NOPM (new orders per minute). During measurement, the primary data disk delivered approximately 51,130 read + 30,469 write IOPS, with 429 MiB/s reads + 412 MiB/s writes. Its guest busy-time counter was approximately 100%, and mean read/write completion times were 7.98 / 10.95 ms. The active-backend sample observations were:

Wait Share
IO:DataFileRead 79.6%
LWLock:WALWrite 10.8%
IPC:SyncRep 5.2%

That is the read-I/O-heavy baseline from part 1, with guest IOPS close to the provisioned 80,000-IOPS budget. The guest counters describe device activity. The wait percentages are shares of sampled active-client observations.

Ultra Disk: faster reads, then WALWrite

After moving the primary to Ultra, I got 55,784 NOPM, a 10.1% increase. Read/write completion times were about 0.31 / 0.23 ms. DataFileRead dropped to 2.56% of active-backend observations. But the wait mix had changed:

Wait Share
LWLock:WALWrite 83.18%
IPC:SyncRep 7.53%
IO:DataFileRead 2.56%

WALWrite was now the dominant wait, and that's why it doesn't fully benefit from the faster disks.

The Ultra primary delivered about 81,221 combined read/write IOPS and 976 MB/s, below its provisioned 294,800 IOPS / 7,800 MB/s. Guest busy time was 99.95%: I/O was almost continuous while the totals remained below the provisioned limits.

WALWrite means a backend is waiting for PostgreSQL's WAL write lock. The holder may be performing writes or waiting for synchronization. A long wait can therefore originate above device execution, even when admitted requests complete quickly.

Faster standby storage could reduce acknowledgment time, but that was a separate part of the path. Before attributing the remaining limit to cross-zone synchronization, I removed that wait from the diagnostic.

Remove the standby wait, for the investigation

The replicas were still on Premium SSD v2. I used session-level synchronous_commit set to local to remove their required acknowledgments from the commit path while retaining local durability and streaming replication. In the benchmark, the override was supplied through the workload's PGOPTIONS, so the worker connections inherited the setting.

Patroni automatic failover was separately paused for this bounded diagnostic. It's a lab to investigate what happens, not a production setup.

The result was that the throughput didn't increase, with 54,556 NOPM, versus 55,784 in the preceding run:

Active-client observation Ultra, commit on Ultra, commit local
LWLock:WALWrite 83.18% 87.29%
IPC:SyncRep 7.53% 0 observed
IO:DataFileRead 2.56% 3.93%

This change tested whether waiting for standby acknowledgments was holding back the workload. SyncRep disappeared from the samples, but throughput stayed near 55,000 NOPM and WALWrite remained dominant. Both standbys continued streaming, with a lag.

The name local explains why. It still waits for local durable WAL flushing. The WAL synchronization counter averaged 25.44 ms in the normal-HA run and 29.55 ms in the local-commit run.

The PostgreSQL commit path flushes local WAL before waiting for synchronous replication. WALWriteLock is released before the standby-acknowledgment wait reported as IPC:SyncRep. SyncRepLock protects the shared queue and state updates and is released while the backend waits on its latch. Group commit can make several transactions durable with a single synchronization.

What faster storage changed

The initial read waits fell substantially. WALWrite remained prominent after acknowledgment waits were removed. The investigation had moved from the device-read budget to what happened inside the primary's write/flush path.

These runs reused the dataset. Cache contents, dirty pages, checkpoint phase, and vacuum state evolved between tests. What we have proved is that even with the fastest disk and the lowest replication latency, we gained only 10%, far from the 8x throughput observed in HorizonDB.

HorizonDB maintains cross-zone durability using its shared WAL service, rather than waiting for a PostgreSQL standby to flush its private WAL disk. WAL replication happens outside the SQL writer, which explains why the workload achieves higher throughput than this PostgreSQL configuration can support.

I next traced the lock holder's path to understand why local synchronization averaged tens of milliseconds while device requests completed in fractions of a millisecond. Part 3 follows that delay into the Linux writeback path.

Top comments (0)