Performance Degradation (Table becomes bloat) During Repeated Bulk UPDATE Operations
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.
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
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.rupireddyforpostgres@gmail.com> wrote:
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 millionrecords.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 increasessignificantly, eventually taking several minutes.
The table and index sizes continue to grow after each UPDATE.
The number of dead tuples also increases substantially despite runningVACUUM 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 UPDATEcycles.
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.
Attachments:
image.pngimage/png; name=image.pngDownload+0-1
Hi,
On Thu, Jun 18, 2026 at 10:16 AM Jeyaprakash Rajamani
<jeyaprakash.rajamani@chainsys.com> wrote:
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.
You may need to look at the VACUUM verbose output - it would tell you
exactly how many dead tuples/pages are present but not removable and
what's the oldest-xmin (which vacuum uses to decide whether or not to
remove the dead tples) etc.
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
Hi,
Vacuum verbose doesn't show anything in detail. It only shows 'vacuuming
table_name'.
*With Regards,*
*Jeyaprakash R*
*PostgreSQL DBA | AppSupport *
On Thu, 18 Jun 2026, 22:54 Bharath Rupireddy, <
bharath.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Thu, Jun 18, 2026 at 10:16 AM Jeyaprakash Rajamani
<jeyaprakash.rajamani@chainsys.com> wrote: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.You may need to look at the VACUUM verbose output - it would tell you
exactly how many dead tuples/pages are present but not removable and
what's the oldest-xmin (which vacuum uses to decide whether or not to
remove the dead tples) 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.
Hi,
On Thu, Jun 18, 2026 at 10:41 AM Jeyaprakash Rajamani <
jeyaprakash.rajamani@chainsys.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]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=# is how the output looks when the vacuum is able to remove dead
rows. [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=# 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=#
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=#
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
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.rupireddyforpostgres@gmail.com> wrote:
Hi,
On Thu, Jun 18, 2026 at 10:41 AM Jeyaprakash Rajamani <
jeyaprakash.rajamani@chainsys.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 valueIt 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.