Pgbench-modernization-2026

From PostgreSQL wiki
Jump to navigationJump to search

Modernizing pgbench: Architectural Rationale, Design, and Standard Workload Suites

Author: Hannu Krosing (hannuk@google.com)
Target: PostgreSQL Core (src/bin/pgbench, src/common, contrib/pgbench, doc/src/sgml)
Patch Series: v5-0001 through v5-0022 (22 commits against master)
Date: October 2026

1. Executive Summary & Motivation

pgbench is PostgreSQL's built-in, zero-dependency benchmarking tool. First introduced in 1999 based on the Wisconsin Benchmark and the classic TPC-B specification, pgbench has served as the universal standard for PostgreSQL performance evaluation for over 25 years. It is distributed with every official binary package, runs identically on Linux, macOS, and Windows, and requires nothing more than libpq.

However, the database engineering landscape has fundamentally transformed:

  1. Modern Workload Complexity: Real-world enterprise systems are rarely characterized by simple single-row primary-key lookups and updates on a single table (pgbench_accounts). Today's systems demand complex transactional workflows (multi-table order entry, dynamic inventory allocations, intentional rollbacks, think time pacing), analytical query streams (multi-table joins, window functions, date rollups), and hybrid transactional/analytical processing (HTAP).
  2. Proliferation of Fragile Third-Party Tools: Because legacy pgbench could not express these workloads, the PostgreSQL community and cloud providers have been forced to rely on external third-party tools (such as HammerDB, sysbench, and BenchmarkSQL). These tools introduce heavy runtime dependencies (Tcl/Tk, Python, Java, JDBC), lack native integration with PostgreSQL's latest wire protocol features, and cannot be run inside PostgreSQL's standard automated regression testing infrastructure (check-world).
  3. Data Loading Bottlenecks: External tools typically generate synthetic test data either on client disks using legacy C binaries (dbgen, dsdgen) or by sending millions of individual INSERT statements over client-server network sockets. This makes initializing terabyte-scale benchmarks excruciatingly slow.

This 22-patch series modernizes pgbench from a monolithic microbenchmark harness into a modular, enterprise-grade workload execution engine. It equips PostgreSQL with native, ready-to-run bundled benchmark suites (pgbench-b, pgbench-c, pgbench-e, pgbench-h, pgbench-ds, and pgbench-htap), rapid in-database parallel data generation, dynamic script control flow, realistic user pacing, heterogeneous client groups, and comprehensive SLA percentile reporting—all while maintaining 100% backward compatibility and zero external dependencies.

2. Limitations of Legacy pgbench

Prior to this patch series, several architectural limitations prevented pgbench from running industry-standard benchmarks:

Subsystem Legacy pgbench Limitation Modernized pgbench Capability
Code Structure Monolithic pgbench.c (>7,400 lines) with tight coupling between UI, state machine, script parsing, polling, and stats. Modular architecture partitioned into 7 dedicated modules (stats, variable, poller, script, commands, init, engine).
Type System Scalar types only: boolean, integer (int64), double precision (float64). No strings, dates, or arrays. Extended PgBenchValue: native text, timestamp, and array types with indexing and manipulation functions.
Synthetic Generators Basic random functions only (random(), random_gaussian(), random_exponential(), random_zipfian()). Full standard suite: nurand(), c_last(), random_astring(), random_nstring(), format_string(), date_add(), int_to_date().
Data Generation Hardcoded C generator for 4 pgbench_* tables. No server-side generation capability for custom schemas. contrib/pgbench SQL extension exposing all generators to SCHEMA pgbench, enabling parallel generate_series() loads.
Control Flow Linear execution only (with basic \if conditional). No looping constructs, no error catching. Structured loops (\for, \while, \break, \continue) and exception handling (\try ... \catch, \expect_error).
Result Inspection \gset limited to single-row scalar columns. No execution metadata. \gset_meta (captures SQLSTATE, rows, latency) and multi-row array capture (\aset, array-aware \gset).
Client Pacing Static literal sleep only (\sleep 10 ms). Dynamic expressions in \sleep, plus standard-compliant \keying_time and \think_time.
Workload Mix Homogeneous client pool running identical script mixtures. Heterogeneous client groups (--group) with independent thread/client counts, rates, weights, and sequential streams.
Metrics & Reporting Global average latency and aggregate TPS only. No percentile distributions. High Dynamic Range (HDR) latency percentiles (p50/p90/p95/p99/p99.9), automated --sla validation, tpmC/QphH, and --json-report.

