Turn performance work into a repeatable regression contract
Benchmark Before/After Changes and Track Performance Regression per Metric/Data Product
Protect query and data-product performance with representative baselines, semantic checksums, tail percentiles, and reversible decisions.
Learning outcomes
Build before/after performance gates per query, metric, and data product using identical inputs and result checksums.
Report p50/p95/p99 and plan/work evidence so regressions are visible beyond averages.
Detect a deliberately non-sargable rewrite that returns the same rows but regresses the protected BI workload.
Define fixture-specific performance budgets without presenting them as universal thresholds.
Package performance decisions with ownership, rollback, known limitations, and environment/version metadata.
Chapter 26 begins from the accepted AtlasMart state established through Chapters 01–25: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit. Source progress remains committed through sequence 208; Chapter 20 metric contracts, Chapter 21 certified marts, Chapter 22 security controls, Chapter 23 lineage/ownership, Chapter 24 tests, and Chapter 25 SLO/incident practices remain authoritative. The 360,000-row benchmark below is a clearly labeled scale fixture for performance mechanics only; it never replaces these business control totals.
Runtime: Python 3.13.5 + SQLite 3.46.1. Benchmark grain: one synthetic paid sales line per row. Scale fixture: 360,000 rows across 180 dates, 100 products, and 5,000 customers; product 1 intentionally receives about 30% of rows and North receives about 50% of customer-linked rows. Storage: a local SQLite database, WAL journal mode, temporary structures configured in memory. Timing: 3 warm-up executions then 17 measured executions; reported cache state is warm. The operating-system cold cache is not forcibly cleared, so no “cold-cache” claim is made. Security: synthetic identifiers only. Distributed limitations: SQLite does not expose distributed shuffle bytes, warehouse queues, or hardware SIMD counters; those mechanisms are explained conceptually and, where useful, modeled explicitly rather than mislabeled as SQLite measurements.
1. Realistic problem: how do we keep today’s speedup from becoming next month’s regression?
AtlasMart has evidence that date-leading indexes improved the current benchmark. The risk now moves from “find one optimization” to “keep representative consumers protected as SQL, data volume, statistics, physical layout, and engine versions evolve.” A performance regression test compares a stable query/data-product contract against a controlled baseline and fails or investigates when measured behavior crosses an explicitly chosen budget.
2. Final before/after regression matrix
| Protected contract | Base p95 ms | Changed p95 ms | p95 improvement | Result checksum preserved? |
|---|---|---|---|---|
| BI_7D_CATEGORY | 24.93 | 10.19 | 59.1% | yes |
| ADHOC_30D_SEGMENT_CATEGORY | 85.27 | 79.03 | 7.3% | yes |
| POINT_HOT_PRODUCT_7D | 18.58 | 4.14 | 77.7% | yes |
| ELT_DAILY_PRODUCT | 256.02 | 162.26 | 36.6% | yes |
Every protected checksum stayed identical. On this run, none of the representative queries regressed at p95 after the physical change. That is evidence for this fixture, engine, and warm-cache environment only.
3. Regression tests are attached to contracts, not filenames
Map each benchmark to the metric/data product it protects.
BI_7D_CATEGORY protects the governed gross-revenue
calculation by category and date window;
ELT_DAILY_PRODUCT protects the build path for daily
product aggregates. If a query is rewritten but the contract is
unchanged, the golden result/checksum remains the oracle. If the
metric semantics intentionally change, version the metric first
and create a new baseline rather than “updating the expected
checksum” in place.
4. Controlled regression: same answer, disabled access path
-- Same ISO-date population in this fixture, but the expression blocks-- the date-range index access path used by the direct predicate.WHERE substr(order_date, 1, 10) BETWEEN '2026-09-15' AND '2026-09-21'The direct indexed BI query measured p95 10.19 ms. The expression-wrapped version returned the same category totals but measured p95 about 33.94 ms and the plan reverted to a full fact scan. A correctness-only test would pass; the performance regression gate catches the lost access path.
5. A fixture-specific acceptance policy
For this educational fixture only, AtlasMart treats any checksum change as an immediate correctness failure. A p95 regression greater than 10% on a protected workload triggers investigation rather than automatic acceptance. This 10% is deliberately a local lab rule, not a universal tuning threshold: real budgets should come from consumer SLOs, benchmark noise, cost, concurrency, and the magnitude of business impact.
{ "contract": "gross_revenue_usd.v1 / BI_7D_CATEGORY", "engine": "SQLite 3.46.1", "cache_state": "warm after 3 warmups", "runs": 17, "baseline_p95_ms": 24.93, "candidate_p95_ms": 10.19, "checksum_equal": true, "decision": "accept fixture change; retain rollback DDL"}6. Version and environment drift can invalidate a baseline
Planner behavior can change after an engine upgrade, statistics refresh, data-growth step, or physical-layout change. SQLite’s own documentation warns that EXPLAIN QUERY PLAN output format can change. Store the engine version, relevant settings, data fingerprint, query/metric version, cache disclosure, and concurrency configuration with each benchmark. Compare like with like; if the environment intentionally changes, establish a new baseline only after correctness and representative-load validation.
7. Cost and maintainability belong in the decision record
The two indexes consume storage and make writes/loads maintain index structures. The daily-category aggregate adds storage plus refresh/invalidation ownership. A cloud warehouse may price bytes scanned, slot/compute time, warehouse uptime, or another resource. Performance engineering therefore asks: how much user-visible latency/cost did the change save, what maintenance did it add, and can operators roll it back safely?
8. Final Chapter 26 decision record
| Decision surface | Evidence | Judgment for this fixture |
|---|---|---|
| Correctness | All protected result checksums unchanged | required and satisfied |
| Tail latency | p95 improves on all four representative queries | positive |
| Skew | hot product 30.08%; naive 8-way hash ratio 3.081 | requires attention in distributed engines |
| Concurrency | two-slot harness creates hundreds of ms of queue wait | workload policy matters |
| Materialization | same BI checksum; p95 ~0.03 ms in local aggregate | use only with Chapter 19 freshness contract |
| Approximation | naive distinct sample scales to impossible 56,200 | reject as metric substitute |
| Portability | SQLite indexes/plans are engine-specific | retain vendor-neutral mechanism language |
9. Migration and rollback
Ship physical changes independently from logical metric changes. For this lab, rollback means dropping the added indexes/materialized table and restoring the prior workload policy; the governed star schema and metric contracts stay untouched. In production, deploy changes with reversible DDL/configuration, capture before/after telemetry, and avoid destructive rewrites until the new path proves stable under real concurrency.
10. Production judgment and bridge
Accept a performance change only after result semantics, security policy, freshness/history behavior, retry/replay behavior, and reconciliation still pass. Performance evidence must name the workload, dataset, engine/version, cache state, concurrency, physical layout, and percentile—not just “faster.” Treat any cost or latency number in this chapter as fixture-specific, not a universal target. Keep rollback simple: indexes/materializations can be removed, query rewrites reverted, and workload-pool assignments restored while the logical model and governed metric contracts remain unchanged.
Next: Shared-Nothing vs Disaggregated Storage/Compute vs Serverless Warehouses: Architectural Consequences.
11. Verification checklist
- Exactly the same normalized result checksum is required for unchanged contracts.
- p50/p95/p99, cache state, run count, engine version, and dataset fingerprint are recorded.
- Performance budgets are tied to a consumer/data product, not copied as universal numbers.
- Representative BI, ad-hoc, point, and ELT workloads are all retained.
- Concurrency/queue behavior is tested separately from single-query speed.
- Rollback DDL/configuration exists before acceptance.
Knowledge check
Acceptance questions
- Why can a query be semantically correct but still fail a performance regression gate?
- When should an old baseline be replaced rather than compared directly?
- Why should a metric-version change create a new golden result instead of overwriting the old one?
Authoritative references
- SQLite — EXPLAIN QUERY PLANOfficial interpretation of scan/search operators, index use, temporary B-trees, and the warning that plan-output format is not a stable application API.
- SQLite — Query PlanningOfficial background on table scans, multi-column indexes, sorting, and why the planner chooses among semantically equivalent algorithms.
- SQLite — EXPLAINOfficial semantics and limitations of EXPLAIN/EXPLAIN QUERY PLAN used by the local lab.
- Python — statisticsUsed for deterministic latency summaries in the local benchmark harness.
- BigQuery — Understand reservationsNon-prerequisite vendor example showing how a cloud warehouse can isolate workloads with resource pools; the Chapter 26 concepts do not require BigQuery.
12. Lab cleanup/reset
The mandatory lab is local and synthetic. Delete
ch26_lab/atlasmart_perf.sqlite and the generated
benchmark JSON, then rerun the setup script to restore the
deterministic 360,000-row fixture. No cloud resources, accounts,
paid services, or production credentials are created.