Workload Twin

Reading the top statements from pg_stat_statements is easy. Knowing which schema change makes the whole workload faster, without making some other query slower, is not. The workload twin answers that question with the database's own optimizer, before anything runs in production.

How it works

1. Snapshot (read-only). QueryInsights reads the schema (with constraints), table and index sizes, column statistics (pg_stats) and planner settings from production. It never writes to production.
2. Twin. It loads the snapshot into a separate, empty PostgreSQL server that QueryInsights runs (the local/twin image, same major version as production). Relation files are extended sparsely to production's page counts (0 bytes on disk) and pg_statistic gets production's statistics, so the twin's planner produces production's plans and costs.
3. Workload. The top N patterns (by total time and by calls) for the instance, with their real execution counts from query_stats_hourly.
4. Search. Candidate indexes are derived from what the planner actually does with every pattern (filters after scans, join keys, sort and group keys). A greedy search adds the candidate that most reduces Σ calls × plan cost for the whole workload, keeping only additions that make no query worse, then prunes anything a wider index already covers. When an LLM is configured, a schema agent also experiments on the twin with tools (evaluate_change_set, explain_pattern, table_stats, submit). Its submission is re-evaluated; the better proven result wins.
5. Proof. A change set is shown as Proven only when it saves cost, adds no regression and every new index is used by some plan. The dashboard shows every pattern's plan before and after.

Hypothetical indexes come from HypoPG, installed only in the twin image. Nothing is installed on production.

Fidelity (measured)

Seeded skewed production (1.5M orders, 2M events, Zipf-distributed users) against its twin, 20 patterns covering point lookups, ranges, sorts, joins, aggregates, IN lists, EXISTS, UPDATE and DELETE:

The one mismatch: the twin chose a parallel hash aggregate where production chose a sorted aggregate over the new index, at costs within 0.5%.

Applying a proven change set to production under the same load:

The predicted ranking of per-query savings matched the measured ranking with a Spearman correlation of 0.89. Costs are planner estimates; the savings ledger (planned) turns verified cost savings into milliseconds per instance.

EXPLAIN ANALYZE is not used on the twin: it executes against zero rows and measures an empty scan.

Setup

Build a twin image for each production major version:

podman build --build-arg PG_MAJOR=16 -t queryinsights-twin:16 local/twin
podman run -d --name qi-twin-16 -p 15416:5432 -e POSTGRES_PASSWORD=... queryinsights-twin:16

Add twin_dsn (a superuser connection to the twin server) to the instance:

[{"name": "prod", "dsn": "postgres://queryinsights:...@db:5432/app",
  "twin_dsn": "postgres://postgres:...@twin-host:15416/postgres"}]

Workload insights for that instance are then found and proven on the twin. QUERYINSIGHTS_TWIN_TOP_PATTERNS (default 50) sets how many patterns are evaluated together.

Requirements and limits:

• The production role needs pg_read_all_stats (already required) and SELECT on the tables (for pg_stats rows).
• Patterns must be plannable with EXPLAIN (GENERIC_PLAN), which needs PostgreSQL 16 or newer on both sides.
• The twin superuser runs COPY ... TO PROGRAM 'truncate ...' inside the twin container to size relation files. Run the twin only as a QueryInsights-managed container, never on a shared server.
• Statistics contain real production values (most-common values, histogram bounds). Treat the twin as sensitive as production.
• Only CREATE INDEX and DROP INDEX are evaluated today.
• CockroachDB and MySQL twins are planned and validated by spike: CockroachDB matched production cost on 8/8 queries in a spike; MySQL on 4/9 because it estimates indexed predicates by reading the (empty) index. The engine plans live in the repository under docs/plans/.

Running the fidelity tests

podman run -d --name qi-prod-16 -p 15426:5432 -e POSTGRES_PASSWORD=pw postgres:16
podman exec qi-prod-16 psql -U postgres -c 'CREATE DATABASE shop'
podman exec -i qi-prod-16 psql -U postgres -d shop < local/twin/seed_prod.sql
QI_PROD_DSN=postgres://postgres:pw@127.0.0.1:15426/shop?sslmode=disable \
QI_TWIN_ADMIN_DSN=postgres://postgres:pw@127.0.0.1:15416/postgres?sslmode=disable \
  go test -tags twin ./internal/whatif/ -v