MIME-Version: 1.0
From: Rajendra Kumar Dangwal <dangwalrajendra888@gmail.com>
Date: Thu, 9 Oct 2025 12:36:27 +0530
Message-ID: <CA+qyiM=5dqKp2qyjoWVQpM1tpDcD7rQxZcgh7gQe73EfLuaUcA@mail.gmail.com>
Subject: Clarification on restart_lsn behavior in logical decoding
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c99fdb0640b4727f"
Feedback-ID: i76984098:Fastmail
Message-ID: <caa70601-f6ee-4dd7-9259-7ab9ecc5497b@aklaver.com>
Date: Mon, 20 Oct 2025 07:42:01 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Arrays vs separate tables
To: Rich Shepard <rshepard@appl-ecosys.com>,
 pgsql-general@lists.postgresql.org
References: <162e5330-861c-ecc7-45c1-aaa1aff26c88@appl-ecosys.com>
 <pyh2kcisxwrhzz255gaiaawo5yubirdifzsfvz3okqcjeaaqqr@6lq5n6idswg7>
 <95de5a9b-4dd8-2f7-6e12-98c3d72c52@appl-ecosys.com>
 <CC75487C-BBDF-474D-880A-87FB6CEED812@kleczek.org>
 <712b8b5c-15d2-e284-a0c0-6263415250c3@appl-ecosys.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <712b8b5c-15d2-e284-a0c0-6263415250c3@appl-ecosys.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/caa70601-f6ee-4dd7-9259-7ab9ecc5497b%40aklaver.com>
Precedence: bulk

On 10/20/25 07:07, Rich Shepard wrote:
> On Mon, 20 Oct 2025, Michał Kłeczek wrote:
> 
>> There is also another concern - do you want to make sure phone numbers 
>> are
>> not shared?
> 
> Michal,
> 
> Shared with whom? I run a solo professional services consultancy so there's
> only me here the database.

That is the heart of the matter, you should do what makes sense to you. 
If you can get the information you need in the manner you want then 
don't change things. Given the size of your datasets I don't see that 
changes will materially affect the performance of your queries. I am 
pretty sure most of the time the Postgres planner is resorting to a 
sequence scan anyway.

> 
> Rich
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com


//lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-general@lists.postgresql.org>
List-Owner: <mailto:pgsql-general-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-general>
Archived-At: <https://www.postgresql.org/message-id/CA%2BqyiM%3D5dqKp2qyjoWVQpM1tpDcD7rQxZcgh7gQe73EfLuaUcA%40mail.gmail.com>
Precedence: bulk

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

Hi PG users.

We are using PostgreSQL=E2=80=99s logical decoding feature with Debezium to=
 capture
CDC events and stream them to Kafka. To verify whether the required
position to resume from after a connector restart is still available on the
server, we compare the processed LSNs with the restart_lsn of the
replication slot.

From the documentation, our understanding is that restart_lsn represents
the oldest WAL position still required by the logical decoding consumer:

> restart_lsn pg_lsn
> The address (LSN) of oldest WAL which still might be required by the
> consumer of this slot and thus won't be automatically removed during
> checkpoints unless this LSN gets behind more than max_slot_wal_keep_size
> <https://postgrespro.com/docs/postgresql/14/runtime-config-replication#GU=
C-MAX-SLOT-WAL-KEEP-SIZE> from
> the current LSN. NULL if the LSN of this slot has never been reserved.


However, there is some confusion around the possible value of restart_lsn.
My current understanding is that the restart_lsn cannot be greater than the
start LSN of the oldest open (non-consumed) transaction.
One of my teammates, however, suggested that there might be cases where the
restart_lsn could advance beyond the start LSN of an unconsumed
transaction, as illustrated below:

               LSN
T1 BEGIN
T1 CHANGE 1    20
T1 CHANGE 2    21  <--- Restart LSN
T1 CHANGE 3    22
T1 COMMIT      30  <--- Confirmed flush LSN
T2 BEGIN
T2 CHANGE 1    10  <--- Start LSN of unconsumed transaction
T2 CHANGE 2    11  <--- Last processed LSN
T2 CHANGE 3    12
T2 COMMIT      40

Could someone please clarify whether the restart_lsn can indeed have
value greater than the start LSN of an unconsumed transaction, or if
it is always expected to be less than or equal to it?


Thanks,

Rajendra.

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

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:arial,he=
lvetica,sans-serif;font-size:small">Hi PG users.</div><div class=3D"gmail_d=
efault" style=3D"font-family:arial,helvetica,sans-serif;font-size:small"><b=
r></div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,s=
ans-serif;font-size:small">We are using PostgreSQL=E2=80=99s logical decodi=
ng feature with Debezium to capture CDC events and stream them to Kafka. To=
 verify whether the required position to resume from after a connector rest=
