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

Hi Team,

We are experiencing a significant performance degradation during repeated
bulk UPDATE operations on the table. Even we execute the vacuum analyze
after each update. After the vacuum, dead tuples were sometimes removed,
but sometimes dead tuples weren't removed. So the table becomes bloated.

Scenario:

A new staging table (Table C) is created by loading approximately 4 million
records from Table A.
Another lookup table (Table B) contains approximately 2 million records.We
perform multiple bulk UPDATE operations on Table C by joining with Table B.
Each UPDATE modifies approximately 3.4 million rows.
VACUUM ANALYZE is executed after every bulk UPDATE.

Issue Observed:

The initial UPDATE operations complete within a few seconds.
As subsequent UPDATE batches are executed, the execution time increases
significantly, eventually taking several minutes.
The table and index sizes continue to grow after each UPDATE.
The number of dead tuples also increases substantially despite running
VACUUM ANALYZE.

From our observations:

Initial table size: ~2.2 GB
Final table size: ~26 GB
Initial index size: ~624 MB
Final index size: ~7.7 GB
Approximately 37 million dead tuples are generated after multiple UPDATE
cycles.

Main Reason: VACUUM ANALYZE sometimes fails to remove dead tuples. Why ?

We also tested:

Reducing the table fillfactor to 70.
Running the workload without secondary indexes.

We appreciate your support and look forward to your recommendations.

*With Regards,*
*Jeyaprakash R*
*PostgreSQL DBA | AppSupport *

--

----
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.