Database Performance Testing in CI/CD: Query Benchmarks, Migration Impact Analysis, and Regression Detection

A query that runs in 8ms in development runs in 340ms in production after a schema change that nobody flagged as risky. The index that the query planner was relying on is now being bypassed because a new column changed the planner’s cost estimate. The migration looked clean in review. The linter said nothing. The tests passed. The incident started four minutes after the deployment.

Database performance regressions are harder to catch than schema safety issues because they are not boolean. A migration that adds a column without an index does not break anything immediately. It degrades performance gradually, or suddenly under load, or only on queries that nobody thought to test. The problem is that the information needed to detect these regressions exists before deployment: you have the query plans, the execution times, the table sizes, and the schema change. Building a CI pipeline that assembles these signals and fails loudly is the difference between catching a regression in review and discovering it at 2am.

Establishing Query Performance Baselines

A baseline is a recorded set of query execution times against a representative schema and dataset. Without a baseline, you cannot answer the question “did this change make queries slower?” You can only answer “how slow are queries right now?”

The baseline has four components: the query set, the dataset, the execution environment, and the recorded metrics. Each needs to be deterministic across runs.

Query set. Pull the top 50 queries by total execution time from pg_stat_statements in production. These are the queries that matter. Store them as parameterized SQL files, not application code queries, so they can run against a CI database without the application layer.

Dataset. Generate a seeded dataset that is large enough for the planner to make realistic decisions (typically 100K-500K rows per major table) but small enough to run in CI (under 5 minutes to load). Use a fixed random seed so the data is identical across runs.

Execution environment. Run benchmarks on the same Postgres version and same PostgreSQL configuration as production. work_mem, effective_cache_size, and random_page_cost all affect planner decisions. If your CI database uses defaults and production uses tuned values, your baselines will disagree on planner choices.

Recorded metrics. For each query, record P50, P95, and P99 execution times across 100 runs. Planning time is separate from execution time. Record both. A migration that invalidates the plan cache will show up as planning time regression even if execution time is unchanged.

Here is a TypeScript harness that runs a query suite and records percentile times:

import { Pool, PoolClient } from "pg";
import { readFileSync, readdirSync, writeFileSync } from "fs";
import { join } from "path";

interface QueryResult {
  queryFile: string;
  planningTimeMs: number[];
  executionTimeMs: number[];
}

interface BaselineRecord {
  timestamp: string;
  gitSha: string;
  queries: {
    queryFile: string;
    p50ExecutionMs: number;
    p95ExecutionMs: number;
    p99ExecutionMs: number;
    p50PlanningMs: number;
    p95PlanningMs: number;
  }[];
}

function percentile(sorted: number[], p: number): number {
  const idx = Math.ceil((p / 100) * sorted.length) - 1;
  return sorted[Math.max(0, idx)];
}

async function runQueryBenchmark(
  client: PoolClient,
  sql: string,
  iterations: number
): Promise<QueryResult> {
  // Warm up: run once to populate buffer cache
  await client.query(sql);

  const planningTimes: number[] = [];
  const executionTimes: number[] = [];

  for (let i = 0; i < iterations; i++) {
    const result = await client.query(`EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ${sql}`);
    const plan = result.rows[0]["QUERY PLAN"][0];
    planningTimes.push(plan["Planning Time"]);
    executionTimes.push(plan["Execution Time"]);
  }

  return {
    queryFile: "",
    planningTimeMs: planningTimes,
    executionTimeMs: executionTimes,
  };
}

export async function recordBaseline(
  connectionString: string,
  queryDir: string,
  outputPath: string,
  gitSha: string,
  iterations = 100
): Promise<void> {
  const pool = new Pool({ connectionString, max: 1 });
  const client = await pool.connect();

  const queryFiles = readdirSync(queryDir)
    .filter((f) => f.endsWith(".sql"))
    .sort();

  const records: BaselineRecord["queries"] = [];

  for (const file of queryFiles) {
    const sql = readFileSync(join(queryDir, file), "utf-8").trim();
    console.log(`Benchmarking ${file}...`);

    const result = await runQueryBenchmark(client, sql, iterations);
    const sortedExec = [...result.executionTimeMs].sort((a, b) => a - b);
    const sortedPlan = [...result.planningTimeMs].sort((a, b) => a - b);

    records.push({
      queryFile: file,
      p50ExecutionMs: percentile(sortedExec, 50),
      p95ExecutionMs: percentile(sortedExec, 95),
      p99ExecutionMs: percentile(sortedExec, 99),
      p50PlanningMs: percentile(sortedPlan, 50),
      p95PlanningMs: percentile(sortedPlan, 95),
    });

    console.log(
      `  P50=${records[records.length - 1].p50ExecutionMs.toFixed(2)}ms ` +
      `P95=${records[records.length - 1].p95ExecutionMs.toFixed(2)}ms`
    );
  }

  const baseline: BaselineRecord = {
    timestamp: new Date().toISOString(),
    gitSha,
    queries: records,
  };

  writeFileSync(outputPath, JSON.stringify(baseline, null, 2));
  client.release();
  await pool.end();
}