art is still available on the server, we compare the processed LSNs with th=
e restart_lsn of the replication slot.<br><br>From the documentation, our u=
nderstanding is that restart_lsn represents the oldest WAL position still r=
equired by the logical decoding consumer:</div><div class=3D"gmail_default"=
 style=3D""><span style=3D"font-family:arial,helvetica,sans-serif"><blockqu=
ote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px=
 solid rgb(204,204,204);padding-left:1ex"><font color=3D"#000000"><span sty=
le=3D"font-family:-apple-system,&quot;system-ui&quot;,&quot;Segoe UI&quot;,=
&quot;Noto Sans&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&qu=
ot;,&quot;Segoe UI Emoji&quot;">restart_lsn pg_lsn<br></span><span style=3D=
"font-family:-apple-system,&quot;system-ui&quot;,&quot;Segoe UI&quot;,&quot=
;Noto Sans&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&=
quot;Segoe UI Emoji&quot;">The address (LSN) of oldest WAL which still migh=
t be required by the consumer of this slot and thus won&#39;t be automatica=
lly removed during checkpoints unless this LSN gets behind more than=C2=A0<=
/span><a href=3D"https://postgrespro.com/docs/postgresql/14/runtime-config-=
replication#GUC-MAX-SLOT-WAL-KEEP-SIZE" rel=3D"nofollow" style=3D"box-sizin=
g:border-box;font-family:-apple-system,&quot;system-ui&quot;,&quot;Segoe UI=
&quot;,&quot;Noto Sans&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color E=
moji&quot;,&quot;Segoe UI Emoji&quot;">max_slot_wal_keep_size</a><span styl=
e=3D"font-family:-apple-system,&quot;system-ui&quot;,&quot;Segoe UI&quot;,&=
quot;Noto Sans&quot;,Helvetica,Arial,sans-serif,&quot;Apple Color Emoji&quo=
t;,&quot;Segoe UI Emoji&quot;">=C2=A0from the current LSN. NULL if the LSN =
of this slot has never been reserved.</span></font></blockquote></span></di=
v><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-se=
rif;font-size:small"><span style=3D"color:rgb(89,99,110);font-family:-apple=
-system,&quot;system-ui&quot;,&quot;Segoe UI&quot;,&quot;Noto Sans&quot;,He=
lvetica,Arial,sans-serif,&quot;Apple Color Emoji&quot;,&quot;Segoe UI Emoji=
&quot;;font-size:14px"><br></span></div><div class=3D"gmail_default" style=
=3D"font-family:arial,helvetica,sans-serif;font-size:small">However, there =
is some confusion around the possible value of restart_lsn. My current unde=
rstanding is that the restart_lsn cannot be greater than the start LSN of t=
he oldest open (non-consumed) transaction.<br>One of my teammates, however,=
 suggested that there might be cases where the restart_lsn could advance be=
yond the start LSN of an unconsumed transaction, as illustrated below:</div=
><div class=3D"gmail_default" style=3D""><pre class=3D"gmail-notranslate" s=
tyle=3D"font-size:small;font-family:arial,helvetica,sans-serif"><code class=
=3D"gmail-notranslate">               LSN
T1 BEGIN
T1 CHANGE 1    20
T1 CHANGE 2    21  &lt;--- Restart LSN
T1 CHANGE 3    22
T1 COMMIT      30  &lt;--- Confirmed flush LSN
T2 BEGIN
T2 CHANGE 1    10  &lt;--- Start LSN of unconsumed transaction
T2 CHANGE 2    11  &lt;--- Last processed LSN
T2 CHANGE 3    12
T2 COMMIT      40</code></pre><pre class=3D"gmail-notranslate" style=3D"tex=
t-wrap-mode: wrap;"><font face=3D"arial, sans-serif" style=3D"">Could someo=
ne please clarify whether the restart_lsn can indeed have value greater tha=
n the start LSN of an unconsumed transaction, or if it is always expected t=
o be less than or equal to it?</font></pre><pre class=3D"gmail-notranslate"=
 style=3D"text-wrap-mode: wrap;"><font face=3D"arial, sans-serif" style=3D"=
"><br></font></pre><pre class=3D"gmail-notranslate" style=3D"text-wrap-mode=
: wrap;"><font face=3D"arial, sans-serif" style=3D"">Thanks,</font></pre><p=
re class=3D"gmail-notranslate" style=3D"text-wrap-mode: wrap;"><font face=
=3D"arial, sans-serif" style=3D"">Rajendra.</font></pre></div></div>

--000000000000c99fdb0640b4727f--