Generated Oct 3, 2026, 12:35:03 AM
Check for long-lived transactions: run SELECT pid, state, query_start, age(now(), query_start) FROM pg_stat_activity WHERE state <> 'idle' OR query_start IS NOT NULL; if any long-running non-idle queries exist, identify and coordinate safe termination.
Inspect replication slots and prepared transactions: SELECT * FROM pg_replication_slots; SELECT * FROM pg_prepared_xacts; if slots are holding WAL and are stale, coordinate with application/DBA before dropping or advancing them.
Enable more aggressive autovacuum for the affected table(s) as a reversible configuration change: ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 50); monitor effect for 24–48 hours.
Use a non-blocking table rewrite tool such as pg_repack (preferred) or perform chunked UPDATE/INSERT-SELECT to rewrite partitions rather than VACUUM FULL. Test on a copy/standby first and ensure backups exist before performing on production.
If immediate space reclamation is required during a maintenance window, perform VACUUM FULL on a single partition at a time to reduce lock impact; ensure a recent backup exists and communicate downtime to stakeholders — this is reversible only by restoring from backup if it causes issues but will free space immediately when successful. Consider scheduling during low traffic window and validate application after each partition processed before proceeding to next partition. Escalate to a DBA if replication slots or prepared transactions appear to be root cause or if you cannot safely stop long-running transactions.