Logo ← PostgreSQL Blog

Top 20 PostgreSQL Maintenance Commands Every DBA Should Know

This guide provides the 20 most essential PostgreSQL maintenance commands, focusing on locking behavior, performance impact, safe execution…

Top 20 PostgreSQL Maintenance Commands Every DBA Should Know

This guide provides the 20 most essential PostgreSQL maintenance commands, focusing on locking behavior, performance impact, safe execution techniques, and real-world production considerations.

Table of Contents

  1. VACUUM
  2. VACUUM FULL
  3. ANALYZE
  4. REINDEX
  5. REINDEX CONCURRENTLY
  6. pg_repack
  7. CHECKPOINT
  8. CLUSTER
  9. pg_stat_statements
  10. EXPLAIN / EXPLAIN ANALYZE
  11. pg_dump
  12. pg_restore
  13. psql meta-commands
  14. ALTER INDEX SET STATISTICS
  15. REFRESH MATERIALIZED VIEW
  16. VIEW autovacuum settings
  17. pg_wal inspection commands
  18. Monitoring table-level statistics
  19. Killing long-running queries safely
  20. Checking table and index bloat

1. VACUUM

VACUUM;

Purpose:
 Removes dead tuples left after UPDATE/DELETE. Prevents table bloat.

Locking:
 Lightweight lock (ACCESS SHARE). Does not block normal queries. Also, you can read my PostgreSQL Locks content.

Use in production:
 Safe anytime. Autovacuum usually handles this, but manual runs help for heavily updated tables.

2. VACUUM FULL

VACUUM FULL table_name;

Purpose:
 Completely rewrites the table and physically shrinks it.

Locking:
 Takes an ACCESS EXCLUSIVE lock.
 Blocks all reads and writes.

Use in production:
 Avoid unless absolutely necessary.
 Use pg_repack instead when possible.

3. ANALYZE

ANALYZE;

Purpose:
 Updates table statistics so the planner makes optimal decisions.

Locking:
 ACCESS SHARE (non-blocking).

Use in production:
 Safe. Run after bulk loads or major updates.

4. REINDEX

REINDEX INDEX idx_name;
REINDEX TABLE table_name;

Purpose:
 Rebuilds corrupted or bloated indexes.

Locking:
 ACCESS EXCLUSIVE (blocks reads and writes).

Use in production:
 Not recommended on busy systems.
 Use REINDEX CONCURRENTLY instead.

5. REINDEX CONCURRENTLY

REINDEX INDEX CONCURRENTLY idx_name;
REINDEX TABLE CONCURRENTLY table_name;

Purpose:
 Rebuilds an index without blocking reads and writes.

Locking:
 Minimal locking.
 Does not take ACCESS EXCLUSIVE.

Drawbacks:
 Slower, requires two index builds internally.

Use in production:
 Preferred for 24/7 systems.

6. pg_repack

pg_repack --table=mytable --dbname=mydb

Purpose:
 Shrinks tables and indexes with almost zero locking.

How it works:
 Creates a shadow table and swaps it at the end (very short lock).

Use in production:
 Best way to remove bloat without downtime.

Note:
 Requires extension installation.

7. CHECKPOINT

CHECKPOINT;

Purpose:
 Forces PostgreSQL to write dirty buffers to disk.

Locking:
 None.

Use in production:
 Typically unnecessary; PostgreSQL handles this automatically.

Useful during maintenance windows after heavy operations.

8. CLUSTER

CLUSTER table_name USING index_name;

Purpose:
 Physically reorder table according to an index.

Locking:
 ACCESS EXCLUSIVE → blocks all activity.

Use in production:
 Only in maintenance windows.
 Works very well for time-series tables.

9. pg_stat_statements

SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;

Purpose:
 Identify slow queries, most frequent queries, highest load.

Use in production:
 Critical for performance tuning.

10. EXPLAIN / EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS) SELECT ...

Purpose:
 Shows how PostgreSQL executes a query.

Use cases:

  • Detect sequential scans
  • Check bad index usage
  • Review buffer hits and disk I/O

Use in production:
 EXPLAIN: safe
 EXPLAIN ANALYZE: executes the query; caution on heavy queries.

