Re: [PATCH] Support automatic sequence replication

On Mon, Feb 23, 2026 at 10:44 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Fri, Feb 20, 2026 at 12:18 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> >
> > On Fri, Feb 20, 2026 at 11:54 AM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
> > >
> > > 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.
> > >
> >
> > In the "Final Catch-up" phase, without automatic sequence sync, there
> > is a possibility that the REFRESH SEQUENCES command may take a lot of
> > time to finish and in turn can delay switchover. With automatic
> > sequence sync, in best case, all sequences were already synced, and
> > even in average or worst case, the sync time would be much less.
> >
> > > 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?
> > >
> >
> > It can avoid manual refreshes entirely in most cases which the user
> > can verify by checking required LSN which we discussed to update in
> > docs. Even when manual REFRESH command is required, the time consumed
> > by the command will be much less, especially when there are large
> > number (say a million sequences) of sequences that need to be synced.
>
> Thanks for clarifying the use case, and I agree this is a valid use
> case, although I am not completely sure how exactly we are achieving
> that? I mean sequence sync workers need to fetch the list of all
> published sequences to identify whether they are in sync with the
> local sequence state or not? Or somehow we can avoid that? If not
> what we would save, updating the local states of the sequences if they
> are already in sync?
>

We would save updating the local state per-sequence which involves
writing to sequence relation page, a WAL recording, and updating the
catalog-state. I feel that for a large number of sequences this cost
could be high. Shveta or Ajin can add if I am missing or misunderstood
anything.

--
With Regards,
Amit Kapila.