Count River's own SQL per query with a counters-only tracer, once LabKit can chain a QueryTracer

Split from #1606 (closed) (gap 2, part 2 of 2). Parent epic: &24088. Blocked by #1721 (closed) and by an upstream LabKit release (see below).

Decision (operator ruling 2026-10-25)

Q4: after #1721 (closed) records the current surface and LabKit ships a tracer option, add a River-only, counters-only tracer. No histogram. Q7: this MR amends S03-B itself; #1718 (closed) lands first.

Problem

River's own SQL (JobGetAvailable, JobSetStateIfRunningMany, JobInsertFastMany, leader election, the rescuer and the cleaner) runs over the shared pool and carries no metric. When the pool saturates, no metric tells River load apart from request load. See #1721 (closed) for the full evidence.

Evidence

  • Every River SQL text begins with -- name: <Query>. riverdriver/riverdatabasesql@v0.49.0/internal/dbsqlc/*.sql.go declares 56 such constants. That is a bounded, version-pinned label source.
  • LabKit has no seam for a second pgx.QueryTracer at v2.63.2 (pinned here) or v2.65.1 (postgres/client.go:207-209, :271, :324-349). #1721 (closed) carries the drafted LabKit request.
  • Series cost: counters-only is about 170 series per pod. A histogram would be about 840.

Fix direction

  1. Bump LabKit to the release that adds the tracer option (for example postgres.WithQueryTracer).
  2. Add a tracer in internal/metrics that books only SQL whose text starts with -- name: , under a new river_query label. It ignores everything else, because the application's own statements are already timed by name, and booking them as unnamed would count them twice.
  3. Publish counters only: database_river_queries_total{river_query}, database_river_query_duration_seconds_total{river_query} and database_river_query_errors_total{river_query}.
  4. Fold any value outside the pinned set into other, so a River upgrade cannot grow the label without bound.
  5. Update S27 #### Database connection (replacing the "cannot show" text #1721 (closed) adds), the S03-B ### Database table and docs/dev/observability.md.

Acceptance criteria

  • LabKit exposes a way to chain a pgx.QueryTracer, and this repository is on that version.
  • River SQL is published under river_query, from a pinned closed set plus other. Application statements are not double-booked: a test drives a named application statement and asserts no river_query series moves.
  • Counters only: no River histogram is registered.
  • The series are seeded at startup and the cardinality audit passes.
  • The S27 #### Database connection text, the S03-B ### Database table and docs/dev/observability.md describe the new families.
  • #1664's PoolSaturated caveat is revisited (a comment on #1664 is enough).

Size and fit

About 300-450 lines here after the LabKit release. This fits one smurfit unit. The upstream LabKit MR (about 50-100 lines plus a test) is owned by the LabKit maintainers and has a longer, cross-team lead time.

Dependencies

  • Blocked by #1721 (closed) (the decision record and the filed LabKit request).
  • Blocked by the LabKit release that adds the tracer option, requested in labkit#154 (caller-supplied query tracer and connection hooks for v2/postgres clients).
Edited by Pawel Rozlach