Cheatsheet for Postgres maintenance. Mostly handled by autovacuum; sometimes manual is needed.

VACUUM basics

VACUUM users;             -- reclaim deleted rows
VACUUM ANALYZE users;     -- + update planner stats
VACUUM FULL users;        -- rewrites table; takes exclusive lock (avoid in prod)

VACUUM FULL reclaims space to OS but locks. Use pg_repack instead for online.

Autovacuum

Enabled by default. Tune per-table:

__table_args__ = (
    {"postgresql_with": {
        "autovacuum_vacuum_scale_factor": 0.05,    # vacuum when 5% of table is dead
        "autovacuum_analyze_scale_factor": 0.02,
        "autovacuum_vacuum_threshold": 1000,
        "autovacuum_analyze_threshold": 500,
    }},
)

For high-churn tables: lower thresholds.

ANALYZE

ANALYZE users;
ANALYZE users (email);   -- specific column

Updates statistics used by the query planner. Run after bulk inserts.

REINDEX

-- Single index
REINDEX INDEX CONCURRENTLY ix_users_email;

-- Whole table
REINDEX TABLE CONCURRENTLY users;

-- Schema or database (non-concurrent)
REINDEX SCHEMA public;
REINDEX DATABASE myapp;

CONCURRENTLY is online. For heavily-bloated indexes.

Find bloat

SELECT
    schemaname, tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size,
    n_dead_tup, n_live_tup
FROM pg_stat_user_tables
JOIN pg_class c ON c.relname = tablename
ORDER BY n_dead_tup DESC LIMIT 20;

High n_dead_tup vs n_live_tup = bloated.

pgstattuple (precise bloat)

CREATE EXTENSION pgstattuple;
SELECT * FROM pgstattuple('users');

dead_tuple_percent field tells the story.

pg_repack (online table rewrite)

pg_repack -h db -U user -d myapp -t huge_table

Online table compaction. No exclusive lock. Use periodically for heavy-write tables.

For partitioned tables: repack each partition.

VACUUM FREEZE / wraparound

Postgres uses transaction IDs (32-bit). After ~2B transactions: wraparound.

Autovacuum runs aggressive VACUUM FREEZE proactively. If you see autovacuum_freeze_max_age warnings: don’t disable autovacuum on those tables.

SELECT
    relname,
    age(relfrozenxid) AS xid_age,
    age(relfrozenxid) > 2_000_000_000 AS approaching_wraparound
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY age(relfrozenxid) DESC LIMIT 20;

Index usage stats

SELECT
    schemaname, tablename, indexname,
    idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC LIMIT 20;

Identify unused indexes (idx_scan = 0).

Stats reset

SELECT pg_stat_reset();   -- whole DB
SELECT pg_stat_reset_single_table_counters('users'::regclass);

After a major schema change, reset to measure fresh.

Long-running queries

SELECT pid, age(clock_timestamp(), query_start), state, query
FROM pg_stat_activity
WHERE state = 'active' AND query_start < now() - interval '5 minutes'
ORDER BY query_start;

Kill if needed:

SELECT pg_cancel_backend(<pid>);    -- gentle
SELECT pg_terminate_backend(<pid>); -- forceful

Locks held

SELECT
    bl.locktype, bl.relation::regclass,
    bl.transactionid, bl.virtualtransaction, bl.pid,
    bl.mode, bl.granted,
    a.query, a.state, age(clock_timestamp(), a.query_start) AS age
FROM pg_locks bl
JOIN pg_stat_activity a ON a.pid = bl.pid
WHERE NOT bl.granted
ORDER BY age DESC;

Find blocked queries and their blockers.

Maintenance window practices

  • Low-traffic window (off-hours).
  • Communicate to ops.
  • Monitor replica lag during the op.
  • Have rollback / abort plan.

Automation

-- pg_cron schedules
SELECT cron.schedule('vacuum-tables', '0 3 * * *', $$VACUUM ANALYZE users$$);
SELECT cron.schedule('reindex-weekly', '0 4 * * 0', $$REINDEX TABLE CONCURRENTLY users$$);

Or via cron + psql.

Common mistakes

  • VACUUM FULL in production — exclusive lock; pg_repack instead.
  • Disabling autovacuum — wraparound risk; bloat.
  • REINDEX without CONCURRENTLY on hot tables.
  • Not monitoring xid_age — surprise wraparound.

Read this next

If you want my maintenance runbook + pg_cron schedule template, it’s at rajpoot.dev .

Building something AI-, backend-, or data-heavy and want a second pair of eyes? I do consulting and freelance work — see my projects and ways to reach me at rajpoot.dev .