11. pg_dump

pg_dump --create --clean --if-exists --format=d --jobs=2 --verbose --file=/pg_backup/31102024*******db.dump postgres://postgres:dummy@**.**.**.**:****/*****db

Purpose:
 Logical backups.

Locking:
 Minimal, ACCESS SHARE.
 Does not block writes.

Use in production:
 Safe.

12. pg_restore

pg_restore --verbose -n public --clean --if-exists --format=d --dbname=postgres://postgres:dummy@**.**.**.**:****/*****db 31102024*******db.dump


# -n only use specific schema restore

Purpose:
 Restore logical backups.

Use in production:
 Used for migration and disaster recovery tests.

13. psql Meta Commands

\d table
\di index
\l

Purpose:
 Fast internal inspection of schema and objects. Psql Cheat Sheet

Use in production:
 Daily DBA routine.

14. ALTER INDEX SET STATISTICS

ALTER TABLE table ALTER COLUMN col SET STATISTICS 500;

Purpose:
 Improves planner accuracy on skewed data.

Use in production:
 Used when queries choose the wrong index.

15. REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW mv_name;

Locking:
 ACCESS EXCLUSIVE → blocks reads.

Non-blocking version:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_name;

Requires unique index on MV.

16. Viewing Autovacuum Settings

SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL;

Purpose:
 Shows per-table autovacuum overrides.

Use in production:
 Critical if tables are not autovacuuming properly.

17. WAL Inspection Commands

SELECT pg_current_wal_lsn();
SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0');

Purpose:
 Monitor WAL growth and replication lag.

Use in production:
 Essential in HA setups (Patroni, repmgr, streaming replication).

18. Monitoring Table Statistics

SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_statio_user_tables;

Purpose:
 Find dead tuples, sequential scans, buffer hits.

Use in production:
 Detect tables needing vacuum or reindex.

19. Terminating Long Queries

-- Find PID 
SELECT 
    pid,
    usename AS username,
    datname AS database_name,
    application_name,
    client_addr,
    backend_start,
    state,
    wait_event_type,
    wait_event,
    query_start,
    query
FROM 
    pg_stat_activity
WHERE 
    state = 'active'
ORDER BY 
    query_start DESC;



-- Only cancel the query
SELECT pg_cancel_backend(12345);



-- terminate the session running the specified query
SELECT pg_terminate_backend(12345);



-- terminate idle connections
WITH idle_connections AS (
    SELECT pid, now() - state_change AS idle_duration
    FROM pg_stat_activity
    WHERE state = 'idle'
)
SELECT pg_terminate_backend(pid)
FROM idle_connections
WHERE
    (SELECT count(*) FROM idle_connections) > 450
    AND (
        (SELECT count(*) FROM idle_connections) > 600 AND idle_duration > interval '3 minutes'
        OR (SELECT count(*) FROM idle_connections) > 475 AND idle_duration > interval '5 minutes'
        OR (SELECT count(*) FROM idle_connections) > 460 AND idle_duration > interval '10 minutes'
        OR (SELECT count(*) FROM idle_connections) > 450 AND idle_duration > interval '15 minutes'
    );



-- terminate all active queries
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND state = 'active';



-- terminate queries running longer than 5 minutes from Bill Coulam
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
  AND state = 'active'
  AND query_start < clock_timestamp() - interval '5 minutes';

Purpose:
 Kill blocking or runaway queries.

Use in production:
 Use carefully; may trigger rollbacks.

20. Checking Bloat


WITH table_stats AS (
    SELECT
        relname AS table_name,
        n_live_tup,
        n_dead_tup,
        pg_total_relation_size(relid) AS total_size,
        pg_relation_size(relid) AS table_size
    FROM
        pg_stat_user_tables
    WHERE
        schemaname = '*****'
        AND relname = '****'
)
SELECT
    table_name,
    total_size,
    table_size,
    n_live_tup,
    n_dead_tup,
    ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_tuple_ratio
FROM
    table_stats;

Purpose:
 Measure table and index bloat precisely.

Use in production:
 Combine with REINDEX, pg_repack, and autovacuum tuning.