RE: Autovacuum not functioning for large tables but it is working for few other small tables.

Hi,

Please find the Vacuum(verbose) output. Can you please suggest what is the reason?
How can we avoid these scenarios?

The customer tried to run the VACUUM(verbose) last night, but it was running continuously for 5 hours without any visible progress. So they had to abort it as it was going to exhaust their maintenance window.

db_Server14=# VACUUM (VERBOSE) audittraillogentry;
INFO: vacuuming "mmsuper.audittraillogentry"
INFO: scanned index "audittraillogentry_pkey" to remove 11184539 row versions
DETAIL: CPU 25.24s/49.11u sec elapsed 81.33 sec
INFO: scanned index "audit_intime_index" to remove 11184539 row versions
DETAIL: CPU 23.27s/59.28u sec elapsed 88.63 sec
INFO: scanned index "audit_outtime_index" to remove 11184539 row versions
DETAIL: CPU 27.02s/55.10u sec elapsed 92.04 sec
INFO: scanned index "audit_sourceid_index" to remove 11184539 row versions
DETAIL: CPU 110.81s/72.29u sec elapsed 260.71 sec
INFO: scanned index "audit_destid_index" to remove 11184539 row versions
DETAIL: CPU 100.49s/87.03u sec elapsed 265.00 sec
INFO: "audittraillogentry": removed 11184539 row versions in 247622 pages
DETAIL: CPU 3.23s/0.89u sec elapsed 6.64 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184545 row versions
DETAIL: CPU 25.73s/45.72u sec elapsed 86.59 sec
INFO: scanned index "audit_intime_index" to remove 11184545 row versions
DETAIL: CPU 34.65s/56.52u sec elapsed 113.52 sec
INFO: scanned index "audit_outtime_index" to remove 11184545 row versions
DETAIL: CPU 35.55s/61.96u sec elapsed 113.89 sec
INFO: scanned index "audit_sourceid_index" to remove 11184545 row versions
DETAIL: CPU 120.60s/75.17u sec elapsed 286.78 sec
INFO: scanned index "audit_destid_index" to remove 11184545 row versions
DETAIL: CPU 111.87s/93.74u sec elapsed 295.05 sec
INFO: "audittraillogentry": removed 11184545 row versions in 1243407 pages
DETAIL: CPU 20.35s/6.45u sec elapsed 71.61 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184547 row versions
DETAIL: CPU 21.84s/43.36u sec elapsed 71.72 sec
INFO: scanned index "audit_intime_index" to remove 11184547 row versions
DETAIL: CPU 33.37s/57.07u sec elapsed 99.50 sec
INFO: scanned index "audit_outtime_index" to remove 11184547 row versions
DETAIL: CPU 35.08s/60.08u sec elapsed 110.08 sec
INFO: scanned index "audit_sourceid_index" to remove 11184547 row versions
DETAIL: CPU 117.72s/72.75u sec elapsed 256.31 sec
INFO: scanned index "audit_destid_index" to remove 11184547 row versions
DETAIL: CPU 103.46s/77.43u sec elapsed 247.23 sec
INFO: "audittraillogentry": removed 11184547 row versions in 268543 pages
DETAIL: CPU 4.36s/1.35u sec elapsed 9.61 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184521 row versions
DETAIL: CPU 26.64s/45.46u sec elapsed 80.51 sec
INFO: scanned index "audit_intime_index" to remove 11184521 row versions
DETAIL: CPU 35.05s/59.11u sec elapsed 111.23 sec
INFO: scanned index "audit_outtime_index" to remove 11184521 row versions
DETAIL: CPU 32.98s/56.41u sec elapsed 105.93 sec
INFO: scanned index "audit_sourceid_index" to remove 11184521 row versions
DETAIL: CPU 117.13s/71.14u sec elapsed 254.33 sec
INFO: scanned index "audit_destid_index" to remove 11184521 row versions
DETAIL: CPU 99.93s/81.77u sec elapsed 241.83 sec
INFO: "audittraillogentry": removed 11184521 row versions in 268593 pages
DETAIL: CPU 3.49s/1.14u sec elapsed 6.87 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184534 row versions
DETAIL: CPU 22.73s/42.41u sec elapsed 69.12 sec
INFO: scanned index "audit_intime_index" to remove 11184534 row versions
DETAIL: CPU 36.78s/68.04u sec elapsed 121.60 sec
INFO: scanned index "audit_outtime_index" to remove 11184534 row versions
DETAIL: CPU 31.11s/52.88u sec elapsed 93.93 sec
INFO: scanned index "audit_sourceid_index" to remove 11184534 row versions
DETAIL: CPU 117.95s/72.65u sec elapsed 247.44 sec
INFO: scanned index "audit_destid_index" to remove 11184534 row versions
DETAIL: CPU 104.25s/82.63u sec elapsed 248.43 sec
INFO: "audittraillogentry": removed 11184534 row versions in 268598 pages
DETAIL: CPU 3.74s/1.17u sec elapsed 9.45 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184546 row versions
DETAIL: CPU 21.24s/40.72u sec elapsed 68.78 sec
INFO: scanned index "audit_intime_index" to remove 11184546 row versions
DETAIL: CPU 34.29s/56.72u sec elapsed 99.63 sec
INFO: scanned index "audit_outtime_index" to remove 11184546 row versions
DETAIL: CPU 33.83s/60.99u sec elapsed 105.22 sec
INFO: scanned index "audit_sourceid_index" to remove 11184546 row versions
DETAIL: CPU 114.26s/70.11u sec elapsed 239.56 sec
INFO: scanned index "audit_destid_index" to remove 11184546 row versions
DETAIL: CPU 100.73s/73.28u sec elapsed 228.37 sec
INFO: "audittraillogentry": removed 11184546 row versions in 268538 pages
DETAIL: CPU 3.80s/1.18u sec elapsed 7.79 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184523 row versions
DETAIL: CPU 25.78s/47.23u sec elapsed 77.60 sec
INFO: scanned index "audit_intime_index" to remove 11184523 row versions
DETAIL: CPU 35.39s/56.45u sec elapsed 103.70 sec
INFO: scanned index "audit_outtime_index" to remove 11184523 row versions
DETAIL: CPU 31.16s/52.24u sec elapsed 90.21 sec
INFO: scanned index "audit_sourceid_index" to remove 11184523 row versions
DETAIL: CPU 114.71s/70.03u sec elapsed 260.11 sec
INFO: scanned index "audit_destid_index" to remove 11184523 row versions
DETAIL: CPU 105.71s/76.33u sec elapsed 228.59 sec
INFO: "audittraillogentry": removed 11184523 row versions in 268611 pages
DETAIL: CPU 3.40s/1.17u sec elapsed 7.10 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184554 row versions
DETAIL: CPU 22.80s/39.22u sec elapsed 67.26 sec
INFO: scanned index "audit_intime_index" to remove 11184554 row versions
DETAIL: CPU 35.38s/57.31u sec elapsed 106.01 sec
INFO: scanned index "audit_outtime_index" to remove 11184554 row versions
DETAIL: CPU 34.15s/54.73u sec elapsed 97.79 sec
INFO: scanned index "audit_sourceid_index" to remove 11184554 row versions
DETAIL: CPU 118.37s/71.55u sec elapsed 243.34 sec
INFO: scanned index "audit_destid_index" to remove 11184554 row versions
DETAIL: CPU 100.43s/72.41u sec elapsed 252.42 sec
INFO: "audittraillogentry": removed 11184554 row versions in 268590 pages
DETAIL: CPU 4.40s/1.34u sec elapsed 9.00 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184533 row versions
DETAIL: CPU 25.01s/40.12u sec elapsed 72.19 sec
INFO: scanned index "audit_intime_index" to remove 11184533 row versions
DETAIL: CPU 34.13s/52.89u sec elapsed 93.53 sec
INFO: scanned index "audit_outtime_index" to remove 11184533 row versions
DETAIL: CPU 31.29s/50.04u sec elapsed 88.22 sec
INFO: scanned index "audit_sourceid_index" to remove 11184533 row versions
DETAIL: CPU 119.38s/66.95u sec elapsed 257.04 sec
INFO: scanned index "audit_destid_index" to remove 11184533 row versions
DETAIL: CPU 102.33s/74.23u sec elapsed 230.70 sec
INFO: "audittraillogentry": removed 11184533 row versions in 268627 pages
DETAIL: CPU 3.94s/1.28u sec elapsed 7.74 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184536 row versions
DETAIL: CPU 22.67s/38.49u sec elapsed 66.67 sec
INFO: scanned index "audit_intime_index" to remove 11184536 row versions
DETAIL: CPU 37.17s/61.79u sec elapsed 107.70 sec
INFO: scanned index "audit_outtime_index" to remove 11184536 row versions
DETAIL: CPU 32.23s/51.13u sec elapsed 90.93 sec
INFO: scanned index "audit_sourceid_index" to remove 11184536 row versions
DETAIL: CPU 117.68s/70.04u sec elapsed 239.51 sec
INFO: scanned index "audit_destid_index" to remove 11184536 row versions
DETAIL: CPU 103.82s/72.82u sec elapsed 228.64 sec
INFO: "audittraillogentry": removed 11184536 row versions in 268597 pages
DETAIL: CPU 4.01s/1.34u sec elapsed 8.74 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184533 row versions
DETAIL: CPU 26.34s/39.03u sec elapsed 70.76 sec
INFO: scanned index "audit_intime_index" to remove 11184533 row versions
DETAIL: CPU 35.98s/53.60u sec elapsed 99.27 sec
INFO: scanned index "audit_outtime_index" to remove 11184533 row versions
DETAIL: CPU 32.57s/50.71u sec elapsed 90.61 sec
INFO: scanned index "audit_sourceid_index" to remove 11184533 row versions
DETAIL: CPU 122.50s/64.66u sec elapsed 254.06 sec
INFO: scanned index "audit_destid_index" to remove 11184533 row versions
DETAIL: CPU 100.87s/78.60u sec elapsed 237.31 sec
INFO: "audittraillogentry": removed 11184533 row versions in 268643 pages
DETAIL: CPU 4.01s/1.23u sec elapsed 7.69 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184535 row versions
DETAIL: CPU 22.65s/36.84u sec elapsed 61.70 sec
INFO: scanned index "audit_intime_index" to remove 11184535 row versions
DETAIL: CPU 37.86s/59.20u sec elapsed 104.94 sec
INFO: scanned index "audit_outtime_index" to remove 11184535 row versions
DETAIL: CPU 32.06s/48.99u sec elapsed 88.31 sec
INFO: scanned index "audit_sourceid_index" to remove 11184535 row versions
DETAIL: CPU 120.01s/69.92u sec elapsed 245.13 sec
INFO: scanned index "audit_destid_index" to remove 11184535 row versions
DETAIL: CPU 102.99s/69.48u sec elapsed 216.71 sec
INFO: "audittraillogentry": removed 11184535 row versions in 268574 pages
DETAIL: CPU 4.27s/1.41u sec elapsed 9.40 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184545 row versions
DETAIL: CPU 26.12s/39.21u sec elapsed 71.64 sec
INFO: scanned index "audit_intime_index" to remove 11184545 row versions
DETAIL: CPU 35.67s/52.12u sec elapsed 95.95 sec
INFO: scanned index "audit_outtime_index" to remove 11184545 row versions
DETAIL: CPU 32.68s/47.59u sec elapsed 86.58 sec
INFO: scanned index "audit_sourceid_index" to remove 11184545 row versions
DETAIL: CPU 118.72s/64.51u sec elapsed 249.14 sec
INFO: scanned index "audit_destid_index" to remove 11184545 row versions
DETAIL: CPU 103.10s/76.75u sec elapsed 248.05 sec
INFO: "audittraillogentry": removed 11184545 row versions in 268662 pages
DETAIL: CPU 3.69s/1.18u sec elapsed 7.75 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184521 row versions
DETAIL: CPU 22.80s/35.86u sec elapsed 61.23 sec
INFO: scanned index "audit_intime_index" to remove 11184521 row versions
DETAIL: CPU 35.79s/53.76u sec elapsed 97.45 sec
INFO: scanned index "audit_outtime_index" to remove 11184521 row versions
DETAIL: CPU 33.41s/46.93u sec elapsed 93.18 sec
INFO: scanned index "audit_sourceid_index" to remove 11184521 row versions
DETAIL: CPU 117.29s/66.18u sec elapsed 224.79 sec
INFO: scanned index "audit_destid_index" to remove 11184521 row versions
DETAIL: CPU 104.67s/68.33u sec elapsed 226.39 sec
INFO: "audittraillogentry": removed 11184521 row versions in 268576 pages
DETAIL: CPU 3.76s/1.08u sec elapsed 7.49 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184525 row versions
DETAIL: CPU 25.06s/39.94u sec elapsed 70.43 sec
INFO: scanned index "audit_intime_index" to remove 11184525 row versions
DETAIL: CPU 35.01s/50.04u sec elapsed 94.04 sec
INFO: scanned index "audit_outtime_index" to remove 11184525 row versions
DETAIL: CPU 31.41s/45.69u sec elapsed 84.37 sec
INFO: scanned index "audit_sourceid_index" to remove 11184525 row versions
DETAIL: CPU 118.28s/63.16u sec elapsed 244.28 sec
INFO: scanned index "audit_destid_index" to remove 11184525 row versions
DETAIL: CPU 105.60s/73.95u sec elapsed 227.47 sec
INFO: "audittraillogentry": removed 11184525 row versions in 268660 pages
DETAIL: CPU 3.91s/1.25u sec elapsed 7.51 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184538 row versions
DETAIL: CPU 23.79s/34.59u sec elapsed 62.01 sec
INFO: scanned index "audit_intime_index" to remove 11184538 row versions
DETAIL: CPU 36.86s/51.24u sec elapsed 99.10 sec
INFO: scanned index "audit_outtime_index" to remove 11184538 row versions
DETAIL: CPU 34.95s/53.11u sec elapsed 98.44 sec
INFO: scanned index "audit_sourceid_index" to remove 11184538 row versions
DETAIL: CPU 115.09s/62.14u sec elapsed 229.85 sec
INFO: scanned index "audit_destid_index" to remove 11184538 row versions
DETAIL: CPU 107.02s/65.97u sec elapsed 218.05 sec
INFO: "audittraillogentry": removed 11184538 row versions in 268584 pages
DETAIL: CPU 3.46s/1.30u sec elapsed 7.03 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184546 row versions
DETAIL: CPU 23.68s/33.59u sec elapsed 60.67 sec
INFO: scanned index "audit_intime_index" to remove 11184546 row versions
DETAIL: CPU 39.63s/54.93u sec elapsed 106.66 sec
INFO: scanned index "audit_outtime_index" to remove 11184546 row versions
DETAIL: CPU 32.55s/44.43u sec elapsed 84.53 sec
INFO: scanned index "audit_sourceid_index" to remove 11184546 row versions
DETAIL: CPU 122.49s/63.49u sec elapsed 235.39 sec
INFO: scanned index "audit_destid_index" to remove 11184546 row versions
DETAIL: CPU 108.09s/69.68u sec elapsed 227.05 sec
INFO: "audittraillogentry": removed 11184546 row versions in 269472 pages
DETAIL: CPU 4.32s/1.33u sec elapsed 8.72 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184536 row versions
DETAIL: CPU 23.70s/32.98u sec elapsed 62.22 sec
INFO: scanned index "audit_intime_index" to remove 11184536 row versions
DETAIL: CPU 35.77s/46.57u sec elapsed 88.27 sec
INFO: scanned index "audit_outtime_index" to remove 11184536 row versions
DETAIL: CPU 32.59s/43.16u sec elapsed 82.06 sec
INFO: scanned index "audit_sourceid_index" to remove 11184536 row versions
DETAIL: CPU 126.27s/60.18u sec elapsed 258.72 sec
INFO: scanned index "audit_destid_index" to remove 11184536 row versions
DETAIL: CPU 112.57s/65.24u sec elapsed 232.06 sec
INFO: "audittraillogentry": removed 11184536 row versions in 269319 pages
DETAIL: CPU 3.73s/1.29u sec elapsed 7.58 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184538 row versions
DETAIL: CPU 23.22s/32.16u sec elapsed 60.22 sec
INFO: scanned index "audit_intime_index" to remove 11184538 row versions
DETAIL: CPU 38.42s/51.43u sec elapsed 101.53 sec
INFO: scanned index "audit_outtime_index" to remove 11184538 row versions
DETAIL: CPU 33.29s/42.79u sec elapsed 88.70 sec
INFO: scanned index "audit_sourceid_index" to remove 11184538 row versions
DETAIL: CPU 124.04s/62.06u sec elapsed 230.83 sec
INFO: scanned index "audit_destid_index" to remove 11184538 row versions
DETAIL: CPU 105.41s/64.14u sec elapsed 223.93 sec
INFO: "audittraillogentry": removed 11184538 row versions in 269384 pages
DETAIL: CPU 3.69s/1.11u sec elapsed 7.79 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184520 row versions
DETAIL: CPU 26.60s/34.89u sec elapsed 64.47 sec
INFO: scanned index "audit_intime_index" to remove 11184520 row versions
DETAIL: CPU 36.01s/45.24u sec elapsed 88.69 sec
INFO: scanned index "audit_outtime_index" to remove 11184520 row versions
DETAIL: CPU 33.00s/41.31u sec elapsed 83.02 sec
INFO: scanned index "audit_sourceid_index" to remove 11184520 row versions
DETAIL: CPU 124.80s/58.92u sec elapsed 246.98 sec
INFO: scanned index "audit_destid_index" to remove 11184520 row versions
DETAIL: CPU 106.35s/71.38u sec elapsed 249.67 sec
INFO: "audittraillogentry": removed 11184520 row versions in 269050 pages
DETAIL: CPU 3.74s/1.16u sec elapsed 8.87 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184523 row versions
DETAIL: CPU 21.95s/30.36u sec elapsed 59.88 sec
INFO: scanned index "audit_intime_index" to remove 11184523 row versions
DETAIL: CPU 33.84s/42.86u sec elapsed 88.67 sec
INFO: scanned index "audit_outtime_index" to remove 11184523 row versions
DETAIL: CPU 35.71s/44.46u sec elapsed 95.35 sec
INFO: scanned index "audit_sourceid_index" to remove 11184523 row versions
DETAIL: CPU 120.51s/61.81u sec elapsed 249.04 sec
INFO: scanned index "audit_destid_index" to remove 11184523 row versions
DETAIL: CPU 103.16s/62.69u sec elapsed 231.34 sec
INFO: "audittraillogentry": removed 11184523 row versions in 266741 pages
DETAIL: CPU 4.27s/1.24u sec elapsed 8.26 sec
INFO: scanned index "audittraillogentry_pkey" to remove 11184551 row versions
DETAIL: CPU 25.89s/37.48u sec elapsed 69.65 sec
INFO: scanned index "audit_intime_index" to remove 11184551 row versions
DETAIL: CPU 35.74s/43.70u sec elapsed 100.58 sec
INFO: scanned index "audit_outtime_index" to remove 11184551 row versions
DETAIL: CPU 31.45s/40.14u sec elapsed 84.00 sec

db_Server14=# SELECT pid, datname, usename, state, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;
pid | datname | usename | state | backend_xmin
-------+----------------+----------+--------+--------------
73583 | fm_db_Server14 | mmsuper | active | 63548809
31359 | fm_db_Server14 | postgres | active | 63548812
52761 | fm_db_Server14 | mmsuper | active | 63548814
53197 | fm_db_Server14 | mmsuper | active | 63548815
53409 | fm_db_Server14 | mmsuper | active | 63548815
38917 | fm_db_Server14 | mmsuper | active | 63548818
(6 rows)

db_Server14=# SELECT slot_name, slot_type, database, xmin FROM pg_replication_slots ORDER BY age(xmin) DESC;
slot_name | slot_type | database | xmin
-----------+-----------+----------+------
(0 rows)

db_Server14=# SELECT gid, prepared, owner, database, transaction AS xmin FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
gid | prepared | owner | database | xmin
-----+----------+-------+----------+------
(0 rows)

Regards
Tarkeshwar