Optimizing queries by using observability

Code snippet: Tracing PostgreSQL queries with OpenTelemetry (Python)

from opentelemetry import trace
from opentelemetry.instrumentation.psycopg2 import Psycopg2Instrumentor
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import BatchSpanProcessor, ConsoleSpanExporter

trace.set_tracer_provider(TracerProvider())
tracer = trace.get_tracer(__name__)
Psycopg2Instrumentor().instrument()

span_processor = BatchSpanProcessor(ConsoleSpanExporter())
trace.get_tracer_provider().add_span_processor(span_processor)

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

with tracer.start_as_current_span("run-heavy-query"):
    cur.execute("SELECT * FROM large_table WHERE condition = 'value';")
    results = cur.fetchall()

Tracing identifies cross-service bottlenecks impacting query speed.

Proactive anomaly detection in query latency

Setting dynamic alerting thresholds based on observability data enables rapid detection of performance degradation.

Code snippet: Python alerting for slow queries

import psycopg2

LATENCY_THRESHOLD_MS = 500

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()

cur.execute("""
SELECT query, mean_time 
FROM pg_stat_statements 
WHERE mean_time > %s;
""", (LATENCY_THRESHOLD_MS,))

for query, latency in cur.fetchall():
    print(f"WARNING: Query exceeding latency threshold: {latency} ms\n{query}")

Automating this helps maintain SLAs and avoid user impact.