3. Architecture of the 22-Patch Series

The patch series is organized into five cohesive, self-contained stages: 

[ Patches 0001 - 0012 ] Part 1: Modular Refactoring
       │
       ▼
[ Patches 0013 - 0014 ] Part 2: Code Extraction & contrib/pgbench SQL Extension
       │
       ▼
[ Patches 0015 - 0019 ] Part 3: Advanced Types, Functions, Control Flow & Pacing
       │
       ▼
[ Patches 0020 - 0021 ] Part 4: Heterogeneous Client Groups, Percentiles, SLAs & JSON
       │
       ▼
[ Patch 0022 ]          Part 5: Bundled Workload Suites (pgbench-b/c/e/h/ds/htap)

Part 1: Modular Refactoring (Patches 0001 - 0012)

pgbench.c had grown to over 7,450 lines, mixing distinct responsibilities into a single compilation unit with extensive global state. Patches 0001–0007 extract these concerns into cleanly separated source files without changing existing behavior or public APIs:

  • stats.c / stats.h: Encapsulates StatsData, transaction latency tallies, rate limiting, and log file output.
  • variable.c / variable.h: Scoped variable stores (VariableSpace), symbol lookup, and value formatting.
  • poller.c / poller.h: Encapsulates socket set polling abstractions across select and poll.
  • script.c / script.h: Script parsing, abstract syntax tree (AST) construction, and expression evaluation.
  • commands.c / commands.h: Meta-command execution, SQL dispatch, and prepared statement management.
  • init.c / init.h: Database initialization (-i), schema creation, and built-in table data generators.
  • engine.c / engine.h: Multi-threaded client state machine, connection management, and asynchronous event loops.

Patches 0008–0012 decouple the remaining global state:

  • mergeStats() for modular statistics aggregation.
  • Structured VariableSpace objects instead of global variable arrays.
  • Pluggable meta-command dispatch table mapping command names to handler callbacks.

Part 2: Code Extraction & In-Database Functions (Patches 0013 - 0014)

  • src/common/pgbench_funcs.c: Extracts pgbench's mathematical and pseudo-random generators (Gaussian, Exponential, Zipfian, Permute, MurmurHash64) into PostgreSQL's shared common library. This allows both frontend client binaries and backend server processes to utilize identical mathematical algorithms.
  • contrib/pgbench Extension: A server-side extension exposing all generator functions directly to SQL under SCHEMA pgbench. This enables synthetic data generation directly inside the PostgreSQL engine:
-- Generate 1,000,000 synthetic customer rows in seconds using parallel SQL
INSERT INTO customer (c_id, c_last, c_balance)
SELECT i,
       pgbench.c_last(pgbench.nurand(255, 0, 999)),
       pgbench.random(-50000, 50000) / 100.0
  FROM generate_series(1, 1000000) AS i;

Part 3: Types, Functions, Control Flow & Pacing (Patches 0015 - 0019)

  • Extended Value Types: Adds PGBT_TEXT (string manipulation, formatting), PGBT_TIMESTAMP (ISO 8601 timestamps, date arithmetic), and PGBT_ARRAY (dynamic arrays, subscripting, array_append, array_length, array_generate).
  • Benchmark Function Library:
    • nurand(A, s, x, y): TPC-C Non-Uniform Random distribution.
    • c_last(num): Syllable-based customer last name generator (BAR, OUGHT, ABLE, etc.).
    • random_astring(min, max) / random_nstring(min, max): Alphanumeric and numeric string generation.
    • format_string(fmt, ...): Printf-style string interpolation.
    • date_add(ts, amt, unit) / int_to_date(int): Date arithmetic supporting days, months, and years.
  • Script Control Flow:
    • \for <start> .. <end> [BY <step>] and \while <expr>: Iterative script loops.
    • \try ... \catch [<sqlstate>] ... \endtry: Client-side exception handling, allowing scripts to catch simulated rollbacks without failing the benchmark run.
  • Result Set Inspection:
    • \gset_meta <prefix>: Captures query execution status, SQLSTATE, rows affected, and backend execution time.
    • \aset and array-aware \gset: Maps multi-row query results into client-side array variables.
  • Client Pacing:
    • Dynamic expressions in \sleep (e.g. \sleep random_exponential(1, 1000, 10) ms).
    • \keying_time and \think_time: Realistic transaction pacing compliant with standard benchmark specifications.

