Monitoring

Comprehensive monitoring stack for PostgreSQL: Prometheus + postgres_exporter + pgbouncer_exporter + Grafana, key metrics by four-tier framework (workload / efficiency / saturation / capacity), log-based metrics via auto_explain + structured logging, alerting thresholds + sampling strategies. Covers PostgreSQL 14-18 with explicit version-introduced view + column annotations.

[!WARNING] PG17 + PG18 silently break PG16-era monitoring queries Three discrete upgrade traps: (1) PG17 split pg_stat_bgwriter → pg_stat_checkpointer — buffers_backend + buffers_backend_fsync removed; queries return NULL silently. (2) PG17 renamed pg_stat_progress_vacuum columns — max_dead_tuples → max_dead_tuple_bytes, num_dead_tuples → num_dead_item_ids. (3) PG18 removed pg_stat_io.op_bytes (always equaled BLCKSZ) — replaced with read_bytes / write_bytes / extend_bytes. Plus PG18 relocated WAL I/O timing from pg_stat_wal to pg_stat_io (removed wal_write / wal_sync / wal_write_time / wal_sync_time columns). Audit dashboards before upgrade.

Table of Contents

• When to Use This Reference
• Mental Model — Five Rules
• Decision Matrix
• Syntax / Mechanics
  • Four-Tier Monitoring Framework
  • Monitoring Stack Catalog
  • postgres_exporter
  • pgbouncer_exporter
  • Log-Based Metrics
  • auto_explain
  • pg_stat_* View Inventory
  • Alerting Thresholds
  • Per-Version Timeline
• Examples / Recipes
• Gotchas / Anti-patterns
• See Also
• Sources

When to Use This Reference

Pick this file when:

• Building Prometheus + Grafana monitoring stack for PostgreSQL fleet
• Choosing between postgres_exporter / pgwatch / pgmonitor / pgmetrics / pg_activity / pganalyze
• Configuring pgbouncer_exporter to scrape pgBouncer console
• Setting alerting thresholds per workload tier
• Auditing existing monitoring queries before PG14 → PG18 upgrade
• Designing log-based metrics complementing counter-based pg_stat_* views
• Tuning auto_explain for production slow-query capture

For per-view deep dives see 58-performance-diagnostics.md. For pg_stat_statements specifics see 57-pg-stat-statements.md. For pgBouncer console commands see 81-pgbouncer.md.

Mental Model — Five Rules

1. Four-tier framework — workload / efficiency / saturation / capacity. Workload = QPS, transaction mix, query latency distribution. Efficiency = cache hit ratio, plan quality, autovacuum effectiveness. Saturation = connections, CPU, I/O wait, lock waits, replication lag. Capacity = disk usage, WAL retention, partition count, slot retention. Each tier needs separate dashboards + separate alert thresholds.

2. postgres_exporter is canonical Prometheus exporter. Supplements pg_stat_* views with derived metrics (cache hit ratio computed as blks_hit / (blks_hit + blks_read), replication lag bytes, table bloat estimates). Supports custom queries via queries.yaml for workload-specific metrics. Default-enabled collectors: database, locks, replication, replication_slot, stat_bgwriter, stat_database, stat_progress_vacuum, stat_user_tables, statio_user_tables, wal¹.

3. pgbouncer_exporter consumes pgBouncer console. Translates SHOW STATS / SHOW POOLS / SHOW CONFIG / SHOW DATABASES / SHOW LISTS to Prometheus metrics. Pool saturation = cl_waiting > 0 sustained + maxwait > 5s — cross-reference 81-pgbouncer.md Recipe 4.

4. Log-based metrics complement counter-based. Counters miss: slow-query rate distribution, deadlock count, autovacuum cancellations, connection-rejected count, error-rate by SQLSTATE. Ship Postgres logs to Loki / Elasticsearch / Splunk + extract metrics via log parsing. log_destination = jsonlog (PG15+) makes parsing tractable.

