Database Observability: Metrics, Logs, Traces, and Prometheus Alerting

Database observability is the ability to explain user-visible database behavior from evidence that can be correlated across the application and server. Metrics show rates, levels, and capacity; logs preserve selected events; traces connect a request to database client operations. A safe design starts with service objectives and diagnostic questions, then chooses the smallest trustworthy signals. It does not begin with an unreviewed dashboard or a universal threshold.

Use Native Sources Before Naming Exported Metrics

Exporter metric names and default collectors can change between releases. Define each signal against the database's native view or status variable, then verify the actual name, type, labels, and unit exposed by the deployed exporter at /metrics. Record that mapping in version control.

PostgreSQL Signals

pg_stat_database provides per-database cumulative counters including xact_commit, xact_rollback, blks_read, blks_hit, temp_bytes, and deadlocks, plus the current numbackends value and a stats_reset timestamp. Derive transaction, rollback, temporary-byte, and deadlock rates from the counters. A ratio based on blks_hit and blks_read describes PostgreSQL shared-buffer hits versus reads requested outside shared buffers; it is not a complete operating-system or storage cache hit rate.

SELECT datname, numbackends, xact_commit, xact_rollback,
       blks_read, blks_hit, temp_bytes, deadlocks, stats_reset
FROM pg_stat_database;

Use pg_stat_activity to classify session state and waits, and join carefully to lock data for a blocking investigation. n_dead_tup in table statistics is an estimated count of dead rows, not a direct measurement of table or index bloat. For physical replication, pg_stat_replication exposes sender state and WAL positions. Its write_lag, flush_lag, and replay_lag describe recent commit-related delay; PostgreSQL explicitly says they are not predictions of catch-up time. On an idle, caught-up standby, lag can retain a recent value briefly and then become NULL. Alert logic must distinguish idle, disconnected, actively falling behind, and fully caught-up states instead of converting every NULL to an outage.

MySQL Signals

MySQL global status includes cumulative Questions, Connections, Aborted_connects, Innodb_buffer_pool_read_requests, Innodb_buffer_pool_reads, and Innodb_row_lock_waits. Threads_connected and Threads_running are current levels. Decide whether the workload-rate definition uses Questions or Queries after reading their exact server-version semantics; do not add both. For an InnoDB buffer-pool miss proportion, compare the rates of physical reads and logical read requests over the same window, guard a zero denominator, and retain the raw rates beside the ratio. Absolute cumulative values since startup are not per-second metrics.

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Questions', 'Connections', 'Aborted_connects',
  'Threads_connected', 'Threads_running',
  'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
  'Innodb_row_lock_waits'
);

Replication delay also needs context. A receiver or applier can be stopped, disconnected, idle, or processing a workload with bursty timestamps. Combine replica-thread health, source connectivity, a WAL or binary-log position backlog, and—when business freshness matters—a controlled heartbeat observed through the application path. Do not page from one nullable lag field without validating its behavior during writes, idle periods, network loss, and deliberate applier pause.

Handle Counters, Rates, and Resets Correctly

Counters increase until a server restart, statistics reset, restore, failover, or exporter-label change creates a new series. Prometheus rate() is designed for counters and adjusts for counter resets within the selected range; apply rate() before aggregating series so reset detection still works. resets() is useful for diagnosing a counter decrease but is not meaningful for a gauge. Never apply a rate to current levels such as active connections, queue depth, or disk capacity.

Choose a range that contains enough scrapes and matches the response time required by the service. A one-minute rate on a low-traffic database may be mostly zeros and spikes; a long window can hide an outage. Preserve reset timestamps where the engine exposes them, monitor target identity across failover, and show missing series separately. A zero result, a missing target, and a reset counter communicate different states.

Secure Exporters and Their Database Sessions

Create a dedicated exporter identity, restrict its network source, set a connection limit where supported, and grant only what enabled collectors require. The Prometheus MySQL exporter documents PROCESS, REPLICATION CLIENT, and SELECT with a small maximum connection count for its standard collectors. Review that broad read visibility against the selected collectors and local policy; never grant write or administrative privileges just to make a dashboard green. PostgreSQL 10 and later provide the predefined pg_monitor role, which the community PostgreSQL exporter documents as its non-superuser path. That role can expose operationally sensitive activity, so it still requires controlled credentials and access.

