pgbench scenarios

A small framework of realistic pgbench scripts. Where the built-in pgbench workload is a 1970s TPC-B bank teller, these scenarios mirror the SQL shapes seen in production fleets today: live dashboards, ELT ingestion, HTAP mixes, window/CTE monoliths, temporal burst patterns and machine-generated "lazy" queries.

Everything is plain SQL + pgbench meta commands (\set, \gset, \if, weights). No extensions, no new tools, no Python.

Quickstart

# 1. create a database
createdb pgbench

# 2. create schema + seed data (~3M rows, takes a minute)
run.sh init

# 3. run a scenario (defaults tuned per scenario)
run.sh dashboard --clients 50 --duration 300 --latency-limit 3000

Connection and workload defaults come from env vars (PGHOST, PGPORT, PGDATABASE, PGUSER, CLIENTS, DURATION, SCALE, ...) or --flags. See run.sh --help.

Layout

schemas/init.sql      schema + seed (heavy-tail, jsonb, skewed categoricals)
scripts/*.sql         one pgbench transaction script per scenario
run/run.sh            runner: per-scenario defaults, weights, dry-run, logs
logs/                 run output (gitignored)

Scenarios

Scenario	Describes	Defaults (clients/s/limit)
dashboard	Live BI reads, parameters change per refresh	50 / 300 / 3000ms
elt	ELT ingestion: raw load -> rollup -> summary refresh	10 / 600 / -
htap	OLTP transfers + OLAP scans, weighted (70/30)	30 / 300 / 5000ms
analytical	Window functions, CTEs, jsonb, LATERAL, percentiles	5 / 600 / 30000ms
temporal	Diurnal read mix + cache-invalidation writes	20 / 3600 / 10000ms
lazy	Machine-generated / ORM-style SQL (defeats indexes)	20 / 300 / 5000ms

# HTAP with a different OLTP/OLAP split
run.sh htap --oltp-weight 80 --olap-weight 20

# run everything back-to-back
run.sh all --duration 120

# temporal with a capped arrival rate (diurnal spikes)
run.sh temporal --rate 500

# see the command before running it
run.sh dashboard --dry-run

Output

Each run writes logs/<scenario>_<timestamp>.log (full pgbench output, including --progress lines) and prints a summary:

number of transactions actually processed: 12345
number of failed transactions: 0
latency average = 12.345 ms
tps = 40.2 (including connections establishing)

For per-transaction latency distributions, append pgbench flags directly, e.g. --log-prefix + --aggregate-interval to get CSV that plots well. run.sh forwards any extra --flag only if you add them via the documented options; for arbitrary pgbench flags call pgbench directly:

pgbench -c 50 -T 300 \
  -f scripts/htap_oltp.sql@70 -f scripts/htap_olap.sql@30 \
  -D scale=100 --aggregate-interval=10 --log-prefix=logs/htap

Notes

• Requires PostgreSQL 13+ (random_zipfian, MERGE-style upserts via ON CONFLICT), 15+ recommended for \aset and --progress niceties.
• README examples assume psql, createdb and pgbench on PATH.
• The temporal scenario branches on real wall-clock business hours; use --rate to shape arrival rate, or run at 9-18h local time for the "busy" path.
• Seed data is deterministic per run only via random(); pass --seed N to pgbench for reproducible query parameters (schema data itself is random).
• elt merges the batch rollup with ON CONFLICT ... ORDER BY user_id, event_date so concurrent sessions lock keys in the same order: hot-key upserts queue instead of deadlocking. --max-tries is enabled just for elt as a backstop (env MAX_TRIES, default 4).
• Validated against PostgreSQL 18: all six scenarios run with 0 failed transactions (run.sh all --duration 15).