DEV Community

Cover image for A 9-TiB OLTP Benchmark on PostgreSQL
Franck Pachot
Franck Pachot

Posted on Edited on

A 9-TiB OLTP Benchmark on PostgreSQL

At Microsoft Ignite, HorizonDB was announced to be 8x faster than traditional PostgreSQL, based on an I/O-bound HammerDB TPROC-C workload that showed 427,770 NOPM without specific tuning, 8x the 52,167 NOPM measured with self-managed PostgreSQL on the same compute.

I observed the same throughput on HorizonDB and reproduced the PostgreSQL side with a tuned HA configuration: 128 GB of shared buffers on huge pages, two standbys, and synchronous commit to at least one. The result was 50,648 NOPM, very close to the published number. But the interesting question is not the number itself. What is PostgreSQL waiting for at that throughput, and what happens when that constraint is removed? This post identifies the first bottleneck. The following three remove the bottlenecks one by one until the limit moves inside PostgreSQL itself.

The workload

Here is the definition of the benchmark workload I've run:

Setting Tested value
HammerDB 6.1.1, direct CLI
Workload PostgreSQL TPROC-C, function-based driver
Dataset 95,000 warehouses, nonpartitioned
Reported size after loading About 8.92 TiB
Workload concurrency 512 virtual users, plus the monitor
Ramp-up / measurement 15 / 45 minutes
Warehouse selection All warehouses enabled
Keying and thinking time Disabled
Connection pooling / time profiling Disabled
Driver-requested vacuum Disabled, while server autovacuum remained enabled
Transaction counter Timestamped samples every 10 seconds

HammerDB reports new orders per minute (NOPM) and PostgreSQL transactions per minute (TPM). These are the relevant driver choices, apart from connection details:

dbset db pg
dbset bm TPC-C
diset tpcc pg_count_ware 95000
diset tpcc pg_storedprocs false
diset tpcc pg_partition false
diset tpcc pg_driver timed
diset tpcc pg_rampup 15
diset tpcc pg_duration 45
diset tpcc pg_allwarehouse true
diset tpcc pg_keyandthink false
diset tpcc pg_connect_pool false
diset tpcc pg_timeprofile false
diset tpcc pg_vacuum false
vuset vu 512
Enter fullscreen mode Exit fullscreen mode

The database was loaded before the timed test (with some pain, as described in a previous blog post, because loading nine terabytes takes a long time and HammerDB can't resume a failed load).

A dataset larger than the caches

The primary had 512 GiB of RAM, including 128 GiB of shared buffers. Even the machine's total memory was a small fraction of the database size. This is how we define an I/O bound workload: the working set is larger than any cache. All warehouses were enabled, and the measured read waits and sustained physical I/O showed frequent reads beyond the caches.

The baseline machines and durability

The initial deployment used three PostgreSQL VMs across three Azure availability zones:

  • Each database VM: E64ds_v5, 64 vCPUs / 512 GiB RAM.
  • Each data disk: 20,480 GiB Premium SSD v2, provisioned at 80,000 IOPS / 1,200 MB/s.
  • Data and WAL: the same ext4 filesystem.
  • Client in the primary's zone: D64ds_v5, 64 vCPUs / 256 GiB RAM.
  • PostgreSQL 17.11, Patroni 4.1.5, Ubuntu 24.04.
  • Three separate etcd members for the distributed configuration store.

This configuration used synchronous_commit=on, fsync=on and full_page_writes=on. A commit required local WAL synchronization and durable WAL acknowledgment from at least one of the two cross-zone standbys. Azure's torn-write prevention requires a supported NVMe controller, kernel and filesystem, with the application issuing aligned atomic direct writes. This run used PostgreSQL 17's ordinary buffered writes, so full-page logging remained enabled for torn-page recovery.

The standby acknowledgment confirms a flush, not replay. Network transport, standby storage, and acknowledgment processing can all contribute to the wait.

Continuous WAL archiving was disabled for this lab.

The PostgreSQL profile

I used the following PostgreSQL profile for the baseline and the later tests. The commit-mode overrides are described in the posts where they apply:

Area Important settings
Memory shared_buffers=128GB, work_mem=16MB, effective_cache_size=352GB
Shared-memory backing Explicit 2-MiB huge pages, huge_pages=on
Connections max_connections=640
WAL wal_compression=zstd, wal_buffers=64MB, max_wal_size=64GB
Synchronization wal_sync_method=fdatasync, fsync=on, full_page_writes=on
Checkpoints checkpoint_timeout=30min, checkpoint_completion_target=0.9
Planner / I/O random_page_cost=1.5, effective_io_concurrency=64, jit=off
Query parallelism max_parallel_workers_per_gather=0
Autovacuum 8 workers, 10-second naptime, vacuum scale factor 0.01
Autovacuum cost control Limit 3000, delay 2 ms
Monitoring track_io_timing=on, track_wal_io_timing=on

effective_cache_size is a planner estimate. WAL volume can trigger checkpoints before the 30-minute timeout.

Result: 50,648 NOPM (New Orders per Minute)

The measured interval ran from November 6, 2026, 23:21:13 UTC to November 7, 00:06:15 UTC. The result message was:

TEST RESULT : System achieved 50648 NOPM from 116618 PostgreSQL TPM
Enter fullscreen mode Exit fullscreen mode

To observe active backends, the collector used grouped pg_stat_activity data:

SELECT count(*) AS sessions,
       wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY sessions DESC;
Enter fullscreen mode Exit fullscreen mode

Repeated measured-window samples gave:

Wait Active-client observation share
IO:DataFileRead 79.6%
LWLock:WALWrite 10.8%
IPC:SyncRep 5.2%

DataFileRead means PostgreSQL is waiting for a relation-file read, which may be served by the operating-system cache or the device. The guest disk counters confirmed substantial physical read activity during this run:

Primary data-disk observation Value
Read / write IOPS Approximately 51,130 / 30,469
Read / write bandwidth Approximately 429 / 412 MiB/s
Mean read / write request completion 7.98 / 10.95 ms
Guest busy time Approximately 100%

Delivered IOPS were near the provisioned disk budget. Together, the read waits, I/O rate, and request latency put the device-read path first in the investigation.

Next: change the storage constraint

HorizonDB combines shared buffers and local NVMe caching with a distributed page-storage fleet. Cache misses are served by this fleet, rather than the single provisioned data disk used in this test. This explains why the workload achieves higher throughput than this PostgreSQL configuration can support.

On PostgreSQL, increasing the storage budget would let me observe how the remaining waits changed. LWLock:WALWrite already accounted for 10.8% of active-client observations and could become the next bottleneck as read waits decreased.

In part 2, Faster SSDs for PostgreSQL: From Read I/O to WAL Waits
, I move the primary to Ultra SSD and then remove standby acknowledgment waiting to examine the primary's own path. In this series, the goal is to identify what limits PostgreSQL throughput as these constraints are reduced or bypassed. The investigation starts with disk reads, checks remote commit acknowledgments, follows local synchronization into the OS, and ends with shared-page coordination inside PostgreSQL.

Top comments (0)