Temporary production-clone migration gate¶

This page is for maintainers who need to collect PostgreSQL diagnostics and check reads against a disposable copy of a production database. It covers the safety checks, how to prepare a clone, how to run the gate, and the evidence recorded from earlier runs.

The gate is temporary and exists only for migration work. It runs on your machine against a restored PostgreSQL clone. It is not part of the normal BenchDB workflow, and the repository does not deploy it.

The current server needs current migrations and a matching local archive. The helper still selects samples and collects row counts, SQL timings, and query plans directly from PostgreSQL. Those queries omit results that have moved to Parquet. A passing gate does not establish complete archive correctness or performance; check archived history through the analytical store and running HTTP service. See Managed Parquet storage for full-archive measurements and their limits.

Never commit passwords, tokens, .pgpass entries, service credentials, private hostnames, private IP addresses, raw production-derived payloads, query plans with identifiers, or raw server logs from these runs.

What the gate checks¶

scripts/prod_clone_compat.sh builds BenchDB, starts benchdb serve against the clone with a read-only connection. For the PostgreSQL samples it can find, it checks that:

• the server starts and answers /api/ping,
• the read API returns valid data for the series list, result list, result detail, history by result, history by fingerprint, result compare, and CI report,
• the CLI reads the same server with results get, series list, and compare,
• row counts in the checked tables are the same before and after the run,
• the server log contains no blocked-write messages.

The row-count check detects changes in the number of rows. The log scan catches writes that the read-only role or default_transaction_read_only blocked. Both must pass for gate acceptance; they do not replace the dedicated read-only role.

The script drives a hidden prod-clone command group under admin in the main benchdb binary. General CLI help does not list it, and it is not a supported user workflow.

Safety conditions¶

Set these variables before every run:

export BENCHDB_PROD_CLONE_DB_URL='postgresql://<read-only-role>@<clone-host>:<port>/<clone-database>'
export BENCHDB_PROD_CLONE_CONFIRM=read-only
export BENCHDB_PROD_CLONE_READONLY_ROLE='<read-only-role>'
export BENCHDB_PROD_CLONE_DATA_DIR='<matching-clone-archive-directory>'

The gate stops before it connects unless:

• BENCHDB_PROD_CLONE_DB_URL is set, uses the postgres or postgresql scheme, and names a host and a database,
• BENCHDB_PROD_CLONE_DATA_DIR names the migrated clone's existing archive directory,
• BENCHDB_PROD_CLONE_CONFIRM is exactly read-only,
• BENCHDB_PROD_CLONE_READONLY_ROLE is set. The only exception is a development dry run, described below.

Preflight then connects and fails unless every one of these holds:

• The connected database has the name in the URL.
• The address the server reports matches the URL host. When BENCHDB_PROD_CLONE_EXPECTED_HOSTS is set, it must match one of those entries instead.
• The server port matches the URL port, or 5432 when the URL has none.
• The connected role is BENCHDB_PROD_CLONE_READONLY_ROLE.
• The connected role is not the development role. That role is benchdb_dev unless BENCHDB_PROD_CLONE_DEVELOPMENT_ROLE names another.
• The role has no INSERT, UPDATE, DELETE, or TRUNCATE privilege on any checked table.
• The role is not a superuser.
• default_transaction_read_only is on.
• These tables exist: benchmark_result, case, context, info, hardware, commit, user, run_verdict, verdict_queue, and benchmark_verdict.

api_token is optional. When it exists, preflight checks its privileges and counts its rows. The row-count comparison covers every checked table except benchmark_result, which preflight checks for privileges only.

The harness also sets read-only connection options. The database role is still the boundary that cannot be bypassed, so do not run the gate with a role that can write.

If the server reports an address that differs from the URL host, list both:

export BENCHDB_PROD_CLONE_EXPECTED_HOSTS='<clone-host>,<server-reported-address>'

Keep that value out of committed files when it names private hosts or addresses.

Development dry run¶

BENCHDB_PROD_CLONE_ALLOW_DEV_ROLE=true lets the development role pass preflight while the dedicated read-only role is being set up. A dry run skips the expected-role and write-privilege checks. It never counts as an acceptance run, and the report fails it.

The server the gate starts¶

The script starts benchdb serve with an empty environment plus these variables:

The server therefore inherits no auth, session, seed, OIDC, GitHub token, or DATABASE_URL setting from your shell. The connection URL sets default_transaction_read_only=on, statement_timeout=30s, lock_timeout=2s, idle_in_transaction_session_timeout=30s, and application_name=benchdb-admin-prod-clone-compat.

The script stops if the port already serves a BenchDB API. On a read-only connection the server skips archive maintenance, artifact collection, and verdict computation. It reads the existing archive and stored statuses.

Prepare the clone¶

Use a clone with the current schema and its matching archive. A database-only copy is incomplete once results have moved to Parquet.

benchdb migrate refuses a database that has benchmark_result but no schema_migrations table. Follow Upgrading BenchDB for a raw legacy snapshot; do not mark migrations as applied by hand. For a BenchDB clone with a valid migration ledger, apply pending migrations with a writable role before the read-only run:

BENCHDB_DB_URL='postgresql://<writable-role>@<clone-host>:<port>/<clone-database>' \
  ./bin/benchdb migrate

Then give the dedicated read-only role SELECT on the tables and no write privilege. Preflight rejects a role that can write.

Run the gate¶

The script needs jq, curl, and uv, plus the Go and Bun toolchains for make build. From the repository root:

BENCHDB_PROD_CLONE_DB_URL='postgresql://<read-only-role>@<clone-host>:<port>/<clone-database>' \
BENCHDB_PROD_CLONE_CONFIRM=read-only \
BENCHDB_PROD_CLONE_READONLY_ROLE='<read-only-role>' \
BENCHDB_PROD_CLONE_DATA_DIR='<matching-clone-archive-directory>' \
scripts/prod_clone_compat.sh --profile

--profile adds serial HTTP timings, SQL timings, EXPLAIN plans, and relation sizes, and makes the report require them. On failure the script prints the last 80 lines of the server log and the report path.

The script writes artifacts to var/prod-clone-compat/, or to BENCHDB_PROD_CLONE_OUT_DIR when set. Git ignores that directory. The files can hold deployment paths, sample identifiers, fingerprints, query plans, and server logs. Delete them after you copy out sanitized aggregate numbers:

rm -rf var/prod-clone-compat/

Expected warnings¶

Sample selection is best effort. The report flags sample warnings for review, but these optional warnings do not fail the gate:

• an optional candidate-source query hit the statement timeout,
• optional sample metadata hit the statement timeout,
• an optional category, such as errored results, mixed units, or high-volume series, is missing from the sample manifest.

An empty sample manifest or missing compare sample does fail validation. Review optional warnings before relying on the run: it covers the selected PostgreSQL samples, not every historical data shape or archived result.

Recorded evidence¶

The first three timing sections below describe runs made before the repository history starts on 2026-08-27. They describe the server and dashboard of that time. The later recent-run experiment has its own limits. These runs are separate from the full-archive measurements, which include stored statuses for a limited verdict fixture.

Compatibility result¶

The profiled gate passed against a production-derived Postgres clone with about 100M result rows:

• Preflight was valid and acceptance-eligible before and after the run.
• The API probes passed for every route listed under What the gate checks.
• The CLI probes passed for results get, series list, and compare.
• Row counts did not change.
• The log scan found no blocked writes.

One table held almost all of the data:

The dimension tables were small by comparison.

First timings¶

HTTP probes with small page sizes. Targeted reads were fast. The unfiltered series list was the slowest call:

A browser probe at the dashboard's normal page sizes found slow pages caused by DOM size and layout:

These results led to row caps, chunked rendering, and a mobile table layout in the dashboard.

After the first fixes¶

Four causes were fixed:

• broad q search scanned commits and results before applying the case filter,
• default browse aggregated the latest result per fingerprint across the whole result table,
• list rows loaded full histories to draw status and sparklines,
• the profiler measured an old unbounded query instead of the one the product runs.

With bounded search, bounded row enrichment, and dashboard row caps, the clone returned:

The recent-runs SQL took 395 ms at page size 25 and 238 ms at page size 100. Its plan scanned a bounded set of the newest results through benchmark_result_timestamp_index, then aggregated by run_id through benchmark_result_run_id_index. It no longer depends on the 14-day predicate and partial index that the old Flask landing page used.

The browser probe loaded /api/ci/report in about 0.39 s for a 6.9 MB payload. The mobile CI report page rendered its first 200 of 4,568 comparison rows in about 1.4 s.

Default browse after the correctness fix¶

A review found that sampling recent commits without results could leave the series page sparse. An exact latest-per-fingerprint query fixed that but timed out on the clone, because it sorted the whole eligible history. Default browse then started from a bounded window of recent default-branch commits that had non-errored results. Search, fingerprint, run, history, and CI report routes handled exact lookups.

The follow-up run measured:

In a browser, /series loaded its 25-row page in about 4.1-4.2 s on desktop and mobile, with no console errors and no page-wide horizontal scroll. Browse was still the slowest page, but it stayed under the read timeout.

Exact recent-run paging at 106 million results¶

A later run on PostgreSQL 16 measured the recent-runs handler against a disposable copy with about 106 million results. The previous default query grouped the entire result table before selecting a page and exceeded a 15-second statement timeout.

The updated path walked timestamp indexes in batches until it found enough distinct runs. It then read those runs through the run-ID index to calculate complete counts and metadata. A repeatable-read transaction kept discovery and summaries consistent. There was no fixed time window or sampled result cap; deep offsets still required walking the preceding results.

Warm measurements used the real HTTP handler in process, with PostgreSQL's default automatic plan selection. They exclude network and browser time:

This experiment added a repository/timestamp index with run IDs included. It occupied about 10 GB on the disposable copy. That index build is not part of the managed-storage migration: the current server moves settled results into Parquet and keeps only the bounded ingestion table's indexes in PostgreSQL.

The SQL profiler now records candidate discovery and run summaries separately. Its HTTP probes measure complete requests, including all batches.

This was a focused performance check, not a new full compatibility gate. The snapshot lacked the current migration ledger and verdict tables. Empty run-verdict and queue fixtures were added only to the disposable copy to exercise the handler. Modern benchmark browsing, verdict computation, and a full legacy-data upgrade were not validated by this run.

What this means for storage¶

These PostgreSQL runs showed fast targeted reads by result, history, compare, and CI report. Broad browsing across benchmark_result remained expensive. The current managed Parquet storage moves settled results out of that table and uses run and series catalogs for browsing. Its scale measurements are recorded separately from the earlier PostgreSQL runs.

Record new evidence¶

Add only sanitized aggregate numbers to this page. Raw artifacts stay under the ignored var/ directory unless someone reviews and scrubs them.

Useful numbers to record:

• row-count and relation-size classes,
• request status and timing ranges,
• whether the row-count and log-scan checks passed,
• browser DOM and layout totals,
• conclusions about query families that affect the storage roadmap.

Do not record sample result IDs, history fingerprints, private repository URLs, private infrastructure details, credentials, or raw query plans.