vacuum to prevent wraparound

From: Marcos Pegoraro <marcos(at)f10(dot)com(dot)br>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: vacuum to prevent wraparound
Date: 2023-06-06 14:36:56
Message-ID: CAB-JLwZ3pSJmGTMuTcG0sSMf2S2Wfi++3DQfbmYYaWrH9dxvtA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

regards
Marcos

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-06-06 14:50:35 Re: No prompt for setting up a master password
Previous Message Erik Wienhold 2023-06-06 11:20:47 Re: Drivers users by connections