PostgreSQL Monitoring with Prometheus and postgres_exporter

PostgreSQL Monitoring Setup with Prometheus and postgres_exporter

Consider this: when PostgreSQL load increases and SQL queries degrade, standard host metrics don't show the root cause. Without proper observability, incidents are detected only after user complaints. Our practice shows that a properly configured postgres_exporter reduces the average time to detect issues from 2 hours to 15 minutes. Plus, automatic alerts allow you to react before the database becomes unavailable. A typical situation: a sudden IO-wait spike or growing bloat — without monitoring, such events go unnoticed until a critical failure. We offer a systematic approach: postgres_exporter, custom metrics, alerts, and dashboards that give a complete picture of database health.

What postgres_exporter Collects

The prometheus-community/postgres_exporter connects to PostgreSQL as a regular client and executes SQL queries against system views (pg_stat_activity, pg_stat_user_tables, pg_locks, pg_replication_slots, and others). Results are exposed at :9187/metrics. The built-in role pg_monitor (available since PostgreSQL 10) provides read-only access to all necessary views — this is secure and sufficient. According to official documentation, pg_monitor includes all privileges on statistical views.

Installation via Docker:

---
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    environment:
      DATA_SOURCE_NAME: "postgresql://exporter:secret@postgres:5432/mydb?sslmode=disable"
    ports:
      - "9187:9187"

The exporter user needs minimal privileges:

CREATE USER exporter WITH PASSWORD 'secret';
GRANT pg_monitor TO exporter;
-- pg_monitor is a built-in role in PostgreSQL 10+, providing read-only access to stat views

Custom Metrics via queries.yaml

By default, the exporter collects about 30 metrics. The most important ones are not included in the basic set — they need to be added manually. File queries.yaml:

pg_table_bloat:
  query: |
    SELECT schemaname, tablename, pg_total_relation_size(schemaname||'.'||tablename) AS total_bytes, pg_relation_size(schemaname||'.'||tablename) AS table_bytes FROM pg_tables WHERE schemaname NOT IN ('pg_catalog','information_schema')
  metrics:
    - schemaname:
        usage: LABEL
    - tablename:
        usage: LABEL
    - total_bytes:
        usage: GAUGE
        description: "Total table size including indexes"
    - table_bytes:
        usage: GAUGE
        description: "Table data size"
pg_long_running_queries:
  query: |
    SELECT count(*) as count, max(EXTRACT(EPOCH FROM (now() - query_start))) as max_seconds FROM pg_stat_activity WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%' AND query_start < now() - interval '5 minutes'
  metrics:
    - count:
        usage: GAUGE
        description: "Number of long-running queries"
    - max_seconds:
        usage: GAUGE
        description: "Max query duration in seconds"

Critical PostgreSQL Metrics

In practice, the first issues are most often:

Bloat and autovacuum. The metric pg_stat_user_tables_n_dead_tup shows the number of dead rows in a table. If autovacuum cannot keep up, this number grows, tables bloat, and indexes degrade. An alert on n_dead_tup > 1,000,000 for critical tables is the bare minimum.

Replication lag. For standby replicas, the metric pg_replication_slots_confirmed_flush_lsn and the difference from pg_current_wal_lsn() give lag in bytes. In Grafana, this is converted to meaningful megabytes. A replica lag greater than 100 MB is a reason to investigate.

Connection pool saturation. pg_stat_database_numbackends vs pg_settings_max_connections. When the ratio exceeds 80%, the database starts refusing new connections. This is especially relevant when working without PgBouncer.

Lock waits. pg_locks joined with pg_stat_activity — a query to detect locks. One long-lived lock on a table can hang dozens of other transactions.

How to Configure Custom Metrics for Your Workload?

We load custom metrics based on workload profile: if the application writes heavily, priority goes to vacuum metrics and bloat; if it reads heavily, we focus on cache hit ratio (pg_stat_database_blks_hit / (blks_hit + blks_read)). For example, for heavy OLTP workloads, we added a metric pg_avg_sync_time, which helps identify I/O subsystem issues.

---
pg_long_tx:
  query: |
    SELECT count(*) as count, max(EXTRACT(EPOCH FROM (now() - xact_start))) as max_age_seconds
    FROM pg_stat_activity
    WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%' AND xact_start < now() - interval '1 minute'
  metrics:
    - count:
        usage: GAUGE
        description: "Number of transactions running >1 min"
    - max_age_seconds:
        usage: GAUGE
        description: "Oldest transaction age in seconds"
---

Comparison: postgres_exporter vs Built-in pg_stat_statements

postgres_exporter provides broader system-level metrics compared to pg_stat_statements, which focuses on query statistics. This makes it more suitable for comprehensive database observability.

What's Included

We provide:

• Configured postgres_exporter with minimal privileges
• Custom metrics tailored to your workload (bloat, long queries, replication)
• Alerts in Alertmanager (connections >80%, replication lag >100 MB, dead tuples >1M)
• Grafana dashboard (provisioned, with cache hit ratio, lock waits)
• Documentation for deployment and maintenance
• 30-minute demo and team training

Our engineers have 10+ years of experience operating PostgreSQL and monitoring. We ensure that after setup, you can catch issues proactively and reduce incident response time dramatically.

Process

1. Audit — manual review of pg_stat_activity, pg_stat_user_tables, pg_replication_slots. Identify bottlenecks as they are. (~1 hour)
2. Deploy — install postgres_exporter, configure scrape in Prometheus, basic alerts. (~1-2 hours)
3. Customization — add queries.yaml for your workload, set thresholds. (~2-4 hours)
4. Visualization — create Grafana dashboards via provisioning. (~2-4 hours)
5. Documentation — runbook with alerts and actions. (~1 hour)

Total: from 4 hours for basic setup to 2 days for full coverage with custom metrics and provisioned dashboards.

How to Avoid Common PostgreSQL Monitoring Mistakes

• Using a superuser for the exporter — instead, grant only pg_monitor.
• Forgetting custom metrics — without them, you won't see bloat and long transactions.
• Not configuring alerts — Prometheus without alerts is useless.
• Using unversioned dashboards — dashboards should be in git for reproducibility.

Contact us for a consultation: we will help set up monitoring of your PostgreSQL in 1-2 days. Get database transparency and reduce downtime. Order monitoring setup today — our engineers will prepare a custom offer for your stack.