postgres-operations

Skill: PostgreSQL Operations

Expertise: PostgreSQL health, vacuuming, lock analysis, PITR, WAL archiving, PgBouncer, K8s-hosted PostgreSQL.

When to load

When investigating a slow database, diagnosing lock waits, running PITR recovery, or managing a PostgreSQL instance.

Health Check Commands

-- Database size overview
SELECT
  datname,
  pg_size_pretty(pg_database_size(datname)) AS size,
  numbackends AS active_connections
FROM pg_stat_database
ORDER BY pg_database_size(datname) DESC;

-- Table sizes (top 20)
SELECT
  schemaname || '.' || tablename AS table,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
  pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
  pg_size_pretty(pg_indexes_size(schemaname || '.' || tablename)) AS index_size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

-- Replication lag (primary)
SELECT
  client_addr,
  state,
  pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replication_lag
FROM pg_stat_replication;

Lock Investigation

-- Active locks and blocking queries
SELECT
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocked.wait_event_type,
  blocked.wait_event
FROM pg_stat_activity blocking
JOIN pg_stat_activity blocked
  ON blocked.wait_event_type = 'Lock'
  AND blocking.pid != blocked.pid
WHERE blocking.state = 'active';

-- Kill blocking query (confirm before running!)
SELECT pg_terminate_backend(<blocking_pid>);

-- Long-running queries (> 5 min)
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state = 'active';

VACUUM and Bloat

-- Check autovacuum health
SELECT
  schemaname || '.' || relname AS table,
  last_autovacuum,
  last_autoanalyze,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Manual VACUUM ANALYZE (non-blocking)
VACUUM ANALYZE VERBOSE orders;

-- VACUUM FULL (rewrites table — locks! use with maintenance window)
VACUUM FULL orders;