5. Alerting thresholds workload-specific, not universal. "Good" cache hit ratio depends on working-set-vs-RAM ratio. OLTP cluster with 95% hit = warning; analytics cluster with 95% hit = healthy. Same metric different thresholds. Set thresholds per role (webapp / reporter / batchjobs) or per database, not cluster-wide.

Decision Matrix

Three smell signals:

1. Cardinality explosion in Prometheus — pg_stat_statements-per-queryid metrics with 10k+ unique queries = exporter scrape time > 30s, breaks Prometheus rule eval. Sample top-N by total_exec_time instead.
2. Cache hit ratio alert firing constantly — threshold wrong for workload. Reporting cluster with 100GB working set on 32GB RAM cannot hit 99% — alert mis-set, not "broken" cluster.
3. Pagerduty floods for idle_in_transaction — symptom is real (cross-reference 27-mvcc-internals.md Gotcha #2) but noise filter via state_change > 1 minute instead of any-idle-in-tx triggers.

Syntax / Mechanics

Four-Tier Monitoring Framework

Each tier collects different metrics with different sample rates + alert horizons. Dashboards organized by tier prevent the "100-graph wall" anti-pattern.

Monitoring Stack Catalog

[!NOTE] PostgreSQL 15 pgwatch versioning naming has progressed beyond the planning-note "pgwatch3" assumption — current is v5.2.0. Cite by URL not chapter version.

[!NOTE] PostgreSQL 18+ Percona Toolkit (pt-pg-summary) does NOT exist — Percona Toolkit is MySQL-focused. Do not reference for PG diagnostics; use pgmetrics or pg_activity instead.

postgres_exporter

Canonical Prometheus exporter. Connects to PG cluster via libpq, scrapes pg_stat_* views, exposes /metrics endpoint for Prometheus pull. Run as sidecar (one exporter per PG cluster) or centralized (one exporter polls many clusters via DATA_SOURCE_NAME env).

Default-enabled collectors:

Custom queries via queries.yaml for metrics not exposed by default. Example for tracking longest-running transaction:

# /etc/postgres_exporter/queries.yaml
pg_long_transactions:
  query: |
    SELECT
      EXTRACT(EPOCH FROM (now() - xact_start))::bigint AS duration_seconds,
      state,
      backend_type
    FROM pg_stat_activity
    WHERE xact_start IS NOT NULL
      AND backend_type = 'client backend'
    ORDER BY xact_start
    LIMIT 1
  metrics:
    - duration_seconds:
        usage: GAUGE
        description: "Longest-running transaction duration in seconds"
    - state:
        usage: LABEL
    - backend_type:
        usage: LABEL

Run as: postgres_exporter --extend.query-path=/etc/postgres_exporter/queries.yaml¹⁰.

Auth pattern (production): dedicated monitoring role with pg_monitor predefined role grant.

CREATE ROLE postgres_exporter LOGIN PASSWORD '...';
GRANT pg_monitor TO postgres_exporter;
-- pg_monitor includes pg_read_all_settings + pg_read_all_stats + pg_stat_scan_tables

Cross-reference 46-roles-privileges.md Recipe 8 for pg_monitor predefined role.

pgbouncer_exporter

Separate exporter for pgBouncer. Connects to pgBouncer's admin console (pgbouncer virtual database) via libpq, scrapes SHOW STATS / SHOW POOLS / SHOW CONFIG / SHOW DATABASES / SHOW LISTS. Cross-reference 81-pgbouncer.md Console Commands H2.

Key metrics exposed:

Auth: pgBouncer console requires user listed in admin_users or stats_users in pgbouncer.ini. Create dedicated read-only stats_users user for exporter — never give admin_users (can run PAUSE / KILL).

Log-Based Metrics

Counter-based pg_stat_* views miss several operationally-critical signals:

Recommended production logging baseline:

# postgresql.conf
logging_collector = on
log_destination = 'stderr,jsonlog'   # PG15+ jsonlog; pre-PG15 use 'stderr,csvlog'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H.log'
log_rotation_age = 1d
log_rotation_size = 1GB
log_truncate_on_rotation = on

log_line_prefix = '%m [%p] %q%u@%d/%a '   # %L PG18+ adds client IP
log_min_messages = WARNING
log_min_error_statement = ERROR
log_min_duration_statement = 500ms        # Slow-query threshold; tune per workload
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0                        # Log all temp file creations
log_autovacuum_min_duration = '10min'     # PG15+ default already 10min

# PG14+
log_recovery_conflict_waits = on          # Standby query cancellation diagnostics

# PG18+
log_lock_failures = on                    # NOWAIT lock acquisition failures

Ship logs to immutable store (S3 with object lock / Azure Blob / GCS with retention) for compliance + forensics. Cross-reference 51-pgaudit.md Recipe 7 for the canonical log-shipping pattern.

auto_explain

In-core module that automatically EXPLAINs slow queries. Loads via shared_preload_libraries (postmaster restart) OR per-session LOAD 'auto_explain'. Production use: postmaster preload.

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements, auto_explain'

auto_explain.log_min_duration = '1s'              # Plans for queries >1s
auto_explain.log_analyze = on                     # Include actual row counts
auto_explain.log_buffers = on                     # Buffer accounting
auto_explain.log_timing = on                      # Per-node timing (expensive)
auto_explain.log_verbose = on                     # Include output columns
auto_explain.log_format = 'json'                  # Structured for parsing
auto_explain.log_nested_statements = on           # Include SQL inside functions
auto_explain.sample_rate = 1.0                    # 1.0 = log every slow query
auto_explain.log_parameter_max_length = 4096      # PG16+ — capture bind values

[!WARNING] auto_explain.log_analyze overhead Setting log_analyze = on makes Postgres execute EXPLAIN ANALYZE for slow queries — adds per-row timing instrumentation. On busy OLTP clusters this can add 5-10% latency to logged queries. Mitigate via sample_rate = 0.1 (log 10% of slow queries) on high-QPS workloads.

Cross-reference 56-explain.md for plan interpretation + 57-pg-stat-statements.md for aggregate query stats.

pg_stat_* View Inventory

Quick-reference catalog with PG-version-introduced annotations. Deep dive lives in 58-performance-diagnostics.md.

[!NOTE] PostgreSQL 17 pg_stat_checkpointer view created. Columns buffers_backend + buffers_backend_fsync removed from pg_stat_bgwriter because "These fields are considered redundant to similar columns in pg_stat_io"¹². Monitoring queries for buffer write distribution must use pg_stat_io filtered by backend_type = 'client backend'.

[!NOTE] PostgreSQL 18 pg_stat_io: op_bytes column removed (always equaled BLCKSZ). New columns: read_bytes, write_bytes, extend_bytes. WAL rows added — WAL receiver + WAL write wait events now visible in pg_stat_io. Plus pg_stat_get_backend_io() for per-backend stats; pg_stat_reset_backend_stats(pid) to clear them.

Alerting Thresholds

No universal threshold — set per workload. Below = starting points for OLTP web-app cluster; reporting / batch / mixed clusters need different values.

Tier 1 — Capacity (slow alerts, days-weeks horizon):

Tier 2 — Saturation (fast alerts, minutes horizon):

Tier 3 — Efficiency (hours-days horizon):

Tier 4 — Workload (hours horizon):

Per-Version Timeline

Examples / Recipes

Recipe 1 — Production monitoring stack baseline

End-to-end: PG cluster → postgres_exporter → Prometheus → Grafana + Alertmanager. Three-config-block.

Step 1 — PG cluster postgresql.conf:

# Enable monitoring extensions + structured logging
shared_preload_libraries = 'pg_stat_statements, auto_explain'

# Cumulative statistics — needed for postgres_exporter
track_activities = on
track_counts = on
track_io_timing = on            # Per-relation I/O timing (small overhead)
track_wal_io_timing = on        # PG14+ WAL timing
track_functions = pl            # Function call counts
track_commit_timestamp = on     # Useful for replication forensics

# Slow-query capture
log_min_duration_statement = 500ms
auto_explain.log_min_duration = 1s
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_format = json
auto_explain.sample_rate = 1.0

# Structured logging
log_destination = 'stderr,jsonlog'
logging_collector = on
log_line_prefix = '%m [%p] %q%u@%d/%a '
log_connections = on
log_disconnections = on
log_lock_waits = on
log_autovacuum_min_duration = '10min'

Step 2 — Monitoring role:

CREATE ROLE postgres_exporter LOGIN PASSWORD 'secret';
GRANT pg_monitor TO postgres_exporter;
-- pg_hba.conf:
-- host all postgres_exporter 10.0.0.0/8 scram-sha-256

Step 3 — postgres_exporter systemd unit:

# /etc/systemd/system/postgres_exporter.service
[Unit]
Description=Prometheus PostgreSQL Exporter
After=network.target

[Service]
User=postgres
Environment="DATA_SOURCE_NAME=postgresql://postgres_exporter:secret@localhost:5432/postgres?sslmode=require"
ExecStart=/usr/local/bin/postgres_exporter \
  --web.listen-address=:9187 \
  --extend.query-path=/etc/postgres_exporter/queries.yaml
Restart=on-failure

[Install]
WantedBy=multi-user.target

Step 4 — Prometheus scrape config:

# prometheus.yml
scrape_configs:
  - job_name: postgres
    scrape_interval: 30s
    static_configs:
      - targets: ['pg-prod-1:9187', 'pg-prod-2:9187']
        labels:
          environment: production
          role: primary

Step 5 — Verify:

curl -s http://pg-prod-1:9187/metrics | grep -E '^pg_up|pg_postmaster|pg_stat_database_xact_commit'

Recipe 2 — Cache-hit-ratio alert rule

Per-database cache hit, computed from pg_stat_database. Alert when sustained below threshold.

# /etc/prometheus/rules/postgres.yml
groups:
- name: postgres_efficiency
  rules:
  - alert: PostgresCacheHitRatioLow
    expr: |
      (
        rate(pg_stat_database_blks_hit{datname!~"template.*|postgres"}[5m])
        /
        (rate(pg_stat_database_blks_hit{datname!~"template.*|postgres"}[5m])
         + rate(pg_stat_database_blks_read{datname!~"template.*|postgres"}[5m]))
      ) < 0.90
    for: 15m
    labels:
      severity: warning
      tier: efficiency
    annotations:
      summary: "Cache hit ratio < 90% on {{ $labels.datname }} for 15m"
      description: "Working set likely exceeds shared_buffers. Check pg_stat_io for buffer reads."

Recipe 3 — pgBouncer pool exhaustion alert

- alert: PgBouncerPoolSaturated
  expr: pgbouncer_pools_client_waiting > 0
  for: 1m
  labels:
    severity: warning
    tier: saturation
  annotations:
    summary: "pgBouncer clients waiting for server connection (database={{ $labels.database }})"
    description: "cl_waiting > 0 sustained. Raise default_pool_size or investigate long-running queries."

- alert: PgBouncerMaxWaitHigh
  expr: pgbouncer_pools_maxwait_seconds > 5
  for: 2m
  labels:
    severity: critical
    tier: saturation
  annotations:
    summary: "pgBouncer maxwait > 5s (database={{ $labels.database }})"
    description: "Longest client wait exceeds SLA. Check server-side query latency + pool size."

Recipe 4 — Replication lag alert

- alert: PostgresReplicationLagHigh
  expr: pg_replication_lag_seconds > 30
  for: 2m
  labels:
    severity: warning
    tier: saturation
  annotations:
    summary: "Standby {{ $labels.application_name }} replay lag > 30s"

- alert: PostgresReplicationSlotInactive
  expr: pg_replication_slots_active{slot_type="physical"} == 0
  for: 5m
  labels:
    severity: warning
    tier: capacity
  annotations:
    summary: "Replication slot {{ $labels.slot_name }} inactive for 5m"
    description: "Inactive slot retains WAL indefinitely without max_slot_wal_keep_size. Check wal_status."

- alert: PostgresReplicationSlotLost
  expr: pg_replication_slots_wal_status{wal_status="lost"} == 1
  labels:
    severity: critical
    tier: capacity
  annotations:
    summary: "Replication slot {{ $labels.slot_name }} status=lost — CANNOT RESUME"

Recipe 5 — XID wraparound capacity alert

- alert: PostgresXIDWraparoundApproaching
  expr: pg_database_xid_age_to_wraparound < 500000000
  for: 1h
  labels:
    severity: warning
    tier: capacity
  annotations:
    summary: "Database {{ $labels.datname }} < 500M transactions to wraparound"

- alert: PostgresXIDWraparoundCritical
  expr: pg_database_xid_age_to_wraparound < 100000000
  for: 5m
  labels:
    severity: critical
    tier: capacity
  annotations:
    summary: "Database {{ $labels.datname }} < 100M transactions to wraparound — EMERGENCY"

Custom queries for pg_database_xid_age_to_wraparound (not built-in to postgres_exporter):

# /etc/postgres_exporter/queries.yaml
pg_database_xid_age:
  query: |
    SELECT
      datname,
      age(datfrozenxid) AS xid_age,
      2147483647 - age(datfrozenxid) AS xid_age_to_wraparound
    FROM pg_database
    WHERE datallowconn = true
  metrics:
    - datname:
        usage: LABEL
    - xid_age:
        usage: GAUGE
        description: "Age in transactions of oldest XID in database"
    - xid_age_to_wraparound:
        usage: GAUGE
        description: "Transactions remaining until wraparound"

Recipe 6 — Long-running transaction detector

Identifies sessions holding xmin horizon back (autovacuum cannot clean their dead tuples). Cross-reference 27-mvcc-internals.md Recipe 2.

# queries.yaml
pg_long_xact:
  query: |
    SELECT
      COALESCE(EXTRACT(EPOCH FROM (now() - xact_start))::bigint, 0) AS duration_seconds,
      state,
      COALESCE(application_name, 'unknown') AS application_name
    FROM pg_stat_activity
    WHERE xact_start IS NOT NULL
      AND backend_type = 'client backend'
      AND state != 'idle'
    ORDER BY xact_start
    LIMIT 1
  metrics:
    - duration_seconds:
        usage: GAUGE
    - state:
        usage: LABEL
    - application_name:
        usage: LABEL

# rules
- alert: PostgresLongTransaction
  expr: pg_long_xact_duration_seconds > 300
  for: 1m
  labels:
    severity: warning
    tier: saturation
  annotations:
    summary: "Transaction running > 5 min ({{ $labels.application_name }})"
    description: "Long transactions block autovacuum. Consider idle_in_transaction_session_timeout."

Recipe 7 — Autovacuum overdue tables audit

Tracks tables with high dead-tuple ratio where autovacuum has not run recently.

pg_autovac_overdue:
  query: |
    SELECT
      schemaname,
      relname,
      n_dead_tup,
      n_live_tup,
      CASE WHEN n_live_tup > 0
        THEN (n_dead_tup::float / n_live_tup)::float
        ELSE 0
      END AS dead_pct,
      COALESCE(EXTRACT(EPOCH FROM (now() - last_autovacuum)), 999999) AS last_autovac_seconds_ago
    FROM pg_stat_user_tables
    WHERE n_live_tup > 1000
      AND (n_dead_tup::float / GREATEST(n_live_tup, 1)) > 0.20
  metrics:
    - schemaname: { usage: LABEL }
    - relname: { usage: LABEL }
    - n_dead_tup: { usage: GAUGE }
    - dead_pct: { usage: GAUGE }
    - last_autovac_seconds_ago: { usage: GAUGE }

Recipe 8 — Detect monitoring queries that break on PG17/PG18 upgrade

Pre-upgrade audit of dashboards + alert rules referencing removed columns.

# Search dashboards + alert rules for PG17-removed columns
grep -r -E 'buffers_backend|buffers_backend_fsync|max_dead_tuples|num_dead_tuples' \
  /etc/prometheus/rules/ /var/lib/grafana/dashboards/

# Search for PG18-removed pg_stat_io.op_bytes + pg_stat_wal timing columns
grep -r -E 'pg_stat_io.*op_bytes|wal_write_time|wal_sync_time' \
  /etc/prometheus/rules/ /var/lib/grafana/dashboards/

# Search for PG18-removed pg_backend_memory_contexts.parent
grep -r 'pg_backend_memory_contexts.*parent' \
  /etc/prometheus/rules/ /var/lib/grafana/dashboards/

Update queries before upgrading cluster to PG17/PG18 or dashboards return NULL silently.

Recipe 9 — pg_activity for incident response

Real-time top-style query monitoring during incidents. Better than watch over pg_stat_activity because it filters per-DB + can kill backends interactively.

# Install
apt install pg-activity     # Debian/Ubuntu
yum install pg-activity     # RHEL/Rocky

# Run
pg_activity -h pg-prod-1 -U postgres -d production

# Keyboard shortcuts (in TUI):
# - F1/F2/F3: switch views (running queries / waiting / blocking)
# - C: change refresh rate
# - K: kill selected backend
# - F: filter by database
# - / : search by query text

Recipe 10 — pgmetrics one-shot snapshot

Single-binary tool collecting 350+ metrics. Useful for: pre-deploy baseline, post-incident forensics, sharing cluster state with vendor support.

# Install (release binary from https://github.com/rapidloop/pgmetrics)
curl -sL https://github.com/rapidloop/pgmetrics/releases/latest/download/pgmetrics_linux_amd64.tar.gz \
  | tar xz

# Run
./pgmetrics -h pg-prod-1 -U postgres -p 5432 -f text   # human-readable
./pgmetrics -h pg-prod-1 -U postgres -p 5432 -f json   # machine-readable
./pgmetrics -h pg-prod-1 -U postgres -p 5432 -f csv    # spreadsheet-friendly

# Common flags:
# --statements N    : Top N from pg_stat_statements
# --schema PATTERN  : Filter to schema
# --no-pgbouncer    : Skip pgBouncer probing
# --connect=NAME    : Use libpq service file entry

Recipe 11 — Top-N query by execution time (workload tier)

Combine pg_stat_statements with Grafana for query-level workload view. Cardinality concern: 10k+ unique queries break Prometheus. Sample top-50 instead.

# queries.yaml — top-50 only, runs every 60s
pg_top_queries:
  query: |
    SELECT
      queryid::text,
      LEFT(query, 100) AS query_sample,
      calls,
      total_exec_time / 1000 AS total_exec_seconds,
      mean_exec_time AS mean_exec_ms,
      rows
    FROM pg_stat_statements
    WHERE queryid IS NOT NULL
    ORDER BY total_exec_time DESC
    LIMIT 50
  metrics:
    - queryid: { usage: LABEL }
    - query_sample: { usage: LABEL }
    - calls: { usage: COUNTER }
    - total_exec_seconds: { usage: COUNTER }
    - mean_exec_ms: { usage: GAUGE }
    - rows: { usage: COUNTER }

Recipe 12 — Log-based deadlock rate metric

Postgres logs deadlock detection to server log. Extract count via log-shipping pipeline (Loki / Promtail / vector).

# promtail-config.yaml — Loki log shipper
scrape_configs:
- job_name: postgres
  static_configs:
  - targets: [localhost]
    labels:
      job: postgres
      __path__: /var/lib/postgresql/16/main/log/*.log
  pipeline_stages:
  - json:
      expressions:
        message: message
        sqlstate: sqlstate
        timestamp: timestamp
  - regex:
      source: message
      expression: 'deadlock detected'
  - labels:
      deadlock_detected:
  - metrics:
      postgres_deadlock_count:
        type: Counter
        description: "Deadlocks detected in PostgreSQL log"
        prefix: postgres_
        source: deadlock_detected
        config:
          action: inc

# Alert
- alert: PostgresDeadlockRateHigh
  expr: rate(postgres_deadlock_count[5m]) > 0.0167   # > 1 per minute
  labels:
    severity: warning
    tier: saturation
  annotations:
    summary: "Deadlock rate > 1/min"
    description: "Application is producing lock ordering bugs. See log for participants."

Recipe 13 — Pre-deploy baseline + post-deploy regression check

Capture pre-deploy pg_stat_statements snapshot; after deploy, find regressions.

-- Pre-deploy: capture baseline (~5 minutes before deploy)
CREATE TABLE deploy_baseline AS
SELECT
  queryid,
  query,
  calls AS pre_calls,
  total_exec_time AS pre_total_ms,
  mean_exec_time AS pre_mean_ms,
  now() AS captured_at
FROM pg_stat_statements
WHERE calls > 100;

-- Post-deploy (15 minutes after): find regressions
WITH current AS (
  SELECT queryid, calls, total_exec_time, mean_exec_time
  FROM pg_stat_statements
),
deltas AS (
  SELECT
    b.queryid,
    LEFT(b.query, 80) AS query_sample,
    c.calls - b.pre_calls AS new_calls,
    (c.mean_exec_time - b.pre_mean_ms) AS mean_change_ms,
    (c.mean_exec_time / NULLIF(b.pre_mean_ms, 0))::numeric(10,2) AS slowdown_ratio
  FROM deploy_baseline b
  JOIN current c ON c.queryid = b.queryid
  WHERE c.calls > b.pre_calls + 10
)
SELECT *
FROM deltas
WHERE slowdown_ratio > 1.5
  AND mean_change_ms > 10
ORDER BY mean_change_ms DESC
LIMIT 20;

Cross-reference 57-pg-stat-statements.md Recipe 13.

Gotchas / Anti-patterns

1. Monitoring queries break silently on PG17 upgrade. pg_stat_bgwriter.buffers_backend returns NULL after PG17 because column moved to pg_stat_io. Same for buffers_backend_fsync. Audit dashboards via Recipe 8 BEFORE upgrading.

2. PG18 removes pg_stat_io.op_bytes. Queries using op_bytes to compute total bytes return NULL. Replace with read_bytes + write_bytes + extend_bytes columns.

3. PG18 relocates pg_stat_wal timing columns to pg_stat_io. wal_write_time / wal_sync_time / wal_write / wal_sync columns REMOVED from pg_stat_wal. Find WAL I/O timing in pg_stat_io rows where backend_type LIKE 'wal%'.

4. PG17 renames pg_stat_progress_vacuum columns. max_dead_tuples → max_dead_tuple_bytes (now in bytes, not tuples!); num_dead_tuples → num_dead_item_ids. Old queries return NULL.

5. pg_stat_statements cardinality explosion in Prometheus. A cluster with 10k+ distinct queryids exported as Prometheus labels = scrape time > 30s, Prometheus rule eval fails. Sample top-N by total_exec_time instead (Recipe 11).

6. PG15 log_checkpoints default flipped from off to on. Verbatim release-note caveat: "This will cause even an idle server to generate some log output"²¹. Resource-constrained clusters need explicit log_checkpoints = off post-upgrade.

7. auto_explain.log_analyze = on adds 5-10% latency on busy OLTP. Mitigate via sample_rate = 0.1 to log 10% of slow queries.

8. pg_monitor predefined role grants seeing query text from all roles. A user-level role granted pg_monitor can read other users' queries via pg_stat_activity. Lock down pg_monitor to dedicated monitoring service accounts only.

9. Cache hit ratio threshold is workload-specific. OLTP cluster with hot small working set in shared_buffers can achieve 99%; analytics cluster with 100GB working set on 32GB RAM cannot exceed ~70-80%. Set threshold per role / per database, not cluster-wide.

10. idle_in_transaction alerts page constantly. Symptom is real (cross-reference 27-mvcc-internals.md Gotcha #2) but filter by state_change > 1 minute to reduce noise. Short-lived idle-in-tx is application normal; long-lived is the bug.

11. pg_stat_replication.replay_lag returns NULL on idle standby. Standby with no replication traffic since last query reports NULL — not zero. Alert rule must OR pg_replication_slot_wal_lag_bytes > X to cover idle gap.

12. pg_replication_slots.active = false is normal during failover but indefinite slot retention fills disk. Alert on active = false for > 5 minutes + pg_wal_lsn_diff(...) > max_slot_wal_keep_size_threshold.

13. postgres_exporter cardinality bloats with pg_stat_user_tables on partition-heavy schemas. Cluster with 10k partitions = 10k per-table rows × N metrics = exporter slow. Filter via pg_class join or limit to parent tables only.

14. pgmonitor copyright transferred 2025-2026. Crunchy Data (2017-2025) → Snowflake (2025-2026). Project still active; cite GitHub URL not the .io domain (which returned ECONNREFUSED at planning time).

15. Percona Toolkit (pt-pg-summary) is MySQL-focused. No PostgreSQL diagnostics in Percona Toolkit. Use pgmetrics or pg_activity instead.

16. pg_stat_statements_reset() returns void pre-PG17, timestamptz PG17+. Scripts comparing return-value need version conditional. Plus PG17 added minmax_only boolean 4th argument for reset signature.

17. Counter views accumulate from cluster start (or last reset). Single-point query gives lifetime value. Always use rate() / irate() in Prometheus for derived metrics like QPS or cache-hit ratio.

18. track_io_timing = on overhead. Some platforms (older kernels, Windows) have slow clock_gettime() — track_io_timing can add 5-15% CPU. Test on staging before enabling cluster-wide. PG14+ pg_test_timing measures the overhead.

19. log_min_duration_statement = 0 logs every query. Catastrophic on high-QPS clusters (10MB/sec log volume not unusual). Use auto_explain.log_min_duration instead with sample_rate for production.

20. pg_stat_database.session_* columns (PG14+) don't track pgBouncer-pooled connections accurately. pgBouncer transaction-mode shares server connections across many client sessions — sessions counter undercounts client sessions.

21. Stats collector process removed in PG15. Old runbooks referencing the stats collector process (PG≤14) are obsolete. Cumulative stats now in shared memory²².

22. pg_stat_io op_bytes column equaled BLCKSZ until PG18 removed it. PG16/17 queries multiplying counts by op_bytes produced correct byte totals; PG18 queries must use the explicit read_bytes / write_bytes / extend_bytes columns.

See Also

• 27-mvcc-internals.md — xmin horizon blockers; idle_in_transaction cost
• 28-vacuum-autovacuum.md — autovacuum monitoring + tuning
• 29-transaction-id-wraparound.md — XID wraparound alerts
• 46-roles-privileges.md — pg_monitor predefined role
• 51-pgaudit.md — audit log shipping pattern
• 53-server-configuration.md — GUC mechanism + pg_settings
• 54-memory-tuning.md — shared_buffers and cache-hit thresholds
• 56-explain.md — EXPLAIN output for auto_explain plans
• 57-pg-stat-statements.md — query-level workload metrics
• 58-performance-diagnostics.md — full pg_stat_* view reference
• 73-streaming-replication.md — replication lag alerting
• 75-replication-slots.md — slot retention monitoring
• 78-ha-architectures.md — HA-pattern alerting requirements
• 79-patroni.md — Patroni /metrics endpoint integration
• 81-pgbouncer.md — pgBouncer console + pgbouncer_exporter
• 83-backup-pg-dump.md — backup completion monitoring
• 88-corruption-recovery.md — checksum_failures alert interpretation + amcheck verification
• 101-managed-vs-baremetal.md — managed-env monitoring constraints

Sources

Additional canonical references:

• PostgreSQL 16 monitoring chapter: https://www.postgresql.org/docs/16/monitoring.html
• PostgreSQL 16 cumulative statistics: https://www.postgresql.org/docs/16/monitoring-stats.html
• PostgreSQL 16 logging GUCs: https://www.postgresql.org/docs/16/runtime-config-logging.html
• PostgreSQL 16 pg_stat_activity columns: https://www.postgresql.org/docs/16/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
• PostgreSQL 16/17/18 release notes: https://www.postgresql.org/docs/release/16.0/ https://www.postgresql.org/docs/release/17.0/ https://www.postgresql.org/docs/release/18.0/
• pgBouncer console commands: https://www.pgbouncer.org/usage.html