Commit the baseline JSON file to the repository alongside the schema snapshot. When the CI pipeline runs on a pull request, it records a new baseline on the post-migration schema and compares against the committed one.

Migration Impact Analysis

Before running a benchmark, you can detect the most common causes of query performance regressions by analyzing the migration SQL itself: sequential scans introduced by missing indexes, and plan changes caused by statistics invalidation.

The two patterns to catch are:

1. A new column is added to a table that existing queries filter or join on, but no index is created for that column.
2. A migration adds a new index, which can improve some queries but change the planner’s cost estimates for others (statistics are stale until ANALYZE runs).

After applying a migration to the CI database, run EXPLAIN on the query suite and compare plan nodes:

interface PlanNode {
  "Node Type": string;
  "Relation Name"?: string;
  Plans?: PlanNode[];
  [key: string]: unknown;
}

function extractSeqScans(node: PlanNode, tableName?: string): string[] {
  const scans: string[] = [];
  if (node["Node Type"] === "Seq Scan") {
    const table = node["Relation Name"] ?? "unknown";
    if (!tableName || table === tableName) {
      scans.push(table);
    }
  }
  if (node.Plans) {
    for (const child of node.Plans) {
      scans.push(...extractSeqScans(child, tableName));
    }
  }
  return scans;
}

export async function detectNewSeqScans(
  client: PoolClient,
  querySql: string,
  preMigrationPlan: PlanNode,
  queryFile: string
): Promise<{ queryFile: string; newSeqScans: string[] }> {
  const result = await client.query(
    `EXPLAIN (FORMAT JSON) ${querySql}`
  );
  const postMigrationPlan: PlanNode = result.rows[0]["QUERY PLAN"][0]["Plan"];

  const preScanTables = extractSeqScans(preMigrationPlan);
  const postScanTables = extractSeqScans(postMigrationPlan);

  const newScans = postScanTables.filter((t) => !preScanTables.includes(t));

  return { queryFile, newSeqScans: newScans };
}

Run this for every query in the benchmark suite against both the pre-migration and post-migration databases. Any query that picks up a new sequential scan is a regression candidate. Not every sequential scan is a problem (small tables are often faster with a seq scan), so the gate should flag new sequential scans on tables with more than a configurable row threshold, not all tables.

Integrating pgbench and Custom Query Suites

pgbench runs workloads against a Postgres database and reports transactions per second and latency percentiles. It is useful for connection pool stress testing and overall throughput measurement, but it cannot run your specific application queries directly. Use both: pgbench for throughput baselines and your custom harness for query-specific P95 regression detection.

A pgbench custom script file that mirrors a common application read pattern:

-- benchmarks/pgbench/read-user-orders.sql
\set user_id random(1, 100000)
SELECT
  o.id,
  o.created_at,
  o.total_cents,
  u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.user_id = :user_id
  AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 20;

Run this in the CI pipeline as part of the benchmark job:

pgbench \
  --file=benchmarks/pgbench/read-user-orders.sql \
  --client=10 \
  --jobs=4 \
  --transactions=500 \
  --no-vacuum \
  "$DATABASE_URL" \
  > benchmarks/results/pgbench-output.txt 2>&1

Parse the output in TypeScript to extract the latency numbers:

import { readFileSync } from "fs";

interface PgbenchResult {
  tps: number;
  latencyAvgMs: number;
  latencyStddevMs: number;
  // pgbench does not natively output P95; use --progress-timestamp and post-process
}

