Which Postgres Monitoring Tools Keep the Evidence You Need After an Incident?

Quick Answer

Most database monitoring tools for Postgres answer "what is slow right now" well and "what was blocking at 03:12" badly. pg_stat_statements keeps running totals, and pg_stat_activity and pg_locks show only the present, so a tool must sample and store them. pganalyze keeps blocking chains on higher plans, Datadog keeps 15 days of query samples, pgwatch and PMM keep query history, and postgres_exporter keeps counts only.

pg_stat_statements is the PostgreSQL extension that provides a means for tracking planning and execution statistics of all SQL statements executed by a server. Nearly every database monitoring tools setup for Postgres reads it, and that is where our problem with most tool comparisons starts. The incident we care about—the one that triggers a pager—is usually over before anyone opens a dashboard.

Say the page fired at 03:12, the lock cleared at 03:19, and the morning review wants to know who blocked whom. Most roundups rank features and collection speed, not what survives afterwards. When you are looking for postgres monitoring tools, the primary question is which one will actually answer questions about an incident after it has ended.

In this post, we compare six options by the evidence they keep and explain why the built-in views "forget" the most important details. Then we will give you five incident questions for a trial and the settings that keep evidence for free.

Which Postgres Monitoring Tools Keep Blocking Chains and Slow Queries After the Incident Ends?

Of the six options we checked, only pganalyze documents a view of past blocking chains, and Datadog Database Monitoring documents the longest query history. pgwatch and Percona PMM keep query history once you choose the right settings. postgres_exporter with Prometheus keeps counts rather than queries, and pg_stat_statements on its own keeps no timeline at all.

We built the table below from each project’s own documentation and source code. The columns are the four questions we ask after every database incident.

Option	Query history from pg_stat_statements	Blocking chain after the fact	Wait events after the fact	Plans of the slow query
Built-in views only	Totals since last reset, up to 5,000 statements	No, pg_locks is live only	No, pg_stat_activity is live only	Only if auto_explain writes them to the log
postgres_exporter + Prometheus	Collector off by default, top 100 statements when on	No, lock counts by mode only	Session counts by wait event, no query text	No
pgwatch (exhaustive preset)	Every 180 seconds	Stored if you add the blocking_locks metric	Not documented; active query text every 30 seconds	Not documented in the pages we read
Percona PMM + pg_stat_monitor	Per-query history in Query Analytics	Not documented in the pages we read	Not documented in the pages we read	Plan capture off by default
pganalyze	Every 1 minute	Yes, on Scale plan and higher	Sampled every 10 seconds, Scale plan and higher	Yes, through the logs pipeline
Datadog Database Monitoring	Every 10 seconds, top 200 queries per host	Not documented in the pages we read	Not documented in the pages we read	Yes, in query samples

Built-in Views Alone (pg_stat_statements, pg_stat_activity, pg_locks)

Postgres ships everything a tool needs, but it keeps no history. According to the official documentation, pg_stat_activity shows one row per server process with its current state and query. Similarly, pg_locks and pg_blocking_pids() show who blocks whom right now, and only right now. We still reach for these first during a live incident, but they are simply the wrong place to look the next morning.

postgres_exporter With Prometheus

The postgres_exporter README flags the stat_statements collector as disabled by default. When you enable it, it returns 100 statements by default, and query text stays off unless you also enable collector.stat_statements.include_query. The locks collector exposes pg_locks_count per database and lock mode, with no process IDs, so a blocking chain cannot be rebuilt from it. This is a common hurdle when using postgres monitoring tools open source for high-stakes SRE work.

Prometheus itself is a time series database plus scraper. Its configuration reference sets a 1 minute default scrape interval. While it is excellent for trends, it often lacks the granularity needed for root cause analysis of short-lived locking incidents.

pgwatch

pgwatch defines every metric as SQL in its internal metrics configuration. In the exhaustive preset, stat_activity runs every 30 seconds and stores active query text with counts, and stat_statements runs every 180 seconds. A blocking_locks metric that joins pg_locks to pg_stat_activity exists, but it is often not included in built-in presets by default.

Percona PMM With pg_stat_monitor

Percona Monitoring and Management (PMM) allows you to feed Query Analytics from pg_stat_statements or Percona's pg_stat_monitor. The pg_stat_monitor configuration defaults to 10 buckets of 60 seconds each, so the extension itself holds about ten minutes of time-bucketed history. Plan capture is off by default because Percona warns it may affect performance.

pganalyze

The pganalyze collector captures query statistics every minute and EXPLAIN plans arriving via the logs. Their documentation describes clicking a past point on a history chart to see which queries were blocking or blocked at that time. However, this wait event and connection trace data is gated behind the Scale plan and higher.

Datadog Database Monitoring

Datadog postgresql monitoring tracks per-query metrics for the top 200 normalized queries per host every 10 seconds. It retains query metrics for 15 months and query samples—which carry explain plans—for 15 days. Queries outside the top 200 in a given interval fold into "Other Queries," which can be a blind spot for rare, high-impact migrations.

Why pg_stat_statements Alone Cannot Tell You What Happened at 03:12

pg_stat_statements answers "which queries cost the most since the last reset," not "which query got slow at 03:12." Postgres keeps the evidence you need in live views and running totals, and neither one is a timeline.

