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

Hi,

Thanks for the email. I got your points.

But why doesn't VACUUM remove the dead tuples? We also checked at that time
and confirmed there are no long running, active transactions or replication
slots.

I've shared our observation below for your knowledge. Please review this
and explain the reason.

PFA,
[image: image.png]

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

On Thu, Jun 18, 2026 at 10:22 PM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> Hi,
>
> On Thu, Jun 18, 2026 at 8:54 AM Jeyaprakash Rajamani
> <jeyaprakash(dot)rajamani(at)chainsys(dot)com> wrote:
> >
> > 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.
>
> Thanks for reporting!
>
> > 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.
>
> This is expected. Query performance degrades with the amount of bloat,
> because UPDATE queries do look at tables/indexes and need to process
> the bloat.
>
> > 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 ?
>
> Vacuum won't be able to remove dead tuples/bloat for many reasons -
> long running transactions, prepared transactions, inactive replication
> slots etc. Do you see any of these lying in your database when you hit
> the issue?
>
> Having said that, more details will help understand the issue - vacuum
> analyze verbose output, whether you are running vacuum command or
> autovacuum etc.
>
> > We also tested:
> >
> > Reducing the table fillfactor to 70.
> > Running the workload without secondary indexes.
>
> To understand the bloat patterns, you may want to look at how the
> update queries are designed - whether they modify any of the indexed
> columns, number of Heap-Only Tuple (HOT) updates, average size of the
> rows (both table and index) etc.
>
> --
> Bharath Rupireddy
> Amazon Web Services: https://aws.amazon.com
>

--

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