Database dashboards in most teams show CPU, memory, disk I/O, and connection count. Those are necessary but not sufficient. The metrics that catch real incidents — long-running queries, blocking locks, replication lag, query plan regressions — live in different places and require deliberate instrumentation.
This guide is about the data sources that produce useful database observability, how to surface the signal without flooding dashboards, and the alerts that actually wake the right person at the right time.
Where the Useful Data Lives
Databases expose three layers of observable data:
- System metrics — CPU, memory, disk, network. Captured by host-level exporters (
node_exporter,postgres_exporter). - Database-engine metrics — connections, transactions, cache hit rate, vacuum activity. Captured by database-specific exporters.
- Query-level data — slow queries, query plans, lock waits, replication state. Lives in database catalog views (
pg_stat_statements,INNODB_STATUS) and slow query logs.
Most teams stop at layer 1 or 2. Layer 3 is where outages actually start, and where the highest-value observability lives.
Slow Query Logging
The single most useful database observability signal is a record of slow queries. Postgres's pg_stat_statements extension aggregates execution statistics for every query (normalized form, with literal values replaced). MySQL's slow_query_log records individual slow executions. Both are essential.
Postgres setup:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, mean_exec_time, max_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
This surfaces the queries that consume the most aggregate execution time, not just the slowest individual queries. A query that runs 50,000 times per minute at 20ms each is a bigger problem than one that runs once per hour at 500ms.
For MySQL, set slow_query_log = ON and long_query_time = 0.5 (or lower for an active service). Ship the resulting logs to your log backend; tools like Percona's pt-query-digest aggregate them into a summary.
In either case, exporters convert this into time-series metrics — slowest query by mean execution time, by call count, by lock wait time — which dashboards can show alongside system metrics.
Lock Waits and Blocking Queries
A query that takes 50ms is fast. A query that takes 50ms but is also blocked by a long-running transaction is the start of an incident. Lock observability is what separates "the database feels slow" from "I know which transaction is holding the lock."
Postgres exposes lock state via pg_locks joined with pg_stat_activity. A standard "who is blocked by what" query:
SELECT
blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query,
blocked.wait_event_type, blocked.wait_event
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid AND NOT blocked_locks.granted
JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE blocked.pid <> blocking.pid;
In an incident, this query identifies the head of a blocking chain in seconds. Export it as a metric (count of blocked sessions, age of longest blocked wait) and you get an alert before the chain causes user-visible failures.
MySQL's equivalent is performance_schema.events_waits_current joined with processlist, or the sys.innodb_lock_waits view.
Replication Lag
If your read traffic goes to replicas, replication lag is one of the most important metrics in the system. Latency above a few seconds means users hitting reads see stale data; latency above a minute usually means a transaction is replaying slowly on the replica.
Postgres exposes lag through pg_stat_replication on the primary and pg_last_wal_replay_lsn() on the replica. The simplest useful metric:
-- on the primary, per replica
SELECT
client_addr,
state,
EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds
FROM pg_stat_replication;
Alert on lag_seconds above a threshold appropriate to your application's staleness tolerance — typically 1-10 seconds. Lag growing over time, even if still under threshold, often signals an impending problem.
MySQL replication lag is exposed via SHOW REPLICA STATUS (SECONDS_BEHIND_SOURCE), with the caveat that it measures the difference between the replica's clock and the timestamp in the binlog event being applied. Long-running transactions can make this misleading; a dedicated heartbeat row updated by a cron job gives more reliable measurement.
Vacuum and Bloat (Postgres)
Postgres-specific, but operationally consequential. Tables accumulate dead tuples from updates and deletes; autovacuum reclaims that space; if autovacuum cannot keep up, queries get slower and disk grows. The pathological case ("XID wraparound") can take the database offline.
Metrics worth dashboarding:
pg_stat_user_tables.n_dead_tup / n_live_tupper table — bloat ratio.- Last autovacuum timestamp per table — staleness of cleanup.
- Age of
relfrozenxidfor the oldest table — distance from XID wraparound.
None of these are emergencies most days. Watching the trend catches problems weeks before they become emergencies.
Query Plan Regressions
A query that ran fast yesterday and slowly today usually went through a plan change — Postgres or MySQL switched to a different join strategy or index, often because statistics drifted or a new index changed the planner's choice.
Detecting plan regressions requires storing plan information. Postgres's auto_explain extension logs the actual plan of slow queries:
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000 # log plans for queries over 1s
auto_explain.log_analyze = on
auto_explain.log_buffers = on
Plans land in the database log. Pipe them into your log backend and you can query for "plans for query X over the last month" — invaluable when a query that was fine for a year suddenly is not.
Connection Pool State
Application-side connection pools have their own observability surface separate from the database. Metrics that catch real incidents:
- Pool utilization (active / max). Above 80% sustained means the pool is the bottleneck, not the database.
- Connection acquisition wait time. The time from "I need a connection" to "I have one."
- Connection lifetime. If average lifetime is very short, the pool is churning — usually a sign of misconfiguration.
Most ORMs and connection poolers (HikariCP, pgbouncer, ProxySQL) expose these metrics natively. Wiring them into Prometheus is a one-time setup.
What to Alert On
A conservative alert set for a typical OLTP database:
- Connection pool saturation above 80% for 5 minutes.
- Replication lag above the application's tolerance (1-10 seconds typical).
- Long-running transactions (over 5 minutes for a transactional service).
- Locked transactions for over 30 seconds.
- Disk space below 20% free.
- Vacuum age for the oldest table approaching 50% of the wraparound limit (Postgres).
Notice what is not on the list: query latency. Per-query latency varies for many normal reasons; alerting on aggregates from the application side (request latency including DB time) is more useful.
Building the Dashboard
A practical database dashboard, in panel order:
- Connection state — total connections, active, idle, idle in transaction. Time-series.
- Slowest queries (table) — top 10 by mean execution time from
pg_stat_statements. - Lock waits — count of blocked queries over time, longest current wait.
- Replication lag per replica.
- Cache hit rate — buffer cache, index hit rate.
- Vacuum activity (Postgres) or innodb buffer pool stats (MySQL).
- System metrics — CPU, memory, disk I/O.
The order matters. System metrics last because they are the least specific. Slow queries and lock state first because they are where the answer usually is.
A Final Word on Cardinality
Database metrics have a cardinality trap: per-query metrics with normalized query text can produce thousands of unique series. pg_stat_statements includes a queryid that is bounded; using it as the time-series label avoids the explosion. Avoid raw query text as a label.
The same applies to per-database, per-schema, or per-user labels. Each adds a multiplicative factor to your time-series count. Bound them or drop them.
Database observability is shallow on most teams. We help engineering teams build the layer of visibility that catches problems before they hit the application. scopeforged.com