PG19 Hacktober: 31 Days of New Features: Logical Replication Finally Replicates Sequences

By /

Welcome back to the PG19 Hacktober series!!

PostgreSQL 19 is on the way with new enhancements, including notable improvements to logical replication. In this blog, we will explore how sequences can be replicated from the publisher to the subscriber using logical sequence replication. We will also discuss the performance improvements, how sequence replication works behind the scenes, and how it worked in previous PostgreSQL versions, such as PostgreSQL 18.

Background / Context

  • We are aware that in PostgreSQL 18, sequences are not logically replicated as part of native table or DDL logical replication.
  • The sequence capture happens in PostgreSQL 18 when a row is inserted into a table that uses an auto-increment column called serial or Identity, PostgreSQL reads the sequence object, assigns the next ID, and commits the row.
  • Here, in PostgreSQL 18, logical replication streams the actual row data containing that new ID, but it does not stream the internal counter or state of the sequence object itself.

Pain points

  • Primary key conflicts during failover: In PostgreSQL 18, logical replication copies table data to the subscriber, but it does not automatically replicate sequence values. If the subscriber is promoted to primary during failover, its sequence may generate an ID that already exists in the table, causing a duplicate key error.
  • Manual sequence synchronization is risky: We often use scripts to find the maximum ID in the publisher table and update the subscriber’s sequence using setval(). However, if a new row is inserted on the publisher between these two steps, the sequence may become outdated, leading to duplicate IDs and insert failures.
  • Standalone sequences can be missed: Custom scripts usually focus on sequences associated with SERIAL or IDENTITY columns. Standalone sequences, which are created manually and called directly by application logic, can easily be overlooked and remain out of sync.

Why was it so hard to implement?

Why Did Sequence Replication Take So Long?

PostgreSQL introduced native logical replication in PostgreSQL 10, but sequence replication remained a challenge for many years. The main reason was the difference between how transactions work and how sequences are designed for performance.

1. Sequences Do Not Follow Transaction Rollbacks

Logical replication reads changes from the Write-Ahead Log (WAL) and applies transactional changes in commit order.

Sequences work differently. They are designed to generate numbers quickly without waiting for transactions to finish.

For example:

  • Transaction A gets sequence value 1.
  • Transaction B gets sequence value 2.
  • Transaction A fails and rolls back.

Even though Transaction A failed, sequence value 1 is not returned to the sequence. The next value continues from 3.

This behavior helps PostgreSQL avoid unnecessary locking and allows multiple transactions to generate sequence values concurrently.

Because sequence values do not roll back with transactions, replicating them correctly through logical replication is more complicated.

2. Replicating Every Sequence Change Can Affect Performance

Imagine a database processing thousands of inserts every second. Each insert may call nextval() to generate a new ID.

If PostgreSQL recorded and replicated every sequence increment, it would create additional replication traffic and processing overhead.

This could affect performance, especially in databases with high transaction volumes.

Therefore, PostgreSQL needed an approach that could replicate sequence state correctly without adding too much overhead.

An earlier implementation was attempted during PostgreSQL 16 development but was later reverted because of the complexity involved in handling sequence changes safely and efficiently.

What’s New in PostgreSQL 19:

PostgreSQL 19 takes a simpler approach. Instead of replicating every sequence increment continuously, it synchronizes sequence values at specific points in time.

Sequence synchronization happens during these operations:

CREATE SUBSCRIPTION
ALTER SUBSCRIPTION ... REFRESH PUBLICATION
ALTER SUBSCRIPTION ... REFRESH SEQUENCES

Sequence Sync Workers: PostgreSQL 19 Use background workers to synchronize sequence values instead of replicating every nextval() increment individually.

Synchronization at Specific Events: Sequence values are synchronized when creating a subscription, refreshing a publication, or running ALTER SUBSCRIPTION … REFRESH SEQUENCES.

Native Publication Support: PostgreSQL 19 introduces FOR ALL SEQUENCES and FOR ALL TABLES, ALL SEQUENCES options, helping include standalone sequences in logical replication.

This means PostgreSQL can copy sequence state without sending every nextval() operation through the logical replication stream.

