Database Observability in Production: Slow Query Detection, Connection Pool Monitoring, and Performance Regression Alerts

Application observability is well-understood. You add middleware, wrap your handlers, ship traces and spans to a collector, and you are done. Database observability does not work this way. The database runs outside your application process. You cannot instrument it by adding code to your service. You query it, it executes, and from your application’s perspective that execution is a black box.

The result is a common failure pattern: engineers have detailed traces showing that a request spent 800ms “in the database,” but no visibility into which query, on which table, with which plan, holding which locks, competing with how many other connections. When performance degrades, the trace tells you where time was lost. It tells you nothing about why.

This article covers the four pillars of practical database observability, the collection mechanics for each, and how to build alerting that catches regressions before users report them.

Why Database Observability Is Different

Application observability instruments code you control. Database observability instruments a system you interact with through a protocol. That distinction has three practical consequences.

First, you cannot push metrics. You must pull them. PostgreSQL exposes performance data through system views (pg_stat_statements, pg_stat_activity, pg_stat_replication, pg_stat_user_tables). You query those views on an interval and ship the results to your metrics store. The database does not push events when a query is slow.

Second, the signal is aggregate, not per-request. pg_stat_statements tracks statistics rolled up across all executions of a query fingerprint. You know that a particular query pattern has run 4,200 times, averages 42ms, and accounts for 6% of total database time. You do not get individual trace spans for each execution unless you instrument the application side as well.

Third, configuration matters enormously. Many of the most useful observability views are disabled or limited by default. pg_stat_statements requires the extension loaded and configured. log_min_duration_statement must be set to capture slow query logs. Connection pool metrics require a separate proxy layer. You are assembling a monitoring system from parts, not enabling a toggle.

The Four Pillars

Before diving into mechanics, naming the four areas gives you a checklist to validate coverage against.

Query performance: Which queries are slow, how often, and are they getting slower? This is the most obvious pillar but also the one most likely to have blind spots without pg_stat_statements.

Connection health: Are connections being acquired and released cleanly? Is the pool exhausted? How many connections are idle, active, or waiting? Connection exhaustion is one of the fastest paths to a complete service outage.

Replication lag: If you have read replicas, how far behind are they? A replica 30 seconds behind serves stale reads. A replica 10 minutes behind during a failover scenario is a data loss risk.

Storage and IO: Is table bloat accumulating? Are indexes being used? Is autovacuum keeping up? Storage problems are slow to develop and fast to become catastrophic when they arrive.

Slow Query Detection with pg_stat_statements

Enable the extension and configure it before anything else:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.save = on

-- After restarting, create the extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

The view pg_stat_statements then gives you normalized query fingerprints with execution statistics:

SELECT
  query,
  calls,
  total_exec_time / 1000 AS total_exec_seconds,
  mean_exec_time AS mean_exec_ms,
  stddev_exec_time AS stddev_exec_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  blk_read_time,
  blk_write_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 25;

The mean_exec_time column tells you average latency per call. stddev_exec_time tells you how variable it is. A query with mean 10ms and stddev 200ms has a p99 that will surprise you. shared_blks_read divided by shared_blks_hit + shared_blks_read gives you a cache miss ratio for that specific query pattern.

To build automated detection, poll this view on an interval and emit metrics:

import { Pool } from "pg";

interface QueryStats {
  queryId: string;
  query: string;
  calls: number;
  meanExecMs: number;
  stddevExecMs: number;
  totalExecSeconds: number;
  cacheHitRatio: number;
}

async function collectQueryStats(pool: Pool): Promise<QueryStats[]> {
  const result = await pool.query<{
    queryid: string;
    query: string;
    calls: string;
    mean_exec_time: string;
    stddev_exec_time: string;
    total_exec_time: string;
    shared_blks_hit: string;
    shared_blks_read: string;
  }>(`
    SELECT
      queryid::text,
      query,
      calls::text,
      mean_exec_time::text,
      stddev_exec_time::text,
      total_exec_time::text,
      shared_blks_hit::text,
      shared_blks_read::text
    FROM pg_stat_statements
    WHERE calls > 10
    ORDER BY total_exec_time DESC
    LIMIT 100
  `);

  return result.rows.map((row) => {
    const hit = parseInt(row.shared_blks_hit, 10);
    const read = parseInt(row.shared_blks_read, 10);
    const total = hit + read;
    return {
      queryId: row.queryid,
      query: row.query.substring(0, 200),
      calls: parseInt(row.calls, 10),
      meanExecMs: parseFloat(row.mean_exec_time),
      stddevExecMs: parseFloat(row.stddev_exec_time),
      totalExecSeconds: parseFloat(row.total_exec_time) / 1000,
      cacheHitRatio: total > 0 ? hit / total : 1,
    };
  });
}

