PostgreSQL Performance Monitoring Tools

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

PostgreSQL doesn’t ship with a built-in monitoring dashboard. Out of the box, you get the pg_stat_* system views, which provide raw metrics but no visualization, alerting, or historical analysis. For production databases, you need additional tooling to answer questions like “which queries are slow?”, “is the connection pool exhausted?”, and “when did performance degrade?”

This post covers the most useful PostgreSQL monitoring tools, from built-in extensions to full observability platforms.

In this post, we’ll cover:

• pg_stat_statements - Built-in query performance tracking
• pg_stat_monitor - Enhanced query statistics by Percona
• pgBadger - Log-based performance analysis
• pgHero - Simple web dashboard
• pgwatch2 - Self-hosted metrics and dashboards
• Prometheus + postgres_exporter - Metrics pipeline for Grafana
• Datadog - Commercial APM with database monitoring

pg_stat_statements

pg_stat_statements is the foundation of PostgreSQL query monitoring. It’s an official extension that ships with PostgreSQL and tracks execution statistics for every SQL statement: call count, total and mean execution time, rows returned, and block I/O.

To enable it, add the extension to shared_preload_libraries in postgresql.conf and restart:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- Then create the extension
CREATE EXTENSION pg_stat_statements;

Once enabled, you can find your slowest queries with a simple query:

SELECT
  query,
  calls,
  mean_exec_time,
  total_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

The extension normalizes queries by replacing literal values with parameters, so SELECT * FROM users WHERE id = 1 and SELECT * FROM users WHERE id = 2 are tracked as a single entry. This gives you aggregate statistics across all executions of a query pattern.

The main limitation is that pg_stat_statements only shows cumulative statistics since the last reset. There’s no time-series data, so you can’t answer “when did this query get slow?” without layering another tool on top. That’s where the rest of this list comes in.

pg_stat_monitor

pg_stat_monitor by Percona is a drop-in replacement for pg_stat_statements that adds several capabilities the built-in extension lacks.

The key improvement is time-bucketed data collection. Instead of accumulating statistics indefinitely, pg_stat_monitor groups metrics into configurable time buckets (default: 60 seconds). This means you can see how query performance changes over time without needing an external time-series database.

Other additions include: actual query plan capture (not just statistics), client connection metadata (application name, client IP), top-level vs. nested query distinction, and histograms of query execution times. It also captures query examples with actual parameter values, which pg_stat_statements strips out during normalization.

-- Install pg_stat_monitor
CREATE EXTENSION pg_stat_monitor;

-- View query stats with timing buckets
SELECT
  bucket_start_time,
  query,
  calls,
  mean_exec_time,
  resp_calls  -- histogram data
FROM pg_stat_monitor
ORDER BY mean_exec_time DESC;

pg_stat_monitor works with PostgreSQL 13 and later. If you’re already using pg_stat_statements and want more granularity without adding external infrastructure, this is a natural upgrade.

pgBadger

pgBadger takes a different approach entirely. Instead of querying system views, it analyzes PostgreSQL log files and generates detailed HTML reports. It was created by Gilles Darold and is written in pure Perl with no dependencies.

To use pgBadger, you first configure PostgreSQL to log the information you need:

# postgresql.conf
log_min_duration_statement = 0   # log all queries (or set a threshold)
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

Then run pgBadger against your log files:

pgbadger /var/log/postgresql/postgresql.log -o report.html

The resulting report includes: slowest queries, most frequent queries, queries by type (SELECT, INSERT, UPDATE, DELETE), connections per second, checkpoint frequency, temporary file usage, lock wait analysis, and more. The reports are interactive HTML with charts and tables.

pgBadger handles compressed log files (gzip, bzip2, lz4, xz, zstd) and auto-detects the log format (syslog, stderr, csvlog, jsonlog). For ongoing monitoring, you can run it incrementally against new log data and generate daily or weekly reports.

The tradeoff is that pgBadger is retrospective. It analyzes what already happened in your logs — it’s not a real-time monitoring tool. It’s best suited for periodic performance reviews, post-incident analysis, and identifying patterns over time.

pgHero

pgHero by Andrew Kane is a lightweight web dashboard for PostgreSQL performance. It surfaces the most common things you need to check: slow queries, missing indexes, unused indexes, connection usage, replication lag, and transaction ID wraparound status.

pgHero is available as a Docker image, a standalone Linux package, or a Rails engine you can mount in an existing Ruby application:

# Run with Docker
docker run -ti -e DATABASE_URL=postgres://user:pass@host:5432/dbname -p 8080:8080 ankane/pghero

The dashboard shows query statistics from pg_stat_statements (it requires the extension to be enabled) and provides actionable recommendations. It highlights queries that would benefit from an index and identifies indexes that exist but are never used.

