Skip to main content

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:

BlockWorkload areaApprox. ms at scale 1
q_idxB-tree index scan, nested loop, window aggregate0.1
q_hashjoinHash join and hash aggregate24
q_regexRegex and md5()15
q_ftsFull-text search using tsvector, tsquery, ts_rank, and GIN12
q_arrayArray containment using GIN and a bounded join8
q_statsOrdered-set and statistical aggregates13
q_toastOut-of-line TOAST fetch and decompression4
q_seqscanSequential scan and aggregate2

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_array version joined GIN-matched products to all 750,000 order_item rows. PostgreSQL chose the same sequential scan and hash join as q_hashjoin, duplicating its cost rather than isolating the array/GIN path. Bounding the join to an indexed order_id slice corrected this. New blocks should be checked with EXPLAIN; the presence of an index does not guarantee that the planner uses it.
  • q_hashjoin has a floor of about 20-24 ms. The order_item probe 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_hashjoin in addition to its Hash Join. This was observed in EXPLAIN, 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.sql switches to a generic cached plan after five calls. A variable LIMIT was then estimated poorly, causing q_array to use the unbounded full-table plan and take 271 ms instead of about 8 ms. SET plan_cache_mode = force_custom_plan fixes this harness artifact. A direct pgbenchpgbenchThe 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 prepared showed no such regression.
  • PostgreSQL has no round(double precision, integer) overload; the relevant values must be cast to numeric before rounding. percentile_cont, stddev_samp, and corr likewise needed explicit numeric casts for the q_stats digest.

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:

RunResult
-c 1 -T 15 -D scale=1179 transactions, 0 failed, 84.1 ms average latency
-c 4 -j 4 -T 20 -D scale=1899 transactions, 0 failed, 89.3 ms average latency
-c 1 -T 15 -D scale=480 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_id slice checked against two regular expressions and hashed with md5() (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, and LIMIT).

Profiling a fresh postgres:18 container with jit=off, EXPLAIN (ANALYZE, BUFFERS), and per-block clock_timestamp() loops identified these issues:

FindingEvidence
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:

  • tmpfs improved 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.
  • tmpfs was 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 type5 min10 min15 min30 min
GCP t2d-standard-16580,228 (0.50%)578,237 (0.49%)576,567 (0.50%)576,145 (0.42%)
GCP t2d-standard-32690,881 (0.90%)691,927 (2.48%)677,111 (3.60%)686,730 (2.25%)
GCP e2-highmem-16260,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 min10 min15 min30 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.