PostgreSQL autovacuum ignores newly created partitions causing rapid bloat and slow queries on recent partitions

Environment

PostgreSQL 14.5 on Ubuntu 22.04 running on a single-primary VM with 32GB RAM. Table is range-partitioned by month with new partitions created via a background job. autovacuum is enabled with mostly-default settings; autovacuum_max_workers=3.

Symptoms

Queries that target the latest partitions (last 1-2 months) are becoming progressively slower over days. Disk usage for the table grows unexpectedly, and index bloat increases only on the newest partitions. Long-running SELECTs on the newest month incur many dead tuples.

Observations

pg_stat_all_tables shows autovacuum_count = 0 for the most recent partitions; last_autovacuum is NULL for several new partitions even after creation and heavy update/delete activity. Manually running VACUUM on a frozen partition reduces bloat and restores performance. There are no long-running transactions in pg_stat_activity at the times checked. autovacuum logs show normal activity for older partitions. The background job that creates partitions uses plain CREATE TABLE ... INHERITS or declarative partition CREATE TABLE FOR VALUES.

Already attempted

Forced VACUUM and ANALYZE on affected partitions (immediate improvement). Increased autovacuum_naptime and lowered autovacuum_vacuum_threshold on one partition manually then reverted (no long-term effect for other new partitions). Restarted PostgreSQL service and verified autovacuum launcher restarted (no change). Reviewed cron job that creates partitions — it sets default WITH (autovacuum_enabled = true) but uses table inheritance in one path and declarative partitions in another.

Question

Why is autovacuum not picking up the newly created partitions automatically? What diagnostics or configuration changes will ensure autovacuum recognizes and maintains new partitions without manual VACUUMs, and how to safely recover current bloat?

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

Autovacuum cost/threshold settings too conservative combined with low autovacuum_max_workers so new partitions aren't scheduled promptly. 75%

Partitions created with autovacuum_enabled = false (explicitly or inherited default) so autovacuum never runs on them. 50%

A long-running transaction or replication slot keeps old xmin, preventing vacuum from reclaiming and thus autovacuum defers work. 10%

Summary

Autovacuum skipping only the newest partitions suggests either the partitions were created with autovacuum disabled at creation, partitions were created as inheritance tables that weren't registered the same way for autovacuum, or autovacuum is being prevented by a long-running transaction or replication slot referencing older snapshots, leaving dead tuples visible to the system. The fact manual VACUUM fixes the issue indicates autovacuum can work but is simply not triggered for those relations. Differences in partition creation method (INHERITS vs declarative) or per-table GUC overrides at creation time are common culprits.

Suggested Fixes

Diagnostic step 1

Check per-partition reloptions: SELECT relname, reloptions FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relname LIKE 'your_partition_prefix%'; ensure autovacuum_enabled is not false.

Diagnostic step 2

Inspect pg_stat_activity and pg_replication_slots for long-running transactions or replication slots holding old xmin; terminate or advance replication where appropriate before vacuuming.

Diagnostic step 3

Enable autovacuum logging (log_autovacuum_min_duration = 0) temporarily and monitor logs when new partitions are created to see if autovacuum schedules them.