🐘 PostgreSQL Debugging & Troubleshooting Guide

-- View all active connections
SELECT pid, usename, application_name, client_addr, state, query_start, query 
FROM pg_stat_activity 
WHERE state = 'active';

-- Count connections per database
SELECT datname, count(*) as connections 
FROM pg_stat_activity 
GROUP BY datname 
ORDER BY connections DESC;

-- Check connection limits
SELECT setting::int as max_connections, 
       count(*) as current_connections,
       setting::int - count(*) as available_connections
FROM pg_settings, pg_stat_activity
WHERE name = 'max_connections'
GROUP BY setting;

-- All databases with sizes
SELECT 
    datname as database,
    pg_size_pretty(pg_database_size(datname)) as size,
    pg_database_size(datname) as size_bytes
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;

-- Table sizes in current database
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,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables 
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC 
LIMIT 20;

-- Should be > 99% for optimal performance
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 as cache_hit_ratio
FROM pg_statio_user_tables;

-- Index cache hit ratio
SELECT 
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit) as idx_hit,
    sum(idx_blks_hit) / nullif(sum(idx_blks_hit) + sum(idx_blks_read), 0) * 100 as idx_cache_hit_ratio
FROM pg_statio_user_indexes;

SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
    n_dead_tup as dead_tuples,
    n_live_tup as live_tuples,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Last vacuum and analyze times
SELECT 
    schemaname,
    relname,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze,
    n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- Queries running longer than 5 minutes
SELECT 
    pid,
    now() - query_start as duration,
    usename,
    datname,
    state,
    query
FROM pg_stat_activity
WHERE state = 'active' 
    AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

-- Enable extension first: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Queries with highest average execution time
SELECT 
    query,
    calls,
    round(mean_exec_time::numeric, 2) as avg_ms,
    round(total_exec_time::numeric, 2) as total_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Explain query execution plan
EXPLAIN ANALYZE 
SELECT * FROM your_table WHERE condition;

-- More detailed analysis
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM your_table WHERE condition;

SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan as avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_scan DESC
LIMIT 20;

SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as scans,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT 
    client_addr,
    application_name,
    state,
    sync_state,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) as lag_bytes,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as lag
FROM pg_stat_replication;

SELECT 
    now() - pg_last_xact_replay_timestamp() as replication_lag,
    pg_is_in_recovery() as is_replica;

-- List all replication slots
SELECT 
    slot_name,
    slot_type,
    database,
    active,
    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as retained_wal
FROM pg_replication_slots;

-- Reset statistics
SELECT pg_stat_reset();

-- Update table statistics
ANALYZE table_name;

-- Get table statistics age
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE tablename = 'your_table';

SELECT 
    locktype,
    database,
    relation::regclass,
    page,
    tuple,
    transactionid,
    mode,
    granted
FROM pg_locks
WHERE NOT granted;

SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement,
    blocked_activity.application_name AS blocked_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Terminate specific connection
SELECT pg_terminate_backend(<PID>);

-- Cancel query (safer than terminate)
SELECT pg_cancel_backend(<PID>);

-- All settings
SELECT name, setting, unit, context 
FROM pg_settings 
ORDER BY name;

-- Specific settings
SELECT name, setting, unit, short_desc 
FROM pg_settings 
WHERE name IN ('max_connections', 'shared_buffers', 'work_mem', 'maintenance_work_mem');

-- Settings that require restart
SELECT name, setting, pending_restart 
FROM pg_settings 
WHERE pending_restart = true;

SELECT name, setting, unit 
FROM pg_settings 
WHERE name LIKE '%wal%' OR name LIKE '%checkpoint%';

SELECT 
    name,
    setting,
    unit,
    pg_size_pretty(setting::bigint * 
        CASE unit 
            WHEN 'kB' THEN 1024
            WHEN 'MB' THEN 1024*1024
            WHEN '8kB' THEN 8192
            ELSE 1 
        END) as value
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem');

SELECT name, setting FROM pg_settings WHERE name LIKE '%config%' OR name LIKE '%hba%';

SELECT 
    datname,
    age(datfrozenxid) as xid_age,
    2147483648 - age(datfrozenxid) as xids_remaining,
    round(100.0 * age(datfrozenxid) / 2147483648, 2) as percent_to_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

SELECT 
    name,
    setting,
    short_desc
FROM pg_settings
WHERE name LIKE 'autovacuum%';

SELECT * FROM pg_stat_bgwriter;

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Kill idle connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
    AND state_change < now() - interval '10 minutes';