perf: advisory lock contention visibility and hot-key mitigation #28

Problem

The codebase uses 25+ advisory lock call sites across 7 modules. Advisory locks serialize concurrent writes (replaceable events, channel membership, push gate, deletion lifecycle), which is correct for consistency. But there is zero visibility into lock contention:

1. No lock-wait metrics — there is no instrumentation around `pg_advisory_xact_lock` calls. When a lock wait blocks for seconds (e.g., replaceable event contention on a popular channel metadata key), the only signal is elevated `datastore_span` durations on the enclosing method — but the span doesn't distinguish "query was slow" from "lock wait was slow."

2. FNV-1a hash collisions — `event_replacement_lock_key` hashes (community_id, kind, pubkey, optional coordinate) into an i64 via FNV-1a. The hash space is 2^64 so collisions are rare, but under load two unrelated replaceable events can serialize on the same lock key. There is no collision-rate metric.

3. Lock key duplication — the FNV-1a hash is duplicated in 3 locations: `buzz-db/src/lib.rs:71`, `buzz-relay/src/handlers/command_executor.rs:149`, and an inline copy in command_executor. A divergence would cause silent missed serialization.

4. Channel membership locks use `hashtextextended` (`channel.rs:481`, `channel.rs:1618`, `event.rs:1696`, `push.rs:28`) — a Postgres-side hash function. This is fine but means the lock key generation is split between Rust-side (FNV for replaceable events) and Postgres-side (hashtextextended for membership), making it harder to reason about the global lock key space.

5. Deploy-time lock stacking — during migration, the exclusive `SCHEMA_DESTRUCTION_LOCK_KEY` blocks all shared-lock holders (serving write leases). Meanwhile, advisory locks from in-flight replaceable event writes pile up behind their own serialization. If the pool fills with connections waiting on advisory locks + migration lock, new requests can't acquire connections.

Proposed changes

1. Wrap advisory lock calls in a timed span: emit `metrics::histogram!("buzz_db_advisory_lock_wait_seconds", "lock_type" => ...)` around every `pg_advisory_xact_lock` call. Distinguish lock types: "replacement", "membership", "push_gate", "deletion", "migration".

2. Consider `pg_try_advisory_xact_lock` for replaceable events: instead of blocking indefinitely, try the lock with a timeout. On failure, return a "conflict, retry later" error. This prevents lock-wait pile-up from exhausting the connection pool.

3. Consolidate the FNV hash into a single shared function (tracked in issue buzz-db: consolidate audit pool into Db's pool #9).

4. Add a Prometheus gauge for active advisory locks: query `pg_locks WHERE locktype = 'advisory'` periodically in the pool-metrics loop.

Priority

Medium — advisory lock contention is the most likely root cause of deploy-time pool exhaustion.

🤖 AI review update (2026-08-23)

Keep bounded lock-wait instrumentation using fixed lock_type values. Remove collision-rate metrics: hash collisions are not directly observable without retaining source coordinates and only cause extra serialization here. Do not switch wholesale to try-lock/conflict responses; that changes client semantics and can lose writes when clients do not retry. Prefer measured contention, scoped timeouts, and server-side bounded retry where operations are idempotent.