MIME-Version: 1.0
References: <CAFPTHDZXX9WQ_X1ZfEvS248T+pKuk6SmCnXcvgPM059N1xPUfA@mail.gmail.com>
 <CAJpy0uDLUEjHHME8om1vAf6qkXCeRR6cBvkpK8yWBAC=T0ZFLA@mail.gmail.com>
 <CAFPTHDZg1JrunGgOj332hr+gUuH_Jm7skqPpYSvd-QE3yEdRDQ@mail.gmail.com>
 <CAJpy0uBz7MCSUkvFJD9ij65vBahNmY+bfCgdGKRqXovYs+K_TA@mail.gmail.com>
 <CAJpy0uDsuNqjWd-TmGBxqSS1rnVCJ3B8=SYrtxQ=Vs8kb71QFA@mail.gmail.com>
 <CAJpy0uAMWg3KcXtVBS7B0rnchLNrCCVYBByJCzAp=u5LERgtfA@mail.gmail.com>
 <CAFPTHDZwEhxhDAeqcPi0GuYN6xBs8gFXHOMUnbg3u2Xigcz4Zg@mail.gmail.com>
 <CAE9k0PmTyCU1A9YEf+MRgfeZ8yK1bAYJu=o0bH8DNUTzXejQyQ@mail.gmail.com>
 <CAA4eK1L6czEzG4mLNZSyjYC5nX0FMSjjk3csKuxPD3Ph5-7Yvw@mail.gmail.com>
 <CAJpy0uAhGQJ=msVsn2GsqWXr+YESJK6x9NBvrUtKvtvp1OVuKQ@mail.gmail.com>
 <CAJpy0uAOuu-M6wobH2wHOdTymm-cX9+MqwPyRNoOt=sPKBdCew@mail.gmail.com>
 <CAFPTHDZiWYXoKoo4VcBYNH9a=gxDZhfkcBeXt5w6cLw4_ysyKw@mail.gmail.com>
 <OS9PR01MB12149D9054CC7F2DC3F0D26A1F577A@OS9PR01MB12149.jpnprd01.prod.outlook.com>
 <CAA4eK1KYxQALt46k5uWOO6SUtNjvjOaXwfNjH0AU656YrcGZEw@mail.gmail.com>
 <CAFPTHDZYonM+SXG19VVjgWduXQJSuDhcOUWq0NCiiuQubCSt6g@mail.gmail.com>
 <CAA4eK1J6zwM1WOnhHR7VYNMAmGiQKf9=KL+Vz+BM3=QuhtL_Lw@mail.gmail.com>
 <CAFiTN-tZ6452cwLzHguSOj7cMLbycgepdWRH4ETUdY9SBx6Qpg@mail.gmail.com> <CAA4eK1+vE1DD6SUG3kQYBXFGEr5a3DVy9SBHqc1j5aY6JqvnQw@mail.gmail.com>
In-Reply-To: <CAA4eK1+vE1DD6SUG3kQYBXFGEr5a3DVy9SBHqc1j5aY6JqvnQw@mail.gmail.com>
From: shveta malik <shveta.malik@gmail.com>
Date: Wed, 25 Feb 2026 09:27:06 +0530
Message-ID: <CAJpy0uD+C0BherWt3AcrDHkxZFvn48RXonqRGXLn1S5A+6PGyA@mail.gmail.com>
Subject: Re: [PATCH] Support automatic sequence replication
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>, Ajin Cherian <itsajin@gmail.com>, 
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, Ashutosh Sharma <ashu.coek88@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, shveta malik <shveta.malik@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAJpy0uD%2BC0BherWt3AcrDHkxZFvn48RXonqRGXLn1S5A%2B6PGyA%40mail.gmail.com>
Precedence: bulk

