workload_description.md

Workload configuration

A workload consists of an initialization command, a measured command, and one iteration axis. Both commands are trusted shell templates executed on the workload-generator host.

Iteration axis

Exactly one axis is required:

• --pgbench-clients 1,4,16 supplies ARG_PGBENCH_CLIENTS;
• --pgbench-time 10,30,60 supplies ARG_PGBENCH_TIME.

Values are comma-separated positive integers. The CLI does not automatically add -c, -T, or any other pgbench flag; the command template decides how the axis value is used.

Every axis value is a separate destructive iteration with a fresh dataset, using database recreation or profile schema reset.

After init_command, the runner executes VACUUM ANALYZE and collects the Before workload size snapshot. It then runs the measured command, waits for any remaining OS sampling to finish, and collects After workload, without another vacuum. This keeps size-query CPU and I/O out of the OS samples. Each snapshot lists all databases on the instance and the workload database's top 100 tables and indexes by measured size. Snapshots are retained separately for every axis value. See storage report contents.

Bundled profiles intentionally use --pgbench-clients: they sweep concurrency to find maximum TPS for one fixed dataset, query mix and environment.

Placeholders

Placeholder	Value source
ARG_PG_HOST	--host
ARG_PG_PORT	--port
ARG_PG_USER	--user
ARG_PG_PASSWORD	--password or PGPASSWORD
ARG_PG_DATABASE	--database
ARG_PGBENCH_PATH	newest installed local pgbench, or validated --pgbench-path
ARG_PGBENCH_PROTOCOL	simple by default; prepared with --pgbench-prepared
ARG_PSQL_PATH	same-major local psql, or validated --psql-path
ARG_WORKLOAD_PATH	--workload-path
ARG_WORKLOAD_SCALE	--workload-scale
ARG_WORKLOAD_DURATION_SECONDS	--workload-duration-seconds, or the profile's default_duration_seconds
ARG_PGBENCH_CLIENTS	current client-axis value
ARG_PGBENCH_TIME	current duration-axis value

An unresolved ARG_* token fails before database mutation. Prefer PGPASSWORD to embedding ARG_PG_PASSWORD in the command text.

Standard pgbench workload

--benchmark-type default \
--pgbench-clients 1,4,16 \
--init-command 'ARG_PGBENCH_PATH -i -s 10 -h ARG_PG_HOST -p ARG_PG_PORT -U ARG_PG_USER ARG_PG_DATABASE' \
--workload-command 'ARG_PGBENCH_PATH -T 60 -c ARG_PGBENCH_CLIENTS -j ARG_PGBENCH_CLIENTS -h ARG_PG_HOST -p ARG_PG_PORT -U ARG_PG_USER ARG_PG_DATABASE'

Keep the scale factor and all non-axis options constant when comparing axis values.

Custom SQL workload

--benchmark-type custom \
--workload-path /srv/workloads/orders \
--pgbench-clients 1,4,16 \
--init-command 'ARG_PSQL_PATH -h ARG_PG_HOST -p ARG_PG_PORT -U ARG_PG_USER -d ARG_PG_DATABASE -f ARG_WORKLOAD_PATH/schema.sql' \
--workload-command 'ARG_PGBENCH_PATH -h ARG_PG_HOST -p ARG_PG_PORT -U ARG_PG_USER -d ARG_PG_DATABASE -f ARG_WORKLOAD_PATH/read-write.sql -T 60 -c ARG_PGBENCH_CLIENTS -j ARG_PGBENCH_CLIENTS'

--workload-path must exist on the workload-generator host. The command is responsible for referencing the required files.

Bundled maximum-TPS profiles

pg-perf-bench profiles lists the packaged imdb analytical, pagila pure-OLTP and pagila-htap mixed (OLTP plus reporting) profiles. Each includes a SQL schema, deterministic Python data generator and pgbench query set. The generators avoid random()/setseed(), whose sequences differ between PostgreSQL 10–11, 12–14 and 15+, so the dataset for a given scale is identical on every supported server major and the compare-postgresql-major join stays controlled. pgbench script selection uses a fixed --random-seed, so identical runs execute the same script sequence and mix-composition noise stays out of comparisons. --workload-duration-seconds overrides each profile's default window (60 s pagila, 120 s imdb). Select one without copying its command templates:

pg-perf-bench benchmark \
  --workload-profile imdb \
  --workload-scale 1 \
  --pgbench-clients 1,2,4,8,16 \
  ...

The profiles do not use or copy pg_workload's profile.yml. That file describes continuous scheduling in pg_workload; pg_perf_bench instead resets the dataset for each concurrency point and measures maximum TPS.

pgbench protocol

All bundled profiles use -M ARG_PGBENCH_PROTOCOL: simple by default, prepared with --pgbench-prepared. Keep the same protocol in paired comparisons; it affects parsing/planning costs and is included in the execution hash and report parameters (invocation.workload.pgbench_protocol). Changing only the protocol changes the execution hash while retaining the workload definition hash.

