Re: [PATCH] Refactor pgbench to make future improvements easier

[PATCH v5 00/22]
* Modular pgbench refactoring,
* contrib/pgbench extension,
* advanced benchmark capabilities,
* and standard workload suites (pgbench-b, pgbench-c, pgbench-e,
pgbench-h, pgbench-ds, pgbench-htap)

Hi hackers,

This patch series presents a comprehensive modernization of pgbench,
transforming
it from a monolithic single-script driver into a modern, modular, multi-workload
benchmarking engine with native support for complex OLTP, OLAP, and
HTAP benchmarks.

See https://wiki.postgresql.org/wiki/Pgbench-modernization-2026 for
more background and rationale.

The series is organized into five logical parts across 22 clean, self-contained
commits rebased cleanly on latest master:

================================================================================
Part 1: Modular pgbench Refactoring (Patches 0001 - 0012)
================================================================================
pgbench.c had grown to over 7,400 lines combining distinct responsibilities:
statistics accumulation, variable management, event polling, script parsing,
meta-command execution, database initialization (-i), and the multi-threaded
state machine.

Patches 0001-0007 extract these modules without altering behavior or
public APIs:
- stats.c / stats.h: StatsData, latency tally, rate-limiting logic.
- variable.c / variable.h: Scoped variable store and value evaluation.
- poller.c / poller.h: Socket set polling abstraction (select/poll).
- script.c / script.h: Script parsing, AST nodes, expression evaluator.
- commands.c / commands.h: Meta-command handlers and prepared
statement helpers.
- init.c / init.h: Database initialization (-i) logic and data generators.
- engine.c / engine.h: Multi-threaded client state machine and event loop.

Patches 0008-0012 decouple remaining global dependencies cleanly:
- stats: add mergeStats() and decouple global flags.
- variable: introduce scoped variable store (VariableSpace).
- poller: encapsulate socket_set and provide poller API wrappers.
- script: decouple chooseScript() from TState.
- commands: introduce pluggable meta-command dispatch table.

================================================================================
Part 2: Code Extraction & In-Database Functions (Patches 0013 - 0014)
================================================================================
Patch 0013 extracts pgbench's mathematical and pseudo-random
generators (Gaussian,
Exponential, Zipfian, Permute, MurmurHash64) into src/common/pgbench_funcs.c,
making them reusable across both client and server binaries.

Patch 0014 introduces contrib/pgbench, a server-side extension that
exposes these
pseudo-random generators and helper functions directly to SQL under
SCHEMA pgbench.
This allows high-performance parallel in-database synthetic data
generation using
standard SQL (e.g. generate_series()) without relying on slow client roundtrips.

================================================================================
Part 3: Advanced Types, Functions, Control Flow & Pacing (Patches 0015 - 0019)
================================================================================
Patch 0015 extends PgBenchValue with text, timestamp, and array types,
along with
array operations (array_append, array_length, array_get, array_generate), text
concatenation, and timestamp arithmetic.

Patch 0016 adds standard benchmark helper functions to pgbench expressions and
contrib/pgbench:
- nurand(A, s, x, y): Non-uniform random distribution generator.
- c_last(num): Syllable-based customer last name generator.
- random_astring(min, max), random_nstring(min, max): Alphanumeric
and numeric strings.
- format_string(fmt, ...), date_add(ts, amt, unit), int_to_date(int).

Patch 0017 adds script control flow and exception handling:
- Loops: \for <var> <start> .. <end> [BY <step>], \while <expr>,
\break, \continue.
- Exception handling: \try ... \catch [<sqlstate>] ... \endtry,
\expect_error <sqlstate>.

Patch 0018 adds result set metadata inspection and array column parsing:
- \gset_meta <prefix>: Extracts query result status, error code,
rows affected, and timing.
- \aset and array-aware \gset: Maps multi-row query results into
client-side array variables.

Patch 0019 introduces dynamic client pacing:
- Dynamic \sleep expressions (e.g. \sleep random_exponential(1, 1000, 10) ms).
- \keying_time and \think_time commands for realistic transaction
pacing per specification.

