,

Database Observability: Queries, Plans, Locks, and Costs

Understand database observability through query statistics, execution plans, waits, connections, and cost. Learn where APM and database tools fit.

Laurent Gil Avatar
Cast AI DBO demo showing statement volume, error rate, and query execution-time metrics.

A query runs in 40ms on Tuesday. By Thursday it takes four seconds, although the application code has not changed. An application-wide latency chart can hide a regression affecting one query or a small group of requests. Database-level evidence helps you identify whether its execution plan, waits, or data distribution changed.

Database observability is the ability to explain database behavior using query statistics, execution plans, waits, connection state, and resource usage. Application traces show where requests spend time; database instrumentation explains what happens inside the engine. APM products with database monitoring modules can provide both. The remaining step is turning those findings into tested changes.

Key takeaways

  • Database observability connects query statistics, execution plans, waits, connections, and resource usage.
  • Application-only traces do not replace database instrumentation; APM database modules can add that coverage.
  • Rank capacity work by total query time, and investigate individual latency regressions against application SLOs.
  • Read-only recommendations, schema changes, pooling, and caching require different validation steps.

What database observability actually covers

Five signal families define database observability, and each answers a different question. A tool is only as observable as the families it collects. Miss one and you will spend incidents guessing.

Queries: which statements consume the most database time

Cast AI database performance view showing query volume, error rate, and execution-time metrics.
Read these signals together: query volume provides workload context, while errors and execution-time percentiles help identify regressions. Cast AI demo data; these are not customer benchmarks.

Normalized query text with call counts, rows returned, and total exec time. The key question: what are my top ten statements by total database time? Rank by share of total time, not average latency: 50ms a million times costs more than 400ms a hundred times.

Plans: how the optimizer executes those statements

Execution plans and, crucially, their history. The key question: what plan did the database choose, and when did that choice change? Plan flips are the classic silent regression. A bulk insert moves the statistics, the row estimate goes wrong, and the index stops being used with no deploy. Plans are the family most APM products miss.

Locks and waits: what the query is blocked on

Blocking sessions, lock waits, and wait events by type. The key question: is this query slow from its own work, or because something else holds a lock? Wait-event data separates CPU-bound work from I/O stalls and lock contention. When p99 doubles and CPU stays flat, wait events show where the time went.

Connections: how clients hold session state

Connection counts by state. Watch for sessions stuck in idle in transaction above all. The key question: is a client holding a transaction open, and is it blocking DDL or vacuum? Such sessions cause outages that look like database trouble yet start as application bugs.

Connection poolers complicate the reading. A pooler such as PgBouncer multiplexes clients onto fewer server sessions. As a result, idle in transaction and session state no longer match what the application does. Read session state on the database side, where PgBouncer versus RDS Proxy explains the difference.

Cost: what those signals bill you for

Rows scanned versus rows returned, I/O volume, and instance capacity consumed. The key question: which queries drive the spend on storage and compute? A query reading a million rows to return ten is a cost line and a latency line at once. Cost ties observability to the bill, and the bill gets the work prioritized.

Query time only becomes savings when it changes a purchasing decision. A faster query pays off when freed headroom lets you shrink an instance class. Without that link, the improvement stays a number on a dashboard.

Use the ranked output from pg_stat_statements to attach an owner to expensive query patterns. Multiplying a statementโ€™s share of execution time by instance cost can support rough showback, but it is not measured per-query cost: concurrent execution, waits, shared resources, storage, and licensing complicate attribution.

For optimization priorities, Cast AI Database Optimization ranks query and index recommendations by their potential impact on database time. Validate savings against the actual instance and workload after implementing a change.

What is the difference between database monitoring and database observability?

Database monitoring fires when a known threshold is crossed: CPU above 80 percent for ten minutes, replication lag above sixty seconds, connections near max_connections. Those checks catch the failures you anticipated and nothing else.

Observability lets you interrogate the system after an unknown failure. Suppose one query degraded on Thursday, nobody alerted on plan flips, and no threshold fired. What you need instead is raw signal kept long enough to compare across the change. Retained signal names which of five hundred statements moved, and why. Keep query history long enough to answer a question you did not plan for.

Why your APM stops at the symptom

