Proposal: add dedicated DB performance test project + CI benchmark job

[joeldickson]

Summary

Introduce a dedicated DB performance test project (new .csproj, similar structure to integration tests) and CI workflow job to benchmark query performance against seeded high-cardinality data.

This provides repeatable baseline + re-measure loops for index strategy decisions across PostgreSQL and SQLite.

---

Motivation

We need measurable evidence in CI to:

• capture baseline timings,
• validate index changes improve target endpoints,
• prevent accidental regressions.

---

Proposal

1) New project

Create a new project alongside integration tests:

• src/Agoda.DevExTelemetry.DbPerfTests/Agoda.DevExTelemetry.DbPerfTests.csproj

Project intent:

• host app using WebApplicationFactory<Program>,
• seed large deterministic + randomized dataset,
• wait for seed completion signal,
• call selected API endpoints,
• record timing distributions and compare to baseline.

2) Data seeding approach (DI-driven)

Inject startup seeding service via test host DI that:

• writes dataset through repository layer,
• combines deterministic + randomized data,
• creates realistic cardinality across:
  • project/repository/branch/platform,
  • local vs CI execution environment,
  • test runners,
  • date windows,
  • build categories + reload types,
• uses fixed random seed for reproducibility.

3) Readiness gate

Do not rely only on health startup.

Use two gates:

1. app startup health check,
2. explicit seed completion signal (test-only seed-status endpoint or in-memory completion marker exposed to test harness).

4) Endpoint benchmark set (initial)

• GET /api/dashboard/build-metrics
• GET /api/dashboard/test-runs
• GET /api/dashboard/api-build-summary
• GET /api/dashboard/clientside-build-summary
• GET /api/dashboard/test-run-summary

Run each with filter profiles matching real query shapes (date ranges + project/repo/branch/platform/env).

5) Engine matrix

Run tests for both:

• SQLite
• PostgreSQL

This is required because index behavior and planner behavior diverge by engine.

---

Seed volume contract (initial targets)

• BuildMetrics: ~100k rows
• TestRuns: ~50k rows
• TestCases: ~500k rows (avg ~10/test run)
• RawPayloads: ~50k rows

Cardinality targets:

• ~200 projects
• ~120 repositories
• ~400 branches
• representative platform/environment/test-runner spread

---

Threshold/regression strategy

Phase 1: stabilization (non-blocking)

• Run perf workflow nightly / on-demand.
• Publish artifacts only.

Phase 2: regression gating

• Use relative regression checks vs baseline (e.g., p95 must not regress >20%).
• Avoid hardcoded absolute ms thresholds on shared runners.

---

CI placement

Start as dedicated workflow:

• workflow_dispatch
• nightly schedule

After variance/runtime analysis, promote selected checks to PR gating if stable and practical.

---

Artifact format and consumption

Primary artifact: versioned JSON schema for machine comparison.

Minimum fields:

• endpoint
• filter profile
• engine
• sample count
• p50 / p95 / p99
• mean / stddev
• baseline delta (%)

Generate Markdown summary from JSON for human review in workflow output/PR comments.

---

Suggested rollout plan

1. Capture baseline query timings for key endpoints.
2. Add PostgreSQL composites first (and optional partial indexes if volume justifies).
3. Add minimal SQLite composites.
4. Re-measure and keep only indexes with measurable benefit.

---

Acceptance criteria (initial)

• New perf test .csproj committed and runnable locally.
• Deterministic+randomized seed generator implemented with target volume/cardinality.
• Seed completion gating implemented (separate from app startup health).
• Dedicated CI perf workflow runs SQLite + PostgreSQL matrix and uploads JSON + Markdown artifacts.
• At least one relative regression assertion per critical endpoint (after stabilization phase).
• Docs added for local execution and artifact interpretation.

---

Additional implementation notes (important)

A) Seeding performance strategy

Given target volume (~700k+ rows total), do not rely on naive row-by-row EF inserts.

• PostgreSQL: use bulk-friendly ingestion strategy (batched insert or COPY-style path where feasible).
• SQLite: prefer high-throughput batched inserts and consider prebuilt seeded DB artifact option for local fast loop.
• Keep repository-layer shape for realism where practical, but optimize seed path explicitly to avoid CI timeout risk.

B) Warm-up phase before measurement

Before capturing benchmark timings:

• run warm-up calls for representative endpoint mix (time- or iteration-based),
• ensure app startup/JIT/connection pool/ORM model initialization effects are not included in measured distribution.

C) Benchmark execution tooling

Do not reinvent stats collection.

Preferred options:

• .NET-native harness with robust distribution capture,
• or dedicated load test tool (e.g., NBomber/k6) integrated into workflow.

Whichever path is chosen must output the agreed JSON schema (p50/p95/p99/mean/stddev/delta).

D) Future PR gating reliability

When moving from nightly to PR-gating, use same-job A/B comparison where possible:

1. run baseline (main),
2. run candidate (PR),
3. compare deltas on same runner.

This reduces false positives from noisy shared CI hardware.

E) Index keep/remove decision criteria

Index retention is based on both sides:

• read-time improvement on target endpoints,
• write-time/ingest penalty overhead from maintaining additional indexes.

Do not keep indexes that improve read latency marginally while significantly degrading ingest throughput.

[joeldickson]

Excellent review — agreed on all major points. I propose these concrete adjustments to the implementation plan:

1) Threshold/regression strategy

• Phase 1 (stabilization): non-blocking, artifact-only runs for ~1 week.
• Phase 2: regression gate uses relative delta (e.g., p95 duration must not regress >20% vs baseline) rather than hardcoded ms.
• Baseline source: persisted JSON artifact committed under docs/perf-baselines/ (versioned schema) or uploaded/retrieved from workflow artifact store.

2) Readiness gate scope

• Add explicit seed completion signal (not just health):
  • either lightweight /internal/seed-status in test host only, or
  • in-memory completion marker read by test harness.
• health remains startup check; seed gate verifies data load complete.

3) Seed volume contract (initial target)

• BuildMetrics: ~100k rows
• TestRuns: ~50k rows
• TestCases: ~500k rows (avg 10/test run)
• RawPayloads: ~50k rows
• Cardinality targets:
  • ~200 projects
  • ~120 repositories
  • ~400 branches
  • platform/environment/test runner spread representative of current usage

4) CI placement decision

• Start as dedicated workflow (workflow_dispatch + nightly schedule), non-blocking.
• After stabilization and variance analysis, promote selected checks to PR gate if runtime/variance is acceptable.

5) Artifact format and consumption

• Commit to a stable JSON schema for machine comparison:
  • endpoint
  • filter profile
  • engine
  • sample count
  • p50/p95/p99
  • mean/stddev
  • baseline delta (%)
• Generate Markdown summary from JSON for human-readable PR/workflow output.

6) Host model clarification

• Use WebApplicationFactory<Program> directly unless a specific perf-host customization requires deviation.
• If custom host is needed, document why.

If there are no objections, I can update the issue body to incorporate these as explicit acceptance criteria and implementation notes.