PostgreSQL uses multiversion concurrency control (MVCC). Updates and deletes leave old row versions that cannot be removed until they are no longer visible to active transactions. VACUUM reclaims reusable space and helps prevent transaction ID wraparound. Poor vacuum behavior can contribute to table bloat, slower queries and growing storage use.
Check whether vacuum is keeping up
Inspect pg_stat_user_tables for dead tuple estimates, vacuum timestamps and modification counts. Review autovacuum logs and long-running transactions. A transaction that remains open for a long time can prevent cleanup of row versions that might still be visible to it.
Understand autovacuum thresholds
Autovacuum is triggered using a base threshold plus a scale factor related to table size. A default scale factor may be too high for very large, frequently updated tables because it allows many dead tuples to accumulate before vacuum starts. Per-table settings can be more appropriate than changing global behavior.
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01
);
Choose values using table size, write rate, available I/O and observed vacuum duration. Very aggressive settings can create I/O pressure; overly relaxed settings allow bloat to grow.
Use the right maintenance operation
Standard VACUUM makes space reusable inside the relation but generally does not return all free space to the operating system. VACUUM FULL rewrites the table and requires an exclusive lock, so it can cause substantial downtime. Consider online rewrite tools only after understanding their locking, replication and disk-space requirements.
Prevent vacuum starvation
Find long-running transactions, idle-in-transaction sessions and replication slots retaining old WAL or row versions. Monitor transaction age and plan capacity for maintenance. Never terminate sessions blindly on a production database; identify the owner and impact first.
Maintenance checklist
- Track dead tuples and vacuum frequency.
- Investigate long-running transactions.
- Tune large hot tables individually.
- Monitor I/O, WAL growth and replication lag.
- Test table-rewrite operations in staging.
Vacuum is routine database housekeeping, not a one-time cleanup command. Pair this runbook with PostgreSQL connection management and pgvector performance tuning.
Design and prerequisites
Understand the difference between reusable space and disk space returned to the operating system. Standard VACUUM makes dead-tuple space available for reuse inside a relation, but usually does not shrink the file. VACUUM FULL rewrites the table and can return space, but requires an exclusive lock and extra disk capacity. Before choosing a rewrite, estimate table size, available free space, replication impact and acceptable downtime. A cleanup command that runs out of disk midway can turn a bloat problem into an outage.
Configuration strategy
Autovacuum thresholds are calculated from a base threshold and a scale factor relative to table size. Large hot tables may accumulate many dead tuples before a default threshold is reached. Per-table settings can lower the threshold, but overly aggressive vacuum can consume I/O and compete with application queries. Use observed modification rates and vacuum duration to tune. Check whether autovacuum is being blocked by long-running transactions, replication slots or prepared transactions before simply increasing worker counts.
Security and compatibility
Monitor transaction age because transaction ID wraparound prevention is a critical safety function. PostgreSQL may force aggressive vacuum behavior when the oldest transaction approaches the wraparound limit. Alert on age and investigate sessions that remain open or idle in transaction. Replication slots can retain old row versions or WAL and should be monitored for inactive consumers. Do not drop a slot without understanding which replica or subscriber depends on it.
Testing and validation
Use pg_stat_user_tables and related statistics to compare dead tuple estimates, vacuum timestamps and analyze frequency. Statistics are estimates, so correlate them with table size, query plans and storage growth. Run ANALYZE when data distribution changes enough to affect planner estimates. A table may have little bloat but still suffer from poor plans because statistics are stale or skewed.
Monitoring and diagnosis
Schedule maintenance with awareness of production workload. Vacuum can run concurrently with ordinary reads and writes, but it still consumes I/O and CPU. Avoid launching many manual maintenance jobs at once on a busy database. Watch replication lag, storage latency and query performance during the operation. For partitioned tables, verify that maintenance reaches the partitions that receive writes; parent-level statistics may not tell the whole story.
Operations and maintenance
Create a runbook that distinguishes routine autovacuum tuning from emergency space reclamation. Include commands to inspect blocking transactions, table size, relation statistics and replication slots. Test any table-rewrite approach on a production-sized copy and verify backup and recovery first. After maintenance, compare storage use, query latency and dead tuple estimates with the baseline. Document the result so future operators can tell whether the change solved the root cause.
Operational handover and review
Before handing this implementation to an operations team, document the configuration owner, supported versions, dependency map and escalation path. Include the commands or dashboard views needed to verify normal operation, along with expected values and known limitations. Store configuration in version control, review changes through the normal deployment process and keep a tested rollback procedure. Schedule periodic reviews because upstream components, security requirements and workload patterns change. A short runbook should explain how to identify a failed component, what evidence to collect, which actions are safe to retry and when to escalate. Test that runbook during a maintenance exercise rather than waiting for an outage. Finally, measure the result against a baseline: reliability improvements should be visible in error rates, latency, resource consumption, recovery time or reduced manual work. This makes the implementation maintainable after the original developer has moved to another project.
Change management and production verification
Create a controlled change record for every production adjustment. Record the current value, intended value, reason, approver and expected impact before changing the live configuration. Use a staging environment or a representative test fixture to verify behavior, and compare the result against a known baseline. Keep rollback instructions beside the change record, not in an engineer’s private notes. If the change affects a shared service, coordinate the maintenance window with its consumers and define how they will report regressions.
Service-level observability
Build operational dashboards around outcomes rather than configuration alone. Track the service-level indicators that show whether the system is working: request success, latency, queue delay, resource pressure, error counts and recovery time. Add component-specific metrics where they help explain a failure, but avoid collecting high-cardinality labels that make monitoring expensive or unstable. Define alert thresholds with an owner and a response action. An alert that has no documented next step will eventually be ignored.
Failure testing and recovery
Test failure modes before declaring the work complete. Simulate a dependency outage, an invalid input, a restart during active work and a resource limit being reached. Confirm that the system fails safely, preserves recoverable state and produces useful logs. Verify that retries are bounded and that repeating an operation does not create duplicate side effects. Capture the observed behavior in a runbook so the next operator can distinguish a transient issue from a configuration defect.
Ongoing review
Review the design after the first production cycle. Compare expected and actual resource use, support incidents, maintenance effort and user impact. Remove temporary exceptions that are no longer required, update version pins deliberately and check upstream release notes before upgrades. Re-run the original validation suite after a dependency or runtime change. A technical implementation should remain understandable to a new engineer, so document trade-offs, known limitations and the reasons behind non-obvious settings.
Apply this review specifically to PostgreSQL VACUUM and Table Bloat: A Practical Maintenance Runbook: confirm the supported runtime and component versions, validate the behavior with a representative workload, and retain evidence from the test. Do not treat a successful configuration reload as proof that the user-facing workflow is healthy. Verify the end-to-end result from the same network path and identity used by the production application.
