Monitoring PostgreSQL: a practical guide for developers

Introduction

Database performance is often the silent bottleneck in production systems. You can have perfectly optimized application code, but if your PostgreSQL instance is struggling under the weight of lock contention, connection exhaustion, or cache misses, your users will feel the pain. Monitoring PostgreSQL: a practical guide for developers means treating your database as a first-class citizen in your observability strategy, not an afterthought.

By the end of this guide, you'll understand which database metrics matter most, how to collect them with postgres_exporter, and how to translate those metrics into actionable insights that prevent incidents rather than just react to them.

Why Database Monitoring Matters

We've worked through production incidents caused by unchecked connection pools, runaway queries, and replication lag that silently cascaded into cascade failures across dependent services. The pattern is always the same: a metric that seemed fine yesterday suddenly spikes, and by then you're already in damage control. Proper database monitoring catches these issues hours or days before they become incidents.

PostgreSQL exposes rich internal metrics through its system catalogs and statistics views. The challenge isn't finding data—it's knowing which metrics to watch and how to act on them.

postgres_exporter for Core Stats

The Prometheus PostgreSQL exporter is your bridge between PostgreSQL's internal statistics and Prometheus scraping. It connects to your database and exposes metrics in a format Prometheus can consume.

Start with a basic Helm deployment if you're on Kubernetes:

apiVersion: v1
kind: Service
metadata:
  name: postgres-exporter
spec:
  ports:
  - name: metrics
    port: 9187
    targetPort: 9187
  selector:
    app: postgres-exporter
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-exporter
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres-exporter
  template:
    metadata:
      labels:
        app: postgres-exporter
    spec:
      containers:
      - name: exporter
        image: prometheuscommunity/postgres-exporter:latest
        ports:
        - containerPort: 9187
        env:
        - name: DATA_SOURCE_NAME
          value: "postgresql://monitor:password@postgres.default.svc:5432/postgres?sslmode=disable"
        resources:
          requests:
            memory: "64Mi"
            cpu: "50m"
          limits:
            memory: "256Mi"
            cpu: "200m"

The DATA_SOURCE_NAME connection string should use a read-only database user with minimal privileges. We typically create a dedicated monitoring user:

CREATE USER monitor WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE postgres TO monitor;
GRANT USAGE ON SCHEMA public TO monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO monitor;
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO monitor;

Once postgres_exporter is running, you'll get metrics like pg_database_size_bytes, pg_stat_database_tup_* (tuples fetched, inserted, updated, deleted), and pg_up to verify connectivity. Add it to your Prometheus scrape config:

scrape_configs:
- job_name: 'postgres'
  static_configs:
  - targets: ['localhost:9187']
  scrape_interval: 30s

Connections, Locks and Replication Lag

Connection exhaustion is one of the most common database incidents we see. Your application's connection pool can fill up when queries hang or database maintenance locks hold too long. Monitor these metrics actively:

Active connections: pg_stat_activity_count breaks down connections by state. Track the idle, active, and idle_in_transaction buckets separately. If idle_in_transaction grows unexpectedly, you have a transaction management problem in your application code.

pg_stat_activity_count{state="idle_in_transaction"} > 10 # Alert if threshold hit

Connection limits: Set alerts well below max_connections. If you have max_connections=100, alert when active connections exceed 80. Connection pool exhaustion doesn't fail gracefully—new requests will be rejected instantly.

Lock contention: pg_locks_count tells you how many locks are held. Spikes here indicate lock waits that are blocking queries. Combine this with slow query logging to find the culprit. In one SaaS platform, we discovered a nightly batch job was holding an exclusive lock on the users table for 45 seconds, blocking all other queries.

Replication lag: If you're using streaming replication or logical replication, pg_replication_lag_seconds is critical. Lag over a few seconds during high write volume is normal. Lag that grows beyond a minute indicates your replica can't keep up and you have a capacity problem.

Create a dashboard panel to track these together:

Slow Queries via pg_stat_statements

Query performance is where theory meets reality. Enable the pg_stat_statements extension to track query execution statistics:

