Subject: [PATCH] Support automatic sequence replication

From: Ajin Cherian <[email-masked]>
Date: Tue, 3 Feb 2026 14:48:25 +1100

Hello hackers,

I'd like to propose an improvement to the sequence replication feature
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.

Proposed Enhancement:

This patch changes the sequence sync worker to run continuously
throughout the subscription lifetime, automatically detecting and
correcting sequence drift. The key changes are:

1. The sequence sync worker remains running instead of exiting after
initial sync, periodically checking for and synchronizing drifted
sequences.

2. The worker uses an exponential backoff strategy - starting at 2
seconds, doubling up to a maximum of 30 seconds when sequences are in
sync, and resetting to the minimum interval when drift is detected.

3. Since synchronization is now automatic, ALTER SUBSCRIPTION ...
REFRESH SEQUENCES is no longer needed and has been removed.

The patch modifies documentation to reflect the new behavior, removes
the REFRESH SEQUENCES command from the grammar and subscription
commands, and implements the continuous monitoring loop in
sequencesync.c. Tap tests have been updated to verify automatic
synchronization rather than manual refresh.

The attached v2 patch is attached and ready for review.

Thoughts and feedback are welcome!

[1] - https://github.com/postgres/postgres/commit/5509055d6956745532e65ab218e15b99d87d66ce

Best regards,
Ajin Cherian
Fujitsu Australia

---

From: Ashutosh Sharma <[email-masked]>
Date: Fri, 13 Feb 2026 11:38:55 +0530

Hi,

On Tue, Feb 3, 2026 at 9:18 AM Ajin Cherian <[email-masked]> wrote:
> [1] -
https://github.com/postgres/postgres/commit/5509055d6956745532e65ab218e15b99d87d66ce

Is this expected behavior?

1) *Publisher:*

*create sequence t1_seq;create table t1 (id int default nextval('t1_seq')
primary key, a int);create publication t1_pub for table t1;create
publication t1_seq_pub for all sequences;*

2) *Subscriber:*

*create sequence t1_seq;create table t1 (id int default nextval('t1_seq')
primary key, a int);create subscription t1_sub connection 'host=127.0.0.1
port=37500 dbname=test user=$USER' publication t1_pub with (create_slot =
false, slot_name = 't1_sub');create subscription t1_seq_sub connection
'host=127.0.0.1 port=37500 dbname=test user=$USER' publication t1_seq_pub
with (create_slot = false, slot_name = 't1_seq_sub');select * from
pg_subscription_rel;select * from pg_sequences;*

3) *Publisher:*

*insert into t1(a) values(10);select * from pg_sequences;*

4) *Subscriber:*

*select * from pg_sequences;* -- in sync with publisher.
*insert into t1(a) values(20);*
*select * from pg_sequences;* -- the sequence gets deviated from publisher.

After a few minutes, re-running the above shows that the sequence value is
reset to match the publisher. However, any new insert on the subscriber
fails:

*insert into t1(a) values(30);*

*ERROR:  23505: duplicate key value violates unique constraint
"t1_pkey"DETAIL:  Key (id)=(2) already exists.SCHEMA NAME:  publicTABLE
NAME:  t1CONSTRAINT NAME:  t1_pkey*

--

Automatic sequence replication resets the last_value on the subscriber to
match the publisher, which leads to duplicate key conflicts and prevents
further inserts on the subscriber.

--
With Regards,
Ashutosh Sharma.

---

From: Dilip Kumar <[email-masked]>
Date: Fri, 20 Feb 2026 10:10:40 +0530

> 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’d like to understand
how updating the value at specific intervals will improve the user
experience.

-- 
Regards,
Dilip Kumar
Google

---

From: Amit Kapila <[email-masked]>
Date: Fri, 20 Feb 2026 10:48:28 +0530

> how updating the value at specific intervals will improve the user

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.

-- 
With Regards,
Amit Kapila.

---

From: Dilip Kumar <[email-masked]>
Date: Fri, 20 Feb 2026 11:54:22 +0530

> 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