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:
drop_schema
create_schema (or create_schema_column for YDB column storage)
set_unlogged when requested on PostgreSQL
load_data, streaming all 24 tables through driver.InsertRequest
create_indexes
set_logged when unlogged loading was enabled
analyze
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.