03.03.2026 Problem:
An application UPDATE statement against a partitioned PostgreSQL table (UPDATE tab1 SET sys_update_date = $1, agg_status = $2, output_filename = $3, merge_type = $4 WHERE period_key = $5 and record_id = $6) experienced consistent slowdowns in a nightly early‑morning window. The customer reported that partition file sizes showed normal small footprints for partitions p1–p12, while partitions p13 and higher had very large physical sizes (examples: p13 ~1 GB, many partitions p14–p29 in the 60–119 GB range). Autovacuum was enabled and no manual VACUUM FULL or pg_repack had been run; a nightly cleanup job (deletes with a 3‑day retention) and regular backups run during the same maintenance window. The observed symptom was that space stopped being returned to the operating system for partitions >= p13 after a specific point in time, and concurrent UPDATEs slowed during the maintenance window.
Process:
Step 1: Reproduce symptom and capture context
Initial review of the supplied data showed the UPDATE statement pattern and partition size map. Activity timing was noted: a nightly backup runs before the maintenance window and a multi‑hour cleanup job performs large DELETEs during the early‑morning period. This established overlapping maintenance activity and identified which partitions were active and large, which mattered because large active partitions were the ones not releasing filesystem space.
Step 2: Verify autovacuum and table statistics
Autovacuum settings and pg_stat_user_tables output were requested and examined. Autovacuum was observed to be running for the table, and statistics showed many dead tuples on the larger partitions. This mattered because autovacuum marking tuples dead is expected, but the presence of large dead-tuple counts indicated growing bloat that autovacuum alone might not reduce to the OS level.
Step 3: Check physical vs logical sizes and index/TOAST footprint
Queries that compared pg_relation_size() and pg_total_relation_size() were run (and reviewed), revealing substantial differences on several partitions: the raw table files were large and a non‑trivial portion of total size was attributed to indexes/TOAST. This showed that free space was fragmented across pages and could be held by main table or indexes, explaining why standard VACUUM did not return space to the OS.
Step 4: Correlate maintenance activity with bloat behavior
Client reports and activity snapshots showed the cleanup job deletes many rows in a short window while backups hold long snapshots. Analysis considered that long‑running backup snapshots and large concurrent deletes would leave dead tuples distributed throughout the relation and prevent truncation of end pages. This was important because PostgreSQL can only shrink a file when end pages become entirely free; scattered dead tuples leave the file length unchanged.
Step 5: Observe concurrent activity and impact on UPDATEs
During the slow window, session activity listings showed many non‑idle sessions and index updates. The interaction of large deletes, index maintenance, and concurrent UPDATEs explained per‑row scan costs and increased I/O. Knowing this influenced the mitigation choice: an online reorganization that removes bloat without long exclusive locks would reduce per‑statement work.
Step 6: Decide and schedule action
Given the size of affected partitions and operational constraints, an online table reorganization tool for PostgreSQL was chosen to reclaim physical space without an exclusive lock, paired with changes to the cleanup job to reduce fragmentation going forward. This step introduced the implemented fix and leads into the Solution below.
Solution:
On the large, active partitions in PostgreSQL, an online reorganization (pg_repack) was run during a controlled low‑impact window to rebuild the table and associated indexes and return unused space to the operating system. In parallel, the nightly cleanup job was modified to perform deletes in smaller batches with short pauses (and explicit commits), and its start time was adjusted to reduce overlap with backup snapshots. Post‑repack, autovacuum was left enabled with tuning of per‑table autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold for the largest partitions.
This approach works because the online reorganization physically rebuilds relation files and indexes to remove scattered dead tuples (reclaiming space at the filesystem level), while batching deletes reduces future fragmentation so autovacuum can truncate end pages when possible. PostgreSQL autovacuum can then maintain cleanliness without frequent need for heavyweight rebuilds.
Conclusion:
After the reorganize and cleanup changes, the partitions returned significant filesystem space and nightly UPDATE latencies during the maintenance window dropped to normal levels. Ongoing stability was achieved by combining online reorganization for immediate bloat removal with operational changes (batched deletes and tuned autovacuum) to prevent reoccurrence while maintaining backup and retention requirements.