Hands-On Example:

Step1: Create publications for the table(s) as well as the sequence(s) and create a subscription.

Create a table and create a sequence on any column by using SERIAL and check the sequence associated with that column along with sequence name.

postgres=# CREATE TABLE public.logical_sequence (
    id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    department TEXT
);
CREATE TABLE

SELECT pg_get_serial_sequence(
    'public.logical_sequence', 'id'
);
     pg_get_serial_sequence
--------------------------------
 public.logical_sequence_id_seq
postgres=# CREATE PUBLICATION pagila_pub FOR TABLE public.logical_sequence;
CREATE PUBLICATION
postgres=# CREATE PUBLICATION pagila_seq_pub FOR ALL SEQUENCES;
CREATE PUBLICATION

Step 2:Check the publications

postgres=# SELECT pubname FROM pg_publication
WHERE pubname IN ('pagila_pub', 'pagila_seq_pub');
    pubname
----------------
 pagila_pub
 pagila_seq_pub

Step3: Create a subscription.

 pagila=#  CREATE SUBSCRIPTION pagila_se
CONNECTION 'host=192.168.56.12 port=5434 dbname=pagila user=repl_user2 password=mydb'
PUBLICATION pagila_pub, pagila_seq_pub;
NOTICE:  created replication slot "pagila_se" on publisher
CREATE SUBSCRIPTION

Subscribe the above to pagila_pub & pagila_seq_pub 

pagila=# SELECT
    pubname AS publication_name,
    schemaname,
    sequencename
FROM pg_publication_sequences
WHERE schemaname = 'public'
  AND sequencename = 'logical_sequence_id_seq';
 publication_name | schemaname |      sequencename
------------------+------------+-------------------------
 pagila_seq_pub   | public     | logical_sequence_id_seq

The logical sequence_id belongs to the publication of ” pagila_seq_pub “

Step 4: Check the sequence synchronization as well as the values being replicated from the primary.

Primary:

pagila=# INSERT INTO public.logical_sequence
    (employee_name, department)
SELECT
    'Employee_' || g,
    'Testing'
FROM generate_series(1, 40) AS g;
INSERT 0 40
pagila=# SELECT COUNT(*) AS row_count,
       MIN(id) AS min_id,
       MAX(id) AS max_id
FROM public.logical_sequence;
 row_count | min_id | max_id

-----------+--------+--------
        43 |      1 |     43
(1 row)

Subscriber:

Before checking the sequences and values, check the subscriber

pagila=# SELECT
    srrelid::regclass AS object_name,
    srsubstate,
    srsublsn
FROM pg_subscription_rel
WHERE srsubid = (
    SELECT oid
    FROM pg_subscription
    WHERE subname = 'pagila_se'
)
ORDER BY srrelid::regclass::text;
       object_name       | srsubstate |  srsublsn
-------------------------+------------+-------------
 logical_sequence        | r          | 11/6A8EEC18
 logical_sequence_id_seq | r          | 11/6A8A71A0
(2 rows)
postgres=# SELECT COUNT(*) AS row_count,
       MIN(id) AS min_id,
       MAX(id) AS max_id
FROM public.logical_sequence;
 row_count | min_id | max_id
-----------+--------+--------
        43 |      1 |     43
(1 row)

pagila=# SELECT pubname, puballsequences, puballtables
FROM pg_publication
WHERE pubname IN ('pagila_pub', 'pagila_seq_pub');
    pubname     | puballsequences | puballtables
----------------+-----------------+--------------
 pagila_pub     | f               | f
 pagila_seq_pub | t               | f

Here, we can observe that when a publication is created, sequences are also replicated from the publication to the subscription.

Primary:

check the sequences which are assiciated to your publication on publication/primary node ,here I have associated my sequence called “logical_sequence_id_seq” to “pagila_seq_pub” publication.


 
postgres=#SELECT *
FROM pg_publication_sequences
WHERE pubname = 'pagila_seq_pub';
    pubname     | schemaname |      sequencename
----------------+------------+-------------------------
 pagila_seq_pub | public     | logical_sequence_id_seq

