Introduce workload model: separate engine (backend) from index/query strategy (workload)

[jamessewell]

Problem

The current architecture conflates two distinct concepts under a single backend identifier:

1. The database engine and its driver (Postgres, MongoDB, Elasticsearch, …).
2. The index/query strategy you actually want to benchmark on that engine (native FTS, BM25 via pg_search, Atlas Search, GIN-on-tsvector, pg_trgm, pgvector, …).

This conflation forces three concrete problems:

1. Driver-level code duplication for non-driver reasons

backends/postgresfts/register.go and backends/paradedb/register.go are both "backends" today, but the postgres driver they use is identical. postgresfts literally points at postgres.New unchanged. paradedb wraps it only to attach two extra GUCs and a version query. The real difference between them is the index DDL in their dataset directories — not the driver. Registering them as separate backends exists solely so the loader can find different datasets/<dataset>/<backend>/pre.sql files.

2. Can't express "multiple workloads on the same backend"

You cannot today set up:

• postgres with native FTS and postgres with pg_trgm and postgres with pgvector — in one benchmark — without inventing three fake backend names.
• mongodb with Atlas Search vs. mongodb with native $text indexes.
• Two ElasticSearch index strategies (e.g., lexical vs. kNN).

Every comparison axis must go through the "backend" identifier, which is the wrong slot.

3. Config metadata is keyed by the wrong thing

Default connection URI, container name, env var, and dashboard color are all attached to backend types via Go init() registration. That means adding a new index-strategy workload (a small SQL file change) requires a new Go package, a new registered backend type, and a new build. The cost of trying things is much higher than it should be.

Why the in-flight rename PR doesn't solve it

The current postgresfts → postgres rename PR fixes the naming lie (Postgres FTS isn't an extension, it's a core feature, so the backend should just be called postgres). But it doesn't change the underlying model — paradedb is still a separate registered backend, and you still can't express multiple workloads on one engine.

Solution

Separate the two concepts:

• Backend stays a registered Go driver. One postgres backend, one mongodb backend, etc.
• Workload becomes first-class: a schema/index/query strategy that runs on a specific backend. Workloads have their own manifest, container coupling, default connection, and dataset content.

Directory layout

workloads/                       # global workload registry
├── postgresfts.yaml
├── paradedb.yaml
├── elasticsearch.yaml
├── opensearch.yaml
├── clickhouse.yaml
└── mongodb.yaml

datasets/sample/
├── schema.yaml
├── data.csv
├── paradedb/
│   ├── pre.sql                  # required
│   ├── post.sql                 # required
│   └── workload.yaml            # OPTIONAL — per-dataset overrides
├── postgresfts/
│   ├── pre.sql
│   └── post.sql
…

Per-dataset workload.yaml is optional and shallow-merged on top of the global manifest. Use it only when one dataset needs to override a specific field (e.g., a different connection URL).

Workload manifest schema

Field	Required	Default	Purpose
backend	yes	—	Registered driver name (postgres, mongodb, …)
container	no	workload name	Docker container to scrape for CPU/mem metrics
connection	no	none — must come from env/flag/k6	Default connection string
envVar	no	UPPER(workload_name) + "_URL"	Env var that overrides connection
description	no	—	Human-readable label
color	no	from palette	Dashboard chart color
extras	no	—	Backend-specific extras (e.g. extension-specific GUCs / metadata queries)

extras shape

extras declares extension- or workload-specific metadata to capture into benchmark results, on top of the generic metadata the driver always collects (Postgres GUCs like shared_buffers, server version, etc.). For Postgres-based workloads, this is how extension-specific GUCs and version queries get recorded.

extras:
  gucs:
    - paradedb.global_mutable_segment_rows
    - paradedb.global_target_segment_size
  queries:
    - SELECT paradedb.version_info() AS paradedb_version

Each queries entry is a single SQL statement returning one row with one column. The column name (AS …) becomes the key in the captured config map. The Go-side postgres driver already supports custom config queries via SetExtraQueries (today hardcoded in backends/paradedb/register.go); under the workload model these become declarative manifest content instead of Go code. Adding pgvector capture later is a YAML change, not a code change.

