A practical guide walks through building a progressively more sophisticated observability workflow for slow SQL queries using OpenTelemetry. It starts with a basic dashboard sorting queries by duration, then adds a traffic-weighted impact score (average duration times call count) to prioritize optimization work, and finally introduces anomaly detection via the spanmetrics connector and Prometheus adaptive baselines to catch queries that deviate from normal behavior. A companion lab (Go app, PostgreSQL, docker-otel-lgtm stack) lets readers reproduce the setup with Docker. Production considerations covered include metric cardinality explosion from raw SQL in labels, redacting sensitive data at the Collector, and the 24-48 hour warm-up period anomaly baselines need. The piece closes by noting that anomaly detection only surfaces symptoms, not root causes, and points to Causely's causal modeling as the next step.
Table of contents
What Makes a Query Slow?The Classic Workflow: DB-Native Tooling + Manual TriageBringing Context to Slow QueriesThe Building BlocksLab SetupQueries by DurationTraffic-Weighted Impact AnalysisSymptom Detection with Anomaly BaselinesTaking This to ProductionThe Remaining Gap: From Symptoms to Root CausesQuestions this post answers
How do you calculate which slow SQL queries are actually worth optimizing first?
Multiply average query duration by call count to get an impact score, then sort queries by that score instead of by raw duration. A query averaging 150ms but running 10,000 times has far more total user impact than one averaging 2.3s but running only 5 times, so impact-weighted sorting surfaces the queries that matter most for optimization prioritization. daily.dev surfaces engineering write-ups like this for teams deciding how to prioritize database optimization work.
How can I detect anomalous database query latency instead of relying on fixed thresholds?
Use the OpenTelemetry Collector's spanmetrics connector to turn database spans into latency histograms labeled by service, db.system, and query text, store them in a Prometheus-compatible backend like Mimir, then apply Grafana's PromQL Anomaly Detection recording rules to compute a smoothed baseline plus upper and lower standard-deviation bands. Latency exceeding those bands flags a real deviation from normal behavior rather than a static threshold breach. track approaches like adaptive anomaly baselines on daily.dev when building your own incident response tooling.
Why does putting raw SQL text into OpenTelemetry metric labels cause problems in production?
Raw SQL in metric labels causes cardinality explosion because a query like SELECT * FROM orders WHERE customer_id = 12345 generates a separate metric series per literal value, such as per customer id. The fix is to use prepared statements so instrumentation captures query templates instead of literals, normalize query text, or set aggregation_cardinality_limit in the spanmetrics connector. developers rolling out span metrics in production can compare cardinality pitfalls like this on daily.dev.