Pgbench-for-tpc-like-benchmarks

Technical Design: Enabling High-Fidelity TPC-C, TPC-E, TPC-H, and HTAP Workloads in pgbench

This document provides a comprehensive gap analysis and architectural design specification for extending pgbench into a high-fidelity workload generator capable of running standard TPC benchmark suites (TPC-C, TPC-E, TPC-H, TPC-DS) and hybrid transactional/analytical (HTAP) benchmarks (CH-benCHmark).

---

1. Executive Summary & Problem Statement

pgbench was originally designed as a simple tool to execute TPC-B-like transactions (SELECT, UPDATE on accounts/tellers/branches) with basic uniform random variables. Over time, features like expressions (\set), Gaussian/Zipfian/Exponential distributions, pipeline mode, and \if branching were introduced.

However, running real-world enterprise benchmarks like TPC-C, TPC-E, TPC-H, or CH-benCHmark directly in pgbench with standard-compliant fidelity remains challenging or impossible due to key architectural limitations:

This design outlines the evolutionary additions to pgbench required to bridge these gaps while maintaining full backward compatibility, high performance, and clean integration with PostgreSQL stored procedures.

---

2. TPC Benchmark Requirements vs. pgbench Capabilities

2.1 Summary Comparison Matrix

Capability	TPC-C Requirements	TPC-E Requirements	TPC-H / TPC-DS Requirements	CH-benCHmark Requirements	Current pgbench Support
Transaction Complexity	5 distinct transactions; variable 5–15 order items per New-Order	10 transactions with complex frames & branching	22 complex analytic queries (TPC-H) / 99 queries (TPC-DS)	Mixed TPC-C OLTP transactions + 22 TPC-H analytic queries	Limited to single static scripts or weighted mix of static scripts
Data Types	Alphanumeric strings, syllables (C_LAST), dates, integer arrays	Fixed/variable text, timestamps, numeric arrays	Date offsets, sliding intervals, text tokens	Same as TPC-C + TPC-H	Only int64, double, boolean, null
Non-Uniform PRNG	<math>NURand(A, x, y)</math>, non-uniform C-Last, item IDs	Multi-variate distributions, Zipfian, customer/security skew	Uniform & Zipfian parameter substitutions	Both TPC-C <math>NURand</math> and TPC-H substitution parameters	Uniform, Gaussian, Exponential, Zipfian integers only
Control Flow	Variable-length item loops; conditional rollbacks (1% invalid item)	Dynamic array inputs, iterative frame execution	Sequential execution of 22 queries in permutation order	Concurrent OLTP loops + long-running OLAP queries	\if, \elif, \else, \endif only; no loops, no error catching
Execution Model	Stored Procedures or multi-statement client scripts	Stored Procedures / multi-frame transactions	Direct SQL streams with substitution parameters	Stored Procedures for OLTP + parallel query streams for OLAP	Client sends SQL queries sequentially; cannot pass arrays or handle multi-row outputs cleanly
Error Handling	1% of New-Order transactions must encounter an intentional rollback and count as valid	Rollbacks on constraint violations	Strict query error isolation	OLTP intentional rollbacks + OLAP isolation	Any server-side error aborts client unless retry-on-error is enabled for serialization
Pacing / Timing	Explicit Keying Time (e.g. 18s) + Negative Exponential Think Time (e.g. 12s)	Terminal pacing & pacing distributions	Throughput test (maximum load) + Refresh Streams	Separate pacing for OLTP vs continuous OLAP streams	Global --rate (Poisson) or static \sleep <time> only
Reporting & Metrics	Primary metric tpmC (New-Order/min) subject to 90th percentile SLA (< 5s) & mix checks	tpsE with strict 90th percentile response times	QphH (geometric mean of Power + Throughput + Refresh)	OLTP tpmC + OLAP queries/hour	Aggregate TPS and average latency; no composite metrics or SLA percentile checks
Stream Roles	Homogeneous terminal clients	Homogeneous client drivers	Multi-stream throughput (<math>S</math> query streams) + concurrent Refresh Streams (RF1/RF2)	Heterogeneous worker groups (e.g. 80 OLTP clients + 4 OLAP streams + 1 refresh)	All worker threads share identical configuration and script mix

---

3. Detailed Gap Analysis

3.1 Data Types & String/Date Manipulation

1. Missing Text Type (PGBT_TEXT):

   pgbench's variable struct (PgBenchValue) only supports numeric and boolean values. In TPC-C, transactions require alphanumeric identifiers (e.g., customer credit BC/GC, carrier IDs, data strings).

