Subject: Re: wal segment size

From: Laurenz Albe <[email-masked]>
Date: Wed, 17 Dec 2025 16:25:17 +0100

On Wed, 2025-12-17 at 16:13 +0100, Colin 't Hart wrote:
> I see very little advice on tuning WAL segment size.
> 
> One of my clients has a few datawarehouses at around 8 - 16 TB
> 
> On one of the nodes there are approx 15000 WAL segments of 16MB each, totalling
> approx 230GB. The archiver is archiving approx one per second, so approx 4 hours to clear.
> 
> Would we gain anything by bumping the WAL segment size?

Very likely yes, if the problem is the overhead of starting the archive_command.

Another thing that can slow down archiving is if you compress these segments
too aggressively.

Yours,
Laurenz Albe

---

From: "Colin 't Hart" <[email-masked]>
Date: Wed, 17 Dec 2025 17:10:19 +0100

Thanks Laurenz, that confirms what I was assuming. Archiving is via
pgbackrest to a backup server, over SSH. Approx 750ms to archive each
segment is crazy -- I'll check compression parameters too.

Any reason not to bump it up to 1GB? Or is that overkill?

/Colin

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 17 Dec 2025 08:31:34 -0800

> segment is crazy -- I'll check compression parameters too.

How much of that time is network travel?

What are the configuration settings for the archiving portion of pgBackRest?

> On Wed, 17 Dec 2025 at 16:25, Laurenz Albe <[email-masked] 
> <mailto:[email-masked]>> wrote:
> 

-- 
Adrian Klaver
[email-masked]

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 17 Dec 2025 12:21:03 -0500

> segment is crazy -- I'll check compression parameters too.

Switch to archive-async = on. When doing that, the typical time drops to
10ms or less. Also use a compress-type of lz4 or zst, which perform way
better than the default gz. If you are encrypting, that's a bottleneck you
just have to deal with, no shortcuts there. :)

tl;dr try other things before messing with the WAL size. The current size
can work very well even on very large and very, very busy systems.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

---

From: Ron Johnson <[email-masked]>
Date: Wed, 17 Dec 2025 12:23:15 -0500

Adding this to ~/.ssh/config will almost totally eliminate the cost of ssh
authentication:

Host *
    ControlMaster auto
    #ControlPath /run/user/%i/%L_%r_at_%n:%p
    ControlPath ~/.ssh/%L_%r_at_%n:%p
    ControlPersist 5m

Pointing ControlPath to /run/user/%i is even faster, but it doesn't always
exist if you sudo into the postgres service account.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!