PostgreSQL monitoring and logs

PostgreSQL observability needs at least three evidence layers: query statistics show where resources go, metrics show when the system leaves its baseline, and logs preserve error and event context. A single dashboard does not replace these layers.

1. Find workload hotspots with pg_stat_statements

pg_stat_statements is an official PostgreSQL extension. Add it to shared_preload_libraries, which normally requires a restart, then create it in each database that needs statistics:

shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  left(query, 160) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Rank total time, mean time, calls, rows, and I/O separately. The slowest individual call and the largest cumulative consumer are different problems. Statistics can reset, so record sampling windows around deployments, incidents, and configuration changes. Use the official pg_stat_statements documentation for field semantics.

2. Preserve event context with JSON logs

jsonlog makes timestamps, SQLSTATE, backend, database, user, application name, and error context reliably parseable:

logging_collector = on
log_destination = 'jsonlog'
log_min_duration_statement = '500ms'  # Example only; derive from workload baseline
log_lock_waits = on
deadlock_timeout = '1s'

Do not copy one threshold into every environment. Too low creates excessive I/O and sensitive query text; too high misses frequent medium-latency queries. Use pg_stat_statements for cumulative hotspots and logs for errors, lock waits, checkpoints, autovacuum, and specific slow requests. See Error Reporting and Logging.

pgBadger can process PostgreSQL native logs and jsonlog, producing query, connection, error, lock, checkpoint, and autovacuum reports. Stabilize format, rotation, and time zones before adding it to offline analysis.

3. Metrics, Prometheus, and Grafana

postgres_exporter fits teams already using Prometheus and Grafana. Prefer pg_monitor or the minimum read-only statistics privileges over superuser access:

CREATE ROLE metrics LOGIN;
GRANT pg_monitor TO metrics;

Verify collectors and privileges against the target version. Upstream still labels multi-target mode Beta, and custom extend.query-path queries are deprecated. Prefer built-in collectors or a separate generic SQL exporter for new custom collection rather than growing an unmaintainable query file.

Minimum signal set

Derive thresholds from normal and peak baselines, and make each alert lead to an actionable diagnostic path. Replication lag in bytes, time, and replay state has different meanings; one global threshold is insufficient.

Adoption order

1. Enable and govern pg_stat_statements on every production instance.
2. Emit parseable logs and collect SQLSTATE, lock waits, archive failures, and autovacuum events.
3. Add postgres_exporter and PostgreSQL-specific dashboards when Prometheus already exists.
4. Add pgBadger for log trends; evaluate pgwatch for multiple instances.
5. Evaluate PoWA for deeper workload analysis and pg_activity for interactive incident diagnosis.

More tools do not automatically remove blind spots. First standardize the dimensions that connect evidence: instance, database, role, application, query ID, time window, and change event.