Re: Performance Degradation (Table becomes bloat) During Repeated Bulk UPDATE Operations - Mailing list pgsql-hackers
Hi,
> 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.
> 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.
Are there any other solutions to resolve this?
With Regards,
Jeyaprakash R
PostgreSQL DBA | AppSupport
On Sat, Jun 20, 2026 at 4:05 PM Álvaro Herrera <alvherre@kurilemu.de> wrote:
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)
----
Disclaimer:
This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. The opinion expressed in this mail is that of the sender and do not necessarily reflect that of ChainSys. Thank you for your co-operation.
This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. The opinion expressed in this mail is that of the sender and do not necessarily reflect that of ChainSys. Thank you for your co-operation.
pgsql-hackers by date: