PostgreSQL Performance Monitoring with Prometheus and Grafana: Complete Setup Guide

Table of Contents

Context & Why: The Blind Spots in Production Databases

Running production PostgreSQL without live telemetry is a gamble you will eventually lose. Everything hums along smoothly until an unindexed JOIN locks your orders table, connections hit the ceiling at 9:00 AM, or autovacuum falls 50 GB behind write volume. Relying on error logs and periodic pg_top checks means you only discover problems after your users do.

Databases rarely crash without warning. Instead, they bleed performance over days. Buffer cache hit ratios drop from 99.2% down to 87%. Disk I/O wait climbs from 2 ms to 45 ms. Replication lag stretches from 10 milliseconds to 8 seconds. A continuous metric pipeline catches these regressions before they cascade into full-blown outages.

Three complementary tools handle this pipeline cleanly:

postgres_exporter: Connects to PostgreSQL via a restricted user and translates catalog views (such as pg_stat_database and pg_stat_activity) into Prometheus gauge and counter formats.
Prometheus: Scrapes the exporter endpoint every 15 seconds, indexes the time-series points, and evaluates alerting thresholds continuously.
Grafana: Plots the ingested data on real-time dashboards and fires alert webhooks to Slack, PagerDuty, or Discord when queries breach SLAs.

Benchmarking and staging require realistic data schemas before deployment. If you need to quickly massage seed files, toolcraft.app/en/tools/data/csv-to-json converts raw CSV extracts into clean JSON payloads right inside your browser without uploading private data to remote servers. Once you populate your test tables and fire synthetic transactions, your monitoring stack will reveal the exact CPU, lock, and I/O footprint of each query pattern.

Installation: Deploying the Monitoring Stack

Docker Compose keeps the entire telemetry stack isolated and reproducible across staging and production hosts. If network latency between containers is a concern, run postgres_exporter as a native systemd unit directly on the database node instead.

1. Create a Dedicated Database User

Never expose the postgres superuser credential to third-party services. PostgreSQL 10 and newer provide the built-in pg_monitor role, giving you full access to internal diagnostic tables without table-read privileges:

-- Run as postgres superuser
CREATE USER postgres_exporter WITH PASSWORD 'YourSecureExporterPasswordHere!';
GRANT pg_monitor TO postgres_exporter;
GRANT CONNECT ON DATABASE production_db TO postgres_exporter;

This role reads server metrics, active query states, and lock tables safely. It cannot read confidential row data or alter runtime settings.

2. Deploy with Docker Compose

Create a directory named pg-monitoring and save the following docker-compose.yml file:

version: '3.8'

services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:v0.15.0
    container_name: postgres_exporter
    restart: unless-stopped
    environment:
      DATA_SOURCE_NAME: "postgresql://postgres_exporter:[email protected]:5432/production_db?sslmode=disable"
    ports:
      - "9187:9187"

  prometheus:
    image: prom/prometheus:v2.52.0
    container_name: prometheus
    restart: unless-stopped
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml:ro
      - ./alert_rules.yml:/etc/prometheus/alert_rules.yml:ro
      - prometheus_data:/prometheus
    command:
      - '--config.file=/etc/prometheus/prometheus.yml'
      - '--storage.tsdb.path=/prometheus'
      - '--storage.tsdb.retention.time=15d'
    ports:
      - "9090:9090"

  grafana:
    image: grafana/grafana:11.0.0
    container_name: grafana
    restart: unless-stopped
    ports:
      - "3000:3000"
    volumes:
      - grafana_data:/var/lib/grafana
    environment:
      - GF_SECURITY_ADMIN_USER=admin
      - GF_SECURITY_ADMIN_PASSWORD=AdminSecurePassword123

volumes:
  prometheus_data:
  grafana_data:

Configuration: Connecting Metrics, Dashboards, and Alerts

1. Enable `pg_stat_statements` in PostgreSQL

To inspect query execution times alongside hardware resource consumption, turn on pg_stat_statements inside your postgresql.conf file:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on
track_activity_query_size = 2048

Restart PostgreSQL to load the shared library. Then register the extension inside your target database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

2. Configure Prometheus Scraping and Alert Rules

