1
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.
FULL AI ANALYSIS
PostgreSQL autovacuum ignores newly created partitions causing rapid bloat and slow queries on recent partitions
Generated Oct 3, 2026, 12:35:03 AM
Analysis 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.
Possible 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%A bug in how the partition creation job creates partitions (e.g., temporary tables or wrong tablespace) so they are not visible to autovacuum launcher until service restart.
5%Suggested fixes
2
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.
3
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.
4
Diagnostic step 4
If partitions were created by a script, review and standardize creation to ensure declarative partitioning or include ALTER TABLE ... SET (autovacuum_enabled = true) immediately after creation.
5
Diagnostic step 5
As an immediate remediation, schedule a controlled manual VACUUM ANALYZE for new partitions during low-traffic windows. If you suspect a deeper catalog visibility bug, capture server logs and consider escalating to a DBA or opening a PostgreSQL support ticket with the logs and a minimal repro.