Database Metrics

A 02:13 page. Users see 504s on checkout. The application dashboard shows normal CPU. Memcached hit ratio is fine. The on-call engineer opens Grafana, looks at the database panel, and sees that pg_stat_activity_count{state="active"} crossed three hundred, pg_stat_database_tup_fetched is climbing at twice last night’s rate, and pg_stat_replication_lag_seconds on the replica is over forty seconds. None of those numbers were alerting; they should have been.

This is what database observability exists to prevent. Every relational database answers the same five operational questions. PostgreSQL exposes them through pg_stat_* views and, when loaded, pg_stat_statements. MySQL and MariaDB expose the equivalent through INFORMATION_SCHEMA and performance_schema. The question for the operator is not whether the data exists; it is how to get it into Prometheus reliably, at a cost that does not contribute to the problem you are trying to see.

What database metrics are

The metric surface of a relational database is the set of counters, gauges, and cumulative distributions that answer: what is the database doing right now, and what has it been doing for the last five minutes? The surface is split into five families. Every production-grade database exposes some form of each:

1. Connections. Active sessions, idle-in-transaction, waiters, the configured maximum. The shape of the pool.
2. Statements. Total calls, total time, mean time, rows touched per normalised query text. The shape of the workload.
3. Locks. Granted, waiting, deadlocks. The shape of contention.
4. Replication. Sender / receiver bytes, applied position delta, replica lag in seconds and in transactions. The shape of durability and read scale.
5. Buffers. Cache hit ratio, eviction count, dirty pages, WAL generated per second. The shape of the I/O surface.

            +---------------------------------------+
            |        Production database            |
            +---------------------------------------+
            |  pg_stat_database / INFORMATION_SCHEMA|
            |  pg_stat_activity / processlist       |
            |  pg_stat_statements / events_statements|
            |  pg_locks / performance_schema locks  |
            |  pg_stat_replication / show replica   |
            |  pg_stat_io / sys.dm_os_buffer        |
            +---------------+-----------------------+
                            |
                  +---------+----------+
                  |                    |
            exporter process       instrumentation
                  |                    |
                  v                    v
              /metrics             OTLP push
                  |                    |
            Prometheus          OpenTelemetry Collector
                  \                  /
                   +------+---------+
                          |
                       Grafana

Exposing those views to Prometheus is the job of an exporter. Two shapes exist and the distinction matters:

• A separate exporter process that opens its own connection to the database, runs read-only queries against the views, and serves the result on a Prometheus /metrics endpoint. Examples: postgres_exporter, mysqld_exporter, sqlserver_exporter, oracledb_exporter. This is the dominant pattern in production.
• An instrumentation library linked into the database (rare; mostly client-side libraries that report pool metrics from the application’s connection pool — HikariCP, pgxpool, JDBC — and not server-side database metrics).

The remainder of this module assumes the separate-exporter shape. If the application reports its own pool metrics, treat those as an additional signal and not a replacement for the database exporter; the application’s view is from inside the connection, not from inside the engine.

Why a sysadmin cares

Database metrics answer five operational questions without which the rest of the platform is investigation from the symptom side only:

1. Has the pool hit its ceiling? Without that, the “connection refused” message at 02:00 is the first time anyone learns there is a ceiling.
2. Which query is slow and is it the same query as yesterday? Without that, “the database is slow” is the entire report and the on-call is guessing the cause.
3. Is there lock contention and which tables? Without that, the queue backing up is invisible until a job times out.
4. Is the replica drifting? Without that, read-after-write promises break silently and customer support learns first.
5. Is the buffer cache serving the working set? Without that, a slow disk looks like a slow database until someone reboots a host and the cache fills again.

Each of these has caused a major incident in at least one team the author has worked with. Each is preventable with a deliberately-configured exporter and the right alert.

How it works

The exporter runs a configured set of read-only queries against the database at a fixed interval, normalises the result into Prometheus sample lines, and exposes them on a TCP port. The exporter itself is stateless — the state is in the database views it reads. The five families map to five families of read-only queries.

  Exporter        Read-only         Postgres / MySQL
  process   --->  SQL queries  ---> pg_stat_* /
  (every           (every 5-30s)     information_schema
  15s by                             performance_schema
  default)
        |
        v
  /metrics (text exposition, see The Exporter Contract)
        |
        v
  Prometheus scrape

Two operational choices dominate the configuration: what to read, and how often.

• What to read. The exporter has a configuration file that decides which views to query. Reading everything is possible but produces a forest of low-value labels. Reading nothing is the default behaviour the moment a permissions check fails.
• How often. Postgres exposes cumulative counters and recomputed views. Reading them more often than every ten seconds rarely buys useful precision and always buys load on the database. The exporter defaults are deliberately conservative (15s); the database itself is the source of the load budget.

