Weekly PostgreSQL Maintenance

1. Review vacuum and analyze history

Autovacuum and autoanalyze should normally handle routine table maintenance. Check the latest run, live/dead tuple estimates, and table size. Estimates are approximate; inspect trends and workload context rather than treating a single dead-tuple count as an error.

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC NULLS LAST
LIMIT 30;

2. Diagnose maintenance lag

1. Check table activity, write rate, oldest transaction, and replication slots retaining WAL.
2. Review PostgreSQL logs and autovacuum configuration for cancellations, long runtimes, or thresholds too high for hot tables.
3. Check disk headroom and I/O before starting manual work.
4. Coordinate with application owners if long transactions or idle-in-transaction sessions prevent cleanup.
5. After an approved change, verify statistics and database health again.

Use a targeted command only when needed, during a suitable window:

VACUUM (ANALYZE, VERBOSE) public.orders;

Ordinary VACUUM does not return all table space to the operating system. Do not run VACUUM FULL as a routine fix: it rewrites and exclusively locks the table.

3. Weekly checks for indexes and storage

Review table and index size trends, unused-index candidates, failed jobs, backup verification, and storage utilization. Index statistics reset and workload coverage matters: a low scan count by itself is not proof that an index can be removed.

SELECT schemaname, relname AS table_name,
       indexrelname AS index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 30;

Use weekly checks to open tracked work items with evidence and owners; do not drop indexes or alter autovacuum settings without a tested plan.

Weekly checklist

• Confirm backups completed and at least one recent restore validation is scheduled or recorded.
• Review dead-tuple and analyze trends on high-write tables.
• Review long-running transactions, replication slots, and WAL/disk growth.
• Check scheduled maintenance, failed jobs, and index/table size outliers.
• Update issue records, action owners, and next review dates.