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

Hi,

Here I've shared the vacuum verbose command output for your reference.

Output:

VACUUM (VERBOSE, ANALYZE) q_cps_mdm_dm.STG_LOADER_ANALZE5;
INFO: vacuuming "q_cps_mdm_dm.stg_loader_analze5"
INFO: launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO: index "stg_eabsmxclglyhis5_bkup_3i" now contains 6808850 row
versions in 143139 pages
DETAIL: 0 index row versions were removed.
109753 index pages have been deleted, 109753 are currently reusable.
CPU: user: 0.13 s, system: 0.13 s, elapsed: 0.27 s.
INFO: index "stg_eabsmxclglyhis5_bkup_2i" now contains 6808850 row
versions in 143139 pages
DETAIL: 0 index row versions were removed.
109753 index pages have been deleted, 109753 are currently reusable.
CPU: user: 0.13 s, system: 0.27 s, elapsed: 0.41 s.
INFO: index "stg_eabsmxclglyhis5_bkup_4i" now contains 6808850 row
versions in 201832 pages
DETAIL: 0 index row versions were removed.
155001 index pages have been deleted, 155001 are currently reusable.
CPU: user: 0.21 s, system: 0.36 s, elapsed: 0.60 s.
INFO: index "stg_eabsmxclglyhis5_bkup_6i" now contains 6808850 row
versions in 380696 pages
DETAIL: 0 index row versions were removed.
288972 index pages have been deleted, 288972 are currently reusable.
CPU: user: 0.36 s, system: 0.31 s, elapsed: 0.67 s.
INFO: "stg_loader_analze5": found 0 removable, 6810856 nonremovable row
versions in 581198 out of 2563909 pages
DETAIL: 3405428 dead row versions cannot be removed yet, oldest xmin:
11374173
There were 241628 unused item identifiers.
Skipped 0 pages due to buffer pins, 1982711 frozen pages.
0 pages are entirely empty.
CPU: user: 1.61 s, system: 1.20 s, elapsed: 2.83 s.
INFO: vacuuming "pg_toast.pg_toast_1240478489"
INFO: "pg_toast_1240478489": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 11374173
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "q_cps_mdm_dm.stg_loader_analze5"
INFO: "stg_loader_analze5": scanned 30000 of 2563909 pages, containing
40373 live rows and 39122 dead rows; 30000 rows in sample, 3450423
estimated total rows
VACUUM

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

On Fri, Jun 19, 2026 at 7:51 AM Bharath Rupireddy <
bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:

> Hi,
>
> On Thu, Jun 18, 2026 at 10:41 AM Jeyaprakash Rajamani <
> jeyaprakash(dot)rajamani(at)chainsys(dot)com> wrote:
> >
> > Vacuum verbose doesn't show anything in detail. It only shows 'vacuuming
> table_name'.
>
> It means the vacuum is still running.
>
> FWIW, [1] is how the output looks when the vacuum is able to remove dead
> rows. [2] is how the output looks when the vacuum is NOT able to remove
> dead rows. The interesting pieces to look for are below. In this case, I
> started a long-running transaction with txn-id = 716 in one session,
> updated the rows in another transaction with txn-id = 717 in a second
> session, then ran vacuum - it says that any dead rows with xmax < 716 are
> removable, but the update created dead rows with xmax = 717 and the
> long-running transaction (which started before the update) may still
> reference those rows, so vacuum will NOT remove them.
> tuples: 0 removed, 221478602 remain, 12950000 are dead but not yet
> removable
> removable cutoff: 716, which was 3 XIDs old when operation ended
> new relfrozenxid: 716, which is 1 XIDs ahead of previous value
>
> It will be helpful to read through the vacuum documentation
> https://www.postgresql.org/docs/current/routine-vacuuming.html and try a
> few experiments on the test environments. It makes these concepts much
> easier to reason about and understand the issue you are facing.
>
> Also, it is worth looking at the vacuum/autovacuum configuration
> parameters:
> https://www.postgresql.org/docs/current/runtime-config-vacuum.html
> [1]
> postgres=# vacuum (verbose, analyze) foo;
> INFO: vacuuming "postgres.public.foo"
> INFO: finished vacuuming "postgres.public.foo": index scans: 0
> pages: 0 removed, 541593 remain, 320165 scanned (59.12% of total), 0
> eagerly scanned
> tuples: 10391455 removed, 63587201 remain, 0 are dead but not yetremovable
> removable cutoff: 710, which was 0 XIDs old when operation ended
> new relfrozenxid: 710, which is 16 XIDs ahead of previous value
> frozen: 320165 pages from table (59.12% of total) had 61965817 tuples
> frozen
> visibility map: 320165 pages set all-visible, 320165 pages set all-frozen
> (0 were all-visible)
> index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
> identifiers removed
> avg read rate: 124.141 MB/s, avg write rate: 130.467 MB/s
> buffer usage: 335810 hits, 304707 reads, 320235 dirtied
> WAL usage: 640390 records, 320240 full page images, 2798348920 bytes,
> 2613151324 full page image bytes, 15653 buffers full
> memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
> 64.00 MB each)
> system usage: CPU: user: 4.25 s, system: 2.31 s, elapsed: 19.17 s
> INFO: analyzing "public.foo"
> INFO: "foo": scanned 30000 of 541593 pages, containing 5654455 live rows
> and 0 dead rows; 30000 rows in sample, 102080442 estimated total rows
> INFO: finished analyzing table "postgres.public.foo"
> avg read rate: 995.019 MB/s, avg write rate: 0.171 MB/s
> buffer usage: 935 hits, 29166 reads, 5 dirtied
> WAL usage: 5 records, 4 full page images, 25816 bytes, 25324 full page
> image bytes, 0 buffers full
> system usage: CPU: user: 0.10 s, system: 0.11 s, elapsed: 0.22 s
> VACUUM
> postgres=#
>
> [2]
> postgres=# vacuum (verbose, analyze) foo;
> INFO: vacuuming "postgres.public.foo"
> INFO: finished vacuuming "postgres.public.foo": index scans: 0
> pages: 0 removed, 1004868 remain, 780310 scanned (77.65% of total), 0
> eagerly scanned
> tuples: 0 removed, 221478602 remain, 12950000 are dead but not yet
> removable
> removable cutoff: 716, which was 3 XIDs old when operation ended
> new relfrozenxid: 716, which is 1 XIDs ahead of previous value
> frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
> visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were
> all-visible)
> index scan not needed: 0 pages from table (0.00% of total) had 0 dead item
> identifiers removed
> avg read rate: 86.102 MB/s, avg write rate: 82.757 MB/s
> buffer usage: 796234 hits, 764731 reads, 735024 dirtied
> WAL usage: 735024 records, 735024 full page images, 6033799850 bytes,
> 5997783604 full page image bytes, 13243 buffers full
> memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit
> 64.00 MB each)
> system usage: CPU: user: 6.71 s, system: 8.01 s, elapsed: 69.38 s
> INFO: analyzing "public.foo"
> INFO: "foo": scanned 30000 of 1004868 pages, containing 6076054 live rows
> and 397314 dead rows; 30000 rows in sample, 203521074 estimated total rows
> INFO: finished analyzing table "postgres.public.foo"
> avg read rate: 862.319 MB/s, avg write rate: 0.029 MB/s
> buffer usage: 465 hits, 29581 reads, 1 dirtied
> WAL usage: 2 records, 1 full page images, 1491 bytes, 1168 full page image
> bytes, 0 buffers full
> system usage: CPU: user: 0.12 s, system: 0.11 s, elapsed: 0.26 s
> VACUUM
> postgres=#
>
> --
> 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.