Re: Performance Degradation (Table becomes bloat) During Repeated Bulk UPDATE Operations

On 2026-Jun-19, Jeyaprakash Rajamani wrote:

> Here I've shared the vacuum verbose command output for your reference.

So what's the table and index size before and after this vacuum?

Anyway, here's the crucial detail:

> INFO:  "stg_loader_analze5": found 0 removable, 6810856 nonremovable row versions in 581198 out of 2563909 pages
> DETAIL:  3405428 dead row versions cannot be removed yet, oldest xmin: 11374173

There are 3.4 million dead rows after the vacuum of a total of 6.8
million unremovable rows.  This matches what analyze says:

> INFO:  analyzing "q_cps_mdm_dm.stg_loader_analze5"
> INFO:  "stg_loader_analze5": scanned 30000 of 2563909 pages, containing
> 40373 live rows and 39122 dead rows; 30000 rows in sample, 3450423
> estimated total rows

In the sample that ANALYZE saw, about half the rows are dead and
the other half are live.

That's a lot of bloat.  You may want to wait a little bit more for older
transactions to go away (or slots to move forward, if you have any)
before running this vacuum.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Finally, the phrase, 'No one was ever fired for buying an IBM' I don't believe
has ever been translated into German."                       (Leonard Tramiel)