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 < [email protected]> wrote: > Hi, > > On Thu, Jun 18, 2026 at 10:41 AM Jeyaprakash Rajamani < > [email protected]> 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.