-- Run once on the database
CREATE EXTENSION pg_stat_statements;

-- In postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = 'all'
pg_stat_statements.max = 10000

postgres_exporter automatically scrapes pg_stat_statements when available. You'll get pg_stat_statements_calls_total, pg_stat_statements_mean_exec_time_ms, and pg_stat_statements_total_exec_time_ms for each normalized query.

The key insight: queries are normalized to their statement pattern (parameter values are stripped), so you see one row per unique query structure. A query like SELECT * FROM orders WHERE id = $1 appears once, regardless of how many times it ran with different IDs.

Build a Grafana dashboard that shows your top 5 slowest queries and top 5 most-called queries:

-- Top 5 slowest (by total time spent)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

In a trading platform we worked on, this revealed that a seemingly innocent order reconciliation query was being called 50,000 times per day with no caching, consuming 12 seconds of CPU time. Moving it to a scheduled batch job cut database load by 15%.

Set alerts on mean query execution time increasing unexpectedly. A 2x jump in mean time often signals a schema change, missing index, or data volume growth that needs attention.

Cache Hit Ratio and Table Bloat

PostgreSQL uses a shared buffer cache. Metrics exported include pg_stat_database_heap_blks_hit and pg_stat_database_heap_blks_read. Your cache hit ratio tells you what percentage of data access comes from memory versus disk:

-- In Grafana query
(pg_stat_database_heap_blks_hit) / (pg_stat_database_heap_blks_hit + pg_stat_database_heap_blks_read)

A healthy ratio is above 99% for OLTP workloads. Below 90% means you're hitting disk frequently—either your shared_buffers is too small, or your working set is larger than available memory. We typically recommend shared_buffers as 25% of system RAM for dedicated database servers.

Table bloat is insidious. PostgreSQL doesn't free space after deletes and updates—it marks rows as dead but leaves the space. If you delete 50% of a 1GB table and then run a sequential scan, you're still scanning all 1GB. Run VACUUM to reclaim space, or VACUUM FULL to rebuild tables (which locks them).

Monitor table size trends. postgres_exporter exposes pg_table_size_bytes per table. If a table that holds active data grows by 50% in a week without corresponding row count increases, you have bloat. Create a custom query that calculates dead tuple ratio:

SELECT schemaname, tablename, 
       n_dead_tup / NULLIF(n_live_tup, 0) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_ratio DESC;

Alert when dead_ratio exceeds 10% for frequently accessed tables. In an e-commerce platform, we discovered their session table had 40% bloat from high churn—switching to a 2-hour VACUUM schedule recovered 200GB of disk space.

Common Mistakes

• Scraping too frequently: postgres_exporter queries system catalogs which can be slow on large databases. Scraping every 10 seconds adds unnecessary load. Stick with 30-60 second intervals for most workloads.
• Creating a monitor user without privileges: Your monitor user needs SELECT on pg_catalog and information_schema. Many teams accidentally lock it down too much and get missing metrics.
• Ignoring cache hit ratio: A database that serves from disk is often 100x slower than one serving from cache. This metric deserves the same attention as CPU usage in application monitoring.
• Not tracking replication lag during business hours: Replication lag that's fine at night can spike during peak load. Alert based on time-of-day patterns or load characteristics, not absolute values.
• Missing the query normalization step: Trying to alert on individual slow queries without understanding that postgres_exporter groups by normalized query pattern will create noisy alerts.

Key Takeaways

• Deploy postgres_exporter with a read-only monitoring user and scrape at 30-60 second intervals to avoid introducing load.
• Track connection count, idle_in_transaction sessions, and lock contention as leading indicators of database health—these show problems before they affect query performance.
• Use pg_stat_statements to identify your slowest and most-called queries; these are high-ROI optimization targets.
• Monitor cache hit ratio above 99%; anything lower suggests memory or working set problems that need architectural review.
• Set up table bloat detection; dead tuple ratio above 10% often means your VACUUM strategy needs adjustment.
• Combine database metrics with application traces so you can see the full picture—high database latency is only useful when you know which application feature caused it.