Database Incident Patterns

-- Check current connections vs max
SELECT
    count(*) AS current,
    current_setting('max_connections')::int AS max,
    ROUND(count(*)::numeric / current_setting('max_connections')::int * 100, 1) AS pct
FROM pg_stat_activity;

-- Find what is using connections
SELECT
    state,
    wait_event_type,
    COUNT(*) AS count
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, wait_event_type
ORDER BY count DESC;

-- Find long-running idle connections
SELECT
    pid,
    usename,
    state,
    now() - state_change AS idle_duration,
    LEFT(query, 50) AS last_query
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > INTERVAL '10 minutes'
ORDER BY idle_duration DESC;

-- Kill idle connections older than 10 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > INTERVAL '10 minutes'
  AND backend_type = 'client backend';

-- Kill specific blocking connection
SELECT pg_terminate_backend(<pid>);

-- Reduce max_connections temporarily (requires restart)
ALTER SYSTEM SET max_connections = 200;

-- Find all locks and who holds them
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query,
    blocking.wait_event_type,
    now() - blocked.query_start AS waiting_duration
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid AND NOT blocked_locks.granted
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
    AND blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid;

-- Check for ACCESS EXCLUSIVE locks (blocks everything)
SELECT
    locktype,
    relation::regclass,
    mode,
    pid,
    granted,
    now() - age AS lock_duration
FROM pg_locks
JOIN (SELECT pid, query_start AS age FROM pg_stat_activity) a USING (pid)
WHERE mode = 'ACCESS EXCLUSIVE LOCK'
ORDER BY lock_duration DESC;

-- Find the blocking query and terminate it
SELECT pg_terminate_backend(<blocking_pid>);

-- If the blocking query is in a transaction, cancel instead
SELECT pg_cancel_backend(<blocking_pid>);

-- Check if VACUUM is blocking (common)
SELECT
    pid,
    query,
    now() - query_start AS duration,
    wait_event_type,
    wait_event
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%'
  AND state != 'idle';

-- Check disk usage
SELECT
    pg_size_pretty(pg_database_size(current_database())) AS db_size,
    pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Emergency: drop old WAL segments (DANGEROUS — breaks replication)
-- Only if replication is not critical right now
SELECT pg_switch_wal();  -- force WAL switch to create smaller segment

-- Emergency: remove old backup files
-- rm /backup/old_dump_*.sql

-- Emergency: VACUUM to reclaim dead tuple space
VACUUM VERBOSE;

-- Emergency: drop non-essential indexes
DROP INDEX CONCURRENTLY idx_non_critical;

-- Emergency: truncate log/audit table
TRUNCATE audit_log;  -- if historical data is not critical

-- Check lag on primary
SELECT
    client_addr,
    state,
    replay_lag,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_pretty
FROM pg_stat_replication;

-- If lag is growing, check replica for blocking queries
-- On the replica:
SELECT
    pid,
    now() - query_start AS duration,
    state,
    wait_event_type,
    LEFT(query, 100) AS query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

-- Check if replica queries are being cancelled due to conflicts
SELECT
    datname,
    confl_tablespace,
    confl_lock,
    confl_snapshot,
    confl_bufferpin,
    confl_deadlock
FROM pg_stat_database_conflicts;