MIME-Version: 1.0
From: Atapys Sharsh <atapys@gmail.com>
Date: Fri, 7 Mar 2025 15:25:54 +0000
Message-ID: <CANrdvu3jMWU6wE7vghiuOUoA5OMo7no3uuYjsnG8RN_03H_cJA@mail.gmail.com>
Subject: Changing column filter for existing publication with WAL backlog
To: pgsql-general@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b1f996062fc23de5"
Archived-At: <https://www.postgresql.org/message-id/CANrdvu3jMWU6wE7vghiuOUoA5OMo7no3uuYjsnG8RN_03H_cJA%40mail.gmail.com>
Precedence: bulk

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

Hi,

Recently, I decided to add column filtering for our publication. The
commands I used were:
ALTER SUBSCRIPTION mysub DISABLE;

ALTER PUBLICATION mypub SET TABLE public.my_table(id, first_column,
another_column), TABLE public.other_table; -- excluding third_column

ALTER SUBSCRIPTION mysub ENABLE;

When I tested this approach in our staging environment, it worked as
expected =E2=80=93 I stopped seeing updates to third_column. However, in pr=
oduction
I still see updates to that column. I also ran ALTER SUBSCRIPTION mysub
REFRESH PUBLICATION after noticing that updates were still being replicated=
.

Could this behavior be due to the large WAL backlog in production? At the
time I applied the column filter, the replication lag was around 4 TB (with
total storage being 16 TB). Should I expect that once production catches up
to WAL records generated after the change, updates to third_column will
eventually stop replicating?

Thanks in advance for your help.

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

<div dir=3D"ltr"><p>Hi,</p><p>Recently, I decided to add column filtering f=
or our publication. The commands I used were:<br><span class=3D"gmail-hljs-=
keyword">ALTER</span> SUBSCRIPTION mysub DISABLE;

<br><br><span class=3D"gmail-hljs-keyword">ALTER</span> PUBLICATION mypub <=
span class=3D"gmail-hljs-keyword">SET</span> <span class=3D"gmail-hljs-keyw=
ord">TABLE</span> public.my_table(id, first_column, another_column), <span =
class=3D"gmail-hljs-keyword">TABLE</span> public.other_table;=C2=A0<span cl=
ass=3D"gmail-hljs-comment">-- excluding third_column<br></span><br><span cl=
ass=3D"gmail-hljs-keyword">ALTER</span> SUBSCRIPTION mysub ENABLE;<br></p><=
p>When I tested this approach in our staging environment, it worked as expe=
cted =E2=80=93 I stopped seeing updates to <code>third_column</code>. Howev=
er, in production I still see updates to that column. I also ran <code>ALTE=
R SUBSCRIPTION mysub REFRESH PUBLICATION</code>=C2=A0after noticing that up=
dates were still being replicated.</p><p>Could this behavior be due to the =
large WAL backlog in production? At the time I applied the column filter, t=
he replication lag was around 4 TB (with total storage being 16 TB). Should=
 I expect that once production catches up to WAL records generated after th=
e change, updates to <code>third_column</code> will eventually stop replica=
ting?</p><p>Thanks in advance for your help.</p></div>

--000000000000b1f996062fc23de5--