Part 4: Heterogeneous Groups, Latency Percentiles & Reporting (Patches 0020 - 0021)

  • Heterogeneous Client Groups (--group):

Allows a single pgbench process to orchestrate multiple distinct client pools with independent concurrency, weights, and pacing: 

pgbench -j 4 -T 300 \
  --group name=oltp,clients=20,file=pgbench_htap_oltp.sql \
  --group name=olap,clients=2,file=pgbench_htap_olap_q1.sql,file=pgbench_htap_olap_q3.sql,sequential=true
  • High Dynamic Range (HDR) Latency Tracking: Maintains microsecond-resolution histogram buckets to report accurate latency percentiles (p50, p90, p95, p99, p99.9) without memory bloat.
  • Automated SLA Validation (--sla): Enforces service level agreements (e.g. --sla target=p99,threshold=50ms), exiting with code 3 if performance targets are breached.
  • Standard Metrics & JSON Reporting: Calculates standard metrics (tpmC, QphH@SF) and emits structured results via --json-report=<path> for automated CI/CD integration.

Part 5: Bundled Workload Suites (Patch 0022)

Delivers six complete, ready-to-run workload suites bundled under src/bin/pgbench/workloads/:

  1. pgbench-b (Banking / OLTP): 4 tables (pgbench_branches, pgbench_tellers, pgbench_accounts, pgbench_history), stored procedures (pgbench_b_transaction, pgbench_b_simple_update), parallel in-database loader.
  2. pgbench-c (OLTP, inspired by TPC-C): 9 tables, 5 transaction procedures (new_order, payment, order_status, delivery, stock_level), NURand distributions, keying and think times.
  3. pgbench-e (OLTP, inspired by TPC-E): 16 tables, 10 transaction procedures, Zipfian customer and security distributions, market feed updates.
  4. pgbench-h (OLAP, inspired by TPC-H): 8 tables, 22 complex analytical queries, RF1/RF2 data refresh streams, composite QphH metric.
  5. pgbench-ds (Decision Support, inspired by TPC-DS): Multi-channel retail schema (store, catalog, web), cross-channel reporting queries, maintenance tasks.
  6. pgbench-htap (Hybrid OLTP + OLAP, inspired by CH-benCHmark): Unified schema executing concurrent pgbench-c transactional mixes and multi-table analytical joins.

4. In-Database Parallel Data Generation

Traditional benchmark data loading is notoriously slow because it relies on single-threaded client programs generating flat files or pushing individual rows across a network socket: 

[ Traditional Loader ] ──( millions of single INSERTs )──► [ Database Engine ]  (Slow, hours to days)

Modernized pgbench leverages contrib/pgbench to perform in-database parallel data generation: 

                               ┌── Worker 0 (client_id = 0) ──► generate_series(..., 2) ──┐
[ pgbench -c 2 -j 2 -t 1 ] ────┼                                                          ├──► [ PostgreSQL Storage Engine ]
                               └── Worker 1 (client_id = 1) ──► generate_series(..., 2) ──┘
                                             ▲
                                             │ Calling pgbench.random_zipfian(),
                                             │ pgbench.nurand(), pgbench.c_last()

Every workload suite divides table generation across workers using :client_id and :num_workers: 

-- Partitioned parallel loading in pgbench_c_init.sql
INSERT INTO customer (c_w_id, c_d_id, c_id, c_discount, c_last, ...)
SELECT w, d, c,
       pgbench.random(0, 5000) / 10000.0,
       pgbench.c_last(pgbench.nurand(255, 0, 999)), ...
  FROM generate_series(1, :scale) AS w,
       generate_series(1, 10) AS d,
       generate_series(1, 3000) AS c
 WHERE (w - 1) % :num_workers = :client_id;

