postgres commands

29 commands, each with a one-line explanation. Click a command to copy it, or its title for details.

Commands 29 shown

  • Connect to database
    psql -h localhost -U myuser -d mydb

    Interactive psql session to a database

  • List databases
    psql -U postgres -c '\l+'

    Show all databases with size and encoding

  • List tables in schema
    psql -U myuser -d mydb -c '\dt public.*'

    List all tables in the public schema

  • EXPLAIN ANALYZE a query
    psql -U myuser -d mydb -c 'EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE status = $1;'

    Show query plan with actual timings and buffer usage

    debug · performance

  • Show slow queries (pg_stat_statements)
    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;"

    Top 10 slowest queries by total execution time

    monitoring · performance

  • Show active queries
    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;"

    Running queries with duration and truncated SQL

    monitoring

  • Kill long-running query
    psql -U postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE now()-query_start > interval '5 min' AND state = 'active';"

    Terminate all queries running longer than 5 minutes

    debug

  • Show table sizes
    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;"

    Top 20 tables by total size including indexes and TOAST

    monitoring

  • Show index usage
    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;"

    Which indexes are actually being used and how often

    monitoring · performance

  • Find unused indexes
    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;"

    Indexes that have never been scanned — safe to drop candidates

    performance

  • Dump database
    pg_dump -U myuser -d mydb -F c -f mydb.dump

    Custom-format dump (fastest, supports parallel restore)

  • Restore database
    pg_restore -U myuser -d mydb -j 4 --clean mydb.dump

    Parallel restore with 4 workers, drop existing objects first

  • Vacuum and analyze table
    psql -U myuser -d mydb -c 'VACUUM (VERBOSE, ANALYZE) orders;'

    Reclaim dead tuples and update statistics for query planner

    performance

  • Check replication lag
    psql -U postgres -c "SELECT client_addr, state, sent_lsn, replay_lsn, (sent_lsn - replay_lsn) AS lag_bytes FROM pg_stat_replication;"

    Replication state and byte lag per replica

    monitoring

  • Show locks
    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;"

    Active locks with blocking/blocked status and query

    debug

  • Find long-running queries
    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;

    List active queries running over 5 minutes, longest first

    performance · troubleshooting · monitoring

  • Kill a stuck backend
    SELECT pg_terminate_backend(<pid>); -- graceful first: SELECT pg_cancel_backend(<pid>);

    Force-kill a backend by pid; note: try pg_cancel_backend first to cancel the query only

    troubleshooting · performance

  • Top queries by total time
    SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

    pg_stat_statements: rank queries by cumulative execution time

    performance · observability · troubleshooting

  • EXPLAIN with buffers
    EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ... ; -- shared hit/read shows cache vs disk I/O

    Run query and show real timings plus buffer hits/reads to spot disk I/O

    performance · debug · disk

  • Blocking lock tree
    SELECT pid, pg_blocking_pids(pid) AS blocked_by, wait_event_type, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

    Show backends waiting on locks and which pids block them

    troubleshooting · performance · debug

  • Replication lag bytes
    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;

    Measure replica lag in bytes plus write/flush/replay lag intervals

    monitoring · observability · troubleshooting

  • Biggest databases
    SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;

    Human-readable size of every database, largest first

    disk · monitoring

  • Table and index sizes
    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;

    Top tables by total size including indexes and TOAST

    disk · performance · monitoring

  • Find unused indexes
    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;

    Indexes never scanned (idx_scan=0); candidates to drop and reclaim space

    performance · disk · troubleshooting

  • Check autovacuum status
    SELECT relname, n_dead_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20;

    Find tables with dead tuples and stale autovacuum runs

    performance · monitoring · troubleshooting

  • Reindex without locking
    REINDEX INDEX CONCURRENTLY <name>; -- note: cannot run inside a transaction block

    Rebuild a bloated index online; note: must run outside a transaction

    performance · disk

  • Create index online
    CREATE INDEX CONCURRENTLY idx_<name> ON <table>(col); -- monitor: SELECT * FROM pg_stat_progress_create_index;

    Build index without blocking writes; track progress via pg_stat_progress_create_index

    performance · monitoring

  • Parallel dump and restore
    pg_dump -Fc -j 4 -d <name> -f db.dump && pg_restore -j 4 -d <name> db.dump

    Custom-format parallel dump/restore; note: -j needs directory or custom format, not plain SQL

    performance · disk · troubleshooting

  • Benchmark with pgbench
    pgbench -c 32 -j 8 -T 60 -P 5 -d <name>; -- init once: pgbench -i -s 100 <name>

    Run 60s load test with 32 clients across 8 threads, progress every 5s

    performance · monitoring