Idle-in-Transaction Detection — pg_stat_activity Alerting

Every production application eventually hits the same wall: something goes wrong with the database at 2 AM, a customer calls to complain, and you're staring at logs trying to figure out what happened 40 minutes ago. Slow queries, connection pool exhaustion, disk space creeping toward 100% — these aren't hypothetical problems. They happen to every team, at every scale, and they're almost always preventable with the right visibility. Database monitoring isn't optional infrastructure — it's the difference between being proactive and being constantly reactive.

MySQL and PostgreSQL are the two most widely deployed open-source databases in the world, and each has its own internal metrics, query execution engine, and failure modes. A tool that works brilliantly for MySQL's InnoDB buffer pool might give you almost nothing useful for PostgreSQL's VACUUM process. Understanding not just which tools exist, but what they actually measure and why those metrics matter, is what separates engineers who can debug production incidents in minutes from those who spend hours guessing.

By the end of this article you'll know how to query the internal monitoring views of both MySQL and PostgreSQL to get real-time health data, how to set up Prometheus with the right exporters to scrape those metrics automatically, how to visualize everything in Grafana, and — most importantly — you'll understand exactly why each metric matters so you can make smart decisions when something breaks. We'll also cover the traps that catch most engineers off guard the first time they set this up.

What Your Database Is Actually Telling You — Core Metrics That Matter

Before you install a single monitoring tool, you need to speak your database's language. Both MySQL and PostgreSQL expose rich internal metrics through system views and information schema tables. The problem is there are hundreds of them, and most tutorials just dump a list without explaining which ones actually predict failure.

Think of database health in three layers: throughput (how much work is being done), latency (how long that work takes), and resource pressure (how close you are to running out of something critical). Every useful metric falls into one of these buckets.

For MySQL, the most critical real-time signals live in performance_schema and information_schema. For PostgreSQL, pg_stat_activity, pg_stat_bgwriter, and pg_locks are your best friends. The queries below show you how to pull actionable snapshots from each — not just raw numbers, but numbers with context.

One critical thing most developers miss: a single metric in isolation is almost meaningless. A query taking 500ms is fine for a reporting job, catastrophic for a login endpoint. Always correlate metrics with the workload context before raising the alarm.

PostgreSQL Deep-Dive — Querying pg_stat Views for Real Operational Insight

PostgreSQL's monitoring story is fundamentally different from MySQL's. Where MySQL centralizes most metrics in performance_schema, PostgreSQL scatters them across a family of pg_stat_* views, each focused on a specific subsystem. This design is actually more powerful once you know where to look, because each view gives you surgical precision instead of one giant table.

The three views you absolutely must understand are pg_stat_activity (who's doing what right now), pg_stat_bgwriter (how hard your background writer is working), and pg_locks (who's waiting on whom). Together they cover the three most common categories of PostgreSQL incidents: long-running queries, I/O bottlenecks, and lock contention.

There's also a uniquely PostgreSQL problem that has no MySQL equivalent: table bloat from VACUUM. PostgreSQL uses MVCC (Multi-Version Concurrency Control), which means old row versions pile up in the table file until VACUUM cleans them. If VACUUM falls behind — because it's disabled, misconfigured, or being blocked by a long transaction — your tables can grow to 10x their logical size and queries slow to a crawl. pg_stat_user_tables exposes exactly this data.

The queries below are battle-tested snippets we'd use in a real production incident response runbook.

Prometheus + Exporters + Grafana — Building a Production Monitoring Stack

Running those SQL queries by hand is great for incident triage, but you can't watch a terminal 24/7. What you want is a system that scrapes those metrics automatically every 15 seconds, stores them historically so you can see trends, and alerts you before the disk fills up or the query latency spikes. That's exactly what the Prometheus + Grafana stack does.

The architecture is simple: Prometheus is a time-series database that periodically scrapes HTTP endpoints called 'exporters'. For MySQL there's mysqld_exporter, for PostgreSQL there's postgres_exporter. Each exporter connects to your database, runs the equivalent of the queries above, and exposes the results as a /metrics HTTP endpoint that Prometheus can consume. Grafana then connects to Prometheus and lets you build dashboards with those metrics.

What makes this stack genuinely powerful isn't the dashboards — it's Prometheus's alerting rules. You write a rule like 'if the PostgreSQL dead_row_pct for any table stays above 30% for more than 10 minutes, fire an alert'. Grafana Alerting or Prometheus Alertmanager then notifies your Slack channel or PagerDuty. You stop checking dashboards and start only seeing problems when they actually need attention.

The configuration below gets you from zero to a working stack with both exporters running.

Prometheus Alert Rules — Catching Failures Before Your Users Do

Dashboards are reactive. Alert rules are proactive. The real value of this monitoring stack isn't the pretty graphs — it's the 3 AM Slack message that says 'PostgreSQL table user_sessions is 46% dead rows — VACUUM may be blocked' before a developer even notices slowness.

Prometheus alert rules use PromQL, Prometheus's query language. They follow a simple pattern: define an expression, a threshold, a duration (how long the condition must persist before alerting), and a severity. The duration is critical — without it, you'll get flooded with noisy one-off alerts for transient spikes that self-resolve.

The rules below cover the five scenarios that account for the vast majority of MySQL and PostgreSQL production incidents: connection exhaustion, query latency spikes, replication lag, disk space pressure, and PostgreSQL VACUUM falling behind. Treat these as your starting template — tune the thresholds to match your specific workload after you've observed normal baseline values for a week.

Debugging Your Monitoring Stack — Common Failures and Fixes

Your database monitoring stack itself can break. Exporters stop responding, Prometheus loses connection, Grafana panels show 'No data'. When that happens you're flying blind — exactly when you need monitoring the most.

Most monitoring stack failures fall into three categories: configuration drift (scrape targets change but prometheus.yml doesn't), credential rotation (database passwords change but exporter DATA_SOURCE_NAME doesn't), and version mismatches (exporter updated and metrics renamed).

The quickest way to verify the whole chain: start at the exporter, then Prometheus, then Grafana. Don't jump to conclusions. A common mistake is debugging Grafana panels when the exporter itself is down.

Below are the debugging commands and patterns we use in production to restore monitoring fast.

Frequently Asked Questions