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

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