Planning maintenance

Edit online

Use VACUUM, ANALYZE, and VACUUM FULL based on bloat level, data change, and downtime availability to keep PostgreSQL healthy while minimizing performance impact.

Key points:

VACUUM: Use for routine cleanup. It is safe to run online.
ANALYZE: Run when data distribution changes significantly.
VACUUM FULL: Use only to reclaim OS disk space or after heavy bloat. It requires downtime.

Here’s a simplified decision tree for PostgreSQL maintenance:

Figure 1. idspgmaint usage decision tree for PostgreSQL maintenance

Here’s a more detailed maintenance plan table:


#	Condition / Trigger	Action	Reasoning Note	Recommended thresholds / starting points	Verification
1	Regular health sweep (daily/weekly)	Review table stats (dead tuples, last autovacuum/analyze)	Early signal if autovacuum is keeping up; prevents surprise bloat and wraparound pressure	Look for n_dead_tup rising without recent autovacuum/analyze; focus on the largest & hottest tables first	Check pg_stat_all_tables for n_dead_tup, last_autovacuum, last_analyze [Understand...all_tables]
2	Routine cleanup; minimal bloat (<~20%); online requirement	VACUUM	Online maintenance to reclaim dead tuples for reuse and update the visibility map, with minimal locking	Run during normal hours on busy tables; parallel vacuum can help large tables	Monitor progress via pg_stat_progress_vacuum; expect no OS free‑space return [PostgreSQL...17: VACUUM], [PostgreSQL...Reporting]
3	Stats drift after large loads or skew changes	ANALYZE (or VACUUM (ANALYZE))	Keeps planner stats accurate for better query plans	After bulk‑load/ETL, or ≥10–20% data distribution change	Confirm via pg_stat_progress_analyze / improved plan quality [PostgreSQL...Reporting]
4	Significant table bloat (e.g., ≥20–30%) and a maintenance window exists; need to return disk to OS	VACUUM FULL	Rewrites table, compacts pages, returns space to the OS; requires ACCESS EXCLUSIVE lock	Use sparingly; schedule a window; ensure 1x table free space during rewrite	Verify reclaimed size via relation size before/after; lock expectations documented in VACUUM docs [PostgreSQL...17: VACUUM]
5	Index bloat / poor locality; hot OLTP needs online ops	REINDEX (prefer CONCURRENTLY for online)	Rebuilds bloated/fragmented indexes; CONCURRENTLY lowers blocking at the cost of runtime	Consider after heavy churn or VACUUM FULL (non‑concurrent ok inside window)	Track pg_stat_progress_create_index during build; confirm size/scan efficiency after [PostgreSQL...Reporting]
6	Autovacuum lag on large, high‑churn tables	Tune per‑table autovacuum storage parameters	Default triggers (20% scale factor) are too high for very large tables; earlier/autonomous cleanup reduces bloat buildup	Start with lower scale factors for “big & hot” tables: vacuum_scale_factor 0.02–0.05, analyze_scale_factor 0.05–0.1; raise autovacuum_vacuum_threshold modestly (e.g., 500–2000) to avoid over‑triggering	Validate with the trigger formula and watch last_autovacuum cadence; tune iteratively [PostgreSQL...Vacuuming], [PostgreSQL...Processes], [How to Set...PostgreSQL]
7	Cluster‑wide pressure: many busy tables	Tune global autovacuum workers, naptime, and cost settings	More workers & shorter naptime increase coverage; cost settings balance churn vs. impact	autovacuum_max_workers 3→5–8, autovacuum_naptime 60s→30–60s, autovacuum_cost_limit 2000–5000, autovacuum_cost_delay 5–10ms on adequate I/O	Observe I/O & latency; ensure not starving foreground queries; adjust gradually [PostgreSQL...Vacuuming, [How to Set...PostgreSQL]
8	Need fast bloat signal	Use pgstattuple (or pgstattuple_approx) for estimates	Low‑cost approximation to decide where to focus; exact function scans the relation	Prefer pgstattuple_approx for speed; run exact only for final checks	Enable extension and query dead/free space %; restrict to privileged roles per docs [F.31. pgst...PostgreSQL], [Estimating...PostgreSQL]

For more information about the idspgmaint utility, see idspgmaint.