How to configure it

The two production shapes are PostgreSQL and MySQL. Both have a single recommended exporter; the configuration discipline is the same. The minimum viable setup follows.

Step 1: create a dedicated read-only role.

-- postgres.sql
CREATE USER db_exporter WITH PASSWORD 'REDACTED' INHERIT;
GRANT pg_read_all_stats TO db_exporter;
GRANT CONNECT ON DATABASE app TO db_exporter;
-- pg_read_all_stats is the minimum for the default collector set.
-- pg_stat_statements and per-table collectors require additional grants;
-- see the next lesson.

Do not reuse an application role. The exporter role exists to be audited, to be revoked in a single statement, and to have the narrowest permission set that still produces a useful metric surface.

Step 2: load the extension the exporter needs (PostgreSQL).

-- one-time, in the application database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

pg_stat_statements lives in shared_preload_libraries and must be added to postgresql.conf before the database starts:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top
pg_stat_statements.track_utility = off

The defaults are suitable for a 64 GiB host. Tune max upward on hosts with very high query-cardinality; leave track_utility = off unless you need to see DDL activity.

Step 3: run the exporter.

# /etc/default/prometheus-postgres-exporter
ARGS="--web.listen-address=10.0.4.7:9187 \
      --extend.query-path=/etc/postgres_exporter/queries.yaml \
      --collector.stat_database \
      --collector.stat_activity \
      --collector.stat_replication \
      --collector.stat_statements"

The --extend.query-path argument loads custom read-only queries. Keep it short; every additional query is an additional load on the database. The default collectors are the right starting point.

Step 4: scrape it from Prometheus.

# prometheus.yml (excerpt)
scrape_configs:
  - job_name: postgres
    static_configs:
      - targets: ['10.0.4.7:9187']
        labels:
          environment: production
          role: primary
    scrape_interval: 15s
    scrape_timeout: 10s
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance
        replacement: 'db-primary-01.internal'

The instance relabel keeps the database’s identity stable even if the exporter moves. Bind the exporter to an internal address; database metrics have no business being on the open network.

How to validate it

Every step is READ-ONLY.

# 1. Confirm the exporter is up and is speaking Prometheus text format.
curl -sI http://10.0.4.7:9187/metrics | head -3

HTTP/1.1 200 OK
Content-Type: text/plain; version=0.0.4; charset=utf-8

# 2. Confirm the families you expect are present.
curl -sf http://10.0.4.7:9187/metrics \
  | grep -E '^pg_stat_(activity|database|replication)_' | head -10

# HELP pg_stat_activity_count Number of all PostgreSQL processes by state.
# TYPE pg_stat_activity_count gauge
pg_stat_activity_count{state="active"} 12
pg_stat_activity_count{state="idle"} 47
pg_stat_activity_count{state="idle in transaction"} 3
pg_stat_activity_count{state="waiting"} 1

# 3. Confirm pg_stat_statements is loaded if you enabled the collector.
curl -sf http://10.0.4.7:9187/metrics | grep -E '^pg_stat_statements_'

# 4. Confirm Prometheus is parsing the body without parse errors.
#    A successful scrape shows up{} equals 1 and a parse error rate of 0.
curl -sG http://prometheus:9090/api/v1/query \
  --data-urlencode 'query=up{job="postgres"}' | jq '.data.result'

[
  {
    "metric": {"job": "postgres", "instance": "db-primary-01.internal"},
    "value": [1734000000.000, "1"]
  }
]

# 5. Confirm the exporter's own logs do not contain permission errors.
docker logs --since 1h prometheus-postgres-exporter 2>&1 \
  | grep -iE 'permission denied|role .* does not exist|relation .* does not exist'

If the grep is empty, the role has the required grants. A silent exporter that returns no error but produces nothing on /metrics is the most common production failure of this exporter.

How it can fail

Six failure shapes account for the overwhelming majority of production incidents with database metrics.

