09.09.2026 Problem:
PostgreSQL 15.17 running in a Patroni-managed cluster experienced autovacuum tasks running for five hours or more on very large, non-partitioned tables. Customer-provided artifacts included table size and index statistics, autovacuum logs, pg_settings, and pg_stat_all_tables. Symptoms reported: multi-hour autovacuum/vacuum runs on large tables, continuous cleanup script execution, and 10-hour REINDEX operations on bloated indexes. The system does not use table partitioning.
Process:
Step 1: Intake and artifact review
Observed the customer-provided CSVs and compressed vacuum logs alongside Patroni configuration. Noted repeated long vacuums and large table/index footprints; this established the investigation scope and confirmed work must respect Patroni-managed configuration layers.
Step 2: Vacuum log analysis
Inspected vacuum run entries in the logs and found these were aggressive anti-wraparound (freeze) vacuums rather than ordinary cleanup passes. Example: one large table scan ran ~6.5 hours, touched ~82% of the table and cleared ~32.7M dead rows out of ~1.27B total rows—indicating freezing activity, not backlog cleanup.
Step 3: Resource-profile and throttling check
Correlated runtime breakdown from logs: I/O wait dominated elapsed time (multiple thousands of seconds) while cost-based delay sleeping was negligible. maintenance_work_mem and autovacuum cost settings were not limiting the run; observed I/O write throughput during the run was low (effective writes ~21 MB/s) while the run generated very large WAL and full-page-image volume. Concluded disk write throughput was the primary bottleneck.
Step 4: Transaction ID consumption and freeze cadence
Analyzed transaction ID consumption from sampled data: ~472 XIDs/sec (~40.8M/day). With autovacuum_freeze_max_age at the default 200,000,000 this implied anti-wraparound vacuums on large tables roughly every 4.9–5 days. That frequency makes large tables repeatedly require expensive freeze scans.
Step 5: Index usage and bloat assessment
Queried pg_stat_user_indexes from the primary node and found very large index footprints on specific tables. Two large indexes on one table were heavily used and must be retained, but a different large table had ten indexes with seven showing zero scans on the observed node (≈66.6 GB of index data unused). Every autovacuum pass reads all indexes; unused indexes add unnecessary I/O and WAL work.
Step 6: Operational context and interfering workload
Customer confirmed a cleanup script was running continuously to keep data volumes manageable and that REINDEX operations take ~10 hours. Continuous deletes are consuming XIDs and producing WAL on the same storage the vacuum needs, increasing contention and elongating freeze vacuums.
Step 7: Plan formation and intervention preparation
Formulated a minimal-risk rollout: (a) confirm index usage on a standby to avoid mistaken drops, (b) remove unused indexes with DROP INDEX CONCURRENTLY, (c) adjust freeze-related autovacuum parameters and autovacuum memory via Patroni configuration, (d) switch WAL compression codec, and (e) repack remaining bloated indexes. This sequence reduces immediate I/O work before changing freeze cadence, and respects Patroni’s configuration control.
Solution:
Implemented changes in PostgreSQL via Patroni-managed configuration: increased autovacuum_freeze_max_age from 200,000,000 to 1,000,000,000 to reduce the frequency of anti-wraparound full-table freeze runs; lowered vacuum_freeze_min_age and vacuum_freeze_table_age so freezing is performed incrementally during normal autovacuum cycles; set autovacuum_work_mem explicitly to 1GB to limit per-worker memory exposure; switched wal_compression from pglz to lz4 to reduce CPU time spent compressing large full-page images; and dropped unused indexes on the high-activity table using DROP INDEX CONCURRENTLY, followed by pg_repack on remaining bloated indexes.
Architecturally, these changes reduce the rate at which tables hit the aggressive freeze threshold (fewer full-table freeze passes), lower per-vacuum index read and WAL volume by removing unused indexes, reduce CPU/WAL overhead through faster compression, and limit memory exposure for autovacuum workers. Applying changes through Patroni preserves configuration across restarts and failovers.
Conclusion:
Raising the freeze threshold moved the expected freeze cycle from roughly every 5 days to about 25 days at the observed transaction rate, and removing unused indexes cut significant per-vacuum I/O. Together with WAL compression and targeted repack, these changes reduce the frequency and duration of multi‑hour vacuums, lower WAL and I/O pressure, and improve overall operational stability without requiring table partitioning.