postgres=# SELECT nextval('public.logical_sequence_id_seq')
FROM generate_series(1, 70);
 generated_value
-----------------
             374
             375
             376
             377
             378
             379
             380
             381
             382
             383
             384
             385
             386
             387
             388
             389
             390
             391
             392
             393
             394
             395
             396
             397
             398
             399
             400
             401
             402
             403
             404
             405
             406
             407
             408
             409
             410
             411...........443

postgres=#  SELECT nextval('public.logical_sequence_id_seq') AS generated_value
FROM generate_series(1, 70);
pagila=# SELECT *
FROM pg_get_sequence_data('public.logical_sequence_id_seq');
 last_value | is_called |  page_lsn
------------+-----------+-------------
        443 | t         | 11/6AB1E540

Subscriber: check synchronization

postgres=#  SELECT last_value, is_called
FROM public.logical_sequence_id_seq;
 last_value | is_called
------------+-----------
        303 | t

(1 row)
postgres=# SELECT srrelid::regclass AS object_name,
       srsubstate,
       srsublsn
FROM pg_subscription_rel
WHERE srsubid = (
    SELECT oid
    FROM pg_subscription
    WHERE subname = 'pagila_se'
)
AND srrelid = 'public.logical_sequence_id_seq'::regclass;
       object_name       | srsubstate |  srsublsn
-------------------------+------------+-------------
 logical_sequence_id_seq | r          | 11/6A8A71A0
(1 row)

The subscriber values are not in sync,so refresh by using Alter command.

postgres=# ALTER SUBSCRIPTION pagila_se REFRESH SEQUENCES;
ALTER SUBSCRIPTION
pagila=# SELECT last_value, is_called
FROM public.logical_sequence_id_seq;
 last_value | is_called
------------+-----------
        443 | t
(1 row)

After refreshing on the subscriber, the values are in sync.

Why It Matters:

NoUse caseReal-world exampleBenefit
1Major version upgradesUpgrading PostgreSQL 14 to PostgreSQL 19 using logical replication.Synchronizes sequence values before cutover and helps prevent duplicate IDs.
2On-premises to cloud migrationMoving a PostgreSQL database from a local data center to Azure or AWS.Helps the target database continue generating IDs correctly after migration.
3Disaster recoveryPromoting a logical subscriber after the primary server fails.Reduces the risk of primary key conflicts on the new primary.
4Blue-green deploymentRunning old and new database environments side by side before switching applications.Helps synchronize sequence state before switching to the new environment.
5Data center migrationMoving a production database from one data center to another.Simplifies sequence synchronization during the final cutover.
6Staging and QA environmentsReplicating production data into a testing database.Helps prevent duplicate IDs when developers test inserts and application workflows.
7Database platform migrationMigrating a database between PostgreSQL-compatible platforms using logical replication.Reduces the need for custom scripts to synchronize sequence values.
8Planned switchoverMoving production workloads from the current primary to a new server during maintenance.Allows administrators to refresh sequence values before promoting the subscriber.

Caveats / Things to Watch Out For

The main limitation for logical sequence replication is that sequence synchronization is not continuous. If the publisher generates more values after synchronization, the subscriber’s sequence can become outdated again. Therefore, synchronize sequences immediately before promoting the subscriber during a planned failover or switchover.

When to Refresh Sequences PostgreSQL

  • Before a planned failover to a logical replica.
  • Before promoting a subscriber to the primary server.
  • After completing a major PostgreSQL version upgrade.
  • After large bulk inserts on the publisher.
  • On a scheduled cron job for hot standbys (every N minutes).
  • Never refresh sequences after every individual INSERT, table replication handles row changes, but sequence state is not automatically synchronized through standard logical replication.

Conclusion:

PostgreSQL 19 introduces native sequence synchronization in logical replication, reducing the need for custom scripts and helping prevent duplicate primary key errors during migrations and failovers. Sequence values can be refreshed using subscription commands without additional server configuration parameters. However, synchronization is not continuous, so sequences should be refreshed before cutover or failover.

References:

Looking Forward to Postgres 19: Logically Sequenced 

https://www.postgresql.org/docs/19/logical-replication-sequences.html

. 

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top