perf: add statement-level timeouts and slow-query observability

[TheSentinel454]

Problem

There are zero SQL statement timeouts or slow-query thresholds configured anywhere in the codebase. No statement_timeout, no lock_timeout (except in the deletion migration which sets lock_timeout = '5s'), and no sqlx log_slow_statements configuration.

This means:

1. A query that takes a Postgres exclusive lock (advisory or row-level) can hold a pool connection indefinitely while waiting for the lock.
2. During deploy, the schema-destruction advisory lock (SCHEMA_DESTRUCTION_LOCK_KEY) is held exclusively by migration — any serving-write-lease code that takes the shared lock will block until migration finishes. The write pool has a 3-second acquire timeout, but once a connection is acquired there is no statement-level timeout on the advisory lock wait itself.
3. If a query plan regresses (e.g., partition pruning fails, index is stale), there is no circuit breaker — the query runs until the connection's max_lifetime (30 minutes) or the OS kills the backend.
4. There is no structured slow-query log — debugging latency requires correlating external Postgres log_min_duration_statement with application traces.

Proposed changes

1. Set statement_timeout on the writer pool via the existing after_connect hook (lib.rs:710). A conservative 30s default covers all current query patterns while catching runaway queries. Make it configurable via BUZZ_DB_STATEMENT_TIMEOUT_MS.

2. Set lock_timeout on the writer pool separately from statement_timeout. Advisory lock waits during high contention (replaceable events, channel membership) should fail fast rather than hold connections. Suggested default: 5s, configurable via BUZZ_DB_LOCK_TIMEOUT_MS.

3. Configure sqlx slow-statement logging — sqlx supports log_slow_statements(LevelFilter::Warn, Duration::from_millis(500)) on ConnectOptions. This gives application-level slow-query logs correlated with datastore spans without requiring Postgres-side configuration.

4. Emit a histogram metric for query durations inside #[datastore_span] — the proc macro already creates a span; adding metrics::histogram!("buzz_db_query_duration_seconds", "operation" => name).record(elapsed) would give per-operation latency percentiles in Prometheus.

Deploy relevance

During a rolling deploy, the migrating pod holds SCHEMA_DESTRUCTION_LOCK_KEY exclusively. Serving pods that attempt assert_community_write_allowed (which takes the shared lock) will block. With no statement timeout, these requests hold pool connections for the entire migration duration. If migration takes longer than acquire_timeout (3s), the pool appears full and subsequent requests fail with acquire timeout errors — but the root cause is invisible because there's no lock-wait metric.

Priority

High — this is the lowest-effort, highest-impact observability improvement for diagnosing deploy-time issues.

🤖 AI review update (2026-08-23)

Split low-risk observability from behavior-changing timeout policy. First add bounded operation/acquire/lock latency and failure signals plus sampled/redacted slow-statement logging; then choose per-workload statement and lock timeouts from measured distributions. Do not describe max_lifetime as a query timeout, and do not apply one writer-session timeout blindly to migrations, deletion, or backfills.

[TheSentinel454]

Completed by block#6700 and block#6229: bounded operation/slow-operation visibility landed first, followed by configurable writer lock, idle-transaction, and statement timeouts with explicit migration exemptions.