MIME-Version: 1.0
References: <CAMon-aQ8X7H7jCC6ggKm9Uzcu3KfGVC34RRqKt8BCfpDn2tq9A@mail.gmail.com>
 <2a301859ab47c2e83ba5c8ebf75112d61246a8cd.camel@cybertec.at>
In-Reply-To: <2a301859ab47c2e83ba5c8ebf75112d61246a8cd.camel@cybertec.at>
From: "Colin 't Hart" <colinthart@gmail.com>
Date: Wed, 17 Dec 2025 17:10:19 +0100
Message-ID: <CAMon-aSkPs=N5GHJsOzLs5RKcrba3=ng_6HUEKZy1=1GJgCEqg@mail.gmail.com>
Subject: Re: wal segment size
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000061569f06462815ce"
Archived-At: <https://www.postgresql.org/message-id/CAMon-aSkPs%3DN5GHJsOzLs5RKcrba3%3Dng_6HUEKZy1%3D1GJgCEqg%40mail.gmail.com>
Precedence: bulk

--00000000000061569f06462815ce
Content-Type: text/plain; charset="UTF-8"

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

On Wed, 17 Dec 2025 at 16:25, Laurenz Albe <laurenz.albe@cybertec.at> wrote:

> 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
>

--00000000000061569f06462815ce
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Thanks Laurenz, that confirms what I was assuming. Ar=
chiving is via pgbackrest to a backup server, over SSH. Approx 750ms to arc=
hive each segment is crazy -- I&#39;ll check compression parameters too.</d=
iv><div><br></div><div>Any reason not to bump it up to 1GB? Or is that over=
kill?</div><div><br></div><div>/Colin</div></div><br><div class=3D"gmail_qu=
ote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 17=
 Dec 2025 at 16:25, Laurenz Albe &lt;<a href=3D"mailto:laurenz.albe@cyberte=
c.at">laurenz.albe@cybertec.at</a>&gt; wrote:<br></div><blockquote class=3D=
"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(2=
04,204,204);padding-left:1ex">On Wed, 2025-12-17 at 16:13 +0100, Colin &#39=
;t Hart wrote:<br>
&gt; I see very little advice on tuning WAL=C2=A0segment=C2=A0size.<br>
&gt; <br>
&gt; One of my clients has a few datawarehouses=C2=A0at around 8 - 16 TB<br=
>
&gt; <br>
&gt; On one of the nodes there are approx 15000 WAL segments of 16MB each, =
totalling<br>
&gt; approx 230GB. The archiver is archiving approx one per second, so appr=
ox 4 hours to clear.<br>
&gt; <br>
&gt; Would=C2=A0we gain anything by bumping the WAL=C2=A0segment size?<br>
<br>
Very likely yes, if the problem is the overhead of starting the archive_com=
mand.<br>
<br>
Another thing that can slow down archiving is if you compress these segment=
s<br>
too aggressively.<br>
<br>
Yours,<br>
Laurenz Albe<br>
</blockquote></div>

--00000000000061569f06462815ce--