Version 14.13
I have a large database 22 TB, and it has lot of tables. Most of the tables do not change (static). But the age(relfrozenxid) of those tables keep increasing because there are some other tables in the database that are updated. The size of these large static tables are about 200 GB on an average. And to prevent transaction ID wrap around, I have been doing manual vacuum table by table (couple of tables a day due to limited WAL disk space). Each table generates WAL size of 90% of the tablesize approx.
e.g
Tablesize = 200 GB. Time takes to run vacuum = 1 hour 45 minutes. WAL generated 182 GB
I tried VACUUM FREEZE also, but the WAL generated and time it takes is no significantly different.
Following is an example output of a table vacuum:
vacuumdb: vacuuming database "large_db"
INFO: aggressively vacuuming "public.tab_111"
INFO: launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO: table "tab_111": found 0 removable, 527846215 nonremovable row versions in 15396753 out of 15396753 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 954951860
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 131.12 s, system: 174.14 s, elapsed: 4111.88 s.
INFO: aggressively vacuuming "pg_toast.pg_toast_17386"
INFO: table "pg_toast_17386": found 0 removable, 32180684 nonremovable row versions in 7981550 out of 7981550 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 955034530
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 52.96 s, system: 87.86 s, elapsed: 2104.04 s.
Is there a way I can minimize WAL generation? My issue is amount of WAL rather than time it takes to run. Since it is not locking the table I do not mind long run time.
I know one way is to pgdump/restore but it takes a long time and further to that I have to rebuild replicas.
Please note, I have autovacuum turned on and it is doing what it is supposed to do on tables that change. So, there is no issue there.
I very much appreciate any help/advice you can provide.