2. Missing Array / Collection Types (PGBT_ARRAY):

   A TPC-C New-Order transaction takes 5 to 15 items in a single transaction. When using stored procedures (CALL new_order(...)), the client must pass arrays of item IDs (int[]), warehouse IDs (int[]), and quantities (int[]). Currently, pgbench cannot construct or serialize array literals.

3. Missing Date/Timestamp Types:

   TPC-H and TPC-DS query generation requires generating dates within sliding temporal windows (e.g., date '1995-03-15' + interval ':delta' day).

---

3.2 TPC-Specific Randomness & Non-Uniform PRNGs

1. TPC-C Non-Uniform Random (<math>NURand</math>):

   The TPC-C specification requires <math>NURand(A, x, y)</math>, defined as:

<math display="block">NURand(A, x, y) = \left( \left( (random(0, A) \mid random(x, y)) + C \right) \bmod (y - x + 1) \right) + x</math>

1. where <math>C</math> is a runtime constant chosen randomly from <math>[0, A]</math> at client startup and held constant throughout the run:

   • <math>C_{last} \in [0, 255]</math> for customer last names (<math>A=255, x=0, y=999</math>)
   • <math>C_{id} \in [0, 1023]</math> for customer IDs (<math>A=1023, x=1, y=3000</math>)
   • <math>C_{ol} \in [0, 8191]</math> for item IDs (<math>A=8191, x=1, y=100000</math>)

   pgbench currently cannot persist per-session runtime constants or evaluate bitwise OR across distinct PRNG calls cleanly in a single function.

2. Deterministic String & Date Generation from Skewed PRNGs:

   In TPC benchmarks, strings (like customer names) and dates must follow specific non-uniform distributions. Rather than creating isolated ad-hoc generators, a unified architecture can map the output of integer distributions (Zipfian, Gaussian, NURand) through deterministic hashing and dictionary functions to generate non-uniform strings and timestamps.

---

3.3 Control Flow & Intentional Rollbacks

1. Looping Constructs:

   pgbench lacks \for or \while loops. When preparing variable-length payloads (such as 5–15 order items) or executing client-side batch statements, scripts cannot iterate.

2. Handling Expected Rollbacks (1% Rule in TPC-C):

   In TPC-C New-Order, 1% of transactions are intentionally given an invalid item ID (ol_i_id = 100001), requiring the transaction to abort and roll back.

   • In pgbench today, an unhandled SQL error immediately terminates the client thread.
   • pgbench needs an \expect_error or \try ... \catch construct that catches expected business-logic exceptions, rolls back the transaction, records the event under a dedicated "expected rollback" counter, and allows the client to proceed.

---

3.4 Workload Pacing, Metrics, and Multi-Stream Roles

1. Keying Time vs. Think Time:

   TPC-C simulates real terminals where a user spends a fixed keying time entering form fields, submits the query, waits for response time, and then spends negative-exponential think time before starting the next transaction.

   • In pgbench today, --rate sets a global target rate, but cannot model per-transaction keying and think-time stages.

2. SLA Response Time Percentile Gates & Composite Metrics:

   TPC specifications require that at least 90% of transactions of each type complete under a strict SLA (e.g. 5 seconds for New-Order). Standard pgbench only outputs averages and standard deviations in its main report.

3. Heterogeneous Client Roles (HTAP & Throughput Streams):

   In TPC-H Throughput Test and CH-benCHmark, the workload consists of different streams (e.g. OLTP transactions + OLAP query streams + data refresh streams). pgbench currently forces all threads in a single instance to execute from the same weighted script pool.

---

4. Architectural Design & Extensions

4.1 Extended Value System (PgBenchValue)

Extend PgBenchValueType in pgbench.h to support text, timestamps, and arrays:

typedef enum PgBenchValueType
{
    PGBT_NO_VALUE = 0,
    PGBT_NULL,
    PGBT_BOOLEAN,
    PGBT_INT,
    PGBT_DOUBLE,
    PGBT_TEXT,          /* String/Text type */
    PGBT_TIMESTAMP,     /* Timestamp / Date type (microseconds since 2000-01-01) */
    PGBT_ARRAY          /* Homogeneous/heterogeneous array of PgBenchValue */
} PgBenchValueType;

typedef struct PgBenchValue
{
    PgBenchValueType type;
    union
    {
        int64       ival;
        double      dval;
        bool        bval;
        char       *sval;       /* allocated string buffer */
        struct
        {
            int64   usec;       /* timestamp in microseconds */
        }           tsval;
        struct
        {
            int                 nelems;
            struct PgBenchValue *elems;
        }           aval;
    } u;
} PgBenchValue;

