Performance Degradation (Table becomes bloat) During Repeated Bulk UPDATE Operations - Mailing list pgsql-hackers

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.