Re: Vacuum Question

On Mon, 2025-09-22 at 15:06 +0000, Murthy Nunna wrote:
> 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?

If the data won't change any more, run a VACUUM (FREEZE) on the table.  That should freeze all
rows, and any subsequent VACUUM will finish very quickly and produce no WAL.

> About a year ago, I did pgdump/restore. But I did not perform vacuum though. So, may be I
> should complete my table by table vacuum at least once then.

If you restore a table from a dump, all the rows will be unfrozen.
It will take another VACUUM (FREEZE) to freeze the rows.

Yours,
Laurenz Albe