perf: DB observability gaps — missing latency histograms, lock-wait metrics, and diagnostic endpoints

[TheSentinel454]

Problem

The relay has basic pool utilization gauges (buzz_db_pool_size/idle/active/max) and replica fence metrics, but several critical observability gaps make it difficult to diagnose performance issues:

Missing metrics

1. No query-level latency histograms: #[datastore_span] creates tracing spans but does not emit Prometheus histograms. Span-based analysis requires a tracing backend (Jaeger/OTEL); the relay's Prometheus metrics endpoint has no per-operation latency data. You cannot answer "what is the p99 latency of query_events?" from Prometheus alone.

2. No pool acquire wait time: the time between requesting a pool connection and receiving one is invisible. When the pool is saturated, all query latencies spike equally — but the root cause (pool wait vs. query time) is indistinguishable.

3. No advisory lock wait time: advisory lock calls block inside a transaction, but the lock wait is lumped into the enclosing span's duration. There is no way to tell whether a 5-second replace_addressable_event was slow due to query execution or lock contention.

4. No transaction duration metric: long-running transactions (replaceable events with large insert + soft-delete + mention insertion) hold connections and potentially block other advisory lock holders. No metric tracks transaction duration.

5. No connection churn metric: pool_size and pool_idle are gauges sampled every 10s. A connection that is acquired and returned 100 times between samples is invisible. A connection-checkout counter would reveal churn rate.

6. No per-kind event write latency: all event writes go through insert_event or replace_*_event, but there is no metric broken down by event kind. A single problematic kind (e.g., large NIP-33 parametrized replaceable events) can dominate write latency without being identifiable.

7. No pg_stat_statements integration: the relay does not query pg_stat_statements (which is typically enabled on managed Postgres like Aurora). This extension provides per-query mean/max latency, call count, and rows returned — exactly the data needed for slow-query diagnosis. A periodic scrape emitted as Prometheus metrics would close the observability gap.

Missing diagnostic endpoints

8. No /debug/db endpoint: there is no admin endpoint that returns current pool state, active queries, advisory lock holders, or connection ages. Diagnosis requires direct Postgres access (pg_stat_activity), which may not be available to all operators.

9. No /debug/locks endpoint: advisory lock contention is the primary deploy-time risk, but there is no way to inspect it without pg_locks access.

Proposed changes

1. Add buzz_db_operation_duration_seconds histogram with an operation label, emitted from #[datastore_span].
2. Add buzz_db_pool_acquire_wait_seconds histogram (writer and reader pools separately).
3. Add buzz_db_advisory_lock_wait_seconds histogram with a lock_type label.
4. Add buzz_db_transaction_duration_seconds histogram.
5. Add buzz_db_pool_checkouts_total counter.
6. Add buzz_db_event_write_duration_seconds histogram with a kind label for the hot-path event write operations.
7. Consider periodic pg_stat_statements scraping if the extension is available.
8. Add an admin-only /debug/db endpoint behind authentication.

Priority

High — without these metrics, diagnosing deploy-time performance issues requires direct Postgres access and real-time correlation of logs, which is impractical during an incident.

🤖 AI review update (2026-08-23)

Merge the core instrumentation work with #26/#27/#28 under a cardinality budget. Operation, pool_role, and fixed lock_type labels are safe; raw event kind/query ID labels need bounding or coarse families. Prefer external Postgres monitoring or bounded top-N diagnostics for pg_stat_statements, whose identifiers are not stable indefinitely and whose query text can be sensitive. Keep diagnostics off the public relay surface; use a separately authenticated/admin-only mechanism with redaction and query/time limits.

[TheSentinel454]

The core observability program was completed by block#6700 and extended by block#7195: operation, pool-acquire, advisory-lock, transaction, outcome, and active-waiter signals now use bounded vocabularies and have staging/dashboard verification. Optional raw pg_stat/debug endpoint ideas were intentionally not put on the public relay surface.