---

4.2 Unified Parameter & String/Time Generation via PRNG Mapping

To ensure all data types (integers, strings, dates, arrays) can share identical statistical distributions (Uniform, Gaussian, Exponential, Zipfian, NURand), string and timestamp generators are designed as deterministic transformations over integer PRNG outputs:

New Built-in Scripting Functions:

1. TPC-C <math>NURand</math>:

   • nurand(A, min, max [, C]) → Evaluates TPC-C Non-Uniform Random.
   • If <math>C</math> is omitted, pgbench uses a persistent per-client constant initialized at session start according to the TPC-C rules (<math>C_{last}</math>, <math>C_{id}</math>, <math>C_{ol}</math>).

2. TPC-C Customer Last Name (c_last):

   • c_last(number) → Maps an integer in <math>[0, 999]</math> to a 3-syllable string formed from the 10 standard TPC-C syllables (BAR, OUGHT, ABLE, PRI, PRES, ESE, ANTI, CALLY, ATION, EING).
   • Example: \set clast c_last(nurand(255, 0, 999)) generates names like "BARPRESATION" with the exact TPC-C skew.

3. Random Alphanumeric Strings (astring / nstring):

   • random_astring(min_len, max_len) → Generates random alphanumeric string ([a-zA-Z0-9]).
   • random_nstring(min_len, max_len) → Generates numeric string ([0-9]).

4. Date / Timestamp Mapping & Arithmetic (int_to_date):

   • int_to_date(base_date, val, max_val, interval) → Scales integer val in <math>[0, max\_val]</math> proportionally across the specified interval (e.g. '7 years', '180 days', '24 hours') added to base_date:

<math display="block">\text{date} = \text{base\_date} + \left( \frac{\text{val}}{\text{max\_val}} \times \text{interval} \right)</math>

1. This preserves the exact skew of the underlying integer distribution (Gaussian, Zipfian, NURand) over any arbitrary date/time window.

   • int_to_date(base_date, val, unit) → Adds val units directly ('day', 'month', 'year', 'hour', 'second') to base_date.
   • to_timestamp('YYYY-MM-DD') → Parses string literal into timestamp value.
   • date_add(ts, offset, unit) → Adds offset units ('day', 'second', etc.) to ts.

2. Array Construction for Stored Procedures:

   • array_generate(length, expr) → Generates an array of length elements evaluated from expr.
   • array(v1, v2, ...) → Direct array literal constructor.
   • Example: \set item_ids array_generate(:ol_cnt, random(1, 100000))

---

4.3 Script Control Flow: Loops, Conditionals & Exception Handling

4.3.1 Looping Constructs (\for and \while)

\for <var> <start_expr> .. <end_expr> [BY <step_expr>]
    -- commands executed in loop
\endfor

\while <condition_expr>
    -- commands executed while condition is true
\endwhile

• Enables preparing multi-item payloads or running client-side multi-statement transaction loops.

4.3.2 Exception Handling & Expected Rollbacks (\try ... \catch and \expect_error)

\try
    -- SQL command or meta-command that may intentionally fail
    CALL new_order(:w_id, :d_id, :c_id, :items, :suppliers, :quantities);
\catch <sqlstate_pattern>
    -- Executed if statement throws matching SQLSTATE (or any error if omitted)
    \set rollback_occurred 1
    ROLLBACK;
\endtry

• Expected Rollback Accounting: When an error is caught in \try ... \catch or marked with \expect_error, pgbench:

1. 1. Issues ROLLBACK to reset the connection.
   2. Increments the expected_rollbacks metric for the script.
   3. Does NOT abort the client thread or increment the fatal error count.

---

4.4 Result Set Inspection & Dynamic Parameter Binding (\gset, \aset)

Enhance \gset and \aset to capture rich metadata from queries and stored procedure OUT parameters:

• \gset [prefix] captures scalar columns into :prefix_<colname>.
• \gset_meta [prefix] automatically populates:
  • :prefix_status → OK, ERROR, ROLLBACK
  • :prefix_sqlstate → 5-character SQLSTATE (e.g. '40001', 'P0001')
  • :prefix_nrows → Number of affected or returned rows
• Arrays returned from PostgreSQL queries (e.g. SELECT array_agg(id) FROM ...) are converted directly into PGBT_ARRAY variables.

---

4.5 Pacing & Phase Management: Keying Time, Think Time, and Dynamic \sleep

1. Dynamic Expression Evaluation in \sleep:

   \sleep accepts expressions evaluating to numbers:

\sleep random_exponential(100, 5000, 2.0) ms

1. Explicit Keying & Think Time Blocks:

\keying_time 18 s
-- Transaction execution begins after keying time
CALL new_order(...);
-- Transaction completes, response time recorded
\think_time random_exponential(1, 120, 0.0833) s

1. • Keying Time: Recorded separately; does not count toward transaction response time.
   • Response Time: Accurately measures server turnaround time.
   • Think Time: Recorded separately to model client pacing without skewing latency histograms.

---

4.6 Heterogeneous Client Groups & Stream Coordination

Allow assigning distinct worker groups in a single pgbench command or configuration:

pgbench \
  --group name=oltp,clients=80,threads=8,file=tpcc_mix.sql \
  --group name=olap,clients=4,threads=2,file=tpch_streams.sql,sequential=true \
  --group name=refresh,clients=1,threads=1,file=tpch_rf.sql,rate=0.1 \
  -T 1800 my_benchmark_db

Key Properties:

• sequential=true: For OLAP streams, queries within the script file (e.g. TPC-H Q1 through Q22) are executed in strict sequential order rather than random choice.
• Per-Group Metrics: Separate TPS, latency, and percentile statistics are reported per client group.

---

4.7 Performance Metrics, SLA Percentiles & Reporting

1. High-Resolution Response Time Percentiles:

   • Calculate exact or HDR-histogram percentiles (p50, p90, p95, p99, p99.9) per script and transaction type.

2. SLA Compliance Validation:

   • Define pass/fail SLA gates:

--sla new_order:p90<=5.0s --sla payment:p90<=5.0s

1. • Summary output displays clear PASS / FAIL status for each SLA threshold.

2. Composite Metric Formulas:

   • Calculate standard benchmark throughput metrics:

<math display="block">\text{tpmC} = \frac{\text{New-Order Count}}{\text{Duration (minutes)}}</math>

<math display="block">\text{QphH@SF} = \frac{22 \times S \times 3600}{\text{Elapsed Time}}</math>

1. Structured JSON Telemetry Export (--json-report=file.json):

   • Emits full configuration, per-interval latency percentiles, SLA verifications, and composite scores for automated CI/CD benchmarking pipelines.

---

5. Workload Walkthroughs

5.1 TPC-C New-Order & Payment via Stored Procedures

-- Script: tpcc_new_order.sql
\keying_time 18 s

-- 1. Generate Warehouse, District, Customer
\set w_id random(1, :scale)
\set d_id random(1, 10)
\set c_id nurand(1023, 1, 3000)

-- 2. Generate 5-15 items and quantities
\set ol_cnt random(5, 15)
\set rollback_prob random(1, 100)

-- 3. Construct batch arrays
\set item_ids array_generate(:ol_cnt, random(1, 100000))
\set quantities array_generate(:ol_cnt, random(1, 10))
\set supplier_w_ids array_generate(:ol_cnt, :w_id)

-- 4. 1% Intentional Rollback (set invalid last item)
\if :rollback_prob = 1
    \set item_ids[:ol_cnt - 1] 100001
\endif

-- 5. Execute Stored Procedure with Exception Handling
\try
    CALL tpcc_new_order(:w_id, :d_id, :c_id, :ol_cnt, :item_ids, :supplier_w_ids, :quantities);
\catch 'P0001'
    -- Expected rollback handled gracefully
    ROLLBACK;
\endtry

-- 6. Think Time (Negative Exponential mean 12s, max 120s)
\think_time random_exponential(1, 120, 0.0833) s

---

5.2 TPC-H Sequential Query Stream with Refresh Stream

-- Script: tpch_stream_1.sql (Executed with sequential=true)
\set stream_id 1

-- Q1: Pricing Summary Report
\set delta random(60, 120)
\set q1_date int_to_date('1998-12-01', -:delta, 'day')

-- Example: Non-uniform / Zipfian shipdate selection over a 7-year window
\set r_zipf random_zipfian(1, 10000, 1.1)
\set skewed_shipdate int_to_date('1992-01-01', :r_zipf, 10000, '7 years')

SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    avg(l_quantity) as avg_qty
FROM lineitem
WHERE l_shipdate <= :q1_date
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

-- Subsequent queries Q2 .. Q22 execute in sequential permutation order...

---

5.3 CH-benCHmark (HTAP) Heterogeneous Setup

pgbench \
  --group name=tpcc_oltp,clients=100,threads=10,file=tpcc_mix.sql@100 \
  --group name=tpch_olap,clients=4,threads=4,file=tpch_queries.sql,sequential=true \
  --sla tpcc_new_order:p90<=5s \
  --json-report=chbenchmark_results.json \
  -T 3600 htap_database