export function parsePgbenchOutput(outputPath: string): PgbenchResult {
  const output = readFileSync(outputPath, "utf-8");

  const tpsMatch = output.match(/tps = ([\d.]+) \(without/);
  const latencyMatch = output.match(/latency average = ([\d.]+) ms/);
  const stddevMatch = output.match(/latency stddev = ([\d.]+) ms/);

  if (!tpsMatch || !latencyMatch) {
    throw new Error(`Could not parse pgbench output:\n${output}`);
  }

  return {
    tps: parseFloat(tpsMatch[1]),
    latencyAvgMs: parseFloat(latencyMatch[1]),
    latencyStddevMs: stddevMatch ? parseFloat(stddevMatch[1]) : 0,
  };
}

For P95 latency from pgbench, use --log to write per-transaction timing to a file, then compute percentiles from that log. This is more accurate than relying on the summary statistics.

Setting Performance Gates

A performance gate compares the current benchmark results against the committed baseline and fails the build if regressions exceed a threshold. The threshold needs to account for measurement noise. Benchmarks in CI have more variance than benchmarks on dedicated hardware: other jobs share the runner, the buffer cache starts cold, and network latency to the database service container varies.

A P95 regression threshold of 20% is a reasonable starting point. P50 regressions are noisier and generate more false positives. P99 regressions are meaningful but can be caused by a single outlier run.

interface RegressionReport {
  queryFile: string;
  baselineP95Ms: number;
  currentP95Ms: number;
  regressionPct: number;
  exceeded: boolean;
}

export function checkRegressions(
  baselinePath: string,
  currentPath: string,
  thresholdPct = 20
): { passed: boolean; regressions: RegressionReport[] } {
  const baseline = JSON.parse(readFileSync(baselinePath, "utf-8")) as BaselineRecord;
  const current = JSON.parse(readFileSync(currentPath, "utf-8")) as BaselineRecord;

  const reports: RegressionReport[] = [];

  for (const baselineQuery of baseline.queries) {
    const currentQuery = current.queries.find(
      (q) => q.queryFile === baselineQuery.queryFile
    );

    if (!currentQuery) {
      console.warn(`Query ${baselineQuery.queryFile} missing from current results.`);
      continue;
    }

    const regressionPct =
      ((currentQuery.p95ExecutionMs - baselineQuery.p95ExecutionMs) /
        baselineQuery.p95ExecutionMs) *
      100;

    reports.push({
      queryFile: baselineQuery.queryFile,
      baselineP95Ms: baselineQuery.p95ExecutionMs,
      currentP95Ms: currentQuery.p95ExecutionMs,
      regressionPct,
      exceeded: regressionPct > thresholdPct,
    });
  }

  const regressions = reports.filter((r) => r.exceeded);

  for (const r of reports) {
    const status = r.exceeded ? "FAIL" : "PASS";
    console.log(
      `[${status}] ${r.queryFile}: baseline=${r.baselineP95Ms.toFixed(2)}ms ` +
      `current=${r.currentP95Ms.toFixed(2)}ms (${r.regressionPct > 0 ? "+" : ""}${r.regressionPct.toFixed(1)}%)`
    );
  }

  return { passed: regressions.length === 0, regressions };
}

Call this at the end of the benchmark job and exit with a non-zero code if regressions are found. The build fails, the PR cannot be merged, and the engineer knows exactly which queries regressed and by how much.

One important caveat: a regression gate only works if the baseline is representative. If the baseline was recorded on a nearly empty dataset and the PR’s benchmark runs on a seeded dataset, the comparison is meaningless. Enforce dataset consistency by seeding with a fixed script and a documented row count in the repository.

Connection Pool Stress Testing

Slow queries under load often reveal themselves only when the connection pool is saturated. A query that runs in 12ms with one connection may run in 180ms when 20 concurrent clients are all holding connections and the pool is exhausted.

The stress test has two goals: verify that the application does not exhaust the pool under expected concurrency, and measure query latency degradation as concurrency increases.

import { Pool } from "pg";

interface StressTestResult {
  concurrency: number;
  totalRequests: number;
  successRate: number;
  p50Ms: number;
  p95Ms: number;
  poolExhaustionErrors: number;
}

export async function runConnectionPoolStressTest(
  connectionString: string,
  querySql: string,
  concurrencyLevels: number[],
  requestsPerLevel: number
): Promise<StressTestResult[]> {
  const results: StressTestResult[] = [];

  for (const concurrency of concurrencyLevels) {
    // Pool size intentionally capped to match production pool size
    const pool = new Pool({ connectionString, max: concurrency });

    const latencies: number[] = [];
    let poolExhaustionErrors = 0;
    let successCount = 0;

    const workers = Array.from({ length: concurrency }, async () => {
      for (let i = 0; i < Math.floor(requestsPerLevel / concurrency); i++) {
        const start = performance.now();
        try {
          const client = await pool.connect();
          try {
            await client.query(querySql);
            latencies.push(performance.now() - start);
            successCount++;
          } finally {
            client.release();
          }
        } catch (err: unknown) {
          const error = err as Error;
          if (error.message.includes("timeout")) {
            poolExhaustionErrors++;
          }
        }
      }
    });

    await Promise.all(workers);
    await pool.end();

    const sorted = [...latencies].sort((a, b) => a - b);
    results.push({
      concurrency,
      totalRequests: requestsPerLevel,
      successRate: successCount / requestsPerLevel,
      p50Ms: percentile(sorted, 50),
      p95Ms: percentile(sorted, 95),
      poolExhaustionErrors,
    });

    console.log(
      `Concurrency ${concurrency}: P95=${results[results.length - 1].p95Ms.toFixed(1)}ms ` +
      `success=${(results[results.length - 1].successRate * 100).toFixed(1)}% ` +
      `pool_exhaustion=${poolExhaustionErrors}`
    );
  }

  return results;
}

Run this with concurrency levels matching your expected traffic profile: [5, 10, 20, 50]. If the P95 latency doubles between concurrency 10 and concurrency 20, the pool size or the query itself is the bottleneck. If pool exhaustion errors appear at concurrency 20, the pool max setting is too low for the load.

Gate on the highest concurrency level you expect in production, not on the single-connection benchmark. A query that passes the single-connection gate but fails at production concurrency is still a regression.

GitHub Actions Pipeline

Here is a complete workflow that ties all of this together:

name: Database Performance Testing

on:
  pull_request:
    paths:
      - "db/migrations/**"
      - "db/queries/**"
      - "benchmarks/**"
      - "src/**"

env:
  DATABASE_URL: "postgresql://test:test@localhost:5432/perfdb"
  BASELINE_PATH: "benchmarks/baseline.json"
  CURRENT_PATH: "benchmarks/results/current.json"
  REGRESSION_THRESHOLD: "20"

jobs:
  db-performance:
    name: Query Performance Regression Check
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:16
        env:
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
          POSTGRES_DB: perfdb
        ports:
          - 5432:5432
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5

    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: "20"
          cache: "npm"

      - run: npm ci

      - name: Apply schema snapshot
        run: psql "$DATABASE_URL" < db/schema-snapshot.sql

      - name: Seed benchmark dataset
        run: psql "$DATABASE_URL" < benchmarks/seed/seed-100k.sql

      - name: Capture pre-migration query plans
        run: npx ts-node benchmarks/capture-plans.ts --output benchmarks/results/pre-migration-plans.json

      - name: Apply pull request migrations
        run: |
          NEW_MIGRATIONS=$(git diff --name-only origin/main...HEAD \
            -- 'db/migrations/*.sql' | sort)
          for migration in $NEW_MIGRATIONS; do
            echo "Applying $migration"
            psql "$DATABASE_URL" < "$migration"
          done

      - name: Run ANALYZE after migrations
        run: psql "$DATABASE_URL" -c "ANALYZE;"

      - name: Detect new sequential scans
        run: |
          npx ts-node benchmarks/detect-seq-scans.ts \
            --pre benchmarks/results/pre-migration-plans.json \
            --queries db/queries/ \
            --min-rows 10000

      - name: Run query benchmark suite
        run: |
          npx ts-node benchmarks/run-benchmarks.ts \
            --queries db/queries/ \
            --iterations 100 \
            --output "$CURRENT_PATH" \
            --git-sha "$GITHUB_SHA"

      - name: Run pgbench workloads
        run: |
          pgbench \
            --file=benchmarks/pgbench/read-user-orders.sql \
            --client=10 \
            --jobs=2 \
            --transactions=500 \
            --no-vacuum \
            "$DATABASE_URL" \
            > benchmarks/results/pgbench-output.txt 2>&1

      - name: Run connection pool stress test
        run: |
          npx ts-node benchmarks/stress-test.ts \
            --concurrency 5,10,20 \
            --requests 200 \
            --output benchmarks/results/stress-test.json

      - name: Check performance regressions
        run: |
          npx ts-node benchmarks/check-regressions.ts \
            --baseline "$BASELINE_PATH" \
            --current "$CURRENT_PATH" \
            --threshold "$REGRESSION_THRESHOLD"

      - name: Upload benchmark results
        if: always()
        uses: actions/upload-artifact@v4
        with:
          name: benchmark-results-${{ github.sha }}
          path: benchmarks/results/
          retention-days: 30

A few implementation details worth calling out:

Run ANALYZE explicitly after applying migrations. Postgres updates table statistics lazily. If you run the benchmark immediately after a migration that adds a large amount of data or changes the table structure, the planner is working with stale statistics and the benchmark results will not reflect production behavior.

Upload benchmark results as artifacts on every run, including failures. When a regression is detected, you want to compare the full results from the failing run against the baseline, not just the summary output in the log.

Gate only on P95 execution time regressions, not planning time regressions alone. Planning time regressions matter but are harder to stabilize in CI. A P95 planning time regression that does not affect P95 execution time is worth investigating in review, not blocking a merge.

Tradeoffs

Approach	Signal quality	CI overhead	False positive rate	When to use
Plan change detection only	Medium: detects new seq scans, not timing	Low: single EXPLAIN per query	Low	Always. Add this before anything else.
Timing benchmark, 20 iterations	Low: too noisy for P95	Low: 1-2 minutes	High	Not recommended. Noise exceeds signal.
Timing benchmark, 100 iterations	Medium: stable P95 on warm cache	Medium: 5-10 minutes	Medium	Good default for most teams.
Timing benchmark, 500 iterations	High: stable P95 and P99	High: 20-30 minutes	Low	Use on slow pre-merge gates, not per-commit.
pgbench throughput test	Throughput under load, not per-query latency	Low: 2-3 minutes	Low	Useful for catching pool saturation and overall degradation.
Connection pool stress test	Latency under concurrency	Medium: 5 minutes	Low	Required before shipping queries that run at high concurrency.

The benchmark iteration count is where most teams get it wrong. Fifty iterations on a CI runner produces P95 values that vary by 30-40% between runs. At that noise level, a 20% regression threshold produces constant false positives or misses real regressions. Run at least 100 iterations. If your query suite is large and 100 iterations per query makes CI too slow, reduce the query set to the 20 most critical queries rather than reducing iterations.

Production Considerations

The CI benchmark database and production are never identical. The buffer cache state, the data distribution, and the hardware profile all differ. A query that passes the CI benchmark can still regress in production if production has different data skew, different Postgres configuration, or a hot buffer cache that makes the query faster than CI suggests.

Treat the CI gate as a regression detector, not an absolute performance guarantee. Its job is to catch regressions introduced by this specific PR compared to the baseline on identical infrastructure. If a query was slow before the PR and is still slow after, the gate will not flag it. That is a separate problem solved by production slow query monitoring, not CI benchmarking.

Baseline drift is the long-term problem. If you update the baseline every time a performance regression is accepted as intentional, the baseline degrades over time. Use a separate baseline management script that records why a baseline was updated and who approved it. A comment in the JSON file and a required reviewer for baseline updates is sufficient:

interface BaselineRecord {
  timestamp: string;
  gitSha: string;
  approvedBy?: string;
  regressionAcceptanceReason?: string;
  queries: { /* ... */ }[];
}

When a migration causes a legitimate P95 regression (because the schema change is necessary and the performance cost is accepted), update the baseline with the new numbers and document the reason. The next PR’s benchmark compares against the new accepted baseline, not the original.

Closing

Query performance regressions are predictable with the right instrumentation. Plan change detection catches the structural causes. Percentile benchmarks catch the measurable impact. Connection pool stress tests catch the concurrency effects. None of these require complex infrastructure, only a Postgres service container in CI and a few hundred lines of TypeScript. The cost is a few minutes of pipeline time per PR. The return is the ability to answer “did this migration make anything slower?” with data instead of guesswork.