Message-ID: <38219e14d655f5b7955446cb0f4c7dfc73b76bfa.camel@cybertec.at>
Subject: Re: Vacuum Question
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Murthy Nunna <mnunna@fnal.gov>, pgsql-admin <pgsql-admin@postgresql.org>
Date: Tue, 23 Sep 2025 08:38:23 +0200
In-Reply-To: 
 <SJ0PR09MB6688E7136F78DDD70C8D1028B812A@SJ0PR09MB6688.namprd09.prod.outlook.com>
References: 
	<SJ0PR09MB6688E7136F78DDD70C8D1028B812A@SJ0PR09MB6688.namprd09.prod.outlook.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/38219e14d655f5b7955446cb0f4c7dfc73b76bfa.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-09-22 at 15:06 +0000, Murthy Nunna wrote:
> Version 14.13
> =C2=A0
> I have a large database 22 TB, and it has lot of tables. Most of the tabl=
es do not change (static).
> But the age(relfrozenxid) of those tables keep increasing because there a=
re 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 man=
ual vacuum table by table
> (couple of tables a day due to limited WAL disk space). Each table genera=
tes WAL size of 90% of
> the tablesize approx.
> e.g
> Tablesize =3D 200 GB. Time takes to run vacuum =3D 1 hour 45 minutes. WAL=
 generated 182 GB
> =C2=A0
> I tried VACUUM FREEZE also, but the WAL generated and time it takes is no=
 significantly different.
> =C2=A0
> Following is an example output of a table vacuum:
> =C2=A0
> vacuumdb: vacuuming database "large_db"
> INFO:=C2=A0 aggressively vacuuming "public.tab_111"
> INFO:=C2=A0 launched 1 parallel vacuum worker for index cleanup (planned:=
 1)
> INFO:=C2=A0 table "tab_111": found 0 removable, 527846215 nonremovable ro=
w versions in 15396753 out of 15396753 pages
> DETAIL:=C2=A0 0 dead row versions cannot be removed yet, oldest xmin: 954=
951860
> Skipped 0 pages due to buffer pins, 0 frozen pages.
> CPU: user: 131.12 s, system: 174.14 s, elapsed: 4111.88 s.
> INFO:=C2=A0 aggressively vacuuming "pg_toast.pg_toast_17386"
> INFO:=C2=A0 table "pg_toast_17386": found 0 removable, 32180684 nonremova=
ble row versions in 7981550 out of 7981550 pages
> DETAIL:=C2=A0 0 dead row versions cannot be removed yet, oldest xmin: 955=
034530
> Skipped 0 pages due to buffer pins, 0 frozen pages.
> CPU: user: 52.96 s, system: 87.86 s, elapsed: 2104.04 s.
> =C2=A0
> Is there a way I can minimize WAL generation?

If the data won't change any more, run a VACUUM (FREEZE) on the table.  Tha=
t 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 thou=
gh. 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