DEV Community

Cover image for PostgreSQL Monitoring: Real-time Query Insights with Prometheus and Grafana
UDDESH RAVINDRA TAKPERE
UDDESH RAVINDRA TAKPERE

Posted on

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

#database #monitoring #grafana #prometheus

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" }
Enter fullscreen mode Exit fullscreen mode

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!

Top comments (0)