I have a replica server using Postgres 14.4.
Replica is done using Publication/Subscription and I have triggers for auditing, so every record which comes from production is audited.
Some months ago I changed the way I was auditing and replaced that audit table for a new one, but didn't remove the old table. So I have both, new (AUDIT) and old (SYS_AUDIT) tables.
Then last night I received this message that to prevent wraparound postgres would do an aggressive vacuum on (SYS_AUDIT), and that took several hours to complete, in a table that is not used for 6 or 8 months. Why ?
I know it would be good to run vacuum on that table, it has lots of dead tuples, but that table is not used anymore, so why vacuum it ?
I have to drop immediately that huge table that is not used anymore because it can stop the server to prevent a wraparound some day ?
automatic aggressive vacuum to prevent wraparound of table "db.sys_tables.sys_audit": index scans: 1
pages: 0 removed, 2990943 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 12125175 removed, 225002719 remain, 0 are dead but not yet removable, oldest xmin: 623406932
index scan needed: 642835 pages from table (21.49% of total) had 19945151 dead item identifiers removed
index "pksysaudit": pages: 682245 in total, 112 newly deleted, 112 currently deleted, 112 reusable
index "idxsys_audit_audit_date_time": pages: 889005 in total, 2950 newly deleted, 2950 currently deleted, 2708 reusable
index "idxsysaudittablenameprimarykey": pages: 1045682 in total, 8 newly deleted, 8 currently deleted, 8 reusable
I/O timings: read: 9426032.316 ms, write: 95896.564 ms
avg read rate: 1.381 MB/s, avg write rate: 0.937 MB/s
buffer usage: 3021218 hits, 6252689 misses, 4243736 dirtied
WAL usage: 7402733 records, 4242832 full page images, 8868995633 bytes
system usage: CPU: user: 999.31 s, system: 84.52 s, elapsed: 35378.45 s
pages: 0 removed, 2990943 remain, 0 skipped due to pins, 0 skipped frozen
tuples: 12125175 removed, 225002719 remain, 0 are dead but not yet removable, oldest xmin: 623406932
index scan needed: 642835 pages from table (21.49% of total) had 19945151 dead item identifiers removed
index "pksysaudit": pages: 682245 in total, 112 newly deleted, 112 currently deleted, 112 reusable
index "idxsys_audit_audit_date_time": pages: 889005 in total, 2950 newly deleted, 2950 currently deleted, 2708 reusable
index "idxsysaudittablenameprimarykey": pages: 1045682 in total, 8 newly deleted, 8 currently deleted, 8 reusable
I/O timings: read: 9426032.316 ms, write: 95896.564 ms
avg read rate: 1.381 MB/s, avg write rate: 0.937 MB/s
buffer usage: 3021218 hits, 6252689 misses, 4243736 dirtied
WAL usage: 7402733 records, 4242832 full page images, 8868995633 bytes
system usage: CPU: user: 999.31 s, system: 84.52 s, elapsed: 35378.45 s
regards
Marcos