Credit where it is due. Datadog Database Monitoring and New Relic both sit on top of the APM agent as separate surfaces. Both do far more than a bare APM. The gap here is an application-only setup, not a vendor refusing to look. Datadog’s own docs put it plainly: Database Monitoring shows database-level metrics not captured by APM, such as rows updated and returned.

Datadog Database Monitoring adds query metrics, samples, explain plans, and database dashboards to application monitoring. Supported engines and collection limits vary by integration; consult its data collection documentation when comparing coverage.

New Relicโ€™s PostgreSQL integration can collect query, wait, blocking-session, and execution-plan information. Collection settings and thresholds affect which detailed events are retained. When evaluating any integration, distinguish aggregated query statistics from sampled slow-query records: frequent fast statements can consume substantial total database time.

Where the instrumentation boundary sits

The boundary is the wire. An APM instruments your application process, where it sees query text, round trips, and results. Nothing there shows the plan the server chose, the lock the session waited on, or the statistics change behind it. Reaching them means reading the database’s own catalogs and statistics views, where Datadog DBM, pganalyze, and Cast AI Database Optimization sit.

Crossing that boundary carries a cost. Catalog readers inherit whatever your statistics settings produce. Turn track_counts off or starve autovacuum and the reader reports a fuzzy picture. Readers also inherit vendor retention.

For AWS deployments, assess CloudWatch Database Insights using its current Standard and Advanced capabilities. Retention, execution-plan analysis, engine support, and recommendations depend on the mode and database configuration. Verify these settings before relying on a historical comparison during an incident.

The ladder: monitoring, observability, and automated action

Three stages build on each other. Stage one is monitoring: dashboards, thresholds, alerts. You know something broke. The second is observability: you reconstruct why from retained signal you gathered before the question arrived. Stage three closes the gap between cause and change.

Here is the honest part. Most teams that buy an observability tool stall at stage two anyway. The tool works as designed and produces a ranked list. This index is unused. One query reads a million rows to return ten. Another plan regressed. Those findings then go into a queue with no natural owner, so recommendations age while the team fights fires.

Cast AI’s DBO documentation names this failure when describing why recommendations sit unactioned. They have “no natural owner in most organizations.” Advice competes with everything else on a small plate. That competition is the ceiling. The third stage removes the research and triage queue, not the review step. Automation here means the reasoning happens for you. The decision stays with a person, who still approves each change before it lands.

The tool landscape

Four categories, judged by what each observes and whether it acts on the findings.

CategoryExamplesWhat it observesRecommendsActsEngines
APM with DB moduleDatadog DBM, New RelicQuery metrics, samples, some plans, wait eventsVaries by product and enabled featuresNoBroad: Postgres, MySQL, MariaDB, Oracle, SQL Server, MongoDB, Db2
Database-nativepg_stat_statements, slow query log, CloudWatch Database Insights, auto_explainQuery stats, logs, wait events, plansNoNoPer-engine, built in
Postgres specialistpganalyzeFull activity, plan history, index advisorYes, index and query adviceNoPostgreSQL, Aurora PostgreSQL
Query recommendations and optional proxy optimizationCast AI DBOpg_stat_statements and performance_schema signalsIndex and rewrite proposals ranked by database time reclaimedAdvisor proposes reviewed changes; optional proxy provides pooling and cachingSupported PostgreSQL and MySQL deployments; check the current version matrix

The APM row is where most teams start, and it is good at breadth. Rows two and three cost less. Native tables are free and cover the built-in setup below. The specialist row trades breadth for Postgres depth. That last row exists because reading signal without changing anything forms the queue.

How to get the basics without buying anything

Stage two on Postgres costs a shared library, an extension, and a log line. Start here before buying anything. The commands below are Postgres-specific. MySQL exposes a different surface through performance_schema and the slow query log, so the equivalent steps differ by engine.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = top
log_min_duration_statement = 500   # ms; -1 disables, 0 logs everything

# then, in a session connected to your database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Add pg_stat_statements to existing shared_preload_libraries entries rather than replacing other libraries, then restart as required. Create the extension in the database being inspected. Slow-query logging can expose SQL and parameter values; choose thresholds, access controls, and retention deliberately. Managed services use provider-specific configuration controls.

