PostgreSQL 14: Rapid table bloat after nightly batch UPDATEs despite autovacuum enabled

Environment

Ubuntu 20.04 LTS, PostgreSQL 14.5 installed from PGDG repository on a bare-metal server (8 cores, 32 GB RAM, 2 x 2 TB NVMe, ext4). Single database with a monthly-partitioned table ~120M rows; autovacuum left at package defaults.

Symptoms

After nightly batch UPDATE that touches ~10% of the partitioned table, total database disk usage increases by ~25–40 GB overnight. User SELECTs on the updated partitions slow down (higher latency) and free space on the filesystem does not return. Autovacuum appears to run but does not reclaim the newly created dead tuples.

Observations

pg_stat_user_tables shows high n_dead_tup on affected partitions; pgstattuple reports ~30–45% bloat on those partitions. autovacuum logs show multiple autovacuum worker runs with no significant reduction in dead tuples. No long-running normal transactions visible in pg_stat_activity. No prepared transactions reported. Replication is streaming synchronous to one standby with replication_slot present, slot not listed as inactive. Manual VACUUM FULL on a partition reclaims space but requires exclusive locks and disrupts services.

Already attempted

Restarted PostgreSQL service (short maintenance window) — no effect on bloat. Ran manual VACUUM (non-FULL) which improved visibility but did not fully reclaim space. Increased autovacuum_vacuum_cost_delay and autovacuum_vacuum_scale_factor on a test instance (no production change). Verified no open prepared transactions and checked pg_replication_slots for long-held slots. Took a physical backup before trying VACUUM FULL on one partition.

Question

What is the most likely root cause of the rapid bloat despite autovacuum being active, and what safe, reversible remediation steps will reclaim space and prevent recurrence without prolonged downtime?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely causes

Bulk UPDATE pattern touches large portions of table at once, creating massive dead tuple churn faster than autovacuum can keep up with under defaults, and TOASTed values accumulating separately. 90%

Default autovacuum settings too conservative for high-update batch workload (scale_factor and thresholds inadequate). 60%

Replication or logical replication slots holding WAL or visibility preventing truncation of dead tuples. 20%

Summary

UPDATE in PostgreSQL produces dead tuples that must be vacuumed away by autovacuum. If dead tuples cannot be removed (or visibility map/cleanup hindered), disk usage grows. Common blockers: long-lived transactions or replication/logical slots preventing VACUUM from truncating, very conservative autovacuum thresholds not keeping up with churn, bulk UPDATE patterns that touch most rows causing many dead tuples in a short window, or TOAST/large-row interaction. Manual VACUUM FULL reclaims space by rewriting the table but requires an exclusive lock, which fixes space but is disruptive. A safer pattern is to remove blockers, tune autovacuum for aggressive cleanup, and rewrite the table in a minimally disruptive way (e.g., pg_repack or concurrent chunked rewrite).

Suggested Fixes

Diagnostic step 1

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.

Diagnostic step 2

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.

Diagnostic step 3

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.