Then alert on thresholds. The thresholds are context-dependent, but these starting points work for most OLTP workloads:

interface SlowQueryAlert {
  queryId: string;
  query: string;
  meanExecMs: number;
  reason: string;
}

function detectSlowQueries(stats: QueryStats[]): SlowQueryAlert[] {
  const alerts: SlowQueryAlert[] = [];

  for (const stat of stats) {
    if (stat.meanExecMs > 500) {
      alerts.push({
        queryId: stat.queryId,
        query: stat.query,
        meanExecMs: stat.meanExecMs,
        reason: `Mean execution time ${stat.meanExecMs.toFixed(1)}ms exceeds 500ms threshold`,
      });
    }

    if (stat.cacheHitRatio < 0.95 && stat.calls > 100) {
      alerts.push({
        queryId: stat.queryId,
        query: stat.query,
        meanExecMs: stat.meanExecMs,
        reason: `Cache hit ratio ${(stat.cacheHitRatio * 100).toFixed(1)}% below 95% with ${stat.calls} calls`,
      });
    }
  }

  return alerts;
}

The WHERE calls > 10 filter matters. A query that ran once and took 2 seconds is noise. A query that runs 5,000 times and averages 80ms is load. Sort by total_exec_time to find what is actually consuming database capacity.

One important note: pg_stat_statements accumulates stats until you call pg_stat_statements_reset(). For regression detection, you need point-in-time snapshots. Store the stats periodically and compute deltas between windows rather than looking at absolute totals.

Connection Pool Monitoring

Application-Level Connection Metrics

If you are using a Node.js connection pool directly, most pool libraries expose internal state:

import { Pool } from "pg";

interface PoolMetrics {
  totalConnections: number;
  idleConnections: number;
  waitingRequests: number;
  utilization: number;
}

function collectPoolMetrics(pool: Pool): PoolMetrics {
  const total = pool.totalCount;
  const idle = pool.idleCount;
  const waiting = pool.waitingCount;

  return {
    totalConnections: total,
    idleConnections: idle,
    waitingRequests: waiting,
    utilization: total > 0 ? (total - idle) / total : 0,
  };
}

// Emit on an interval, not per-request
setInterval(() => {
  const metrics = collectPoolMetrics(pool);

  if (metrics.waitingRequests > 0) {
    console.warn(
      `Pool pressure: ${metrics.waitingRequests} requests waiting for a connection`
    );
  }

  if (metrics.utilization > 0.8) {
    console.warn(
      `Pool utilization ${(metrics.utilization * 100).toFixed(1)}% (approaching exhaustion)`
    );
  }
}, 5000);

waitingRequests > 0 is the most important signal. It means requests are queuing for a connection. Sustained queue means your pool is undersized, your queries are too slow, or both.

PgBouncer Metrics

If you are running PgBouncer as a connection proxy (which you should be for any application running more than a handful of application servers), it exposes metrics through a special pgbouncer virtual database:

-- Connect to pgbouncer database on port 6432
SHOW POOLS;
SHOW STATS;
SHOW CLIENTS;
SHOW SERVERS;

The SHOW POOLS output gives you the columns that matter most:

Column	Meaning
cl_active	Clients currently connected and executing
cl_waiting	Clients waiting for a server connection
sv_active	Server connections currently in use
sv_idle	Server connections idle and available
sv_used	Server connections returned to pool but not yet tested
maxwait	Wait time in seconds of the longest-waiting client

cl_waiting > 0 with maxwait > 1 is an alert condition. maxwait > 5 is a page.

interface PgBouncerPoolStats {
  database: string;
  clActive: number;
  clWaiting: number;
  svActive: number;
  svIdle: number;
  maxWaitSeconds: number;
}

async function collectPgBouncerStats(
  bouncerPool: Pool
): Promise<PgBouncerPoolStats[]> {
  const result = await bouncerPool.query<{
    database: string;
    cl_active: string;
    cl_waiting: string;
    sv_active: string;
    sv_idle: string;
    maxwait: string;
  }>("SHOW POOLS");

  return result.rows.map((row) => ({
    database: row.database,
    clActive: parseInt(row.cl_active, 10),
    clWaiting: parseInt(row.cl_waiting, 10),
    svActive: parseInt(row.sv_active, 10),
    svIdle: parseInt(row.sv_idle, 10),
    maxWaitSeconds: parseInt(row.maxwait, 10),
  }));
}