================================================================================
Part 4: Heterogeneous Groups, Latency Percentiles & Reporting (Patches
0020 - 0021)
================================================================================
Patch 0020 introduces heterogeneous client groups via --group:
- Run concurrent mixes of different scripts with distinct client
counts and rates
(e.g., --group name=oltp,clients=10,weight=100 --group
name=olap,clients=2,sequential=true).
- Supports sequential stream execution for multi-query analytical streams.

Patch 0021 implements High Dynamic Range (HDR) latency histogram tracking:
- Configurable percentiles (p50, p90, p95, p99, p99.9).
- SLA validation via --sla target=<metric>,threshold=<value>.
- Metric computation for tpmC and QphH(at)SF(dot)
- Structured machine-readable JSON output via --json-report=<path>.

================================================================================
Part 5: Standard Workload Suites (Patch 0022)
================================================================================

<THIS WAS LEFT OUT FROM THIS EMAIL>

* Trademark & Naming Rationale:
These workloads are inspired by and modeled after industry-standard
specifications
(TPC-C, TPC-E, TPC-H, TPC-DS, and CH-benCHmark). To respect and
avoid infringing
upon Transaction Processing Performance Council (TPC) trademarks,
and to clearly
distinguish these community engineering tools from official
council-audited benchmarks,
they are explicitly named pgbench-b, pgbench-c, pgbench-e,
pgbench-h, pgbench-ds, and pgbench-htap.
Full background and guidelines are documented in
src/bin/pgbench/workloads/README.

* In-Database Parallel Loading:
Each workload includes an initialization script
(<workload>_init.sql) designed to
run in parallel across multiple worker sessions (-c N -j N -t 1 -D
num_workers=N).
Data generation is executed entirely within the database engine
using generate_series()
and contrib/pgbench SQL functions, achieving rapid parallel load
rates without external
file generation or ETL binaries.

Comprehensive documentation is provided in doc/src/sgml/ref/pgbench.sgml and
doc/src/sgml/pgbench-ext.sgml, and full end-to-end TAP coverage is added in
src/bin/pgbench/t/001_pgbench_with_server.pl (passing all tests across
all platforms).

Feedback, testing, and reviews are warmly welcomed!

Hannu Krosing (22):
Refactor pgbench: extract statistics structures and functions to
stats.c/h
Refactor pgbench: extract variable management and value types to
variable.c/h
Refactor pgbench: extract socket polling abstraction to poller.c/h
Refactor pgbench: extract script parsing, built-in scripts, and
expression evaluation to script.c/h
Refactor pgbench: extract meta-command execution and prepared
statement helpers to commands.c/h
Refactor pgbench: extract database initialization (-i) to init.c/h
Refactor pgbench: extract benchmark execution engine and client state
machine to engine.c/h
Refactor pgbench stats: add mergeStats() and decouple global flags
Refactor pgbench variables: introduce scoped variable store
Refactor pgbench poller: encapsulate socket_set and add poller API
aliases
Refactor pgbench script: decouple chooseScript() from TState
Refactor pgbench commands: introduce pluggable meta-command dispatch
Extract pgbench low-level functions into src/common
Add contrib/pgbench extension exposing benchmark functions in SCHEMA
pgbench
pgbench: Extend PgBenchValue with text, timestamp, and array types
pgbench: Add TPC-C NURand, syllable, string, date/timestamp, and array
functions
pgbench: Add script loops (\for, \while) and exception handling (\try,
\catch, \expect_error)
pgbench: Add \gset_meta and array column parsing in \gset and \aset
pgbench: Support dynamic \sleep expressions, \keying_time, and
\think_time
pgbench: Add heterogeneous client groups (--group) and sequential
streams
pgbench: Add HDR latency percentiles, --sla validation, tpmC/QphH
metrics, and --json-report
pgbench: Add pgbench-b, pgbench-c, pgbench-e, pgbench-h, pgbench-ds,
and pgbench-htap workload suites

---
Hannu