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

Hi,

On Thu, Jun 18, 2026 at 8:54 AM Jeyaprakash Rajamani
<jeyaprakash.rajamani@chainsys.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