public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Murthy Nunna <[email protected]>
To: pgsql-admin <[email protected]>
Subject: Re: Vacuum Question
Date: Tue, 23 Sep 2025 08:38:23 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <SJ0PR09MB6688E7136F78DDD70C8D1028B812A@SJ0PR09MB6688.namprd09.prod.outlook.com>
References: <SJ0PR09MB6688E7136F78DDD70C8D1028B812A@SJ0PR09MB6688.namprd09.prod.outlook.com>

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

view thread (4+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Vacuum Question
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox