# experiment.yml — Is pg_duckdb better than PostgreSQL on joins, even under concurrency
# and RAM pressure?
#
# STATUS: sample experiment, prototype scope. Resolved against
# contracts/contract_catalog.yml by bexhoma/spec.py into a `tpch.py`
# argument vector. See docs/Design-Catalog-Contract.md for the full
# contract design.
#
# Designed from the contract alone:
#   - "joins" -> workload.params.active_queries uses the catalog's own
#     labeled multi-way-join subset (workloads.tpch.params.active_queries.why:
#     "[5, 7, 8, 9, 21] = multi-way joins" in contract_catalog.yml) instead
#     of the full 22-query set.
#   - "10GB dataset" -> scaling_factor: 10 (catalog: scaling_factor unit is GB).
#   - "even under concurrency" -> workload.rounds sweeps parallel-client
#     counts from 1 up to 16 instead of a single round, so the comparison is
#     checked at low and high concurrency, not just once.
#   - "and under RAM pressure" -> resources.memory is a *list* instead of a
#     single value: every system in `systems:` is crossed against every list
#     entry, one resolved configuration per (system, memory) pair. This is
#     the same "list = swept, scalar = shared" idiom `rounds:` already
#     establishes for concurrency -- see resources.memory below and
#     bexhoma/spec.py::build_argv()'s cpu_cells/memory_cells handling. The
#     analytical-ssd profile's derive: formulas (e.g. shared_buffers =
#     0.3125 * memory_limit) are re-evaluated per cell, so PostgreSQL/PgDuckDB
#     each get 20GB shared_buffers at the 64Gi cell and 10GB at the 32Gi cell.
#   - repetitions: 3 satisfies the "min. 3 repetitions for conclusions"
#     behavioral rule from issue #764.

mode: run

title: "TPC-H joins under concurrency and RAM pressure: PostgreSQL vs. PgDuckDB at SF10"
hypothesis: "pg_duckdb outperforms PostgreSQL on join-heavy TPC-H queries (Q5, Q7, Q8, Q9, Q21) at SF=10 under a matched analytical-ssd profile, and the advantage (if any) holds as concurrent client load increases from 1 to 4 and RAM tightens from 64Gi to 32Gi"
discriminates: [system, concurrency, memory]

workload:
  name: tpch
  params:
    scaling_factor: 10
    timeout: 1200
    query_repeats: 3
    verify_result: true
    active_queries: [5, 7, 8, 9, 21]  # multi-way joins, per contract_catalog.yml's own labeling
  rounds: [1, 2, 3, 4]  # concurrency sweep: 1 client baseline, then 2/3/4 concurrent clients
  repetitions: 3  # issue #764 behavioral rule: minimum 3 repetitions for conclusions

loading:
  pods: 4
  threads: 4
  # shared default for every system below — indexes/stats matter directly for
  # join plan quality. Deliberately identical for both systems: this pair is
  # compared under a matched profile, so post_load is a "parity" input, same
  # as `profile: analytical-ssd` below. A systems[].post_load override (not
  # used here, on purpose) would let one experiment apply post_load to some
  # named systems and not others — see contract_catalog.yml's post_load comment.
  post_load: {indexes: true, constraints: true, statistics: true}

systems:
  - name: PostgreSQL
    profile: analytical-ssd
  - name: PgDuckDB
    profile: analytical-ssd  # same profile as PostgreSQL — this is what "parity" means for this pair
    override: {duckdb_force_execution: false}  # let pg_duckdb's own cost-based routing decide per query, as it would in real deployment

observe: {monitoring_sut: true, monitoring_cluster: true, monitoring_app: true}

placement:
  sut: cl-worker36
  loading: cl-worker19
  benchmarking: cl-worker19

resources:
  cpu: {request: 16, limit: 16}
  memory:
    - {request: 32Gi, limit: 32Gi}
    - {request: 64Gi, limit: 64Gi}  # "our servers have at most 64GB of RAM"
  storage: {size: 50Gi}
  # storage_class deliberately left unset: analytical-ssd's `requires.storage_class`
  # allows [ssd, null], and this run accepts ephemeral (fast node-local disk, no PVC,
  # no guaranteed-NVMe SLA) rather than requiring the cluster's declared `ssd` class
  # — see contract_catalog.yml's header comment