Verify the database server identity over TLS. For a remote PostgreSQL target, use sslmode=verify-full with a trusted root certificate; never publish an example that disables TLS verification. Keep usernames and passwords in mounted secret files rather than command arguments, URLs, images, logs, or repository configuration. The PostgreSQL exporter supports separate URI, user-file, and password-file settings, while the MySQL exporter supports a client configuration file and CA configuration. Protect the exporter endpoint itself with network policy and, where needed, its supported TLS and authentication configuration. Prometheus scraping traffic can reveal topology, versions, database names, and workload patterns.

DATA_SOURCE_URI=db.internal:5432/postgres?sslmode=verify-full&sslrootcert=/etc/db-ca/ca.pem
DATA_SOURCE_USER_FILE=/run/secrets/postgres_exporter_user
DATA_SOURCE_PASS_FILE=/run/secrets/postgres_exporter_password

Instrument Traces Without Leaking Data

Database client spans can connect application latency and errors to a database operation, but automatic instrumentation is neither zero-risk nor guaranteed to capture row counts or safe SQL. Pin and document the OpenTelemetry semantic-convention version emitted by each instrumentation library. Current database conventions use attributes such as db.system.name, db.namespace, db.operation.name, db.query.summary, and db.query.text; older names such as db.statement require a planned migration rather than a blind dashboard rename.

Query text can contain personal data, credentials, access tokens, tenant identifiers, or literals embedded by an application. Prefer a low-cardinality query summary or normalized fingerprint. Disable parameter-value capture by default, allowlist any retained attributes, truncate defensively, and test redaction before production traffic. Treat span export, storage, and analyst access as data processing with documented retention. OpenTelemetry marks query parameters as opt-in, and SQL-commenter context propagation is not enabled by default; injected high-cardinality comments can also affect some database workloads. Test overhead and prepared-statement behavior before enabling it.

Design Alerts Around Actionable Symptoms

Page first on symptoms tied to user harm: sustained database-backed request errors, latency outside the service objective, exhausted connection capacity that blocks work, or data freshness beyond an agreed bound. Use database causes—lock waits, buffer reads, replica state, temporary I/O, or connection churn—to route diagnosis or create lower-urgency warnings. Prometheus guidance recommends simple, symptom-oriented alerts and a for duration that tolerates brief blips. Every page needs an owner, dashboard, current runbook, and an action that can improve the outcome.

Build each rule from a reviewed recording query. Inspect its raw label sets to avoid one page per query, table, or ephemeral container. Add an explicit target-missing rule instead of allowing absent data to look healthy. Establish thresholds from a production baseline, capacity model, and objective; test both quiet and peak periods. A cache ratio, connection count, or lag duration is context, not a universal severity level.

Validation Runbook

1. Define every signal. Record native source, exporter version and name, counter or gauge type, unit, labels, reset behavior, owner, and diagnostic question.
2. Verify transport and privilege. Confirm certificate and hostname validation, rotate the exporter secret, deny an unneeded write, and ensure the scrape endpoint is not publicly reachable.
3. Exercise controlled failures. In a safe environment, generate a slow request, failed login, blocked transaction, connection surge, exporter outage, server restart, statistics reset, and paused replica. Record expected metrics, logs, traces, and missing-data behavior.
4. Test alert delivery end to end. Let the condition survive its for interval, verify routing and inhibition, open the linked dashboard and runbook, and confirm resolution behavior.
5. Audit privacy. Search collected spans and logs for known canary secrets and personal-data patterns, verify query normalization, and test deletion and retention controls.
6. Revalidate changes. Repeat mapping and failure tests after database, exporter, driver, OpenTelemetry, dashboard, or failover changes.

For implementation patterns, continue with the Prometheus and Grafana database monitoring guide. For MySQL's native diagnostic sources, use the MySQL Performance Schema practical guide.

Official Primary Documentation

• PostgreSQL cumulative statistics and replication views
• MySQL 8.4 server status variables
• Prometheus MySQL exporter configuration and grants
• Prometheus community PostgreSQL exporter configuration
• Prometheus rate and reset function semantics
• Prometheus alerting practices
• OpenTelemetry database client span conventions
• OpenTelemetry database convention migration guide