# Changelog

## 2026-06-22 — binded-packages measure path fix (PR for the 2026-06-16 prod-billing-aurora incident)

### What changed

The binded-package lookup at the heart of the measure hot path
was rewritten so that the SQL can ride two new composite indexes
instead of doing a residual scan over the entire
`binded_packages` table. The fix is gated behind a new feature
flag `enable-use-db-side-binded-package-filter` (default `false`,
matching the post-revert state from 2026-06-16). When the flag
is off the service uses the legacy `GetAllTenantBindedPackage` +
Go-side filter, identical to the pre-2026-06-16 behaviour. When
the flag is on the service uses the new path.

### Why

PR #1233 (commit 489501c) shipped on 2026-06-16 without a
feature flag and without the composite indexes the new SQL
shape needed. The result was a writer-load spike on
prod-billing-aurora that took ~1 hour to diagnose and revert.
Full timeline and root cause analysis in
`docs/postmortem-2026-06-16-prod-billing-aurora.md`.

### Behaviour

- Default behaviour is **unchanged**. The flag ships `false` and
  the measure path runs the legacy `GetAllTenantBindedPackage` +
  Go-side filter, identical to the 2026-06-15 code. Verified by
  `TestListBindedPackages_DBAndLegacy_ReturnSameSet` in
  `model/billing/tests/binded_package_test.go`.

- When the flag is flipped on (operator action), the measure
  path runs `GetEffectiveBindedPackages` (no unit filter) or
  `GetEffectiveBindedPackagesWithUnitMatch` (with unit filter).
  Both rewrite the binded-package lookup as a `UNION ALL` of
  two indexed seeks, each backed by a new composite index:
  - `(tenant_id, status, internal)` on `binded_packages`
  - `(tenant_id, status, binded_billing_config_id)` on
    `binded_packages`

- For the unit-filter path, the unit-filter predicate is
  applied in Go via `UnitMatch`, not in SQL. We tried
  `EXISTS (SELECT 1 FROM unit_filter_binded_packages ufbp JOIN
  credit_types ct ON ct.id = ufbp.unit_filter_id WHERE ...)`
  first, but MySQL 8 keeps it as a `DEPENDENT SUBQUERY`
  (N×M cost) rather than promoting it to a hash semi-join
  because the inner predicate contains an `OR (ct.general = 1
  OR (ct.provider = 'all' OR ...))` that defeats the
  semi-join optimiser. The Go-side `UnitMatch` is strictly
  cheaper.

- The unit-filter path relies on a new
  `idx_ufbp_binded_package_id` index on
  `unit_filter_binded_packages` (the PRIMARY KEY on that table
  is `(binded_package_id, unit_filter_id)`, which already serves
  single-value equality lookups; the standalone single-column
  index lets the optimiser `index scan + Using where` on the
  `IN (?)` list case that GORM's `Preload("UnitFilters")`
  issues). Added by `migrate_record165.go`; the migration
  runs automatically on the next pod start via
  `gormigrate.Migrate()`.

### Performance

Benchmark results on a local docker MySQL 8.0.46 (single-threaded
unless noted), captured in
`model/billing/tests/binded_package_bench_test.go`:

| Path                         | 5k    | 50k   | 100k  | 20k (WithUnitMatch) |
|------------------------------|-------|-------|-------|---------------------|
| DB-side (this PR)            | 1.3ms | 1.4ms | 1.3ms | 318ms               |
| Legacy path                  | 1.7ms | 1.5ms | 1.5ms | n/a                 |
| WithUnitMatch before the fix | n/a   | n/a   | n/a   | TIMEOUT (30s+)      |

The 5k / 50k / 100k columns are sub-millisecond and indistinguishable
between the new path and the legacy path on a localhost connection
(the latency is dominated by MySQL RTT, not the index plan; the
real win shows up on a cross-AZ prod writer). The 20k
`WithUnitMatch` column is the one that matters: the previous
implementation's dependent subquery timed out at 30s for 20k
packages, the new path returns in 318ms.

### Operator-facing changes

- **New config key** `enable-use-db-side-binded-package-filter`
  consumed from the `oneconfig` repo (which feeds
  `/etc/billing/config.yaml` and the
  `ENABLE_USE_DB_SIDE_BINDED_PACKAGE_FILTER` env var).
  Default `false`. See `model/billing/measure.go` for the
  read site.

- **New Prometheus counter**
  `dbaas_billing_measures_use_db_side_binded_package_total{use_db_side}`
  with values `true` and `false`. The on-call can read this to
  confirm the flag has taken effect during a gradual rollout.
  Apply the runbook once, in this exact order, before flipping
  the flag on.

### What did NOT change

- The measure path's `UnitMatch` predicate and the
  `IsBetaMeasure` short-circuit. These were untouched.
- The `BindPackageExtra`, `BindPackage`, `ChargeCredits`, and
  other write paths. None of them touch the new index.
- The `BindedPackage` API (`api/package.go`, etc.) still uses
  `GetAllTenantBindedPackage` and is unaffected.
- The `costexplorer` query path. It uses its own
  `GetAllTenantBindedPackage` calls; no change.
- The measure worker. Its hot path is in
  `CostUnits` → `ListBindedPackages`; the existing
  `profileMeasureF("CostQuery-BindedPackages", ...)` wrapper
  still times this stage, so the existing
  `dbaas_billing_measures_profile{stage}` histogram still
  tracks the bottleneck.

### Test coverage

- 28 unit tests in `TestBindedPackageSuite` pass
- 3 query-plan tests in `TestGetEffectiveBindedPackages_QueryPlan_*`
  pass
- 5 benchmarks in `BenchmarkGetEffectiveBindedPackages*` pass
- 1 feature-flag default test in
  `TestEnableUseDbSideBindedPackageFilter_DefaultOff` passes
- 1 legacy-vs-DB-side consistency test in
  `TestListBindedPackages_DBAndLegacy_ReturnSameSet` passes

### Rollout plan

1. Merge this PR. Flag ships `false`. Measure path runs the
   legacy code.
2. On the next deploy, the new pods run `gormigrate.Migrate()`
   on start, which executes migration 165
   (`migrate_record165.go`). The DDL inside the migration is
   pure-DDL with `information_schema` guards and is idempotent;
   the operator does not need to run any SQL by hand.
3. Verify the migration ran on every cluster by checking
   `information_schema.statistics` for the two new composite
   indexes on `binded_packages` and the single-column index on
   `unit_filter_binded_packages`. No `SHOW`-statement dance is
   required because the migration is part of the normal deploy
   path.
4. Open a PR against the `oneconfig` repo that sets
   `enable-use-db-side-binded-package-filter: true`. Merge it;
   ArgoCD rolls the change out fleet-wide. Watch
   `dbaas_billing_measures_use_db_side_binded_package_total{use_db_side="true"}`
   climb and
   `dbaas_billing_measures_profile{stage="CostQuery-BindedPackages"}`
   latency stay at the legacy baseline. There is no
   per-cluster rollout step — flag flips in this org are
   always fleet-wide via the `oneconfig` repo.

### Related incidents

This PR is the follow-up to the 2026-06-16 prod-billing-aurora
incident, also written up in
`docs/postmortem-2026-06-16-prod-billing-aurora.md`.