SHOW STATS gives you rolling throughput numbers: total queries, total received bytes, total sent bytes, and average query duration. Track avg_query (average query duration) over time from the PgBouncer stats rather than the PostgreSQL view when you want to understand the full round-trip cost including connection acquisition.

Schema-Level Metrics

Table Bloat

PostgreSQL’s MVCC implementation means dead tuples accumulate in tables until autovacuum clears them. Under high write or update load, autovacuum may not keep up, and bloat accumulates:

SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  n_dead_tup,
  n_live_tup,
  CASE WHEN n_live_tup > 0
    THEN round(100.0 * n_dead_tup / n_live_tup, 2)
    ELSE 0
  END AS dead_tuple_ratio,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

A dead_tuple_ratio above 20% on a heavily-read table is a performance issue. Bloated tables require more IO per scan. Alert at 10% with a warning, 25% as a page condition.

Index Usage Ratios

Indexes that are never used consume write overhead and storage without benefit:

SELECT
  schemaname,
  tablename,
  indexname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
  idx_scan AS index_scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY pg_relation_size(indexrelid) DESC;

Zero-scan indexes on tables with real traffic are candidates for removal. But be careful: some indexes exist for constraint enforcement (unique constraints) and will show zero scans even while doing useful work. Correlate with pg_indexes to check the index type.

The inverse is also worth monitoring: sequential scans on large tables where an index scan would be cheaper:

SELECT
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  CASE WHEN seq_scan + idx_scan > 0
    THEN round(100.0 * idx_scan / (seq_scan + idx_scan), 2)
    ELSE 0
  END AS index_scan_pct,
  n_live_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
  AND seq_scan > idx_scan
ORDER BY seq_scan DESC;

Tables with more sequential scans than index scans and substantial row counts are likely missing indexes on common query predicates.

Performance Regression Alerts via Query Plan Changes

Slow query detection catches queries that are already slow. Plan change detection catches queries that are about to get slow.

PostgreSQL’s query planner switches execution plans when statistics change, after ANALYZE runs, after index creation, or when row estimates drift. A plan that was optimal at 10,000 rows may switch to a sequential scan at 500,000 rows. The switch can happen without any code changes on your part.

pg_stat_statements does not expose plan information. To capture plans, you need auto_explain:

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements, auto_explain'
auto_explain.log_min_duration = 500
auto_explain.log_analyze = true
auto_explain.log_buffers = true
auto_explain.log_format = json

This logs the full execution plan for any query exceeding 500ms. Parse these logs and extract the plan node types. If a query switches from Index Scan to Seq Scan, that is a regression worth alerting on regardless of whether the current mean latency has crossed a threshold yet.

For a more structured approach, store query plans captured at deploy time and compare on a schedule:

interface QueryPlan {
  queryId: string;
  capturedAt: Date;
  planNodes: string[];
  estimatedCost: number;
}

function extractPlanNodes(explainOutput: Record<string, unknown>[]): string[] {
  const nodes: string[] = [];

  function walk(node: Record<string, unknown>): void {
    if (typeof node["Node Type"] === "string") {
      nodes.push(node["Node Type"]);
    }
    const plans = node["Plans"];
    if (Array.isArray(plans)) {
      for (const child of plans) {
        walk(child as Record<string, unknown>);
      }
    }
  }

  if (explainOutput.length > 0) {
    const plan = explainOutput[0]["Plan"];
    if (plan && typeof plan === "object") {
      walk(plan as Record<string, unknown>);
    }
  }

  return nodes;
}

async function captureQueryPlan(
  pool: Pool,
  query: string,
  params: unknown[]
): Promise<QueryPlan> {
  const explainQuery = `EXPLAIN (FORMAT JSON, ANALYZE false) ${query}`;
  const result = await pool.query(explainQuery, params);
  const output = result.rows[0]["QUERY PLAN"] as Record<string, unknown>[];

  return {
    queryId: hashQuery(query),
    capturedAt: new Date(),
    planNodes: extractPlanNodes(output),
    estimatedCost: extractEstimatedCost(output),
  };
}

function detectPlanRegression(
  baseline: QueryPlan,
  current: QueryPlan
): string | null {
  const baselineHasSeqScan = baseline.planNodes.includes("Seq Scan");
  const currentHasSeqScan = current.planNodes.includes("Seq Scan");

  if (!baselineHasSeqScan && currentHasSeqScan) {
    return `Query ${current.queryId} switched from index scan to sequential scan`;
  }

  if (current.estimatedCost > baseline.estimatedCost * 2) {
    return `Query ${current.queryId} estimated cost increased ${(current.estimatedCost / baseline.estimatedCost).toFixed(1)}x`;
  }

  return null;
}