On Wed, Feb 25, 2026 at 8:32=E2=80=AFAM Amit Kapila <amit.kapila16@gmail.co=
m> wrote:
>
> On Tue, Feb 24, 2026 at 6:49=E2=80=AFPM Dilip Kumar <dilipbalaut@gmail.co=
m> wrote:
> >
> > On Tue, Feb 24, 2026 at 4:55=E2=80=AFPM Amit Kapila <amit.kapila16@gmai=
l.com> wrote:
> > >
> > > On Tue, Feb 24, 2026 at 4:34=E2=80=AFPM Ajin Cherian <itsajin@gmail.c=
om> wrote:
> > > >
> > > > Currently REFRESH SEQUENCES can only be called if the subscription =
is
> > > > enabled. All it does is change the states of all the sequences in
> > > > subscription_rel to INIT, this will prompt the sequence worker to w=
ake
> > > > up and unconditionally sync all the sequences. For sequences in the
> > > > INIT state, it doesn't check if there is drift or not, it updates a=
ll
> > > > sequences unconditionally. From your discussions with Dilip, I
> > > > understand we want to reduce the time it takes to REFRESH SEQUENCES=
 at
> > > > the time of an upgrade. If so, then this might not be a good approa=
ch.
> > > >
> > >
> > > The point I was trying to make is that with automatic sequence sync,
> > > we won't need to execute REFRESH SEQUENCES before upgrade or failover
> > > as the sequences will be in sync.
> > >
> >
> > That is a valid goal, however, the sequence sync worker is an
> > asynchronous process triggered at specific intervals. For a switchover
> > to guarantee consistency, we must disconnect all connections from the
> > current publisher and wait for all pending data to sync. Relying on an
> > automated, interval-based worker at this critical time is risky, as it
> > directly extends our downtime. To minimize the cutover window, we
> > should either treat sequence data the same as relational data
> > (synchronous streaming) or manually trigger for the sequence sync
> > during the switchover phase, i.e. REFRESH SEQUENCES.  Anyway this is
> > my understanding, do you have anything else in mind that how you are
> > trying to completely avoid REFRESH SEQUENCES.  Having said that I
> > agree that in many cases where sequences are not frequently modified
> > it is very much possible that when we are in switchover phase all
> > sequences are already in sync and if we can identify that then we can
> > avoid REFRESH SEQUENCES, but my point is we can not completely avoid
> > that in all caes.
> >
>
> Right, I am also of the point that we can't completely avoid REFRESH
> SEQUENCES in all cases. Having sequence sync worker syncing
> periodically reduces the chances that one need to perform REFRESH
> SEQUENCES, so we need both. Do we agree with that?
>
> Additionally, I am thinking that after sync-worker we change REFRESH
> SEQUENCES functionality such that it will refresh all sequences during
> command. And, even we fail to copy one sequence, the command should
> fail. This should use almost the same functionality (like we sync only
> the required ones) as syncworker but online. This is because the case
> where it would be used will be less likely, say when due to some
> reason like max_logical_workers limit, we are not able to invoke
> sequencesync worker or just before upgrade (when sequences are not
> already synced), so doing sync during refresh command sounds
> reasonable. What do you think?
>

I agree with the overall direction of the discussion. But this is how
I look at it:

Given that sequences can be large, it=E2=80=99s not always practical and ma=
y
place an unnecessary burden on users to manually verify whether all
sequences are in sync before an upgrade. Requiring users to inspect
sequence values and manually determine drift before deciding whether
to run REFRESH introduces unnecessary operational complexity. So
instead of trying to find ways to avoid REFRESH, we can position it as
the final safety step before upgrade, optimized to refresh only
drifted sequences and to fail if synchronization of any required
sequence does not succeed. This approach gives us several advantages:

--Users don=E2=80=99t need to manually check for drift.
--REFRESH becomes a safe, final synchronization step before upgrade.
--In most real-world scenarios, sequences are likely already in sync
due to the periodic sync worker. When everything is already
synchronized, REFRESH effectively becomes a lightweight confirmation
check.
--If some sequences are out of sync (due to high activity, worker
limits such as max_logical_replication_workers, or other edge cases),
REFRESH will correct only those.

This creates a practical win-win situation: fast execution when
sequences are already in sync, guaranteed correctness when they are
not, and no additional manual validation burden on the user. Thoughts?

thanks
Shveta