PostgreSQL Monitoring: Real-time Query Insights with Prometheus and Grafana

UDDESH RAVINDRA TAKPERE — Sat, 28 Jun 2025 09:00:57 +0000

Why Monitor PostgreSQL?
PostgreSQL is widely used in production-grade systems, but performance issues often hide in unoptimized queries or excessive connections. With a sharp monitoring setup, we can observe live query activity, track expensive queries, and alert on anomalies, ensuring our database layer is not the bottleneck.

Our Tech Stack:

PostgreSQL
Prometheus
Postgres Exporter (Custom queries via queries.yaml)
Grafana

What We Did
We took the default Postgres Exporter a step further by adding custom SQL queries to extract:

Top N Expensive Queries
Query Execution Counts (per 15m window)
Average Execution Time
Active Query Tracking with Username & Database Filters
Connection State Breakdown

Grafana Panels We Built :
Panel: Top Queries by Total Execution Time
Insight: Highlights queries that consume the most time overall.

Panel: Top Queries by Call Rate
Insight: See which queries are being called most often in the last 15 minutes.

Panel: Real-Time Active Queries
Insight: Tracks ongoing query executions with user and database context.

Panel: Connection State Breakdown
Insight: Monitors how many connections are actively doing work vs. just open.

*Custom Exporter Config : *

pg_query_duration:
  query: |
    SELECT DISTINCT ON (query)
      datname,
      usename,
      query,
      calls,
      total_exec_time / 1000 AS total_time_sec,
      (total_exec_time / calls) / 1000 AS avg_time_sec
    FROM pg_stat_statements
    WHERE query NOT ILIKE '%pg_stat_statements%'
    ORDER BY query, calls DESC
    LIMIT 50;
  metrics:
    - datname: { usage: "LABEL" }
    - usename: { usage: "LABEL" }
    - query: { usage: "LABEL" }
    - calls: { usage: "COUNTER" }
    - total_time_sec: { usage: "COUNTER" }
    - avg_time_sec: { usage: "GAUGE" }

Results

Real-time visibility into slow queries
Developer ownership over query performance
Faster debugging and production insights

Monitoring is more than uptime checks. With the right visibility into your PostgreSQL workload, you enable your engineering teams to write better queries and optimize performance, without flying blind.
This setup isn’t just for DBAs. It’s for every DevOps engineer, SRE, and developer who wants performance clarity.

We're always looking to improve our monitoring setup. If you have suggestions, tweaks, or battle-tested tricks, feel free to share them—feedback from fellow DevOps engineers is always welcome!

DEV Community: UDDESH RAVINDRA TAKPERE

PostgreSQL Monitoring: Real-time Query Insights with Prometheus and Grafana