tpcds

package
v6.4.2 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Nov 17, 2026 License: Apache-2.0, Apache-2.0 Imports: 26 Imported by: 0

README

TPC-DS workload

The registered tpcds workload loads all 24 TPC-DS tables and runs the 99-query suite (103 SQL statements because queries 14, 23, 24, and 39 have two parts). Canonical dsdgen rows stream through the pkg/datagen/tpcdsgen batch adapter and Bench.InsertTpcds.

Run it

./build/stroppy run tpcds -d pg \
  -D url=postgres://postgres:postgres@localhost:5432/stroppy \
  --scale-factor 0.01

# Greenplum, Greengage, WarehousePG and Cloudberry use the pg driver and their own schema.
./build/stroppy run tpcds -d pg --sql-dialect gp \
  -D url=postgres://gpadmin@localhost:5432/stroppy --scale-factor 1

./build/stroppy run tpcds -d mysql \
  -D 'url=root:pass@tcp(127.0.0.1:3306)/stroppy?charset=utf8mb4&parseTime=true' \
  --scale-factor 0.01 --load-workers 4

./build/stroppy run tpcds -d ydb \
  -D url=grpc://localhost:2136/local --scale-factor 0.01

# Picodata supports loading only; query execution is not yet supported.
./build/stroppy run tpcds -d pico \
  -D url=postgres://admin:T0psecret@localhost:1331/admin --scale-factor 0.01 \
  --no-steps workload

Typed workload parameters are listed by stroppy run tpcds --help:

  • --scale-factor accepts any positive value.
  • --load-workers sets the workers used to load each table.
  • --pg-unlogged uses PostgreSQL unlogged tables during the load.
  • --ydb-store-mode selects column (default) or row tables on YDB.
  • --streams selects the number of generated query streams.
  • --query-stream explicitly selects one generated stream; when omitted, the checked-in canonical query set is used.
  • --query-seed seeds generated streams.
  • --schema-file and --sql-file override the dialect schema and query files.
  • --sql-dialect selects a built-in dialect: pg, gp, or mariadb; empty picks by driver. pg and gp need the pg driver. gp is for Greenplum-family MPP servers and cannot be combined with --schema-file or --pg-unlogged; --sql-file works with it and runs your queries on the gp schema. mariadb needs the mysql driver, uses the MySQL schema and mariadb.sql, and applies to generated streams too. Schema/query file overrides still take precedence.
  • --validate-force compares reference answers outside SF=1 on supported baked query dialects; comparison remains diagnostic.

Reason cardinality follows specification 4.0.0 Table 3-2: 35 rows at SF=1, 65 at SF=1000, and 75 at SF=100000. The kit 4.0.0 C generator instead fixes this table at 75 rows, which disagrees with its qualification answers. Reload existing datasets after this correction: return-fact reason keys change too. C-oracle comparisons for reason and return tables require the specification's reason counts in tools/scaling.dst before compiling the reference generator.

Some static dimensions do not shrink with the scale factor. customer_demographics, for example, always has about 1.9 million rows, so small-scale loads are still substantial.

Setup and execution

Setup runs these gatable steps in order:

  1. drop_schema
  2. create_schema (or create_schema_column for YDB column storage)
  3. set_unlogged when requested on PostgreSQL
  4. load_data, streaming all 24 tables through driver.InsertRequest
  5. create_indexes
  6. set_logged when unlogged loading was enabled
  7. analyze
  8. validate_answers for the baked query set on PostgreSQL, MySQL, TiDB, YugabyteDB, or YDB

On supported query dialects, each workload iteration resolves the selected stream and executes every query in order. The default is the checked-in canonical qualification set. On PostgreSQL, MySQL and TiDB, --query-stream generates a reproducible stream in process, while --streams N assigns generated streams to virtual users for a throughput run. Each stream uses its own reproducible parameter values and query order derived from --query-seed.

./build/stroppy run tpcds -d pg --scale-factor 1 \
  --query-stream 0 --query-seed 42
./build/stroppy run tpcds -d pg --scale-factor 1 --streams 4 \
  --executor constant-vus --vus 4 --duration 10m

Generated streams include all 99 query IDs (103 SQL statements, including multipart queries). Any generator skip is a setup error listing the unsupported queries, rather than a successful partial run. Load-only runs that exclude the workload step do not require query generation.

YDB supports only the baked power-test query set; generated streams are rejected. PostgreSQL, MySQL, TiDB, YugabyteDB, and YDB compare the same SF=1 reference answers. YDB submits complete YQL requests on pinned connections. Differences and query errors remain visible as failed validation results; a warning explains that data generation, SQL-port semantics, collation, or numeric behavior may be responsible rather than a database defect. Cross-engine answer comparison is diagnostic, not certification of the SQL port.

Per-query measurements

Every attempted workload statement records tpcds_query_<N>_duration in milliseconds, plus _runs, _errors, and _elapsed_total counters. Multipart queries use _a and _b before the metric suffix, for example tpcds_query_14_a_duration. Baked queries and generated streams use the same names. SQL overrides retain their original query labels. Labels containing characters other than ASCII letters, digits, and underscores, or exceeding 200 bytes, use the metric suffix custom_<SHA-256 of the original label>. Labels already beginning with custom_ are hashed too, so literal labels cannot impersonate an encoded label. Setup and answer validation do not enter these measurements.

Duration covers the complete Bench.Exec call, including cursor closure and error detection. SQL failures, statement timeouts, and canceled in-flight attempts retain their elapsed time; queries not started after cancellation do not count. _runs counts all attempts, _elapsed_total sums all attempt times, and _errors counts failed attempts. Histogram series carry outcome=success|error, so successful timings remain separable for scoring. The metric-level count, sum, and average combine both outcomes; use successful series and reject incomplete/error-containing runs for a complete-suite score. These are 103 statement measurements, not a computed 99-query TPC-DS score.

Histograms store a fixed number of bucket counts, not a bounded list of samples: run length and observation count do not consume additional sample storage. Buckets span sub-millisecond timings through hours and days, ending at the largest Go duration. Counts and sums preserve long observations; percentiles are approximate bucket upper bounds, not exact timings. Repeated iterations and concurrent streams aggregate by query/outcome rather than retaining each pass. Summary, JSON report, and OTLP expose the same measurements. Abrupt process termination or unavailable telemetry transport cannot guarantee remote delivery.

Qualification provenance

dsqgen/qualification.json contains the fixed substitutions from Appendix B of the TPC-DS 4.0.0 specification, including its PDF SHA-256. Qualification is not an RNG seed: the kit's dsqgen -QUALIFY selects ascending query order but does not apply Appendix B values. Random query streams remain independently seeded.

make gen-tpcds-qualification renders pg.sql, mysql.sql, and mariadb.sql from that manifest through the shared dialect transforms, retaining grouped-average rewrites and numeric/date portability fixes. Contract tests require the embedded files to match this output. Picodata and YDB retain their existing SQL ports with the same fixed substitutions; this does not expand their supported query features.

Reference rows are imported from the kit's answer_sets with go run ./cmd/tpcds-answers -in PATH/answer_sets -out workloads/tpcds/answers_sf1.json. The importer removes terminal formatting, normalizes NULL markers and source encoding, and preserves both result blocks for multipart queries. No expected rows are generated from Stroppy execution.

make integration-tpcds-sf1 loads a fresh PostgreSQL SF=1 database and requires all 103 reference comparisons to pass, with no skips, errors, or differences. The integration workflow runs this check before publishing its tested binary. Use a disposable database: this target resets its schema.

Dialect status

  • PostgreSQL: all 103 baked statements, generated streams, and answer comparison are supported.
  • Greenplum family (--sql-dialect gp): Greenplum 6 and 7, Greengage 6 and 7, WarehousePG and Apache Cloudberry run the pg queries on schema.gp.sql, which differs from the pg schema in physical design only. The fact tables, inventory and item are hash-distributed on their *_item_sk, so item joins and sales-to-returns joins stay on one segment; customer, customer_address and customer_demographics are hash-distributed on their key; the other dimensions are DISTRIBUTED REPLICATED. Fact tables and the three large dimensions are append-optimized, column-oriented, zstd level 1. There are no constraints, indexes or partitions, as in the pivotalguru/TPC-DS Greenplum kit, whose distribution keys these follow (it hashes the sales and returns tables on item plus ticket or order number; one column keeps them co-located with item as well). The server is checked before the first step: a Greenplum-family server without --sql-dialect gp, or gp against plain PostgreSQL, stops the run before any statement changes the database. A run with --schema-file skips this check, because its author owns the schema; --sql-file alone does not. Generated streams work.
  • MySQL: all 103 baked statements, generated streams and answer comparison are supported. Generated queries 51 and 97 emulate FULL OUTER JOIN with LEFT JOIN UNION ALL an anti-matched RIGHT JOIN.
  • MariaDB 11.8 (-d mysql --sql-dialect mariadb): uses dedicated mariadb.sql and the MySQL schema. Rollup rows are sorted and limited in an outer query; explicit grouping levels replace MySQL-only GROUPING() while preserving real NULL keys and hierarchy ranks. Baked queries, generated streams, and diagnostic answer comparison use this dialect; the default MySQL dialect is unchanged. make integration-tpcds-mariadb checks all statements in baked, generated, and two-stream modes at SF=0.01, then compares rollup results with MySQL fixtures. Set STROPPY_MARIADB_URL and STROPPY_MYSQL_URL to disposable databases; this target resets both schemas. MariaDB defaults to localhost:3368, MySQL to localhost:3307.
  • TiDB: uses the MySQL baked queries, generated streams, and shared answer comparison.
  • YugabyteDB: uses PostgreSQL baked queries, generated streams, and shared answer comparison.
  • YDB: all 103 baked statements run on the YQL port, with shared answer comparison. Column storage is the default; row storage is optional. Dates are ISO text because TPC-DS spans years outside YDB's date epoch. Generated streams are not supported.
  • Picodata: loading is supported. Query execution is not supported yet; use --no-steps workload for load-only runs.

The SQL ports also account for each engine's date arithmetic, grouping, correlated-subquery, join, and type restrictions. See the headers of the checked-in dialect files for the exact rewrites.

Benchmark coverage

Ordinary runs retain the load and query-only behavior above. Opt into the complete execution sequence on PostgreSQL, Greenplum-family servers (--sql-dialect gp), MySQL, or MariaDB (--sql-dialect mariadb):

./build/stroppy run tpcds -d pg -D url=postgres://... \
  --benchmark=true --scale-factor 1 --streams 4

--benchmark=true uses one power stream, then Sq concurrent streams, maintenance test 1, another Sq streams, and maintenance test 2, without reloading or changing the selected database. --streams defaults to 4 in this mode and must be even and at least 4. Each query stream runs all 99 query IDs (103 statements) once on its own retained session, in Appendix D order. The normal executor, iteration, and VU settings must remain at their single-iteration defaults. Custom schema/query files, explicit --query-stream, and unlogged loading are rejected before setup. Query substitutions use the existing reproducible --query-seed generator, not certified reference-dsqgen substitutions.

The native dsdgen implementation generates refresh flat files before database loading. Generation is not timed as maintenance; reading/loading staging files, SQL transformations, deletes, and commits are timed. Each maintenance test applies Sq/2 distinct numbered sets serially. All four delete functions precede the seven load functions, and each function has its own transaction. tpcds.benchmark in the JSON report records each attempted function's set, committed rows, elapsed seconds, and failure status, plus per-phase/per-stream query measurements.

The generator follows kit 4.0.0 source-schema values, with specification-correct reason cardinalities. It corrects the kit's reused order IDs: refresh ticket/order ranges start after initial data and all earlier refresh sets, preserving sales/returns relationships and avoiding duplicate warehouse keys. Oracle tests normalize only these corrected IDs before comparing every remaining byte.

Full returns can leave a query 75 previous-year group with zero net quantity; this alone does not indicate mismatched refresh keys. PostgreSQL (including the Greenplum-family dialect), MySQL, and MariaDB guard that divisor with NULLIF, excluding undefined ratios while retaining comparisons with nonzero divisors. This applies to generated streams and baked qualification queries.

Maintenance permanently changes the dataset. A database-side stroppy_tpcds_refresh marker rejects replay, wrong scale, and continuation after an incomplete or ambiguous set. Fresh loads start at set 1. Continue existing data only with explicit --refresh-set from the preceding successful report and both drop_schema and create_schema disabled; inconsistent selections fail before setup:

