Vacuum Best Practices

Q: Disable autovacuum on load test?

Only isolated clone; never production fleet.

Q: freeze max age tuning?

Lower on static archives if needed; default OLTP usually fine.

Q: vacuum vs analyze order?

VACUUM ANALYZE together post bulk ops is fine.

Q: standby bloat?

Primary vacuum advances horizons; replicas do not vacuum user tables.

Q: toast tables?

Monitor reltoastrelid size alongside heap.

Q: cloud autovacuum?

Tune per-table anyway on large facts.

Q: partitioned vacuum?

Each partition vacuums independently; tune parent settings.

Q: manual vacuum role?

Superuser or pg_maintain role with vacuum privileges.

Q: Next group?

Connection pooling for workload isolation from vacuum-heavy batch.

Healthy vacuum is invisible until it fails. Follow this checklist to stay ahead of bloat, wraparound, and index-only scan regressions on PostgreSQL 18.4.

How to Use This List

Review top 20 tables by update rate monthly.
Alert on age(datfrozenxid) and high n_dead_tup ratios.
Never ship migrations that disable autovacuum without expiry plan.

A - Autovacuum Policy

Keep autovacuum enabled globally and per table. No silent disable on facts.
Lower autovacuum_vacuum_scale_factor on large hot tables. Default 20% is too slow at scale.
Increase workers/IOPS headroom before raising autovacuum_max_workers. Avoid vacuum/OLTP fights.
Log autovacuum runs over duration threshold. Find stragglers.
Manual VACUUM ANALYZE after bulk delete or COPY. Before reopening BI.

B - Transaction Hygiene

Set idle_in_transaction_session_timeout on app roles. Long transactions block freeze.
Monitor replication slots and logical decoding lag. Slots hold xmin on primary.
Kill orphaned app pool sessions stuck in transaction. PgBouncer cannot fix server-side idle.
Avoid all-day open transactions in admin tools. Dashboard SQL tabs included.
Track pg_stat_activity.xact_start in incidents. Correlate vacuum lag.

C - Bloat Response

Measure dead tuple ratio and toast size before VACUUM FULL. Prefer in-place vacuum first.
Use pg_repack or partition swap for OS-level shrink. When live rows << file size.
REINDEX CONCURRENTLY when index bloat proven. After vacuum tuned.
Consider fillfactor on extreme update tables. Plan for HOT updates.
Project columns in APIs; avoid SELECT * on TOAST-wide tables. Cut I/O at source.

D - Monitoring

Dashboard age(datfrozenxid) and top age(relfrozenxid). Wraparound early warning.
Alert when hot table has no autovacuum in 24h despite churn. Worker starvation signal.
Include vacuum metrics in post-incident reviews. Not only query tuning.
Document per-table autovacuum overrides in catalog spreadsheet. On-call clarity.
Test vacuum behavior after major version upgrades. Planner and vacuum changes ship together.

FAQs

Disable autovacuum on load test?

Only isolated clone; never production fleet.

VACUUM FULL schedule?

Last resort; prefer pg_repack.

freeze max age tuning?

Lower on static archives if needed; default OLTP usually fine.

vacuum vs analyze order?

VACUUM ANALYZE together post bulk ops is fine.

standby bloat?

Primary vacuum advances horizons; replicas do not vacuum user tables.

toast tables?

Monitor reltoastrelid size alongside heap.

cloud autovacuum?

Tune per-table anyway on large facts.

partitioned vacuum?

Each partition vacuums independently; tune parent settings.

manual vacuum role?

Superuser or pg_maintain role with vacuum privileges.

Next group?

Connection pooling for workload isolation from vacuum-heavy batch.

Vacuum Basics - Mechanics
Autovacuum Tuning - Parameters
Transaction ID Wraparound - Freeze alerts
Table & Index Bloat - Remediation

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Vacuum Best Practices

Healthy vacuum is invisible until it fails. Follow this checklist to stay ahead of bloat, wraparound, and index-only scan regressions on PostgreSQL 18.4.

How to Use This List

Review top 20 tables by update rate monthly.
Alert on age(datfrozenxid) and high n_dead_tup ratios.
Never ship migrations that disable autovacuum without expiry plan.

A - Autovacuum Policy

Keep autovacuum enabled globally and per table. No silent disable on facts.
Lower autovacuum_vacuum_scale_factor on large hot tables. Default 20% is too slow at scale.
Increase workers/IOPS headroom before raising autovacuum_max_workers. Avoid vacuum/OLTP fights.
Log autovacuum runs over duration threshold. Find stragglers.
Manual VACUUM ANALYZE after bulk delete or COPY. Before reopening BI.

B - Transaction Hygiene

Set idle_in_transaction_session_timeout on app roles. Long transactions block freeze.
Monitor replication slots and logical decoding lag. Slots hold xmin on primary.
Kill orphaned app pool sessions stuck in transaction. PgBouncer cannot fix server-side idle.
Avoid all-day open transactions in admin tools. Dashboard SQL tabs included.
Track pg_stat_activity.xact_start in incidents. Correlate vacuum lag.

C - Bloat Response

Measure dead tuple ratio and toast size before VACUUM FULL. Prefer in-place vacuum first.
Use pg_repack or partition swap for OS-level shrink. When live rows << file size.
REINDEX CONCURRENTLY when index bloat proven. After vacuum tuned.
Consider fillfactor on extreme update tables. Plan for HOT updates.
Project columns in APIs; avoid SELECT * on TOAST-wide tables. Cut I/O at source.

D - Monitoring

Dashboard age(datfrozenxid) and top age(relfrozenxid). Wraparound early warning.
Alert when hot table has no autovacuum in 24h despite churn. Worker starvation signal.
Include vacuum metrics in post-incident reviews. Not only query tuning.
Document per-table autovacuum overrides in catalog spreadsheet. On-call clarity.
Test vacuum behavior after major version upgrades. Planner and vacuum changes ship together.

FAQs

Disable autovacuum on load test?

Only isolated clone; never production fleet.

VACUUM FULL schedule?

Last resort; prefer pg_repack.

freeze max age tuning?

Lower on static archives if needed; default OLTP usually fine.

vacuum vs analyze order?

VACUUM ANALYZE together post bulk ops is fine.

standby bloat?

Primary vacuum advances horizons; replicas do not vacuum user tables.

toast tables?

Monitor reltoastrelid size alongside heap.

cloud autovacuum?

Tune per-table anyway on large facts.

partitioned vacuum?

Each partition vacuums independently; tune parent settings.

manual vacuum role?

Superuser or pg_maintain role with vacuum privileges.

Next group?

Connection pooling for workload isolation from vacuum-heavy batch.

Vacuum Basics - Mechanics
Autovacuum Tuning - Parameters
Transaction ID Wraparound - Freeze alerts
Table & Index Bloat - Remediation

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Vacuum Best Practices

Search Documentation

Vacuum Best Practices