Authoritative DB migration: Way 1 vs Way 2 benchmark results

[bupd]

Authoritative DB migration: Way 1 vs Way 2 benchmark results

Background

Harbor Next's numbered migrations (make/migrations/postgresql/NNNN_*.up.sql) share one number
space with upstream goharbor/harbor, tracked by golang-migrate as a single "current version"
integer — not content, not checksums. Harbor Next previously shipped its own 0181/0182 on a
release-2.15 branch (branding, identity providers). Upstream has since shipped its own
0181/0182 in later 2.15.x patches. Because the migrator only checks "have I passed this
number," an install that already applied Harbor Next's old 0181/0182 will skip upstream's
real 0181/0182 on upgrade — silently losing schema changes. This is a structural collision
between two independently-numbered migration streams sharing one space, and it will keep
recurring.

Fix direction: stop injecting Harbor Next-owned numbers into that shared space, permanently.
Move all Harbor Next-specific schema needs into a separately-tracked, idempotent mechanism
decoupled from golang-migrate's counter. Two designs were built and benchmarked:

• Way 1 (experiment/authoritative-db-single-file) — one evergreen harbor_next.sql,
  reconciled idempotently after every startup, not tracked in schema_migrations at all. Verified
  to already be the exact idempotent union of the old colliding 0181/0182 DDL, so it fully
  self-heals any install regardless of migration-number history.
• Way 2 (experiment/authoritative-db-versioned-file) — harbor_next_<version>.sql per
  release, tracked in a small harbor_next_schema_migrations table, only applying files newer
  than the last recorded version (skips entirely once caught up).

Methodology

