postgres
29 commands
29 shown
Connect to database
Interactive psql session to a database
psql -h localhost -U myuser -d mydb
List databases
Show all databases with size and encoding
psql -U postgres -c '\l+'
List tables in schema
List all tables in the public schema
psql -U myuser -d mydb -c '\dt public.*'
EXPLAIN ANALYZE a query
Show query plan with actual timings and buffer usage
psql -U myuser -d mydb -c 'EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE status = $1;'
Show slow queries (pg_stat_statements)
Top 10 slowest queries by total execution time
psql -U postgres -d mydb -c "SELECT round(total_exec_time::numeric,2) ms, calls, round((total_exec_time/calls)::numeric,2) avg_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;"
Show active queries
Running queries with duration and truncated SQL
psql -U postgres -c "SELECT pid, now()-query_start AS duration, state, left(query,80) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY duration DESC;"
Kill long-running query
Terminate all queries running longer than 5 minutes
psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE now()-query_start > interval '5 min' AND state = 'active';"
Show table sizes
Top 20 tables by total size including indexes and TOAST
psql -U myuser -d mydb -c "SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) size FROM pg_tables ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC LIMIT 20;"
Show index usage
Which indexes are actually being used and how often
psql -U myuser -d mydb -c "SELECT relname, indexrelname, idx_scan, idx_tup_read FROM pg_stat_user_indexes ORDER BY idx_scan DESC LIMIT 20;"
Find unused indexes
Indexes that have never been scanned — safe to drop candidates
psql -U myuser -d mydb -c "SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND NOT indisprimary ORDER BY pg_relation_size(indexrelid) DESC;"
Dump database
Custom-format dump (fastest, supports parallel restore)
pg_dump -U myuser -d mydb -F c -f mydb.dump
Restore database
Parallel restore with 4 workers, drop existing objects first
pg_restore -U myuser -d mydb -j 4 --clean mydb.dump
Vacuum and analyze table
Reclaim dead tuples and update statistics for query planner
psql -U myuser -d mydb -c 'VACUUM (VERBOSE, ANALYZE) orders;'
Check replication lag
Replication state and byte lag per replica
psql -U postgres -c "SELECT client_addr, state, sent_lsn, replay_lsn, (sent_lsn - replay_lsn) AS lag_bytes FROM pg_stat_replication;"
Show locks
Active locks with blocking/blocked status and query
psql -U postgres -d mydb -c "SELECT pid, relation::regclass, mode, granted, left(query,60) FROM pg_locks l JOIN pg_stat_activity a USING(pid) WHERE relation IS NOT NULL ORDER BY granted;"
Find long-running queries
List active queries running over 5 minutes, longest first
SELECT pid, now()-query_start AS dur, state, query FROM pg_stat_activity WHERE state='active' AND now()-query_start > interval '5 min' ORDER BY dur DESC;
Kill a stuck backend
Force-kill a backend by pid; note: try pg_cancel_backend first to cancel the query only
SELECT pg_terminate_backend(<pid>); -- graceful first: SELECT pg_cancel_backend(<pid>);
Top queries by total time
pg_stat_statements: rank queries by cumulative execution time
SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;
EXPLAIN with buffers
Run query and show real timings plus buffer hits/reads to spot disk I/O
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... ; -- shared hit/read shows cache vs disk I/O
Blocking lock tree
Show backends waiting on locks and which pids block them
SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;
Replication lag bytes
Measure replica lag in bytes plus write/flush/replay lag intervals
SELECT client_addr, state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes, write_lag, flush_lag, replay_lag FROM pg_stat_replication;
Biggest databases
Human-readable size of every database, largest first
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;
Table and index sizes
Top tables by total size including indexes and TOAST
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS total, pg_size_pretty(pg_relation_size(oid)) AS table FROM pg_class WHERE relkind='r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;
Find unused indexes
Indexes never scanned (idx_scan=0); candidates to drop and reclaim space
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes WHERE idx_scan=0 ORDER BY pg_relation_size(indexrelid) DESC;
Check autovacuum status
Find tables with dead tuples and stale autovacuum runs
SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;
Reindex without locking
Rebuild a bloated index online; note: must run outside a transaction
REINDEX INDEX CONCURRENTLY <name>; -- note: cannot run inside a transaction block
Create index online
Build index without blocking writes; track progress via pg_stat_progress_create_index
CREATE INDEX CONCURRENTLY idx_<name> ON <table>(col); -- monitor: SELECT * FROM pg_stat_progress_create_index;
Parallel dump and restore
Custom-format parallel dump/restore; note: -j needs directory or custom format, not plain SQL
pg_dump -Fc -j 4 -d <name> -f db.dump && pg_restore -j 4 -d <name> db.dump
Benchmark with pgbench
Run 60s load test with 32 clients across 8 threads, progress every 5s
pgbench -c 32 -j 8 -T 60 -P 5 -d <name>; -- init once: pgbench -i -s 100 <name>
no commands match