Custom commands keep their explicit -M / --protocol option. To make the CLI flag control a custom command, include -M ARG_PGBENCH_PROTOCOL. An explicit -M prepared is recognized even without the flag, including for the schema-reset preflight. The flag fails early if the custom command would still use another protocol. See managed connection pooling for prepared-statement cleanup requirements.

Literal options inside sh -c / bash -c wrappers are also recognized. For example, replace a profile's workload command with the following (all queries here are read-only, so this measures a different workload from the default mixed profile):

--workload-command "bash -c 'ARG_PGBENCH_PATH -n -M ARG_PGBENCH_PROTOCOL -T ARG_WORKLOAD_DURATION_SECONDS -c ARG_PGBENCH_CLIENTS -h ARG_PG_HOST -p ARG_PG_PORT -U ARG_PG_USER -f ARG_WORKLOAD_PATH/sql/01_select.sql ARG_PG_DATABASE'"

Add --pgbench-prepared to select prepared mode even inside the wrapper. Literal -M prepared needs no flag. Adjacent commands such as echo do not determine the pgbench protocol. Multiple detected pgbench commands must agree on the protocol.

The utility does not execute scripts or evaluate shell variables to discover their protocol. An opaque command such as run-bench.sh, or a dynamic -M "$MODE", is recorded as unknown and retains the prepared-statement preflight in schema mode. A script that accepts a protocol argument can declare it by accepting ARG_PGBENCH_PROTOCOL, for example run-bench.sh ARG_PGBENCH_PROTOCOL. Such a script must pass that value to pgbench. Use literal options or this placeholder when comparing reports with a known protocol.

Command timeout

--command-timeout is a positive number of seconds applied independently to:

• every initialization command;
• the pre-workload VACUUM ANALYZE;
• every measured workload command;
• transport host commands unless an item defines a smaller timeout.

Choose a timeout above the workload duration and initialization budget. A timeout terminates the local process group; Docker commands also run through an in-container timeout wrapper.

Recorded evidence

For every completed init and workload command the report records:

• redacted command text;
• return code;
• stdout and stderr;
• UTC start time;
• elapsed seconds;
• iteration index, axis name, and axis value;
• parsed pgbench clients, duration, transaction count, average latency, latency standard deviation, failed/retried transaction percentages, initial connection time, and TPS.

The result section contains separate charts for TPS and the five latency, failure/retry and initial connection metrics. Only the overall pgbench summary is used; per-script statistics are excluded. Optional fields absent from a run remain null, rather than zero, and charts show gaps or an explicit no-data message. Percentages are preserved as printed by pgbench. JOIN includes these charts with a separate series for each source report.

The top-level workload_evidence object also embeds the complete SQL schema, setup and query files, full Python generator source, profile manifest, source hashes, scale, command templates, exact resolved commands, pgbench/psql paths, and client-axis values. Definition and execution hashes let JOIN scenarios verify workload identity without relying on external files.

Local profile directories also contribute their other UTF-8 files, including configuration files and profile.json; its files entries supply roles without applying its command templates or defaults. VCS metadata, virtual environments (.venv, venv) and Python caches are excluded from automatic traversal. Literal psql/pgbench -f and --file inputs are included even outside the profile directory, with pgbench @weight suffixes removed from file paths. Relative file arguments are resolved from the benchmark's launch directory; use absolute paths for external files. Dependencies opened indirectly by code must be kept under the local profile directory. Files must exist before the run, be UTF-8 text, be at most 5 MiB each and not be symlinks.

All captured file contents affect both workload hashes. HTML displays the manifest and supporting files under Workload initialization and configuration and the query sources under pgbench workload.

The report also records an explicit maximum_tps object containing the winning axis value and its complete latency/transaction metrics. A joined report keeps the per-source maxima in joined_maximum_tps.

If TPS cannot be parsed, raw output is retained and the result/chart items are marked partial.

Reproducibility checklist

With --managed, the benchmark runs through the PostgreSQL endpoint without a host transport or server paths. Use --reset-mode schema --init-fsync keep for a pre-created database without CREATEDB or access to postgres. Workload execution remains enabled; service restarts, filesystem/cache operations, OS sampling and server log collection are disabled. Their report items contain No data. Managed PostgreSQL. with status unsupported. An optional --managed-pg-info FILE also implies --managed; its content is embedded intact and displayed directly as plain_text; binary content is stored and displayed as Base64 with an encoding note. See the README's Managed PostgreSQL example for required permissions and connection settings.

• Use a dedicated disposable database and stable PostgreSQL configuration.
• Keep the workload generator, network path, scale factor, and non-axis flags constant across compared runs.
• Isolate the target from unrelated workloads.
• Set CPU frequency, power management, and storage-cache policy explicitly when those factors matter.
• Record whether OS caches were dropped.
• Do not infer statistical confidence from a single iteration per axis value.

Warm-up runs, automatic repetitions, confidence intervals, and continuous target sampling are not implemented yet.