PostgreSQL Monitoring: pg_stat_statements and pgBadger Setup

PostgreSQL Monitoring Setup with pg_stat_statements and pgBadger

Imagine this: you deploy a new feature, and within an hour the database starts crawling. SELECT statements hang for 30 seconds, and the logs only show 'checkpoint time: 200ms'. What happened? Without monitoring, it's a guessing game. Let's walk through how to set up PostgreSQL observability to spot issues before they become incidents.

Our experience shows: PostgreSQL stays silent until the last moment. Queries run slowly for weeks. The database looks alive, but a SELECT on 10 million rows without an index consumes all memory. Autovacuum can't keep up with bloat, which can reach 40% of a table's volume. Without proper monitoring, these problems surface only during an incident. We have configured monitoring for dozens of projects—from startups to enterprise clusters with terabytes of data. Over 5 years, we have completed more than 30 projects, including clusters with replication and sharding. On average, we reduced query times by 50% per project. Our basic setup starts at $500, and complex clusters up to $2,000. Typical downtime costs $5,000–$20,000 per incident, so monitoring pays for itself quickly.

Why pg_stat_statements Alone Isn't Enough for Performance Analysis

pg_stat_statements is the first thing people enable after installing PostgreSQL. This extension aggregates query execution statistics: total time, call count, rows, shared_blks_hit/read. It is enabled with one line in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements' pg_stat_statements.track = all pg_stat_statements.max = 10000 

After CREATE EXTENSION pg_stat_statements in the target database, the pg_stat_statements view becomes the primary tool for finding heavy queries. To find the top 10 by total time:

SELECT round(total_exec_time::numeric, 2) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The problem is that pg_stat_statements does not analyze logs—it only holds live statistics in memory. Historical data is lost on restart. For analyzing past incidents, you need pgBadger. Additionally, pg_stat_statements does not show table bloat, lock waits by session, or checkpoint activity. These metrics are critical for performance—for example, bloat can reach 40% of a table's volume, slowing sequential scans by up to 70%. In one case, bloat was 60% of a table. The pg_stat_statements documentation confirms these limitations.

How pgBadger Recovers Incident History

pgBadger parses the postgresql.log and generates an HTML report with top slow queries, lock waits, checkpoint warnings, and temporary files. First, configure the correct log format in postgresql.conf:

log_min_duration_statement = 1000 # log queries longer than 1 second
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
lc_messages = 'C' # pgBadger requires English format

Generate the report:

pgbadger /var/log/postgresql/postgresql-*.log \
    -o /var/www/reports/pgbadger_$(date +%Y%m%d).html \
    --format stderr \
    --jobs 4

The report shows not just slow queries, but patterns: peak load times, which applications generate lock waits, how many temporary files are created (a sign of insufficient work_mem). For example, a client saw checkpoint warnings every 2 minutes; after increasing max_wal_size from 1GB to 4GB, checkpoints occurred every 30 minutes, improving write throughput by 20%. pg_stat_statements stores data only in memory, while pgBadger stores reports in files, allowing you to keep months of history—100x more data. The pgBadger GitHub repository contains detailed documentation.

How postgres_exporter and Grafana Enable Real-Time Monitoring

For online metrics, we use postgres_exporter from Prometheus Community. It collects dozens of metrics: database sizes, active connections, replication state, bloat, time since last vacuum/analyze.

Configuration via environment variable:

DATA_SOURCE_NAME="postgresql://monitoring_user:password@localhost:5432/postgres?sslmode=disable" 

Custom queries in queries.yaml allow adding metrics not in the standard set—for example, per-table bloat from the pgstattuple extension or replication lag per slot.

In Grafana, dashboard ID 9628 (PostgreSQL Database) covers most basic use cases. Alerts for critical events:

Wraparound is a special concern. If age(datfrozenxid) approaches autovacuum_freeze_max_age (default 200 million transactions), PostgreSQL first slows down and then stops completely, requiring manual VACUUM FREEZE. An alert for this is mandatory. We recommend setting a warning 2 days before the limit—enough time for unscheduled maintenance.

Tool Comparison for PostgreSQL Monitoring

pgBadger combined with postgres_exporter gives a complete picture: the former for deep analysis of past events, the latter for current status. This is better than using only one tool.

Deliverables and What’s Included

As part of our monitoring setup service, we provide:

1. Audit of current configuration: Check logging parameters, extensions, autovacuum policies.
2. Enable and configure pg_stat_statements (without restart if possible).
3. Configure pgBadger: Set log format, cron job for daily report generation.
4. Deploy postgres_exporter as a systemd service, configure Prometheus target.
5. Import and customize Grafana dashboard for your architecture (replicas, shards).
6. Set up alerts: Wraparound, bloat, lag, active connections.
7. Documentation: Metric descriptions, runbook for common incidents.
8. Access: Provide dashboards and alerting systems access.
9. Team training: Interpreting pgBadger reports, working with alerts.
10. Support: Post-deployment support for 30 days.

How to Interpret a pgBadger Report

• Top slowest queries: Look for queries with high average time and many calls.
• Lock waits: Which application causes blocking.
• Temporary files: Many indicate insufficient work_mem—need to increase.
• Checkpoint warnings: Too frequent checkpoints—max_wal_size may be too short.
• Time pattern: Peak load at the same hours—consider replicating reads.

Timelines and Cost

Setup time ranges from half a day for a basic configuration to 2–3 days if custom metrics, integration with an existing monitoring stack, and historical analysis of accumulated logs are needed. The cost starts at $500 for a basic setup and can go up to $2,000 for complex clusters. By preventing a single outage, you save thousands of dollars—typical downtime costs $5,000–$20,000 per incident.

Setting up PostgreSQL monitoring is not a one-time action but an investment in stability. We guarantee the quality of the setup. Order a PostgreSQL monitoring audit and get a complete picture of your database health. Contact us for a consultation. Get a consultation right now.