Run this comparison after deployments and after scheduled ANALYZE runs. A plan switch on a high-frequency query is worth waking someone up for even before latency degrades.

Putting It Together: Monitoring Approach Comparison

Approach	What it catches	Blind spots	Operational cost
Slow query log only	Individual slow executions	Aggregate load, plan changes	Low setup, noisy at scale
pg_stat_statements polling	Aggregate query latency trends	Per-execution variance, plan info	Medium setup, scalable
PgBouncer SHOW POOLS	Pool exhaustion, wait times	Application-level pool state	Requires PgBouncer
auto_explain + log parsing	Plan changes on slow queries	Fast queries with bad plans	High setup, log volume
pg_stat_user_tables polling	Bloat, vacuum health, scan ratios	Does not surface query text	Low setup, high value
Stored plan baselines	Plan regressions before latency degrades	Requires known query corpus	High setup, targeted value

No single approach gives you full coverage. A production database observability system combines all of them. pg_stat_statements is the foundation. PgBouncer stats for connection health. pg_stat_user_tables for schema health. Plan capture for regression detection on your critical query paths.

Dashboard Design for Database Health

Dashboards fail when they show too many metrics without hierarchy. For database health, two levels work: a summary view and a query drill-down.

The summary view shows four numbers at a glance: mean query latency (from pg_stat_statements aggregated across all queries weighted by total time), active connection count vs. pool maximum, replication lag in seconds, and autovacuum debt (sum of dead tuples across all tables).

Color thresholds for the summary view:

interface HealthThresholds {
  meanLatencyMs: { warn: number; critical: number };
  connectionUtilizationPct: { warn: number; critical: number };
  replicationLagSeconds: { warn: number; critical: number };
  maxDeadTupleRatio: { warn: number; critical: number };
}

const defaultThresholds: HealthThresholds = {
  meanLatencyMs: { warn: 100, critical: 500 },
  connectionUtilizationPct: { warn: 70, critical: 90 },
  replicationLagSeconds: { warn: 10, critical: 60 },
  maxDeadTupleRatio: { warn: 10, critical: 25 },
};

The query drill-down view shows the top 25 queries by total execution time with three columns: query text (truncated), mean latency, and call rate per minute. Clicking a row expands to show the full query, its historical mean latency trend, and its current plan if you have plan capture in place.

One useful addition: track mean latency over a rolling 7-day window and display the current value as a percentage change from the 7-day average. A query running at 120ms when the 7-day average was 40ms is more interesting than a query consistently at 120ms.

Production Considerations

Polling interval: Five seconds is a reasonable floor for connection pool metrics. One minute is fine for pg_stat_statements and schema stats. More frequent polling on pg_stat_statements increases monitoring load without meaningfully improving signal resolution since the stats are cumulative.

Permissions: Create a dedicated monitoring user with read access to the system views. Do not use your application database user for monitoring queries.

CREATE USER monitoring_user WITH PASSWORD '...';
GRANT pg_read_all_stats TO monitoring_user;
GRANT CONNECT ON DATABASE your_database TO monitoring_user;

Stats reset risk: pg_stat_statements_reset() clears all accumulated data. If you run this routinely (some teams do to keep the view clean), your trend data disappears. Prefer storing periodic snapshots to an external store rather than relying on the live view for historical comparison.

Vacuum tuning: If bloat is accumulating despite autovacuum running, autovacuum is either too slow or being blocked. Check pg_stat_activity for long-running transactions, which block vacuum from reclaiming dead tuples. A transaction open for 30 minutes will prevent vacuum from running effectively across the entire database.

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - pg_stat_activity.query_start > interval '5 minutes'
ORDER BY duration DESC;

Any transaction older than a few minutes in an OLTP system is a red flag worth alerting on separately from query latency.

The Baseline Problem

The most common gap in database observability is not missing metrics. It is missing baselines. Teams instrument everything but then alert on absolute thresholds that were set once and never revisited. A query that averages 300ms is not inherently a problem. A query that was averaging 30ms last week and is now averaging 300ms is.

Build your alerting around relative change: compare the current collection window against the same time window from last week (to account for traffic patterns). A 2x increase in mean latency over 24 hours on a high-call-rate query should fire even if the absolute value is below your threshold. The threshold approach catches fires. The regression approach catches the smoke.

Database performance degrades gradually until it does not. The slow creep of bloat, the index that stops being used after a statistics update, the connection pool that was sized for last quarter’s load: none of these announce themselves. Observability that only alerts when something is already broken is not observability; it is a post-mortem generator. The goal is to surface changes while there is still time to understand them.