MIME-Version: 1.0
References: <CAMon-aQ8X7H7jCC6ggKm9Uzcu3KfGVC34RRqKt8BCfpDn2tq9A@mail.gmail.com>
 <2a301859ab47c2e83ba5c8ebf75112d61246a8cd.camel@cybertec.at> <CAMon-aSkPs=N5GHJsOzLs5RKcrba3=ng_6HUEKZy1=1GJgCEqg@mail.gmail.com>
In-Reply-To: <CAMon-aSkPs=N5GHJsOzLs5RKcrba3=ng_6HUEKZy1=1GJgCEqg@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Wed, 17 Dec 2025 12:23:15 -0500
Message-ID: <CANzqJaAXOGvS3sEN1o0xAkA1Ud0JmzBEuPSJiF0gOkVBgKFYog@mail.gmail.com>
Subject: Re: wal segment size
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000023e4f90646291acf"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAXOGvS3sEN1o0xAkA1Ud0JmzBEuPSJiF0gOkVBgKFYog%40mail.gmail.com>
Precedence: bulk

--00000000000023e4f90646291acf
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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.

On Wed, Dec 17, 2025 at 11:10=E2=80=AFAM Colin 't Hart <colinthart@gmail.co=
m> wrote:

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

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

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

<div dir=3D"ltr"><div>Adding this to ~/.ssh/config will almost totally elim=
inate the cost of ssh authentication:</div><div><br></div><div><font face=
=3D"monospace">Host *<br>=C2=A0 =C2=A0 ControlMaster auto<br>=C2=A0 =C2=A0 =
#ControlPath /run/user/%i/%L_%r_at_%n:%p<br>=C2=A0 =C2=A0 ControlPath ~/.ss=
h/%L_%r_at_%n:%p<br>=C2=A0 =C2=A0 ControlPersist 5m<br></font></div><div di=
r=3D"ltr"><br></div><div>Pointing ControlPath to=C2=A0<span style=3D"font-f=
amily:monospace">/run/user/%i</span>=C2=A0is even faster, but it doesn&#39;=
t always exist if you sudo into the postgres service account.</div><div dir=
=3D"ltr"><br></div><div dir=3D"ltr">On Wed, Dec 17, 2025 at 11:10=E2=80=AFA=
M Colin &#39;t Hart &lt;<a href=3D"mailto:colinthart@gmail.com">colinthart@=
gmail.com</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_contain=
er"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div=
>Thanks Laurenz, that confirms what I was assuming. Archiving is via pgback=
rest to a backup server, over SSH. Approx 750ms to archive each segment is =
crazy -- I&#39;ll check compression parameters too.</div><div><br></div><di=
v>Any reason not to bump it up to 1GB? Or is that overkill?</div><div><br><=
/div><div>/Colin</div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr"=
 class=3D"gmail_attr">On Wed, 17 Dec 2025 at 16:25, Laurenz Albe &lt;<a hre=
f=3D"mailto:laurenz.albe@cybertec.at" target=3D"_blank">laurenz.albe@cybert=
ec.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"ma=
rgin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,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>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div></div>

--00000000000023e4f90646291acf--