1. It Stores Totals Since the Last Reset, Not a Timeline: As noted by PostgreSQL documentation, the module provides cumulative counters. A query that ran badly for seven minutes and normally for three weeks looks almost normal in a total. We only see the spike by subtracting two snapshots taken on either side of it.
2. It Drops the Least-Executed Statements Past 5,000: pg_stat_statements.max defaults to 5,000. When more distinct statements appear, the least-executed ones are discarded. The rare admin query or one-off migration that caused the incident is exactly the kind of statement that gets evicted first.
3. A Crash or Restore Resets the Counters: Cumulative statistics counters reset after an unclean shutdown, a server crash, or starting from a base backup. If the incident ended in a crash, the counters that described it are gone unless they were scraped externally.

Key Takeaway: Turn on log_lock_waits, a statement duration threshold, and auto_explain today, then replay your last real incident against each candidate tool using a five-question trial.

Five Incident Questions to Test Postgres Monitoring Tools Against Before You Buy

The fastest useful trial is to replay your last real incident and ask the tool five questions about it. If the tool answers from stored data rather than from what is running now, it passes.

• Who blocked whom at 03:14? This tests postgres blocking queries history. Since pg_locks is transient, the tool must have sampled it. (See: Postgres lock queues)
• Which query got slower? This looks for postgres monitoring queries that show latency deltas over time, not just lifetime totals.
• What were sessions waiting on? This tests postgres wait events capture. You need the specific wait event (IO, Lock, CPU) attached to the query text.
• Which plan did the slow query run? Since plans change based on statistics, an EXPLAIN run the next morning might not match the incident. (See: plan changes guide)
• What survived the failover? Evidence stored outside Postgres should survive a restart.

The Postgres Settings That Keep Evidence Even Without a Monitoring Tool

Four settings make Postgres record its own evidence, and every one of them is off or unset by default. We turn these on before evaluating any postgresql monitoring tools.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,auto_explain'
log_lock_waits = on                    # default off
log_min_duration_statement = '500ms'   # default -1 (disabled)
auto_explain.log_min_duration = '1s'   # default -1 (disabled)

• Log Lock Waits: According to the logging docs, log_lock_waits writes a message when a session waits longer than deadlock_timeout (usually 1s).
• Log Slow Statements: log_min_duration_statement creates a permanent record of what crossed your latency budget.
• Capture Plans With auto_explain: This captures the plan at the time of execution. We keep this threshold higher than the statement threshold to avoid log bloat.
• Load pg_stat_statements at Startup: This requires a server restart. Plan this before you need the data, not during the crisis.

How Self-Hosted and SaaS Database Monitoring Tools Differ on Evidence Retention

Self-hosted tools keep evidence as long as you pay for disk, while SaaS tools follow a set retention policy. Both can work; what fails is assuming a default you never read.

Where the evidence lives	Example	Documented default we found
Inside Postgres	pg_stat_statements	Until reset, crash or eviction
Your own TSDB	Prometheus	15 days, 1 minute scrape
Vendor cloud	Datadog query metrics	15 months
Vendor cloud	Datadog query samples	15 days

Retention shorter than your postmortem cycle is the usual trap. If reviews happen two weeks after an incident, a 15-day sample window leaves only a day of margin. We wrote about the same trap for logs in our post on log retention windows.

Choosing the right database monitoring tools is about more than just seeing a dashboard; it is about ensuring that when you land on the Operate platform, the evidence needed to diagnose the root cause is actually there.

Common Pitfalls

• Assuming pg_stat_statements is a timeline: It is a bucket of counters. Without external snapshots, you cannot see when a spike occurred.
• Neglecting the 5,000 statement cap: In busy systems with many dynamic queries, the "bad" query may be evicted before you check.
• Trusting live views for past events: pg_stat_activity only tells you what is happening this second.

Sources & further reading

• According to the PostgreSQL documentation, the pg_stat_statements module provides statistics for all SQL statements.
• The postgres_exporter GitHub notes that stat_statements collection is disabled by default.
• Datadog's documentation confirms their 15-day retention for query samples.

Conclusion

Pick the Postgres monitoring tool that answers your last incident, not the one with the flashiest real-time dashboard. Turn on log_lock_waits, a statement duration threshold, and auto_explain first, since they are free and fill the biggest gaps. Then test each candidate on stored blocking chains, per-query history, and captured plans.

We built Operate to read that evidence alongside your logs and code, find the root cause of a slowdown, and draft a fix for an engineer to review.

---

Frequently Asked Questions

What is the best tool for managing PostgreSQL? For management rather than monitoring, psql and pgAdmin are the standards. The pgAdmin project is an open source tool for administration and development. Its dashboards show current activity, not pg_stat_statements history.

What are the top 10 database activity monitoring tools? Database activity monitoring (DAM) is a security category. For Postgres audit trails, look at pgAudit, which writes session and object audit logs, rather than just performance metrics.

Is Zabbix really free? Yes. Zabbix states that from version 7.0, every release ships under the AGPLv3 license. The cost lies in the engineering time to maintain templates and storage.

What are some free SQL monitoring tools? pgBadger builds reports from server logs, and pg_activity is a top-style command line view of live server activity. Both are excellent for budget-constrained environments.

How can I monitor performance in SQL? On SQL Server, you would use Query Store. According to Microsoft, it captures a history of queries, plans, and runtime statistics, similar to how pg_stat_statements functions for Postgres.