Notes on a long-running VACUUM

A roughly 400 GB partitioned table on Postgres 16 had been bloated for weeks. pg_stat_user_tables said n_dead_tup was within an order of magnitude of the live count, so a regular autovacuum was either being skipped or losing the race against the write load. The plan was a controlled VACUUM (FULL, VERBOSE) on a Saturday night. It did not finish that Saturday night.

what went wrong

First mistake: running VACUUM FULL on the parent partitioned table. That doesn't recurse the way you'd expect — it locks the parent, then walks children one by one, holding an ACCESS EXCLUSIVE lock for the duration. With a hot write path still pointed at the same table, the lock queue grew and a handful of long-running selects piled up behind it.

Second mistake: not pre-checking maintenance_work_mem. The cluster default was 64 MB. For a table of this size, that means VACUUM repeatedly re-scans the heap to clean dead tuples in small batches. Bumping it to 2 GB cut the per-partition wall-clock by roughly 6×.

what actually worked

• Detach each partition first, VACUUM FULL on the detached child, re-attach. No long-held lock on the parent, no queue.
• SET maintenance_work_mem = '2GB'; in the session — process-local, no postgresql.conf reload needed.
• pg_repack for two of the largest children. The detach-and-rebuild approach is faster, but pg_repack doesn't need a maintenance window.

what I'd do differently

Tune autovacuum aggressiveness on a per-table basis before the table is this large. Specifically, lower autovacuum_vacuum_scale_factor from the default 0.2 to something like 0.02 for any table over ~10 GB. The cost-based delay is the next dial to touch (autovacuum_vacuum_cost_limit), but it's the one I usually leave alone first.

A reminder I keep forgetting: autovacuum is not a backup, and it is not magic. It's a budgeted background task. If the budget is too small, it loses to the workload, and the only way to know is to look.