Environment note: the target real k3d + Helm chart 3-replica deployment wasn't achievable —
rootless podman in this environment doesn't delegate the cpuset cgroup controller that k3s's
server requires unconditionally (failed to find cpuset cgroup (v2)), and fixing it needs a
sudo systemd drop-in plus a session restart, which was out of scope to do unattended. Instead,
this benchmark uses a 3-replica podman-compose stack (postgres + redis + 3× core, no
portal/registry/jobservice — core doesn't dial those at startup) — the concurrent-migration /
advisory-lock behavior under test is identical to a real k8s deployment; what's missing is
Helm/Ingress/HPA fidelity and true pod-reschedule semantics (approximated here with an explicit
container kill + restart).

Four variants were planned; three ran (the fourth, pulling the actual released v2.15.3 image, was
out of scope for this pass — noted as a follow-up, not silently dropped):

• baseline-next-main — bare next/main, no authoritative-schema mechanism at all
• way1 — experiment/authoritative-db-single-file
• way2 — experiment/authoritative-db-versioned-file

Applying the real 8 commercial patches (0001–0008) for full realism was attempted (octopus
merge via task apply-patches succeeded, including resolving the expected i18n lang-file
conflicts and a go mod tidy for the combined dependency set) but hit a pre-existing, unrelated
swagger-codegen gap: src/server/v2.0/handler/handler.go references a grouped FederatedIdpAPI
operations field that task build:gen-apis doesn't currently generate for the federated-idp tag
against the merged spec. This reproduces with zero involvement from the migration branches —
confirmed on bare next/main + patches. Filed as a follow-up for whoever owns the
0004-identity-providers patch / gen-apis tooling. Since the only patches touching schema
(0002-branding, 0004-identity-providers) are already fully subsumed by Way 1/Way 2's
harbor_next.sql, benchmarking the three unpatched images is still a fair, meaningful comparison
of the migration mechanisms themselves.

Each variant ran the same pass: podman-compose up (cold 3-replica start) → per-replica
time-to-healthy → k6 cold burst (10 VUs, 20s) → k6 sustained load (20 VUs, 180s) with one core
replica killed and explicitly restarted ~45s in → pg_stat_activity/pg_locks polled every 0.5s
throughout. Single run per variant — treat as indicative, not statistically hardened (no repeated
trials to bound variance).

One known load-script gap: the k6 script's login check (POST /c/login) returns non-200
consistently across all three variants identically (same failure mode, same rate) — likely a
form-encoding/CSRF detail in the minimal compose stack, not something the DB-migration mechanism
affects. Since it's identical across variants, the comparison stays fair; ping succeeds
throughout and drove the real request-latency numbers below.

Results

Variant	Cold p95 (ms)	Cold p99 (ms)	Cold start, slowest replica (s)	Warm p95 (ms)	Warm p99 (ms)	Pod-kill→healthy (s)	Max DB conns
baseline (no mechanism)	1.09	1.90	7.44	2.09	3.42	3.80	10
Way 1 (single file)	1.06	1.90	7.60	2.07	3.36	4.58	10
Way 2 (versioned file)	1.05	1.82	7.76	2.07	3.39	4.36	10

Request-latency (p95/p99) is indistinguishable across all three variants — differences are
within single-run noise. Cold-start time and pod-kill recovery time show Way 1/Way 2 roughly
0.2–0.8s slower than baseline, which is consistent with — and fully explained by — the extra
migration phase each performs (see below); it isn't a request-serving regression. No lock
contention was observed on any variant (0 waiting connections throughout).

Per-replica migration-phase timing (cold start)

golang-migrate's own advisory lock means only one of the three replicas actually executes the
numbered migrations (the "winner"); the other two see a fast no-op. That pattern holds across all
three variants:

Variant	Winner replica: numbered	Winner replica: authoritative/versioned	Loser replicas: numbered	Loser replicas: authoritative/versioned
Way 1	647ms	16.5ms	9–15ms	2.4–3.8ms
Way 2	1044ms	28.2ms	9.7–11.1ms	8.7–12.0ms

Way 1's authoritative-file reconciliation (unconditional idempotent re-execution) costs
2.4–16.5ms per replica per boot. Way 2's versioned mechanism costs a comparable 8.7–28.2ms
cold (it isn't meaningfully cheaper cold, since applying the one relevant file is the dominant
cost either way) — the two only diverge on an already-caught-up restart, below.

Pod-kill/restart (steady-state, contention against 2 warm siblings)

Variant	Numbered phase	Authoritative/versioned phase
Way 1	14.4ms	3.4ms (full idempotent re-check, every restart)
Way 2	13.1ms	2.5ms (SELECT MAX(version) short-circuit, no DDL re-run)

This is the real trade-off the two designs make, and at Harbor Next's current schema size
(4 tables, 2 indexes) it's negligible in absolute terms — under 1ms difference. Way 2's
short-circuit would matter more as the authoritative schema grows large enough that re-scanning
IF NOT EXISTS guards on every boot becomes measurable; that isn't the case yet.

Recommendation

Way 1 (single evergreen idempotent file), matching the original preference: it's simpler (no
tracking table, no versioned-file generation step, no release-time stamping automation), it
self-heals any install regardless of migration-number history — directly solving the collision
bug this work exists to fix — and its steady-state overhead vs. Way 2 is sub-millisecond at
current schema size. Way 2's real advantage (skip-if-caught-up) only pays off once the
authoritative schema is large enough for a full idempotent re-check to become measurably
expensive; revisit if/when that happens.

Known limitations / follow-ups

• Real k3d/Helm 3-replica benchmark not run — needs cpuset cgroup delegation fixed on the host
  (sudo + systemd drop-in + session restart), tracked separately.
• v2.15.3 baseline arm not run in this pass.
• Full 8-patch octopus-merged image not built due to a pre-existing swagger-codegen gap for the
  federated-idp tag, unrelated to this work — tracked separately for the 0004-identity-providers
  patch owner.
• Single run per variant; no repeated-trial variance bounding.
• k6 login check fails uniformly across all variants (compose-stack auth detail, not
  migration-related) — ping carried the real latency measurements instead.

Branches

• experiment/authoritative-db-single-file (Way 1)
• experiment/authoritative-db-versioned-file (Way 2)
• experiment/authoritative-db-benchmark-harness (this benchmark's tooling, benchmark/authoritative-db/)

All pushed to next (container-registry/harbor-next), none merged — pending this review.