./build/stroppy run tpcds -d pg -D url=postgres://... \
  --benchmark=true --scale-factor 1 --streams 4 --refresh-set 5 \
  --steps generate_maintenance,prepare_maintenance,maintenance_1,maintenance_2

next_set is omitted after a failed run. Earlier successful functions can remain committed; reload or reconcile rather than replaying a failed set. A fresh benchmark load resets the marker. Do not drop the marker while retaining maintained data.

The report includes sq, scale_factor, t_ld, t_pt, t_tt, and t_dm in decimal hours, with qphds_at_sf calculated as:

Q = Sq * 99
T_PT = T_Power * Sq
T_TT = T_Throughput1 + T_Throughput2
T_DM = T_Maintenance1 + T_Maintenance2
T_LD = 0.01 * Sq * T_Load
QphDS@SF = floor(SF * Q / (T_PT * T_TT * T_DM * T_LD)^(1/4))

sequence_valid is true only for a complete successful fresh-load sequence. Filtering phases, continuing an existing dataset, query errors/timeouts, maintenance failures, and cancellation withhold the score. A score is an execution measurement, not a certified TPC result: tpc_compliant remains false. Fractional scales are development measurements; pricing, audit, accessibility testing, and reference-generator substitution compliance are not established. Cloud consumers must require sequence_valid and a present score, and retain the scale factor when comparing results.

Run shapes and two-pass runs

./build/stroppy run tpcds --executor shared-iterations --iterations 1
./build/stroppy run tpcds --executor constant-vus --vus 4 --duration 10m

# Load first, then measure the existing data.
./build/stroppy run tpcds --scale-factor 1 --no-steps workload
./build/stroppy run tpcds --executor constant-vus --vus 4 --duration 10m \
  --steps workload

A normal run with no step filter performs setup and measurement together. Environment variables and -e remain compatibility inputs; direct flags and typed config are preferred.

The standalone query-stream generator remains available through make gen-tpcds-streams.

Documentation

Overview

Package tpcds owns Stroppy's TPC-DS implementation, tests, dialect SQL, and answer data. It loads 24 tables through the canonical generator and runs the 99 business queries from the baked qualification set or an in-process stream. Supported baked dialects share SF=1 multiset comparison against answers_sf1.json.

Index

Constants

This section is empty.

Variables

View Source
var Test = bench.Test{Name: "tpcds", Define: define, Source: publication("Test")}

Functions

This section is empty.

Types

This section is empty.

Directories

Path Synopsis
Package dsdgen is a spec-faithful Go port of the TPC-DS data generator (dsdgen, v4.0.0).
Package dsdgen is a spec-faithful Go port of the TPC-DS data generator (dsdgen, v4.0.0).
Package dsqgen is a lean port of the TPC-DS query generator (dsqgen).
Package dsqgen is a lean port of the TPC-DS query generator (dsqgen).
cmd/dsqgen command
Command dsqgen renders TPC-DS query streams for a target SQL dialect, drawing valid, scale-correct parameter values with a seeded RNG (it does not reproduce the C dsqgen's exact value stream).
Command dsqgen renders TPC-DS query streams for a target SQL dialect, drawing valid, scale-correct parameter values with a seeded RNG (it does not reproduce the C dsqgen's exact value stream).
published
source
Package source defines the row-production contract that every load-time data generator implements and every driver consumes.
Package source defines the row-production contract that every load-time data generator implements and every driver consumes.
workloadtest
Package workloadtest provides contract assertions for embedded workload assets.
Package workloadtest provides contract assertions for embedded workload assets.
Package tpcdsgen adapts the ported TPC-DS dsdgen generator (third_party/gotpcds/dsdgen) to the source.Partitionable contract, so the spec-faithful, byte-exact generator feeds any Stroppy driver through the same load path as the native evaluator and the TPC-H generator.
Package tpcdsgen adapts the ported TPC-DS dsdgen generator (third_party/gotpcds/dsdgen) to the source.Partitionable contract, so the spec-faithful, byte-exact generator feeds any Stroppy driver through the same load path as the native evaluator and the TPC-H generator.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL