# ParadeDB Benchmarks Vector CI uses pdbench for recall calibration and k6 for latency and throughput: - [Cohere](cohere/README.md) - [Hacker News](hn/README.md) - [MS MARCO](msmarco/README.md) SQL assets live under `assets//`, with engine-specific queries in `assets///`. Shared CI actions build pdbench, start the cluster, install comparison extensions, report index builds, and run k6 with profiling. Dataset workflows own fixture checks, recall profiles, and result context. For MS MARCO 113M vector CI, see [the MS MARCO pipeline](msmarco/README.md). RunsOn benchmarks default to Spot. For on-demand, add `benchmark-nonspot` before the PR benchmark trigger label, or select `runner_type=on-demand` in **Run workflow** (CLI: `-f runner_type=on-demand`). Stack Overflow comment-triggered runs also honor the label. Scheduled runs remain on Spot. This is a basic, single-query latency, benchmarking suite for ParadeDB. It executes all of the queries belonging to a dataset, capturing both hot and cold measurements of their performance. After setup, queries are executed until a 3-run window shows <0.1% variance/mean (or 10 runs pass). Then `--runs` samples are taken for the hot measurements. The cold measurement is always recorded from the first query run. ## Prerequisites The benchmarking scripts require a Postgres database with [`pg_search`](/pg_search) installed. If you are building `pg_search` with `cargo pgrx`, make sure to build in `--release` mode. It also requires AWS credentials with access to the CI account’s `paradedb-benchmark` bucket to load data and restore snapshots. ### pg_stat_statements Query timing is done via `pg_stat_statements`, so you'll need to configure it. The important settings are: - `pg_stat_statements` must be in `shared_preload_libraries`. - `ALTER SYSTEM SET shared_preload_libraries = 'pg_search,pg_stat_statements';` - After restarting Postgres, configure it: ```sql CREATE EXTENSION pg_stat_statements; ALTER SYSTEM SET pg_stat_statements.track_planning = on; ALTER SYSTEM SET pg_stat_statements.track = top; SELECT pg_reload_conf(); ``` New sessions will use these settings after the configuration reload. ## Usage The `benchmark` subcommand runs benchmarks against an already-loaded heap. `load-heap` bulk-loads CSV data into Postgres, `snapshot-heap` and `restore-heap` manage pgBackRest heap snapshots, and `sample`/`convert` prepare source data. See [DATASET_PREPARATION.md](DATASET_PREPARATION.md) for the full dataset flow. To reproduce a benchmark locally from existing CSV data, load the heap once and then run `benchmark`: ```bash POSTGRES_URL="postgresql://localhost:28818/postgres" cargo run --release -- load-heap \ --url "${POSTGRES_URL}" \ --dataset stackoverflow \ --size 100k cargo run --release -- benchmark \ --url "${POSTGRES_URL}" \ --dataset stackoverflow \ --index bm25 ``` Heap snapshots live at `s3://paradedb-benchmark/snapshots/{dataset}/v1/{size}/pg_backrest/`. To reuse that heap through pgBackRest, stop Postgres before snapshotting or restoring: ```bash PGDATA="/home/runner/.pgrx/data-18" BACKREST_ARGS=( --stanza bench --repo-bucket paradedb-benchmark --repo-path-prefix /snapshots --repo-region us-east-1 --repo-endpoint s3.us-east-1.amazonaws.com --repo-s3-key-type shared ) (cd ../pg_search && cargo pgrx stop pg18) cargo run --release -- snapshot-heap \ --dataset stackoverflow \ --size 100k \ --pgdata "${PGDATA}" \ "${BACKREST_ARGS[@]}" cargo run --release -- restore-heap \ --dataset stackoverflow \ --size 100k \ --pgdata "${PGDATA}" \ "${BACKREST_ARGS[@]}" (cd ../pg_search && cargo pgrx start pg18) ``` For more options: ```bash cargo run -- --help ``` ## Notable `benchmark` Options - `--dataset` defaults to "stackoverflow" - `--index` (required): Selects the index to build/benchmark, `datasets/{dataset}/indexes/{index}.sql` (e.g. `bm25`). - `--clear-caches` must be set to `false` if you're running on a non-Linux system. (It defaults to `true`). - `--skip-index`: Including this skips index creation (and the after-create-index hook). Useful for iterating on queries against an already-indexed database. - `--runs`: How many warm samples to capture from each query. Defaults to 3. - `--query-filter`: Runs only the queries whose name (`{query}` or `{query} - {variant}`) matches this regex. The dirty (`% visible`) runs of a matching query also run. - `--vacuum`: Controls whether `VACUUM FULL ANALYZE`, followed by `VACUUM ANALYZE` to update the visibility map, runs before the queries. Defaults to `true`. ## Notable Heap Options - `load-heap --size`: Selects `sampled/{size}/csv` under the data source. - `load-heap --data-source`: Overrides `s3_base_path` in `datasets/{dataset}/config.toml`. - `snapshot-heap --pgdata` / `restore-heap --pgdata`: Points pgBackRest at the stopped Postgres data directory. This can also be provided with `PGDATA`. - `snapshot-heap --config` / `restore-heap --config`: Uses an existing pgBackRest config instead of generating one. - `snapshot-heap --repo-*` / `restore-heap --repo-*`: Provides the S3 repository settings when generating a pgBackRest config. ## Benchmarking a PR The `benchmark-stackoverflow` label runs every Stack Overflow query on a PR. To run only some of them, add a conversation comment (not a review comment) with a regex. The workflow passes it to `--query-filter`: ```text @paradedb stackoverflow ^count_filter ``` With a size before the regex, the workflow runs only that dataset size. The `20m` size takes most of a full run, so `100k` or `1m` gives a faster result: ```text @paradedb stackoverflow 1m ^count_filter ``` The commenter needs write access, and the PR branch must be in `paradedb/paradedb`. The workflow replies with a link to the run. Then it posts the results to the PR, one comment for each dataset size. ## Datasets Each benchmark run uses a single dataset located under `datasets/$name`. The heap must already be present — loaded by `load-heap` (which reads from the dataset's `data-source` at the given `--size`) or restored from a snapshot. The queries that are benchmarked for a dataset are located directly under `datasets/$name/queries/`, or under `datasets/$name/queries/{index}/` when index variants need different SQL. Queries can be defined either as a nested directory (e.g. `queries/{query}/` containing a `README.md` and one or more `.sql` files representing variants such as `postgres.sql`, `hash_partitioned.sql`, `range_partitioned.sql`) or as a standalone `queries/{query}.sql` file. Each SQL file contains a single query. When multiple variants are present in a query directory, they are benchmarked and charted together under the `{query} - {variant}` naming convention. ### Dataset Directory Layout `datasets/{name}/`: - `config.toml` - `create_tables.sql` - `indexes/{index}.sql` (one file per index variant, e.g. `bm25`; chosen with `benchmark --index`) - `prewarm.sql` - `queries/{query}/*.sql` or `queries/{query}.sql` (or under `queries/{index}/`) - `after_create_index.sql` (optional) ### Preparing Datasets For preparing and managing non-synthetic datasets (loading source data, sampling, and conversion), see [DATASET_PREPARATION.md](DATASET_PREPARATION.md).