Range Day Pro

SQL query performance trending

3 min read

SQL Query Performance Trending

Idea

Instrument all database queries with latency tracking so we can observe how query performance trends over time as the user base grows. Distinct from the one-time API performance audit — this is continuous, always-on visibility into SQL response times in production.

Motivation

We’re starting with straightforward query-time Postgres (no Redis, no materialized views). That’s fine at low scale, but we need to know when it stops being fine. If a leaderboard query that takes 8ms at 1k users takes 800ms at 50k users, we want to see that curve forming — not discover it when users start complaining.

Trending data also helps justify (or avoid) optimization work. If queries are holding steady, we don’t need to prematurely add complexity.

Approach

Instrumentation Layer

Use the existing tracing crate integration. Each database call in the provider layer should emit a span with:

  • db.statement — the query name or template (not the full SQL with parameters, to avoid cardinality explosion)
  • db.operation — SELECT, INSERT, UPDATE, DELETE
  • db.table — primary table involved
  • db.duration_ms — query execution time
  • RPC context — which service/method triggered the query

sqlx already emits tracing spans for every query (sqlx::query target). The key decision is whether the default sqlx spans are sufficient or whether we want custom provider-level spans with richer labels.

Metrics Pipeline

Two complementary paths:

Option A: Trace-derived metrics (via OpenTelemetry)

  • OpenTelemetry Collector receives spans from the server
  • Connector or span metrics processor derives histograms from db.duration_ms
  • Prometheus scrapes the collector
  • Grafana dashboards visualize p50/p95/p99 per query name over time

Option B: Direct Prometheus histograms

  • Add a metrics crate histogram (sql_query_duration_seconds) in the provider layer
  • Label with query_name, table, operation
  • Prometheus scrapes the /metrics endpoint
  • Simpler pipeline, no collector dependency

Recommendation: Start with Option A if the observability stack already uses OTel Collector. Fall back to Option B if we want something lighter.

Grafana Dashboards

  • SQL Overview: p50/p95/p99 latency across all queries, request rate, error rate
  • Per-Query Detail: drill down by query name — latency distribution, trend line, outlier spikes
  • Slow Query Log: queries exceeding a threshold (e.g., >100ms) with timestamp, query name, duration
  • Growth Correlation: overlay query latency trends against user count / score count to see scaling curves

Alerting (Future)

  • Alert on p95 > 200ms sustained for 5 minutes
  • Alert on p99 > 1s
  • Alert on sudden latency jump (>2x baseline over rolling window)

Open Questions

  • Should we track individual query executions or just aggregate histograms? Histograms are cheaper and sufficient for trending; individual traces are useful for debugging specific slow requests.
  • What cardinality is acceptable for query labels? One label per provider method (e.g., list_drills, get_user_achievements) keeps it manageable. Avoid per-parameter labeling.
  • Should we log slow queries (>100ms) to Loki as structured log events in addition to metrics? Useful for debugging but adds log volume.
  • Does sqlx’s built-in tracing integration give us enough, or do we need custom spans in each provider method?
  • observability-setup — the observability stack this plugs into (Prometheus + Loki + Grafana)
  • API performance audit — one-time audit against staging data; this idea covers continuous production monitoring
  • deployment-pipeline — dashboard/alerting config would be deployed alongside the server

Graph View