perf: migration-time connection starvation during rolling deploys

[TheSentinel454]

Problem

The relay runs migrations at startup (`BUZZ_AUTO_MIGRATE=true`). During a rolling deploy, the first new pod to start acquires the exclusive `SCHEMA_DESTRUCTION_LOCK_KEY` session lock, runs all pending migrations, then releases the lock. This creates a critical window:

Timeline of a rolling deploy with migrations

1. Old pods are serving traffic normally. Connection pools are ~50 connections each.
2. New pod starts, connects to Postgres, acquires `SCHEMA_DESTRUCTION_LOCK_KEY` exclusively.
3. Migration runs (duration varies: simple DDL is fast, but a backfill migration can take minutes).
4. During migration, old pods continue serving. Any write path that calls `assert_community_write_allowed` (which takes the shared `SCHEMA_DESTRUCTION_LOCK_KEY`) blocks until migration completes.
5. Blocked connections pile up in old pods' writer pools. Pool utilization → 100%.
6. New requests to old pods fail with `acquire_timeout` (3s) errors.
7. Kubernetes sees old pods as unhealthy (readiness probe hitting the DB-backed `/_readiness` endpoint fails) → removes them from service → traffic shifts to the new pod → which is still migrating → brief total outage.
8. Migration completes → new pod starts serving → recovery.

Contributing factors

• No migration-in-progress signal: readiness probe doesn't know about migration state. The `/_readiness` endpoint doesn't check whether migration is running on another pod.
• All pools share the same lock space: audit, search, and writer pools all use the same Postgres — a migration lock blocks them all.
• No statement timeout on advisory lock waits: connections blocked on `SCHEMA_DESTRUCTION_LOCK_KEY` have no timeout (only the pool acquire has a 3s timeout).
• Partition creation during startup (`ensure_future_partitions`): this runs DDL (`CREATE TABLE ... PARTITION OF`) during startup, before the pod is ready. If partition creation conflicts with migration, both can block.

What the shutdown sequence does right

The shutdown sequence is well-designed: SIGTERM → readiness 503 → 5s grace → jittered drain → 30s hard timeout. This is correct for normal deploys. The problem is specifically migration-time: the lock blocks serving pods during the migration window.

Proposed changes

1. Separate migration from relay startup: run migrations in a dedicated pre-upgrade Job (the Helm chart already has `migrate.preUpgradeJob` scaffolding, but it's disabled by default). This way migrations complete before any pod swap begins.

2. Add a migration-in-progress health signal: if `BUZZ_AUTO_MIGRATE` is used, expose a metric `buzz_migration_in_progress` (1 while migrating, 0 otherwise) and a startup log line. Other pods can read this via the metrics endpoint to warn operators.

3. Use `pg_try_advisory_lock` with timeout for migration: instead of `pg_advisory_lock` (blocks forever), use a loop with `pg_try_advisory_lock` and a configurable timeout. If another pod is migrating, fail fast with a clear error rather than blocking.

4. Consider using `lock_timeout` on the migration connection: `SET lock_timeout = '300s'` caps the maximum migration lock wait.

5. Add a serving-pod migration-awareness check: before each request that will take the shared lock, check whether migration is in progress (via a lightweight probe or cached flag) and return a 503/retry-later if so.

Priority

High — this is the most likely cause of deploy-time service disruption.

🤖 AI review update (2026-08-23)

The central causal model needs correction. SCHEMA_DESTRUCTION_LOCK_KEY serializes migrations against destructive deletion transactions; ordinary assert_community_write_allowed calls use per-community deletion locks and do not queue behind the migration's global schema lock. Preserve a narrower issue for implementing the reserved Helm pre-upgrade migration job, measuring migration/DDL lock duration, adding bounded acquisition behavior, and verifying compatibility/readiness from actual schema state rather than a speculative cross-pod flag.