ParadeDB Benchmarks

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 installed. If you are building pg_search with cargo pgrx, make sure to build in --release mode. It also requires AWS credentials to be available in order to load the data.

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:
  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 for the full dataset flow.

To reproduce a benchmark locally from existing CSV data, load the heap once and then run benchmark:

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

To reuse that heap through pgBackRest, stop Postgres before snapshotting or restoring:

PGDATA="/home/runner/.pgrx/data-18"
BACKREST_ARGS=(
  --stanza bench
  --repo-bucket paradedb-ci-benchmarks
  --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:

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, hnsw, ivfflat).
  • --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.
  • --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.

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, hnsw, ivfflat; 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.