A team notices a checkout endpoint slowing down. Response times have crept from 80ms to 900ms over two weeks, but the infrastructure dashboard shows nothing abnormal. So the engineer does what most teams do first: adds an index on the column mentioned in the ticket, deploys, and moves on. Two weeks later, the same endpoint is slow again. A different engineer adds another index. Then another. The table now carries 23 indexes. Every INSERT pays write amplification across all of them. The original slow query is still slow, because the root cause was never the missing index. Stale statistics after a schema migration had triggered a plan regression, and no one caught it because no one was watching query-level execution data. This guide inverts the usual approach. Instead of starting with indexing techniques and treating observability as an afterthought, it starts with the telemetry pipeline: how to capture query-level execution data, correlate it with application traces, and build the feedback loop that makes every subsequent optimization decision measurable. From there, it moves into execution plan analysis, indexing strategies, and resource management, each one grounded in the signals your pipeline surfaces. The principles apply across PostgreSQL, MySQL, and most relational engines. It assumes working knowledge of SQL and basic database administration. Instrumenting before you optimize Database optimization requires three categories of signals, and most teams have at best one of them in place. The first is query execution metrics: per-query call count, mean latency, execution time standard deviation, rows scanned versus rows returned, and cache hit ratio. In PostgreSQL, pg_stat_statements captures these metrics directly, though p99 latency approximations require pg_stat_monitor (which provides histogram-based latency distributions) or an external metrics store for precise percentile calculations (stddev_exec_time is the closest proxy pg_stat_statements provides). Enable it by adding the extension to shared_preload_libraries, restarting the server, and creating the extension in each target database: -- postgresql.conf (restart required after saving) -- In managed clouds like AWS RDS or GCP Cloud SQL, enable via Parameter Groups or database flags shared_preload_libraries = 'pg_stat_statements' -- pg_stat_statements.track = top -- default: tracks only top-level statements -- Set to 'all' if your workload runs queries inside functions or stored procedures -- After restart, run in each target database CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- Top consumers by total execution time SELECT query, calls, total_exec_time, rows, mean_exec_time, stddev_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; In MySQL, the Performance Schema is enabled by default and provides equivalent data. Sort by total time consumed, not worst-case single execution. A query that takes 20ms per call but runs 50,000 times per hour contributes 1,000 seconds of database time, far more than a 5-second query that runs twice a day. The second signal is infrastructure-level database metrics: connection counts, operation rates, and table I/O. The OpenTelemetry Collector (otelcol-contrib, not the core distribution) scrapes these on a configurable interval with no application code changes: First, create the monitoring user with the required permissions: -- Create monitoring user (PostgreSQL 10+) CREATE USER otel_monitor WITH PASSWORD 'your_password'; GRANT pg_monitor TO otel_monitor; -- covers pg_stat_statements, pg_stat_activity, etc. -- If pg_monitor is unavailable (pre-10), grant individually: -- GRANT SELECT ON pg_stat_statements TO otel_monitor; -- GRANT SELECT ON pg_stat_user_tables TO otel_monitor; -- On AWS RDS and GCP Cloud SQL, pg_monitor is available and the preferred approach. Then configure the collector: receivers: postgresql: endpoint: localhost:5432 username: otel_monitor password: $env:PG_PASSWORD collection_interval: 30s databases: - myapp_prod processors: batch: exporters: otlp: endpoint: your-backend:4317 service: pipelines: metrics: receivers: [postgresql] processors: [batch] exporters: [otlp] The third signal is application traces. Auto-instrumentation libraries for most languages and database clients (Python and Java have the most mature support; Go and Rust require more manual setup) emit a trace span for every database call, carrying the query text and operation type as span attributes. Without application-level tracing, you can identify slow queries but not which service, endpoint, or user action generated them. With all three in place, build a baseline dashboard before changing anything. Run four panels for at least one full business cycle (24 to 48 hours): top queries by total execution time, active connections over time, cache hit ratio, and index scan versus sequential