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 ```sql -- 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 ```sql -- 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 blockin...