Save this baseline prometheus.yml file in your deployment directory:

global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "alert_rules.yml"

scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['postgres_exporter:9187']
        labels:
          environment: 'production'
          cluster: 'main-postgres'

Next, define practical alert conditions in alert_rules.yml. Keep thresholds calibrated to prevent alert fatigue:

groups:
  - name: postgresql_alerts
    rules:
      - alert: PostgresDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL instance is unreachable"
          description: "postgres_exporter cannot connect to target PostgreSQL instance for over 1 minute."

      - alert: HighConnectionUsage
        expr: (sum(pg_stat_database_numbackends) by (instance) / pg_settings_max_connections) * 100 > 85
        for: 3m
        labels:
          severity: warning
        annotations:
          summary: "Connection pool over 85% capacity"
          description: "Active database backend connections currently exceed 85% of max_connections limit."

      - alert: LowCacheHitRatio
        expr: (rate(pg_stat_database_blks_hit[5m]) / (rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m]))) * 100 < 90
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "Buffer cache hit ratio dropped below 90%"
          description: "Buffer cache hit ratio has stayed under 90% for 10 minutes. Check for missing indexes."

      - alert: DeadlockDetected
        expr: increase(pg_stat_database_deadlocks[5m]) > 0
        labels:
          severity: critical
        annotations:
          summary: "Transaction deadlocks detected"
          description: "PostgreSQL recorded one or more deadlocks within the last 5 minutes."

3. Launch Services and Configure Grafana

Bring the containers up in detached mode:

docker compose up -d

Open http://<your-server-ip>:3000 in your browser and log in with your admin credentials. Connect Grafana to Prometheus with these quick steps:

Go to Connections > Data Sources > Add data source and pick Prometheus.
Enter http://prometheus:9090 in the Prometheus server URL field.
Click Save & Test. Look for the green success message confirming connectivity.
Navigate to Dashboards > New > Import, provide community dashboard ID 9628 (PostgreSQL Database), and select your Prometheus data source.

Verification & Monitoring: Validating Telemetry and Reading Key Signals

1. Verify the Exporter Endpoint

Verify that postgres_exporter is scraping internal metrics properly by hitting its raw HTTP endpoint:

curl -s http://localhost:9187/metrics | grep -E "^(pg_up|pg_stat_database_xact_commit)"

Expected output looks like this:

pg_stat_database_xact_commit{datname="production_db"} 482910
pg_up 1

A value of pg_up 1 confirms valid database credentials and working network connectivity.

2. Key Dashboard Signals to Watch

Keep these five indicators front and center during your daily operations review:

Commit vs. Rollback Ratio: A healthy application maintains a commit ratio above 99.5%. A sudden surge in rollbacks signals application-layer exceptions, foreign key violations, or serialization errors.
Active Connections by State: Distinguish between active execution, idle client sockets, and idle in transaction states. A connection stuck in idle in transaction for more than 30 seconds holds lock trees and blocks autovacuum cleanup.
Buffer Cache Hit Ratio: OLTP workloads should consistently exceed 98%. If your ratio falls below 90%, review whether shared_buffers is starved for memory or unindexed sequential scans are flooding cache blocks.
Replication Lag in Bytes: For streaming physical replicas, monitor pg_stat_replication. Lag exceeding 50 MB warns you of replica disk write bottlenecks or oversized transactional batches on the primary.
Transaction ID Wraparound Age: Track pg_database_datfrozenxid_age closely. If age surpasses 200 million transactions without an autovacuum freeze running, you face forced database shutdown at 2 billion.

3. Test Alert Firing

Never assume alerts work until you trigger them yourself. Generate test concurrency against your staging instance using pgbench under an application account (do not use the read-only monitoring user):

# Initialize 20 scale factors (~2,000,000 rows) with an application write user
pgbench -i -s 20 -U app_user -h localhost -d production_db

# Open 80 concurrent connections across 4 worker threads for 2 minutes
pgbench -c 80 -j 4 -T 120 -U app_user -h localhost production_db

Head to http://localhost:9090/alerts. The HighConnectionUsage alert will flip from Pending to Firing as soon as connection volume sustains past your 3-minute threshold. When the alert fires, you know your notification pipeline works.