The Coroot team is happy to announce coroot-pg-agent – an open-source (Apache 2.0) Postgres exporter for Prometheus focusing on query performance statistics.

Motivation:

At Coroot, we work on a product that detects the root cause of a service outage and provides our users with a list of possible fixes.

Initially, we planned to use metrics collected by existing open-source Prometheus exporters, such as postgres_exporter (1.7k stars on Github). Unfortunately, we realized that the metrics it collects are useless for identifying the most common performance issues, like:

• A CPU-intensive query is affecting other queries
• A server has reached its max_connections limit because most of the connections are stuck in the idle in transaction state
• An I/O-intensive query is affecting other queries
• A query is blocking other queries by holding an exclusive lock

Proposed changes to postgres_exporter would be too massive, so we decided to build another exporter.

Features:

Comprehensive query metrics

The agent aggregates statistics from pg_stat_statements and pg_stat_activity to take into account not only finished queries but also those still executing.

Locks monitoring

It is not enough to gather the number of active locks from pg_locks. What DBAs really want to know is which query is blocking other queries. The collected metrics can provide the answer to that.

Query normalization and obfuscation

In addition to query normalization, which Postgres does, the agent obfuscates all queries so that no sensitive data gets into the metrics labels.

Links

• GitHub repository
• The full announcement on our blog
• Metrics description