Examples

workloads/paradedb.yaml:

backend: postgres
container: paradedb
connection: postgres://postgres:postgres@localhost:5432/benchmark
description: ParadeDB pg_search BM25 index
extras:
  gucs:
    - paradedb.global_mutable_segment_rows
    - paradedb.global_target_segment_size
  queries:
    - SELECT paradedb.version_info() AS paradedb_version

workloads/postgresfts.yaml:

backend: postgres
container: postgres
connection: postgres://postgres:postgres@localhost:5433/benchmark
description: PostgreSQL native FTS (GIN + tsvector)

workloads/mongodb-atlas.yaml (future):

backend: mongodb
container: mongodb-atlas
connection: mongodb://localhost:27017
description: MongoDB with Atlas Search ($search operator)

Driver-level changes

• backends/paradedb/ deleted. ParadeDB becomes a workload (workloads/paradedb.yaml) on the postgres backend. The GUCs and version query that this package hardcodes today move into the workload manifest's extras block.
• backends/shared/postgres/driver.go already supports SetExtraGUCs / SetExtraQueries. Wire the workload manifest's extras through these existing hooks at driver construction.
• backends/elasticsearch/ and backends/opensearch/ stay as separate backends. Their differences (VersionInfoField, TLS skip behavior) are genuine driver-level differences, not just dataset content.

Loader CLI changes (cmd/loader/main.go)

• New --workload <name> flag. Picks the dataset subdir (datasets/<dataset>/<workload>/) and resolves connection/container/env-var from the manifest.
• When --workload is omitted, defaults to the --backend value (preserves 1:1 ergonomics for simple cases like elasticsearch, mongodb).
• Connection resolution order: --connection flag → manifest envVar → manifest connection field → error.

k6 config changes (backends.go)

workload becomes a field in each backend block:

backends: [
  { type: "postgres", workload: "postgresfts", alias: "pg-fts" },
  { type: "postgres", workload: "paradedb",    alias: "paradedb" },
]

When workload is omitted, defaults to type. At startup, the runtime loads the manifest and applies container/connection/extras to the driver.

Dashboard changes

Color map keyed by alias (or workload), not backend type. Aliases default to workload names, so existing run JSON stays readable.

Migration

1. PR 1 ✅ merged (Rename postgresfts backend to postgres #47): rename postgresfts backend → postgres at the Go level. Independent of the workload model.
2. PR 2: introduce the workload concept, backward-compatible. --workload defaults to backend type when absent. Add manifest support, workload field in k6 config, plumb extras into the existing SetExtraGUCs / SetExtraQueries hooks. ParadeDB stays as a backend in this PR.
3. PR 3: migrate paradedb from backend to workload. Delete backends/paradedb/. Add workloads/paradedb.yaml (with the GUCs/queries in extras) and workloads/postgresfts.yaml. Update dataset directory naming as needed. Update docs.

Acceptance criteria

• workloads/ directory exists with manifests for all current workloads.
• loader load --workload paradedb ./datasets/sample reads the manifest and uses the right container/connection/dir.
• k6 backend blocks accept a workload field; omitting it falls back to type.
• backends/paradedb/ is removed; paradedb runs as a workload on the postgres backend.
• The extras block in workloads/paradedb.yaml produces the same captured GUCs/queries that backends/paradedb/register.go produces today.
• Per-dataset workload.yaml (when present) overrides the global manifest field-by-field.
• Existing k6 scripts using backends.get("paradedb") continue to work unchanged.
• PARADEDB_URL and POSTGRES_URL env vars continue to work unchanged.
• Documentation (CONTRIBUTING, docs/datasets, docs/loader, docs/scripting) reflects the new model.

Out of scope

Adding new workloads beyond what exists today. Once the framework lands, things like postgres-pgvector, postgres-pgtrgm, mongodb-atlas vs mongodb-text, elasticsearch-knn become trivial follow-ups.