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.