1. Exporter role revoked. The grants were set, the exporter ran, somebody rotated the password and the exporter’s connection pool is now authenticating with the wrong secret. Symptom: up{job="postgres"} == 0; the exporter log shows pq: password authentication failed for user "db_exporter". No application behaviour has changed.
2. pg_stat_statements not loaded. The extension was created, but shared_preload_libraries was never updated and the database was never restarted. Symptom: the pg_stat_statements_* metric families are absent from /metrics. The exporter is up; the data is missing.
3. pg_read_all_stats not granted (older Postgres). On PostgreSQL versions before 14, the role lacks the pg_read_all_stats membership. Symptom: the exporter returns permission denied for table pg_stat_database; individual pg_stat_* metrics may or may not be present.
4. instance relabel absent. The exporter runs on a pod whose IP changes on every restart. Symptom: the Grafana dashboard shows new “instances” appearing and disappearing in minutes; alerts across instance flap. Apply the relabel rewrite shown in the configuration section.
5. Custom query file breaks after upgrade. The queries.yaml file used a column name that the database version no longer carries. Symptom: the exporter logs column ... does not exist once per scrape; metrics from the custom file disappear; the rest of /metrics is unaffected. The page never fires because the underlying up is 1.
6. High-cardinality query labels enabled. The exporter is configured with a per-datname label on pg_stat_activity or a per-query hash label. Symptom: Prometheus time-series count jumps from tens of thousands to millions; ingestion stalls; --storage.tsdb.retention.size is reached in days instead of months. The exporter is the cause, not the symptom.

How to troubleshoot it

Diagnose in this order.

1. Is the exporter reachable from Prometheus? curl -sI http://10.0.4.7:9187/metrics and up{job="postgres"} confirm two halves of the same fact.
2. Is the connection credentials set? Look at the exporter log. password authentication failed and connection refused are the two most common first lines.
3. Are the grants sufficient? From the application database, run SELECT has_table_privilege('db_exporter', 'pg_stat_database', 'SELECT'); and expect t. Do the same for pg_stat_activity and pg_stat_replication.
4. Is pg_stat_statements loaded? SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'; should show installed_version populated. If installed_version is empty, the extension is available but not loaded; restart with shared_preload_libraries updated.
5. Is a custom query set drifting? Run each query in queries.yaml from psql with the exporter role. A column rename or a view upgrade is the usual cause.
6. Is cardinality exploding? prometheus_tsdb_head_series{job="postgres"} should sit on the order of 10-50k series for a production database with the default collectors. If it is in the millions, the label set is too rich.

Security implications

Two surfaces need attention: transport and credentials.

• Transport. The exporter returns the database’s activity view, including query text from pg_stat_activity. That query text can include parameter values if pg_stat_statements.track = all is set. Bind the exporter to an internal address and front it with mTLS at the network boundary; do not expose 9187 on the open network.
• Credentials. The exporter role must be the narrowest that the collector set requires. pg_read_all_stats is the PostgreSQL convention; SELECT on the relevant performance_schema and information_schema tables is the MySQL convention. Do not give the exporter SUPERUSER, SUPER, or BYPASSRLS. Revoke the role in a single statement during incident response if the database is compromised.
• Audit. The exporter’s own log shows every connection and every grant failure. Ship those logs to Loki and alert on permission denied strings. Treat the exporter role as a named, audited identity and not an anonymous convenience.

Performance implications

Performance comes from three levers, all in the exporter’s configuration.

• Scrape interval. The dominant cost. 5s is too frequent for a default collector set; 30s is too coarse for alerting. Start at 15s.
• Collector set. Every enabled collector is one or more queries. --collector.stat_activity runs against the system view and is harmless; per-table collectors (in older versions) run per-table and pay a fixed cost per query.
• Custom query file. Every line in queries.yaml is a round-trip. Keep the file short and the queries bounded. Long-running queries that block behind a hot table are the most common silent exporter regression.

The exporter itself uses little CPU and memory; the dominant cost is the load it places on the database. The trade-off is deliberate: production observability that the database cannot support is observability that the database will optimise away under load.

Production guidance

• Pin the exporter version. Record it in the exporter’s build_info label and alert on unexpected changes.
• Use a dedicated role. Audit the grants on a schedule.
• Use pg_read_all_stats and the equivalent performance_schema grants. Do not give the exporter superuser.
• Bound pg_stat_statements.max so a runaway application does not fill the shared memory with millions of normalised query rows.
• Validate /metrics weekly with promtool check metrics.
• Alert on up == 0 and on the sample count dropping below a baseline. Permission failures present as zero samples, not as a failed scrape.
• Treat the exporter’s connection pool separately from the application’s pool. The exporter pool is small (1-5 connections) and never receives query traffic.

Verification

You should now be able to answer:

• What are the five families of database metrics and what does each answer?
• What is the minimum set of grants a Postgres role needs to expose them through postgres_exporter?
• What is the difference between an exporter and an instrumentation library for database metrics?
• How do you distinguish a working exporter from one that is silently producing empty metrics?
• What is the most common cause of a cardinality explosion in database metrics?

Quiz