Subject: VacuumDB generating huge WAL filed

From: SASIKUMAR Devaraj <[email-masked]>
Date: Thu, 12 Dec 2024 06:08:26 +0000 (UTC)

Hi All
When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB of WAL files? Any way we can reduce this WAL file generation?
RegardsSasi

Sent from Yahoo Mail for iPhone

---

From: "David G. Johnston" <[email-masked]>
Date: Wed, 11 Dec 2024 23:25:52 -0700

On Wednesday, December 11, 2024, SASIKUMAR Devaraj <[email-masked]>
wrote:

>
> When we are running vacuumdb for our database of 1.5TB it is generating
> approximately 60GB of WAL files? Any way we can reduce this WAL file
> generation?
>
>
Set a much more aggressive autovacuum so you don’t accumulate as much dead
tuples between runs?

David J.

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 12 Dec 2024 07:35:47 +0100

On Wed, 2024-12-11 at 23:25 -0700, David G. Johnston wrote:
> On Wednesday, December 11, 2024, SASIKUMAR Devaraj <[email-masked]> wrote:
> > When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB
> > of WAL files? Any way we can reduce this WAL file generation?
> 
> Set a much more aggressive autovacuum so you don’t accumulate as much dead tuples between runs?

You can also activate "wal_compression" for smaller full-page images
and increase "max_wal_size" to get fewer of them.

But 60GB of WAL shouldn't be a problem.  Why are they a problem for you?

Yours,
Laurenz Albe

---

From: SASIKUMAR Devaraj <[email-masked]>
Date: Thu, 12 Dec 2024 12:27:44 +0000 (UTC)

Hi
Since it is generating 60GB in few mins, my replication lag is increasing upto 10Gb for around 10 mins, which may seen as possible data loss in case of primary node failure 
RegardsSasi

On Thursday, December 12, 2024, 12:06 PM, Laurenz Albe <[email-masked]> wrote:

Yours,
Laurenz Albe

---

From: vignesh kumar <[email-masked]>
Date: Thu, 12 Dec 2024 15:07:10 +0000

Check for the max walsize allocated in postgresql.conf and also check on checkpoint completion target default is 0.9 this says how frequent the checkpoint can be applied that adds to wal lan generation.

First do a table level vaccuum and see what's causing it to write more wal files.

Sent from Outlook for Android<https://aka.ms/AAb9ysg>
________________________________
From: SASIKUMAR Devaraj <[email-masked]>
Sent: Thursday, December 12, 2024 11:38:26 AM
To: Pgsql-admin <[email-masked]>
Subject: VacuumDB generating huge WAL filed

Hi All

Regards
Sasi

Sent from Yahoo Mail for iPhone<https://mail.onelink.me/107872968?pid=nativeplacement&c=Global_Acquisition_YMktg_315_Internal_EmailSignature&af_sub1=Acquisition&af_sub2=Global_YMktg&af_sub3=&af_sub4=100000604&af_sub5=EmailSignature__Static_>

---

From: SASIKUMAR Devaraj <[email-masked]>
Date: Fri, 13 Dec 2024 10:03:51 +0000 (UTC)

We were able to identify the table generating around 40GB in few mins by vaccum process. It is partition table with dead tuple less than 0.5 percent. What may be next steps?

On Thursday, December 12, 2024, 8:37 PM, vignesh kumar <[email-masked]> wrote:

Check for the max walsize allocated in postgresql.conf and also check on checkpoint completion target default is 0.9 this says how frequent the checkpoint can be applied that adds to wal lan generation.
First do a table level vaccuum and see what's causing it to write more wal files.
Sent from Outlook for AndroidFrom: SASIKUMAR Devaraj <[email-masked]>
Sent: Thursday, December 12, 2024 11:38:26 AM
To: Pgsql-admin <[email-masked]>
Subject: VacuumDB generating huge WAL filed Hi All
When we are running vacuumdb for our database of 1.5TB it is generating approximately 60GB of WAL files? Any way we can reduce this WAL file generation?
RegardsSasi

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 13 Dec 2024 11:12:28 +0100

On Fri, 2024-12-13 at 10:03 +0000, SASIKUMAR Devaraj wrote:
> > When we are running vacuumdb for our database of 1.5TB it is
> > generating approximately 60GB of WAL files? Any way we can
> > reduce this WAL file generation?
>
> We were able to identify the table generating around 40GB in
> few mins by vaccum process. It is partition table with dead
> tuple less than 0.5 percent. What may be next steps?

I think I told you: increase "max_wal_size" and enable "wal_compression".
That should reduce the amount of WAL.

Yours,
Laurenz Albe