pgHero also supports historical query stats. When configured with a storage backend, it captures periodic snapshots of pg_stat_statements data so you can track query performance trends over time.

The appeal of pgHero is simplicity. It doesn’t try to be a full observability platform. It answers the most common PostgreSQL performance questions in a single-page dashboard that takes minutes to set up.

pgwatch2

pgwatch2 by Cybertec is a self-hosted monitoring solution that collects PostgreSQL metrics and displays them through Grafana dashboards. It’s designed to be non-invasive — it doesn’t require superuser access or custom extensions.

The architecture consists of a metrics collector, a metrics storage backend (PostgreSQL, InfluxDB, Graphite, or Prometheus), and pre-built Grafana dashboards. It ships as a Docker image with everything included:

docker run -d --name pgwatch2 \
  -e PW2_PG_HOST=your-postgres-host \
  -e PW2_PG_DBNAME=your-database \
  -p 3000:3000 -p 8080:8080 \
  cybertec/pgwatch2-postgres

pgwatch2 collects a broad set of metrics: query statistics, table and index statistics, replication status, WAL generation, bloat estimates, lock information, and system-level metrics (CPU, memory, disk I/O when running on the database host). A single pgwatch2 collector can monitor thousands of databases with minimal overhead.

The Grafana dashboards are well-designed and cover most monitoring scenarios out of the box. You can customize them or build your own dashboards on top of the collected metrics.

The project has been archived on GitHub as pgwatch3 development continues, but pgwatch2 remains functional and widely deployed.

Prometheus + postgres_exporter

If your infrastructure already uses Prometheus and Grafana, the postgres_exporter from the Prometheus community fits naturally into your existing stack.

postgres_exporter exposes PostgreSQL metrics as Prometheus time series. It scrapes the pg_stat_* views and presents them in Prometheus format on an HTTP endpoint. Prometheus scrapes the exporter on its configured interval, stores the time-series data, and you visualize it in Grafana.

# Run the exporter
DATA_SOURCE_NAME="postgresql://user:pass@host:5432/dbname?sslmode=disable" \
  ./postgres_exporter

The exporter provides metrics for connections, transactions, tuples, buffer cache hit ratios, replication lag, table sizes, and more. You can extend it with custom queries that expose application-specific metrics.

The main advantage is integration with your existing observability stack. If your team already monitors services through Prometheus and Grafana, adding PostgreSQL monitoring is just another exporter. You get alerting through Alertmanager, dashboards in Grafana, and long-term storage through whatever Prometheus backend you’re using.

The downside is setup complexity. You need Prometheus, Grafana, and the exporter configured and running. If you don’t already have a Prometheus stack, starting one just for PostgreSQL monitoring is overkill — pgHero or pgwatch2 would be simpler.

Datadog

Datadog Database Monitoring is a commercial APM platform that provides deep PostgreSQL monitoring as part of its broader observability suite.

The Database Monitoring (DBM) feature goes beyond basic metrics. It captures query samples with explain plans, tracks query performance over time, identifies slow queries and their sources, and correlates database metrics with application traces. The query-level analysis lets you see which application endpoints generate the most expensive database queries.

Datadog requires installing an agent on your database host (or a sidecar in Kubernetes). Once connected, you get out-of-the-box dashboards for PostgreSQL metrics plus the deeper DBM capabilities.

The strength is correlation. If your application and infrastructure are already monitored with Datadog, adding database monitoring connects the dots — you can trace a slow API response from the HTTP endpoint through the application code to the specific PostgreSQL query causing the bottleneck.

The tradeoff is cost. Datadog DBM is a paid feature on top of Datadog’s infrastructure monitoring. For teams already paying for Datadog, the marginal cost may be justified. For teams without existing Datadog investment, the cost of the full platform is substantial for database monitoring alone.

Comparison

Tool	Type	Cost	Best for
pg_stat_statements	Extension	Free (built-in)	Foundation for all query monitoring
pg_stat_monitor	Extension	Free	Time-bucketed stats, query plans
pgBadger	Log analyzer	Free	Periodic reports, post-incident analysis
pgHero	Web dashboard	Free	Quick setup, actionable recommendations
pgwatch2	Metrics + Grafana	Free	Self-hosted full monitoring
Prometheus + exporter	Metrics pipeline	Free	Existing Prometheus/Grafana stacks
Datadog	Commercial APM	Paid	Full-stack observability, query traces

Wrapping up

Start with pg_stat_statements — it’s the baseline that most other tools build on. From there, choose based on your existing infrastructure and how much operational overhead you’re willing to take on. pgHero is the fastest path to a useful dashboard. pgwatch2 and Prometheus give you comprehensive self-hosted monitoring. Datadog provides the deepest insights if you’re already invested in their platform.

For most teams, a combination works best: pg_stat_statements for the raw data, one visualization tool for dashboards and alerting, and pgBadger for periodic deep dives into log-level detail.