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;"
tool
postgres29 commands
tags
postgresperformance

More postgres commands

  • 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

  • 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

  • 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

all 29 postgres commands