MIME-Version: 1.0
References: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
 <CAFiTN-tFanrK2S=ALc++vmOnS07mumSLA91aaYejBHi7X8+rUg@mail.gmail.com> <CAA4eK1+Tn+T3qRts6hSqko7oyNiWS1U_mDkXw6+zGhAAde3CZA@mail.gmail.com>
In-Reply-To: <CAA4eK1+Tn+T3qRts6hSqko7oyNiWS1U_mDkXw6+zGhAAde3CZA@mail.gmail.com>
From: Dilip Kumar <dilipbalaut@gmail.com>
Date: Fri, 20 Feb 2026 11:54:22 +0530
Message-ID: <CAFiTN-tW1n2O_U4tCeBQDQbaeVo+dQiDEC1kqSRzUDc2-L_6Pg@mail.gmail.com>
Subject: Re: [PATCH] Support automatic sequence replication
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Ajin Cherian <itsajin@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFiTN-tW1n2O_U4tCeBQDQbaeVo%2BdQiDEC1kqSRzUDc2-L_6Pg%40mail.gmail.com>
Precedence: bulk

On Fri, Feb 20, 2026 at 10:48=E2=80=AFAM Amit Kapila <amit.kapila16@gmail.c=
om> wrote:
>
> On Fri, Feb 20, 2026 at 10:11=E2=80=AFAM Dilip Kumar <dilipbalaut@gmail.c=
om> wrote:
> >
> > On Tue, Feb 3, 2026 at 9:18=E2=80=AFAM Ajin Cherian <itsajin@gmail.com>=
 wrote:
> > >
> > > Hello hackers,
> > >
> > > I'd like to propose an improvement to the sequence replication featur=
e
> > > that was committed in [1].
> > >
> > > The current implementation synchronizes sequences during initial
> > > subscription setup, but the sequence sync worker exits after this
> > > initial sync. This means that as sequences advance on the publisher,
> > > they drift from the subscriber values over time. Users must manually
> > > run ALTER SUBSCRIPTION ... REFRESH SEQUENCES to resynchronize, which
> > > requires monitoring and intervention.
> >
> > Thanks, Ajin, for the proposal. I am trying to think: what is the use
> > case for automatically updating the sequence values? IIUC, the only
> > use case for the sequence sync worker was when using logical
> > replication for an upgrade; after the upgrade, you should have
> > up-to-date values for the sequences. By adding an automatic update,
> > are you targeting any new use case?
> >
> > If we are still targeting the same use case, I=E2=80=99d like to unders=
tand
> > how updating the value at specific intervals will improve the user
> > experience.
> >
>
> Even for an upgrade, to avoid large downtime, it would be better if
> the subscriber has the latest values of sequences, so that during the
> upgrade of the publisher, the subscriber can receive all client
> requests. It is possible with the REFRESH SEQUENCES command as well
> but in the worst case (when there are large number of sequences), it
> can take much longer time.

To clarify, here is the specific sequence synchronization use case I
have in mind (major version upgrade with minimal downtime )

Setup: A PUB-SUB environment, both on PG17.
Goal: Upgrade to PG18 with near-zero downtime.
Upgrade: Run pg_upgrade on the Subscriber to move it to PG18.
Replication: Logical replication continues from the PG17 Publisher to
the PG18 Subscriber, including sequences.
Sync: Wait for replication lag to hit near-zero and synchronize sequences.
Cutover: Stop all traffic on the Publisher.
Final Catch-up: Allow the PG18 Subscriber to catch up and perform a
final sequence refresh.
Switchover: Redirect all traffic to the Subscriber.

In this major version upgrade scenario, the downtime is minimal (only
while waiting for Final Catch-up). However, before the final
switchover, the user must ensure all data and sequences are fully
synced. They usually can't afford to wait for an automatic background
sync, they need to force it using REFRESH SEQUENCES.

My question is, does the proposed automatic sync allow us to avoid
manual refreshes entirely in this process? Or is there another use
case where the advantages of automation are more apparent?

--
Regards,
Dilip Kumar
Google