This architecture achieves linear scaling with server CPU cores and eliminates client-server network serialization bottlenecks.

5. Trademark Compliance and Fair Use Rationale

The Transaction Processing Performance Council (TPC) holds registered trademarks on TPC, TPC-C, TPC-E, TPC-H, and TPC-DS. The TPC policies strictly govern how benchmarks may be named and published:

  1. Official Audited Results: Only results audited and certified by an official TPC-certified auditor and reviewed by the TPC council may use the official marks TPC-C, TPC-H, etc.
  2. Derived and Inspired Workloads: The TPC policies require that any benchmark derived from or inspired by a TPC specification must clearly state that it is not an official TPC benchmark and cannot be compared to official results.

To respect TPC intellectual property rights while providing PostgreSQL developers with rigorous tools, the suites in this series strictly adhere to the following rules:

  • Suite Naming: The suites are named pgbench-b, pgbench-c, pgbench-e, pgbench-h, pgbench-ds, and pgbench-htap.
  • Database Object Naming: All database entities use non-infringing prefixes (pgbench_b_*, pgbench_c_*, pgbench_e_*, pgbench_h_*, pgbench_ds_*, htap_*).
  • Prominent Documentation: Every workload directory and documentation file includes a prominent disclaimer explaining that the workloads are community engineering tools inspired by industry specifications, designed for internal tuning and regression testing, and are not official council-audited benchmarks.

6. Comparative Feature Matrix

Capability PostgreSQL pgbench (Legacy) Modernized pgbench (Series v5) Sysbench HammerDB BenchmarkSQL
Zero External Dependencies Yes Yes (C only) No (Lua, MySQL libs) No (Tcl/Tk) No (Java, JDBC)
Shipped in Core PostgreSQL Yes Yes No No No
Built-in Workloads (in binary) Wisconsin / TPC-B (tpcb-like, simple-update, select-only) Wisconsin / TPC-B (tpcb-like, simple-update, select-only) OLTP point selects/updates None None
Bundled Workloads (in tree/package) None pgbench-b, pgbench-c, pgbench-e, pgbench-h, pgbench-ds, pgbench-htap OLTP basic scripts TPC-C, TPC-H TPC-C
In-Database Parallel Data Gen No Yes (contrib/pgbench) No No No
Script Loops & Exception Handling No Yes (\for, \try/\catch) Yes (Lua) Yes (Tcl) Yes (Java)
Dynamic Pacing (Keying/Think) No Yes No Yes Yes
Concurrent Heterogeneous Groups No Yes (--group) No Partial No
Latency Percentiles & SLAs No Yes (HDR, --sla) Partial Partial No
Machine-Readable JSON Output No Yes (--json-report) Partial No No
Runs in make check-world Yes Yes No No No

7. Patch Breakdown & Inventory

The complete series consists of 22 git commits rebased cleanly on latest master, structured into 5 stages matching the architecture:

Part 1: Modular Refactoring (Patches 0001 - 0012)

Patch # Commit Summary Key Files Touched Purpose
0001 Refactor pgbench: stats.c/h src/bin/pgbench/stats.[ch] Extracts StatsData, latency tracking, and rate limiting.
0002 Refactor pgbench: variable.c/h src/bin/pgbench/variable.[ch] Extracts scoped variable store and value evaluation.
0003 Refactor pgbench: poller.c/h src/bin/pgbench/poller.[ch] Extracts socket multiplexing abstraction (select/poll).
0004 Refactor pgbench: script.c/h src/bin/pgbench/script.[ch] Extracts script parser, AST structures, and expressions.
0005 Refactor pgbench: commands.c/h src/bin/pgbench/commands.[ch] Extracts meta-command execution and prepared statements.
0006 Refactor pgbench: init.c/h src/bin/pgbench/init.[ch] Extracts database initialization (-i) and data generator.
0007 Refactor pgbench: engine.c/h src/bin/pgbench/engine.[ch] Extracts multi-threaded client state machine and runner.
0008 Refactor pgbench stats: mergeStats src/bin/pgbench/stats.[ch] Decouples global flags and provides modular aggregation.
0009 Refactor pgbench: VariableSpace src/bin/pgbench/variable.[ch] Introduces scoped variable store instances.
0010 Refactor pgbench: poller API src/bin/pgbench/poller.[ch] Encapsulates socket_set internals into clean APIs.
0011 Refactor pgbench: chooseScript src/bin/pgbench/script.[ch] Decouples script selection from thread state.
0012 Refactor pgbench: command dispatch src/bin/pgbench/commands.[ch] Implements pluggable meta-command dispatch table.

