Design history
Current pgbench_ro Workload: V2 Subsystem Rebalance
The v2 redesign spreads transaction work across PostgreSQLPostgreSQLAn open-source object-relational database system, developed by a global community and descended from the POSTGRES project at UC Berkeley in 1986. It uses and extends SQL, with transactions, many index types, extensions, and JSON data.PostgreSQL docsGlossary executor, access
method, and data type subsystems so no single block dominates. The schema adds
full-text search, arrays, TOAST data, BRIN, and related access paths. The
transaction in ro_cpu_txn.sql uses eight tagged blocks:
| Block | Workload area | Approx. ms at scale 1 |
|---|---|---|
q_idx | B-tree index scan, nested loop, window aggregate | 0.1 |
q_hashjoin | Hash join and hash aggregate | 24 |
q_regex | Regex and md5() | 15 |
q_fts | Full-text search using tsvector, tsquery, ts_rank, and GIN | 12 |
q_array | Array containment using GIN and a bounded join | 8 |
q_stats | Ordered-set and statistical aggregates | 13 |
q_toast | Out-of-line TOAST fetch and decompression | 4 |
q_seqscan | Sequential scan and aggregate | 2 |
The maximum single-block share fell from about 82% in the old regex block to
about 30 to 34% in q_hashjoin. Apart from q_idx at 0.1 ms, the remaining
blocks measured in a narrower band of 2 to 15 ms. The query plans and block
timings were checked using the same local Docker profiling method used to
investigate v1.
Calibration Findings
Calibration found the following:
- The first
q_arrayversion joined GIN-matched products to all 750,000order_itemrows. PostgreSQL chose the same sequential scan and hash join asq_hashjoin, duplicating its cost rather than isolating the array/GIN path. Bounding the join to an indexedorder_idslice corrected this. New blocks should be checked withEXPLAIN; the presence of an index does not guarantee that the planner uses it. q_hashjoinhas a floor of about 20-24 ms. Theorder_itemprobe side must be scanned even when the time window narrows, so reducing the window from 22,400 seconds to 1,500 seconds only reduced the measured time from about 51 ms to 24 ms. This is retained as a deliberate large-scan/hash-join case.- PostgreSQL selected a Merge Join for the outer product join in
q_hashjoinin addition to its Hash Join. This was observed inEXPLAIN, not forced. - The time-window predicate may use BRIN or a B-tree skip scan over
(status, ordered_at), depending on window width. The single-statement design prevents per-block planner GUCs without affecting the other blocks, so the documentation records either observed path rather than promising one. - The PL/pgSQL timed-loop harnessharnessThe code that runs a benchmark tool. It prepares the inputs, starts the tool with fixed settings, often several times or in parallel, checks and collects the tool's output, and prints the results in one format. Its settings, not the tool's defaults, decide what the benchmark measures.WikipediaGlossary in
profile_v2_breakdown.sqlswitches to a generic cached plan after five calls. A variableLIMITwas then estimated poorly, causingq_arrayto use the unbounded full-table plan and take 271 ms instead of about 8 ms.SET plan_cache_mode = force_custom_planfixes this harness artifact. A directpgbenchpgbenchThe benchmark tool shipped with PostgreSQL. It runs a SQL script repeatedly over many concurrent client sessions, and reports the throughput in transactions per second and the latency. Its built-in script is modeled on TPC-B, and custom scripts can define any other workload.PostgreSQL docsGlossary run without-M preparedshowed no such regression. - PostgreSQL has no
round(double precision, integer)overload; the relevant values must be cast tonumericbefore rounding.percentile_cont,stddev_samp, andcorrlikewise needed explicit numeric casts for theq_statsdigest.
Validation
Real pgbench runs against the redesigned schema and script used local Docker
postgres:18, jit=off, work_mem=64MB, and
max_parallel_workers_per_gather=0:
| Run | Result |
|---|---|
-c 1 -T 15 -D scale=1 | 179 transactions, 0 failed, 84.1 ms average latency |
-c 4 -j 4 -T 20 -D scale=1 | 899 transactions, 0 failed, 89.3 ms average latency |
-c 1 -T 15 -D scale=4 | 80 transactions, 0 failed, 187.8 ms average latency; growth is sub-linear because the q_hashjoin floor does not scale with -D scale |
Recalibration Procedure
Run the setup and profiling scripts against a fresh PostgreSQL 18 database, then
run the transaction directly after adjusting its block widths. Run these
commands from images/benchmark-pgbench-postgres/ in a checkout of this
repository; profile_v2_breakdown.sql is a development helper that is not in
the published image:
docker run -d --name ro-cpu-cal -e POSTGRES_PASSWORD=bench -e POSTGRES_DB=bench \
-v "$PWD:/sql:ro" postgres:18 -c shared_buffers=1GB -c jit=off \
-c work_mem=64MB -c max_parallel_workers_per_gather=0
docker exec -e PGPASSWORD=bench ro-cpu-cal psql -U postgres -d bench -f /sql/ro_cpu_setup.sql
docker exec -e PGPASSWORD=bench ro-cpu-cal psql -U postgres -d bench -f /sql/profile_v2_breakdown.sql
# Adjust widths in ro_cpu_txn.sql, repeat until no block dominates, then:
docker exec -e PGPASSWORD=bench ro-cpu-cal pgbench -h localhost -U postgres -d bench \
-n -M prepared -c 1 -T 20 -D scale=1 -f /sql/ro_cpu_txn.sql
Re-run profile_v2_breakdown.sql after any schema/query change, or on
significantly different hardware, to confirm no block has drifted back into
dominance.
Initial Custom Workload: V1
Plain pgbench -S (one primary-key SELECT) was too cheap per transaction to
measure CPUCPU: Central Processing UnitThe general-purpose processor of a computer, which runs the operating system and programs. A CPU has one or more physical cores, and with SMT each core runs two or more hardware threads.WikipediaGlossary behavior under network latency. With netemnetem: Network EmulatorA Linux traffic control queueing discipline that adds delay, jitter, packet loss, duplication or reordering to the packets leaving a network interface. It is used to test how software behaves on slow or unreliable networks.SRT cookbookGlossary (Network
Emulator)
simulating RTTRTT: Round-Trip TimeThe time a network packet takes to reach its destination plus the time its response takes to get back. It sets a lower bound on the latency of every request-response exchange, and grows with the distance between the endpoints.WikipediaGlossary (Round-Trip Time), its throughput fell by about 98% at one
connection with an additional 5 ms of one-way delay, while the CPU-heavy
transaction barely changed.
The first custom script used a cached, multi-query transaction sized for 100-130
ms of server CPU time at -c 1, with a roughly 170 MB schema intended to fit in
shared_buffers. It had four blocks:
- q1: One customer's recent paid/shipped/done orders, joined and processed
with window functions (index scan, nested loop, and
WindowAgg). - q2: A region/plan cohort joined to orders and line items, grouped by
status (broader join and
GroupAggregate). - q3: A contiguous 12,000-row
order_idslice checked against two regular expressions and hashed withmd5()(regex, JSONJSON: JavaScript Object NotationA text format for structured data, built from objects (key-value pairs), arrays, strings, numbers, booleans, and null. Most languages and tools read and write it, which makes it a common format for API responses and benchmark output.JSONGlossary, and hashing). - q4: The top buyers of one product (
GROUP BY,ORDER BY, andLIMIT).
Profiling a fresh postgres:18 container with jit=off, EXPLAIN (ANALYZE, BUFFERS), and per-block clock_timestamp() loops identified these issues:
| Finding | Evidence |
|---|---|
q3's regex and md5() work took about 70-82% of the transaction. | Timings were q1 0.09 ms, q2 9.8 ms, q3 49.1 ms, and q4 0.10 ms (59.6 ms total); regex alone took 42.3 ms in q3. |
| The script only exercised B-tree, nested-loop, and regex work. | It did not use GIN, BRIN, hash or merge joins, full-text search, arrays, TOAST, or ordered-set aggregates. The local PostgreSQL source was checked under src/backend/access/, src/backend/executor/nodeX.c, and src/backend/utils/adt/. |
| A data-generation bug made all five orders for a customer share one status. | With 50,000 customers, a multiple of five, status = 1 + (g % 5) assigned each customer's orders the same residue. status IN ('paid','shipped','done') returned zero rows for about 40% of sampled customers and five for the rest, making q1 inconsistent. |
q1's LIMIT 40 did not limit results. | The generated dataset contained only five orders per customer. |
q2 limited a cohort without ORDER BY. | The selected rows could vary across plans and runs, making digests and debugging less reproducible. |
| Several indexes did not match the query's actual hot paths. | The attrs->>'tier' and email indexes were unused, while q2 filtered on profile->>'plan' without an index. |
| Generated data was nearly uniform rather than ZipfianZipfianA skewed probability distribution, named after Zipf's law, in which an item's frequency is inversely proportional to its rank, so a few items are accessed far more often than the rest. Benchmarks use it to model real-world hot spots, such as popular products or users.WikipediaGlossary. | The g % k arithmetic remained a known simplification in v2; see Uniform Data. |
Experiments That Informed the Design
Dataset Comparisons
sysbenchsysbenchA scriptable, multi-threaded benchmark tool built on LuaJIT. Its tests cover CPU, memory, file I/O, threads, and mutexes, and its Lua scripts run transactional database workloads against MySQL and PostgreSQL. It reports rates and latency percentiles.GitHubGlossary, HammerDBHammerDBAn open-source database benchmark engine. It runs standardized workloads, such as TPROC-C, with the same test definitions on SQL Server, Db2, Oracle, PostgreSQL, MariaDB, and MySQL, so that results are comparable across database systems. Runs can be scripted and repeated automatically.HammerDBGlossary TPROC-C, BenchBaseBenchBaseA framework that benchmarks SQL databases, the successor to OLTPBench. It runs workloads such as YCSB and a Wikipedia workload against any relational database with a JDBC (Java Database Connectivity) driver, and collects per-transaction-type latency and throughput logs.GitHubGlossary (Wikipedia read-only and YCSB datasets),
and pgbench were compared using regular block storage and tmpfs, with
baseline and host-tuned PostgreSQL configurations. The comparison showed the
following:
tmpfsimproved write-heavy OLTP results by about 10-25% or more, indicating that those suites were measuring storage and WAL behavior as well as server performance.tmpfswas not available for DBaaS comparisons. Warehouse and scale-factor sizing also did not cover the tested range from 1 vCPUvCPU: virtual CPUA CPU of a virtual machine, the unit in which cloud vendors sell compute. It is usually one hardware thread of a physical core with SMT, so two vCPUs may share one physical core.WikipediaGlossary to hundreds of vCPUs.
PostgreSQL Configuration Sweep
Twenty-one experiments on a 32 vCPU host found a winning configuration with
about 20% more throughput than the baseline. The combination used modest
work_mem and io_uring, small WAL buffers, parallel gather disabled, and
right-sized shared_buffers. The results motivated per-host tuning for IaaS and
provider-managed tuning for DBaaS.
Measurement Duration
Two experiments checked whether measuring longer than 5 minutes changes mean
throughput or only its variance. Each tested 5-, 10-, 15-, and 30-minute
measurement windows after a 2-minute warmup, with five independent trials per
window on an interleaved schedule and a fresh database load for every trial.
PostgreSQL 18 ran with pgtunepgtuneA web tool that suggests PostgreSQL settings, such as shared_buffers and work_mem, from the memory, CPU count, storage type and workload of a server. Its suggestions are a starting point for tuning rather than an optimum.pgtuneGlossary form defaults.
BenchBase Wikipedia, with as many terminals as vCPUs, gave the following mean TPM and CV per window:
| Server type | 5 min | 10 min | 15 min | 30 min |
|---|---|---|---|---|
GCP t2d-standard-16 | 580,228 (0.50%) | 578,237 (0.49%) | 576,567 (0.50%) | 576,145 (0.42%) |
GCP t2d-standard-32 | 690,881 (0.90%) | 691,927 (2.48%) | 677,111 (3.60%) | 686,730 (2.25%) |
GCP e2-highmem-16 | 260,997 (2.17%) | 261,574 (3.65%) | 264,425 (1.67%) | 260,321 (3.86%) |
pgbench -S at scale 65 with 60 clients on GCP t2d-standard-60 gave the
following:
| 5 min | 10 min | 15 min | 30 min |
|---|---|---|---|
| 58,070,697 (0.47%) | 58,738,095 (1.06%) | 58,639,217 (0.75%) | 58,366,021 (0.78%) |
The findings were the following:
- No window shifted the mean by more than 2% from the 5-minute result; the largest shift (-1.99%) was partly one low outlier.
- Longer windows did not tighten the CV. The 5-minute window often had the smallest CV, because occasional dips from load, the OSOS: operating systemThe software that manages a computer's hardware and runs programs on it, such as Linux or Windows. A cloud server runs the OS of the image it was started from.Glossary, or noisy neighbors hurt a long average as much as a short one.
- The budget is better spent on more server types or repeated short trials than on longer windows, so the measurement window stayed at 5 minutes.
Note that these experiments used the previously considered BenchBase Wikipedia
and pgbench -S, and not the current pgbench_ro workload.
Latency and Pipelining
The experiments measured pgbench -S under induced network delay, then used a
custom sliding-window --pipeline-depth mode in a pgbench fork. Pipelining
improved RTT-bound scripts by about 10x at +5 ms delay, but added nothing to a
CPU-bound transaction at low concurrency and reduced throughput at high
concurrency. The resulting design made the transaction heavier rather than
pipelining a light transaction: serial mode, fixed {1, V/2, V, 2·V}
concurrency, and a TPM score.
The outcome is a custom, cached, CPU-heavy pgbench_ro workload; the built-in
pgbench_tpcb workload remains available as a conventional TPC-B-like
reference.