Running PostgreSQL in production without proper monitoring is like driving at night with the headlights off. You might get away with it for a while, but eventually something goes wrong and you won't see it coming. Whether it's slow queries piling up, connections maxing out or disk space running low, monitoring gives you the visibility to catch problems before they become outages.
This article covers seven monitoring tools that work well with PostgreSQL. Some are purpose-built for Postgres, others are general-purpose but have strong PostgreSQL support. Each has its own strengths. The right choice depends on your team size, infrastructure complexity and how deep you need to go.
1. pg_stat_statements
This is the starting point for PostgreSQL monitoring. pg_stat_statements is a built-in extension that tracks execution statistics for all SQL statements. It records how many times each query ran, how long it took, how much I/O it consumed and more. If you're not using it yet, enable it today.
To enable pg_stat_statements, add it to your postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
After a restart, you can query it directly:
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
This gives you the top 20 queries by total execution time. That's usually where your biggest optimization opportunities are. A query that runs 50,000 times a day with a mean execution time of 200ms is probably a better target than a query that runs once and takes 10 seconds.
pg_stat_statements doesn't give you dashboards or alerts. It's raw data. But it's the foundation that many other tools build on. It's free, it ships with PostgreSQL and it has minimal overhead. There's really no reason not to use it.
2. pgAdmin
pgAdmin is the official administration and monitoring tool for PostgreSQL. It's a web-based interface that lets you manage databases, run queries and view server activity. It's been around for a long time and most DBAs are familiar with it.
For monitoring, pgAdmin provides a dashboard that shows active sessions, transactions per second, tuples in and out and server resource usage. You can see running queries, lock information and database statistics. It's not the most powerful monitoring solution, but it's solid for day-to-day operations and quick troubleshooting.
The dashboard refresh interval is configurable and you can drill into individual databases or tables for detailed statistics. pgAdmin also lets you view and cancel long-running queries directly from the UI, which is handy during incidents.
Where pgAdmin falls short is in historical data and alerting. It shows you what's happening right now, but it doesn't store metrics over time or send you notifications when something goes wrong. For production monitoring with alerts and historical trends, you'll want something more specialized. But for ad-hoc monitoring and administration, pgAdmin is hard to beat.
3. Prometheus with postgres_exporter
Prometheus is one of the most popular open-source monitoring systems, and it integrates well with PostgreSQL through the postgres_exporter. This combination gives you time-series metrics, alerting and integration with Grafana for dashboards.
postgres_exporter collects metrics from PostgreSQL's system catalog views and statistics collectors. It exposes them in Prometheus format so you can scrape, store and query them. Out of the box, you get metrics for:
- Active connections and connection states
- Transaction rates (commits, rollbacks)
- Tuple operations (inserts, updates, deletes, fetches)
- Table and index sizes
- Replication lag
- Lock counts
- Cache hit ratios
Setting up postgres_exporter is straightforward. You point it at your PostgreSQL instance and Prometheus scrapes it on a regular interval. Then you build Grafana dashboards to visualize the data.
| Feature | Details |
|---|---|
| Cost | Free, open source |
| Setup complexity | Medium (requires Prometheus infrastructure) |
| Alerting | Built-in with Alertmanager |
| Historical data | Yes, configurable retention |
| Custom metrics | Supported via custom queries |
| Scaling | Handles large fleets of PostgreSQL instances |
The main downside is setup effort. You need to run Prometheus, configure exporters and build dashboards. It's not a plug-and-play solution. But if you're already using Prometheus for other infrastructure, adding PostgreSQL monitoring is trivial.
4. Datadog
Datadog is a managed monitoring platform with deep PostgreSQL support. It collects metrics from PostgreSQL automatically using its agent, correlates database performance with application metrics and provides pre-built dashboards. If you want comprehensive monitoring without managing your own infrastructure, Datadog is a strong option.
What makes Datadog stand out for PostgreSQL monitoring is its Database Monitoring feature. It goes beyond basic metrics and shows you:
- Query-level performance with execution plans
- Wait event analysis to understand what queries are waiting on
- Active session history
- Explain plans captured automatically
- Host-level metrics correlated with database activity
Datadog normalizes and aggregates query patterns, so you see "SELECT * FROM users WHERE id = ?" as a single entry regardless of the specific parameter values. This makes it easy to spot problematic query patterns.
The trade-off is cost. Datadog charges per host per month, and Database Monitoring is an additional cost on top of the base infrastructure monitoring. For a small team with a few PostgreSQL instances, it might be overkill. For larger organizations running dozens of PostgreSQL servers, the time savings and visibility often justify the expense.
5. pgwatch
pgwatch is an open-source monitoring tool specifically designed for PostgreSQL. Unlike general-purpose tools, pgwatch understands PostgreSQL deeply and comes with pre-configured metrics and dashboards tuned for Postgres-specific concerns.
pgwatch collects metrics through SQL queries against PostgreSQL's system views. It stores the data in a time-series database and provides Grafana dashboards out of the box. The default metric set covers the essentials:
- Bloat monitoring for tables and indexes
- Replication lag and status
- Lock monitoring and deadlock detection
- Checkpoint frequency and duration
- WAL generation rate
- Autovacuum activity
- Sequence usage (approaching max values)
One of pgwatch's strengths is detecting problems that general monitoring tools miss. Things like index bloat creeping up over weeks, sequences approaching their maximum value or autovacuum falling behind on specific tables. These are PostgreSQL-specific issues that tools like Prometheus won't catch unless you write custom queries.
Setup is simple. pgwatch ships as a Docker container with everything included. Point it at your PostgreSQL instances and you get dashboards immediately. No need to configure exporters or build custom dashboards from scratch.
| Feature | Details |
|---|---|
| Cost | Free, open source |
| Setup complexity | Low (Docker-based, batteries included) |
| Alerting | Via Grafana integration |
| PostgreSQL-specific metrics | Extensive, pre-configured |
| Custom metrics | Supported via SQL |
| Best for | Teams that want PostgreSQL-focused monitoring fast |
For teams that want solid PostgreSQL monitoring without the overhead of managing a full Prometheus stack, pgwatch is an excellent choice.
6. Percona Monitoring and Management (PMM)
PMM is an open-source monitoring platform from Percona that supports PostgreSQL, MySQL and MongoDB. It combines metrics collection, query analytics and alerting in a single package. Think of it as a self-hosted alternative to Datadog's database monitoring.
The Query Analytics feature in PMM is particularly useful. It captures query execution statistics from pg_stat_statements and pg_stat_monitor, then presents them with detailed breakdowns of execution time, rows examined, I/O wait and more. You can sort queries by various dimensions, see their explain plans and track how performance changes over time.
PMM includes pre-built dashboards for:
- PostgreSQL instance overview
- Query analytics and top queries
- Replication monitoring
- Table statistics and bloat
- Checkpoint and WAL metrics
- Node-level performance (CPU, memory, disk)
What sets PMM apart is that it's self-hosted and free. You get Datadog-level query analytics without the monthly bill. The trade-off is that you need to run and maintain the PMM server yourself. But it's packaged as a Docker container and reasonably easy to operate.
PMM also has an advisor system that runs automated checks against your PostgreSQL configuration and alerts you about common misconfigurations. Things like shared_buffers being too small, autovacuum being disabled or old PostgreSQL versions with known vulnerabilities.
7. check_postgres
check_postgres is a Nagios plugin for monitoring PostgreSQL. It might seem old-school compared to modern tools, but it's still widely used and incredibly reliable. It runs health checks against PostgreSQL and reports results in a format that any monitoring system (Nagios, Icinga, Zabbix) can consume.
check_postgres performs specific checks rather than collecting continuous metrics. Each check targets a specific concern:
- Connection count and proximity to max_connections
- Database size and growth rate
- Replication lag
- Transaction ID wraparound risk
- Bloat levels
- Long-running queries
- Lock contention
- Last vacuum and analyze times
The beauty of check_postgres is its simplicity. Each check is a single command that returns a status (OK, WARNING, CRITICAL) and a message. You configure thresholds and your monitoring system handles the rest. No dashboards to build, no time-series database to maintain.
Transaction ID wraparound monitoring deserves special mention. This is one of PostgreSQL's nastier failure modes. If transaction IDs aren't cleaned up by VACUUM, PostgreSQL will eventually refuse to process new transactions to prevent data corruption. check_postgres monitors this and warns you well before it becomes a problem.
For teams that already run Nagios or similar tools, check_postgres is a natural fit. It's lightweight, well-tested and covers the critical health checks that prevent outages.
Choosing the right tool
There's no single best monitoring tool. The right choice depends on your situation. Here are some practical guidelines.
For small teams with a few PostgreSQL instances, start with pg_stat_statements and pgwatch. You get PostgreSQL-specific monitoring with minimal setup. Add check_postgres if you need basic alerting without building a full metrics pipeline.
For medium teams running multiple services, Prometheus with postgres_exporter fits well if you already have a Prometheus stack. You get consistent monitoring across all your services, and PostgreSQL becomes just another data source. PMM is a good alternative if you want better query analytics than stock Prometheus provides.
For larger organizations with dedicated database teams, Datadog provides the least operational overhead and the deepest insights. The cost is justified when you have dozens of servers and need quick incident resolution. PMM is the self-hosted alternative if budget is a concern.
One thing these tools don't cover is backup monitoring. Knowing that your database is healthy is important, but knowing that your backups are running successfully is equally critical. Databasus is an industry standard for PostgreSQL backup tools, providing automated scheduled backups with notifications across Slack, Discord and email so you'll know immediately if a backup fails.
Whatever tools you choose, start simple and expand as needed. pg_stat_statements costs nothing to enable and gives you immediate value. Build from there based on what you actually need, not what looks impressive on a vendor's demo page.
Top comments (0)