Part 2: Code Extraction & In-Database Functions (Patches 0013 - 0014)

Patch # Commit Summary Key Files Touched Purpose
0013 Extract pgbench funcs to src/common src/common/pgbench_funcs.c Shares random/math functions across frontend and backend.
0014 Add contrib/pgbench extension contrib/pgbench/* Exposes generator functions in SQL under SCHEMA pgbench.

Part 3: Types, Functions, Control Flow & Pacing (Patches 0015 - 0019)

Patch # Commit Summary Key Files Touched Purpose
0015 Extend PgBenchValue types variable.[ch], script.[ch] Adds text, timestamp, and array types with operations.
0016 Add TPC helper functions pgbench_funcs.c`, `script.c Adds NURand, c_last, astring, nstring, date/time functions.
0017 Add script loops & exception handling commands.[ch], engine.[ch] Adds \for, \while, \try, \catch, \expect_error.
0018 Add \gset_meta & array parsing commands.[ch], engine.[ch] Adds metadata capture and multi-row array population.
0019 Dynamic \sleep, \keying_time, \think commands.[ch], engine.[ch] Adds dynamic sleep expressions and realistic client pacing.

Part 4: Heterogeneous Groups, Latency Percentiles & Reporting (Patches 0020 - 0021)

Patch # Commit Summary Key Files Touched Purpose
0020 Heterogeneous client groups (--group) engine.[ch], pgbench.c Adds concurrent heterogeneous client pools & sequential streams.
0021 HDR percentiles, --sla, JSON report stats.[ch], engine.[ch] Adds latency histograms, SLA validation, and JSON reporting.

Part 5: Bundled Workload Suites (Patch 0022)

Patch # Commit Summary Key Files Touched Purpose
0022 Add standard workload suites src/bin/pgbench/workloads/* Six ready-to-run benchmark suites (b, c, e, h, ds, htap) with parallel loaders.

8. Conclusion & Next Steps

This patch series equips PostgreSQL with a first-class, built-in benchmarking system capable of executing modern multi-table OLTP, OLAP, and HTAP workloads. By keeping data generation inside the database and orchestrating heterogeneous workloads directly within pgbench, PostgreSQL developers, QA automation, and users gain a standardized, reliable, and reproducible tool that eliminates external dependencies.

Verification Status:

  • All 657 subtests in t/001_pgbench_with_server.pl PASS.
  • All 264 subtests in t/002_pgbench_no_server.pl PASS.
  • SGML documentation build check PASS (make -C doc/src/sgml check).
  • GitHub Actions CI matrix: 100% green across Linux (Meson 64-bit, Meson 32-bit, Autoconf), macOS, and Windows (MSVC Slice 1/2, MinGW).

Future Extensions:

  • Vector Database Benchmarking (pgbench-vec): Evaluating HNSW and IVFFlat index builds, recall vs. latency tradeoffs, and vector similarity search QPS using pgvector.
  • Graph Database Benchmarking (pgbench-graph): LDBC-like Social Network Benchmark (SNB Interactive / Business Intelligence) evaluating recursive CTEs, variable-length path traversal, graph pattern matching, and relational/graph hybrid extensions such as Apache AGE.
  • Distributed Cluster Coordination: Multi-host client synchronization across physical worker nodes for multi-terabyte distributed testing.
Retrieved from "https://wiki.postgresql.org/index.php?title=Pgbench-modernization-2026&oldid=44846"