A frequently updated 40 GB table holds roughly 6 GB of live data and full-table scans have become several times slower. Walk through how you confirm the extra space is dead-version bloat, reclaim it while the table stays online, and stop it recurring.

## Step 1: confirm it is bloat, not just a big table Estimate live size as live row count multiplied by average row width, then compare with the physical size of the heap. Do the same for each index. A 40 GB relation over 6 GB of live data is roughly 6-7x, which is far outside healthy steady state (typically 1.2x to 2x for a churning table). Two refinements matter: - **Separate heap from indexes.** On update-heavy tables the indexes are frequently the bigger offender, and their remedy (rebuild) is cheaper and less disruptive than a heap rewrite. - **Separate dead versions from reusable free space.** Space already reclaimed and tracked as free is not a leak; it will absorb future writes. If the table churns at 5 GB per day, some free space is exactly what you want. What matters is the *trend*: a ratio that keeps climbing means reclamation is losing. Also sanity-check the symptom. Scans slowing by roughly the same factor as the size ratio is consistent with bloat; a much larger slowdown suggests you have also fallen out of cache, which bloat causes but which compounds nonlinearly. ## Step 2: find out why cleanup is losing The two causes need opposite responses, so never skip this. **It cannot reclaim.** Cleanup runs and reports dead versions it is not allowed to remove. Something holds the oldest-snapshot horizon: a session idle inside a transaction, a long analytical query, an orphaned prepared transaction, a replication or change-data-capture slot with no consumer, or a standby whose feedback pins the primary. Check the age of the oldest transaction and the retained bytes per slot. Making cleanup more aggressive here changes nothing. **It cannot keep up.** The horizon is healthy but the churn rate exceeds cleanup throughput. Symptoms: cleanup on this table runs continuously or takes hours, its I/O budget is throttled, or its trigger thresholds are proportional to table size so a huge table waits far too long between passes. A third variant: cleanup starts but is repeatedly cancelled because it conflicts with a lock the workload keeps taking, so it never finishes a pass. There is also a self-reinforcing spiral to name: bloat slows the cleanup pass (more pages to scan), which lets bloat grow, which slows it further. Once you are in it, tuning alone may not recover; you have to reclaim space first. ## Step 3: reclaim, with the right amount of disruption Understand what each tool does: - **Routine cleanup** makes space reusable inside the relation. It does not shrink the file, except by truncating trailing empty pages. If your goal is stopping growth, this plus fixing the cause is enough, and it is the cheapest outcome. - **Full rewrite in place** compacts the relation and returns space, but takes an exclusive lock for the duration and needs temporary space for a full copy. Acceptable only in a real maintenance window, and on 40 GB that window is not short. - **Online reorganisation** builds a copy while the table stays writable, tracks concurrent changes, then swaps under a brief lock. This is the standard answer for reclaiming without downtime, and it needs roughly double the space plus enough slack to catch up with ongoing writes. - **Concurrent index rebuild.** Often recovers most of the space with far less risk than touching the heap. Do this first and re-measure; you may not need the heap rewrite at all. Sequence: fix the cause, run cleanup, rebuild indexes concurrently, re-measure, and only then decide whether an online heap reorganisation is worth it. Reclaiming before fixing the cause just replays the growth. ## Step 4: prevent recurrence - **Cap transaction and idle-in-transaction lifetime** so no client can pin history for hours, and alert on oldest-transaction age. - **Tune cleanup per table.** Global thresholds scaled to table size under-serve very large, hot tables; give this table its own more aggressive settings and enough I/O budget. - **Reduce version generation.** Avoid updating indexed columns when nothing else requires it, so in-page update optimisations can apply; split a wide, hot-updated table so the churning columns live in a narrow one; batch or debounce counter-style updates instead of writing on every event. - **Leave page headroom** so new versions can land on the same page as the original rather than scattering. - **Partition by time.** This is the strongest structural fix: expiring data leaves by dropping a partition, which is instant and generates no dead versions at all, while cleanup work per partition stays bounded. - **Chunk bulk deletes** into short transactions, and prefer partition drops to mass DELETEs entirely. ## What a strong answer sounds like Measure, then diagnose *why*, then reclaim, then prevent, in that order, with an explicit statement that ordinary cleanup does not shrink files and that shrinking requires a rewrite. Candidates who jump straight to "run a full compaction" miss both the cause and the downtime it implies.