Rank by total execution time using PostgreSQLโ€™s documented statistics columns. This example targets PostgreSQL 14 and later and scopes the results to top-level statements in the current database:

SELECT
  round(total_exec_time::numeric, 0) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / NULLIF(sum(total_exec_time) OVER (), 0))::numeric, 2) AS pct_total,
  left(query, 120) AS query
FROM pg_stat_statements
WHERE toplevel
  AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC
LIMIT 20;

toplevel distinguishes application-level statements from statements nested inside functions or procedures. It does not remove separate ORM queries. Scoping by dbid avoids combining databases; retaining userid, dbid, queryid, and toplevel is important when comparing snapshots.

Triage the output in this order: top statements by total time, active waits in pg_stat_activity, blockers, plan history.

Three more moves fill out the rest. Turn on auto_explain with auto_explain.log_min_duration around 500ms so slow plans land in your logs; set that threshold deliberately, because blanket capture adds planning overhead and log volume on busy systems. Re-capture top plans after every deploy and statistics change, since that is when plan flips appear. Check pg_stat_activity for idle in transaction sessions too, because they block vacuum and cause lock pileups.

Our guide to Postgres slow queries walks through the full diagnostic flow. For the engine-agnostic version, read database optimization techniques.

How Cast AI addresses this

Everything above gets you to stage two, and none of it clears the queue. That queue is a staffing problem. Cast AI Database Optimization removes the research and triage behind that queue and hands back a proposal you review. The review step stays put.

Cast AI Database Optimization is generally available. Its read-only Performance Advisor analyzes supported PostgreSQL and MySQL workloads and prioritizes index and query-rewrite recommendations. Recommendations include proposed SQL and before-and-after execution-plan analysis with projected impact. Review and test those projections against representative data before applying a change; the advisor does not apply schema changes.

The optional DBO proxy adds connection pooling and query caching after a connection-string change. These capabilities are separate from read-only analysis. Current deployment guidance covers Kubernetes and, for MySQL, ECS. Check the supported-platform matrix for engine versions and deployment requirements.

That model suits teams that want the search work gone but keep schema authority. The person making the change is you. Start a free trial with read-only database analysis.

Conclusion

Application traces identify slow requests, while database-level statistics, plans, and waits explain the work behind them. Integrate both views, retain enough history for meaningful comparisons, and assign owners to the highest-impact findings.

Start today with the built-in setup: enable pg_stat_statements, rank by total time, turn on auto_explain, then re-capture plans after deploys. Once you have that signal, decide whether the remaining triage work is worth automating.

For the breadth of the tool market, read our database optimization tools guide. Then see the platform GA announcement on how DBO turns recommendations into reviewed changes, or start at the database optimization page.

FAQ

What is database observability?

Database observability is the ability to answer why a database is slow: which queries, plans, locks, connection states, and costs produce the latency you observe. It goes past threshold alerts and reconstructs unknown causes from retained signal.

Monitoring versus observability for databases?

That distinction drives tooling choices. A threshold alert fires at CPU 80 percent. Observability lets you investigate a failure you did not predict, such as one query degrading after a statistics change. Thresholds answer pre-set questions. Retained signal answers new ones.

Does Datadog monitor database queries?

Yes, if you enable Database Monitoring. Datadog DBM exposes query metrics, samples, and explain plans. Its docs describe it as showing database-level metrics not captured by APM, such as rows updated and returned. It reads the database side of the wire, which is why it adds detail an APM alone misses.

What should database observability include?

Five signal families: query stats, plans and plan history, locks and wait events, connection states, and cost. A tool is only as observable as the families it collects, so check each against your stack.

Is pg_stat_statements observability?

Yes. It is a useful query-statistics layer, but it does not store execution plans. Add plan capture such as auto_explain, activity and wait information, and historical retention to investigate causes beyond aggregated query timing.

Can database observability be automated into action?

Yes, with different levels of control. Cast AIโ€™s read-only advisor proposes index and query changes for human review. Its optional proxy can pool connections and cache eligible queries once configured. Validate correctness, compatibility, and performance before enabling changes in production.

Cast AI โ€บ Blog โ€บ Database Observability: Queries, Plans, Locks, and Costs