Optimizing queries by using observability

In a modern enterprise data environment, the increasing volume of data, distributed architecture, and complex application dependencies challenge traditional query-tuning methods. Observability enhances query optimization by providing constant, fine-grained visibility into query behavior, resource consumption, and systemic interactions. Taking advantage of this data shifts the query tuning into strategic, active engineering.

Observability metrics essential for query optimization

To optimize queries effectively, observability captures critical metrics such as:

Execution time: Total time to complete the query

Resources used: CPU, memory, and I/O that were consumed by the questions

Locking and controversy: Time waiting on the database lock or latch

Index uses: Whether you take advantage of the query-available index or return to the expensive full-table scan

Frequency and throughput: How many times and how intensive are the questions

Tools best suited to monitor your queries: MySQL Enterprise Monitor, Middleware, etc.

Analyzing these metrics helps identify slow, resource-intensive queries that can be targeted for improvement.

Code snippet: Using PostgreSQL pg_stat_statements to identify slow queries

Enable statement statistics collection:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Query to list the slowest queries:

SELECT
query,
calls,
total_time,
mean_time,
rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

This identifies and prioritizes queries impacting overall performance.

Execution plan analysis via observability

Query execution plans reveal how the database engine processes SQL. Observability enriches this by tracking plans over time to detect regressions and inefficiencies.

Code snippet: Extracting execution plan details with Python

Get detailed JSON execution plans:

import psycopg2
import json

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

cur.execute('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM large_table WHERE condition = 'value';')
plan_json = cur.fetchone()[0]
plan = json.loads(plan_json)

def find_expensive_nodes(node):
if 'Plans' in node:
for sub in node['Plans']:
find_expensive_nodes(sub)
if node.get('Relation Name') == 'large_table':
print(f'Scan Type: {node['Node Type']}, Actual Rows: {node['Actual Rows']}, Buffers: {node['Shared Hit Blocks']}')

find_expensive_nodes(plan[0]['Plan'])

Use insights to optimize indexes or rewrite queries.

Distributed tracing of queries across microservices

Modern queries often span multiple services. Observability frameworks like OpenTelemetry provide distributed tracing, connecting questions with back-end services and network calls for overall delay analysis.

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} msn{query}')

Automating this helps maintain SLAs and avoid user impact.

The continuous optimization cycle

Monitor: Collect metrics, logs and traces continuously.

Analyze: Identify patterns, bottlenecks and outliers.

Plan: Connecting reforms from execution plans and telemetry.

Implement: Configuration of sequencing, rewriting or changes.

Measure: Measure the effect with the first and the second observation data.

Automate: Integrated and alerting in CI/CD pipelines.

Advanced techniques

Adaptive indexing was informed by the patterns observed in the data.

ML in operating future performance modeling.

Business correlation with quality KPI for priority adaptation.

Observability provides deep, constant insight into query performance, fueling accurate and proactive adaptation. By combining traditional methods with traces and execution plans, engineers can optimize complex distributed systems, reduce delays and increase resource efficiency. Eventually, better applications can provide accountability and positive business effects.

This article is published as part of the Foundry Expert Contributor Network.Want to join?