MIME-Version: 1.0
From: Phillip Diffley <phillip6402@gmail.com>
Date: Tue, 25 Mar 2025 11:56:39 -0500
Message-ID: <CAGAwPgR=B9VTDwKkHZRHjYTmQzvJ+30mK-uyc7MMw6Tf9s9Fsw@mail.gmail.com>
Subject: Replication slot WAL reservation
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006cd46e06312d9ba3"
Archived-At: <https://www.postgresql.org/message-id/CAGAwPgR%3DB9VTDwKkHZRHjYTmQzvJ%2B30mK-uyc7MMw6Tf9s9Fsw%40mail.gmail.com>
Precedence: bulk

--0000000000006cd46e06312d9ba3
Content-Type: text/plain; charset="UTF-8"

I am trying to understand how logical replication slots work, specifically
in regard to how WAL is reserved and freed by a replication slot.

My current understanding of the WAL (set to wal_level logical) is that:
1. Every DML operation (insert, update, delete, truncate) will have a row
in the WAL and that row will have an LSN assigned to it.
2. The LSNs are assigned when the operation happens.
3. Operations within a transaction are written to the WAL in a contiguous
block when the transaction commits.
4. Each transaction block in the WAL has a commit timestamp associated with
it, and these timestamps are monotonically increasing (I'm only inferring
this, so please correct me if this is not always the case).

A result of an operation being assigned an LSN when the operation occurs
but not being recorded to the WAL until it's transaction commits is that
LSNs in the WAL are not necessarily in order.

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?

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

<div dir=3D"ltr"><div></div><div></div><div>I am trying to understand how l=
ogical replication slots work, specifically in regard to how WAL is reserve=
d and freed by a replication slot.</div><div><br></div><div>My current unde=
rstanding of the WAL (set to wal_level logical) is that:</div><div>1. Every=
 DML operation (insert, update, delete, truncate) will have a row in the WA=
L and that row will have an LSN assigned to it.=C2=A0</div><div>2. The LSNs=
 are assigned when the operation happens.</div><div>3. Operations within a =
transaction are written to the WAL in a contiguous block when the transacti=
on commits.</div><div>4. Each transaction block in the WAL has a commit tim=
estamp associated with it, and these timestamps are monotonically increasin=
g (I&#39;m only inferring this, so please correct me if this is not always =
the case).</div><div><br></div><div>A result of an operation being assigned=
 an LSN when the operation occurs but not being recorded to the WAL until i=
t&#39;s transaction commits is that LSNs in the WAL are not necessarily in =
order.</div><div><br></div><div>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 post=
gres identify what parts of the WAL can be freed?</div><div><br></div></div=
>

--0000000000006cd46e06312d9ba3--