Monitoring PostgreSQL

-- ---------- WHAT IS HAPPENING RIGHT NOW ----------
-- The first query to run when something is wrong.

SELECT pid,
       usename,
       client_addr,
       state,
       now() - query_start AS running_for,
       wait_event_type,
       wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY query_start;

-- state values and what they mean:
--   active               running a query right now
--   idle                 connected, doing nothing (normal)
--   idle in transaction  IN A TRANSACTION, doing nothing  <- a problem if long
--   waiting              blocked on a lock (see wait_event)

-- ---------- CONNECTIONS ----------

SELECT count(*) AS total,
       count(*) FILTER (WHERE state = 'active')              AS active,
       count(*) FILTER (WHERE state = 'idle')                AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn,
       (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS max_conn
FROM pg_stat_activity;

-- ALERT when total exceeds ~80% of max_connections. Hitting the limit
-- means new connections are REFUSED and the application starts failing.

-- Long "idle in transaction" sessions hold locks and block VACUUM
-- database-wide. Worth alerting on by itself:
SELECT pid, usename, now() - state_change AS idle_for, left(query,60)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '5 minutes';

-- ---------- SLOW QUERIES (needs pg_stat_statements) ----------

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- also requires:  shared_preload_libraries = 'pg_stat_statements'  + restart

SELECT round(total_exec_time::numeric)      AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)    AS avg_ms,
       round(max_exec_time::numeric, 2)     AS max_ms,
       rows,
       left(query, 70)                      AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

-- Rank by TOTAL time, not average — a 15ms query run a million times
-- costs far more than a 30-second report that runs twice a day.

-- Reset the counters after making a change, to measure the effect:
SELECT pg_stat_statements_reset();

-- ---------- LOCKS AND BLOCKING ----------

SELECT blocked.pid              AS blocked_pid,
       left(blocked.query, 50)  AS blocked_query,
       blocking.pid             AS blocking_pid,
       left(blocking.query, 50) AS blocking_query,
       now() - blocked.query_start AS blocked_for
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0
ORDER BY blocked_for DESC;

-- Alert if anything has been blocked for more than about a minute.

-- ---------- DISK AND SIZES ----------

SELECT pg_size_pretty(pg_database_size(current_database())) AS database_size;

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total,
       pg_size_pretty(pg_table_size(relid))          AS table,
       pg_size_pretty(pg_indexes_size(relid))        AS indexes
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- WAL directory size — grows without limit if archiving fails or a
-- replication slot is abandoned. Both stop the database when the disk fills.
SELECT count(*) AS wal_segments,
       pg_size_pretty(sum(size)) AS wal_size
FROM pg_ls_waldir();

-- ---------- CACHE EFFECTIVENESS ----------

SELECT round(100.0 * sum(blks_hit) /
             NULLIF(sum(blks_hit) + sum(blks_read), 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();

-- Below ~95% on an OLTP workload usually means shared_buffers is too small
-- or the working set has outgrown RAM. Analytics workloads legitimately
-- read more from disk — interpret in context.

-- ---------- AUTOVACUUM AND BLOAT ----------

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup,0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Autovacuum running right now:
SELECT pid, now() - xact_start AS duration, left(query, 70)
FROM pg_stat_activity WHERE query LIKE 'autovacuum:%';

-- ---------- TRANSACTION ID WRAPAROUND — ALERT ON THIS ----------

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_limit
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

-- Under 200 million: healthy.
-- Over ~1 billion: investigate now.
-- At 2 billion: THE DATABASE STOPS ACCEPTING WRITES and recovery
-- requires single-user mode. This is one of very few PostgreSQL
-- problems with a hard stop, and it is entirely preventable.

-- ---------- REPLICATION LAG ----------

-- On the PRIMARY:
SELECT client_addr, state, sync_state,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag,
       replay_lag AS replay_lag_time
FROM pg_stat_replication;

-- On the STANDBY — the single most useful number:
SELECT now() - pg_last_xact_replay_timestamp() AS lag;

-- ---------- WAL ARCHIVING HEALTH ----------
SELECT archived_count, last_archived_time,
       failed_count,   last_failed_time,
       now() - last_archived_time AS since_last_archive
FROM pg_stat_archiver;

-- failed_count rising -> archiving is broken -> pg_wal grows -> disk fills
-- -> database stops. Alert on this.

-- ---------- ERRORS, DEADLOCKS AND ROLLBACKS ----------

SELECT datname,
       xact_commit, xact_rollback,
       round(100.0 * xact_rollback /
             NULLIF(xact_commit + xact_rollback,0), 2) AS rollback_pct,
       deadlocks,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_written
FROM pg_stat_database
WHERE datname = current_database();

-- A rising rollback_pct suggests application errors or lock contention.
-- Growing temp_files means work_mem is too low for your queries
-- (see the configuration lesson).

---------- WHAT TO ACTUALLY ALERT ON ----------

Alert on a small number of things that mean real trouble:

  PAGE SOMEONE (the database will stop or is down)
    * PostgreSQL not responding
    * Disk usage above 85% on the data or WAL filesystem
    * Transaction ID age above 1 billion
    * WAL archiving failing (failed_count rising)
    * Connections above 90% of max_connections

  WARN (look at it this week)
    * Replication lag above your tolerance
    * A query blocked for more than a minute
    * "idle in transaction" older than 10 minutes
    * Cache hit ratio falling below 95%
    * Dead tuple percentage above 20% on a large table
    * A backup that did not run, or a failed restore test

  TREND ONLY (graph it, do not alert)
    * Database and table sizes
    * Query throughput
    * Connection counts over time

Everything else is context for investigation, not an alarm.

# ---------- Tools that collect this for you ----------

# postgres_exporter + Prometheus + Grafana   the common open-source stack
# pgwatch2                                   PostgreSQL-specific, batteries included
# pganalyze                                  commercial, strong query analysis
# PgHero                                     simple web dashboard, easy to start with

# Whatever you use, make sure it keeps HISTORY. "Is this normal?" is
# unanswerable without knowing what last week looked like.