MIME-Version: 1.0
References: <CAGAwPgR=B9VTDwKkHZRHjYTmQzvJ+30mK-uyc7MMw6Tf9s9Fsw@mail.gmail.com>
 <E08677AC-AE92-40A1-9C46-9C8BA4529FE7@thebuild.com>
In-Reply-To: <E08677AC-AE92-40A1-9C46-9C8BA4529FE7@thebuild.com>
From: Phillip Diffley <phillip6402@gmail.com>
Date: Tue, 25 Mar 2025 16:27:58 -0500
Message-ID: <CAGAwPgTVG_0X9Y9UOFFYcOBDmmo9k6NGb_gJn0Zk=6pRng_=0A@mail.gmail.com>
Subject: Re: Replication slot WAL reservation
To: Christophe Pettus <xof@thebuild.com>
Cc: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b0f8f206313165d0"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgTVG_0X9Y9UOFFYcOBDmmo9k6NGb_gJn0Zk%3D6pRng_%3D0A%40mail.gmail.com>
Precedence: bulk

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

Oh I see! I was conflating the data I see coming out of a replication slot
with the internal organization of the WAL. I think the more specific
question I am trying to answer is, as a consumer of a replication slot, how
do I reason about what replication records will be made unavailable when I
confirm an LSN? Here I am worried about situations where the replication
connection is interrupted or the program processing the records crashes,
and we need to replay records that may have been previously sent but were
not fully processed.

For example, are the records sent by a replication slot always sent in the
same order such that if I advance the confirmed_flush_lsn of a slot to the
LSN of record "A", I will know that any records that had been streamed
after record "A" will be replayable?


On Tue, Mar 25, 2025 at 12:14=E2=80=AFPM Christophe Pettus <xof@thebuild.co=
m> wrote:

> Missed this question!
>
> > On Mar 25, 2025, at 09:56, Phillip Diffley <phillip6402@gmail.com>
> wrote:
> > But when processing data from a replication slot, we confirm rows that
> have been processed and can be deleted from the WAL based on the LSN (eg.
> with pg_replication_slot_advance). How does postgres identify what parts =
of
> the WAL can be freed?
>
> Basically, if no part of the system "needs" a particular LSN position, th=
e
> segments that include that LSN position and earlier can be free.
>
> The various things that can "need" a particular LSN point are:
>
> 1. Replication slots, if the other side has not confirmed that it has
> received it (under whatever synchronous commit rules that slot is operati=
ng
> under).
> 2. The wal_keep_size setting.
> 3. The max_wal_size setting.
> 4. The archive_command, if a WAL segment hasn't been successfully archive=
d
> yet.
>
> One thing to remember is that the WAL does *not* contain contiguous block=
s
> of operations for a single transaction.  The operations are written to th=
e
> WAL by every session as they do operations, so the WAL is a jumble of
> different transactions.  One of the jobs of the logical replication
> framework is to sort that out so it can present only the operations that
> belong to committed transactions to the output plugin.  (This is why
> there's an internal structure called the "reorder buffer": it reorders WA=
L
> operations into transaction blocks.)

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

<div dir=3D"ltr"><div dir=3D"ltr"><div>Oh I see! I was conflating the data =
I see coming out
 of a replication slot with the internal organization of the WAL. I=20
think the more specific question I am trying to answer is, as a=20
consumer of a replication slot, how do I reason about what replication=20
records will be made unavailable when I confirm an LSN? Here I am=20
worried about situations where the replication connection is interrupted
 or the program processing the  records crashes, and we need to replay=20
records that may have been previously sent but were not fully processed.</d=
iv><div><br></div><div>For
 example, are the records sent by a replication slot always sent in the=20
same order such that if I advance the=C2=A0confirmed_flush_lsn of a slot to=
=20
the LSN of record &quot;A&quot;, I will know that any records that had been=
=20
streamed after record &quot;A&quot; will be replayable?</div></div><br></di=
v><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">On Tue, Mar 25, 2025 at 12:14=E2=80=AFPM Christophe Pettu=
s &lt;<a href=3D"mailto:xof@thebuild.com">xof@thebuild.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex">Missed this quest=
ion!<br>
<br>
&gt; On Mar 25, 2025, at 09:56, Phillip Diffley &lt;<a href=3D"mailto:phill=
ip6402@gmail.com" target=3D"_blank">phillip6402@gmail.com</a>&gt; wrote:<br=
>
&gt; But when processing data from a replication slot, we confirm rows that=
 have been processed and can be deleted from the WAL based on the LSN (eg. =
with pg_replication_slot_advance). How does postgres identify what parts of=
 the WAL can be freed?<br>
<br>
Basically, if no part of the system &quot;needs&quot; a particular LSN posi=
tion, the segments that include that LSN position and earlier can be free.<=
br>
<br>
The various things that can &quot;need&quot; a particular LSN point are:<br=
>
<br>
1. Replication slots, if the other side has not confirmed that it has recei=
ved it (under whatever synchronous commit rules that slot is operating unde=
r).<br>
2. The wal_keep_size setting.<br>
3. The max_wal_size setting.<br>
4. The archive_command, if a WAL segment hasn&#39;t been successfully archi=
ved yet.<br>
<br>
One thing to remember is that the WAL does *not* contain contiguous blocks =
of operations for a single transaction.=C2=A0 The operations are written to=
 the WAL by every session as they do operations, so the WAL is a jumble of =
different transactions.=C2=A0 One of the jobs of the logical replication fr=
amework is to sort that out so it can present only the operations that belo=
ng to committed transactions to the output plugin.=C2=A0 (This is why there=
&#39;s an internal structure called the &quot;reorder buffer&quot;: it reor=
ders WAL operations into transaction blocks.)</blockquote></div>

--000000000000b0f8f206313165d0--