Looking Forward to Postgres 19: Logically Sequenced

Logical replication has been an integral part of Postgres since version 10 released in 2017. It's a very convenient system for synchronizing one or more tables from one running Postgres cluster to another, and the community has embraced it almost without reservation. It's a great feature we've all come to rely on.

For all that, it has never been a flawless panacea. Perhaps the most glaring and conspicuous omission in Postgres logical replication is that of sequences. Novice users might run a logical migration or promote the new cluster, and meet a wall of duplicate key violations on the very first insert. The more experienced mind the gap and bolt on supplementary steps to circumvent this, quietly hoping they remember to run it during the cutover window.

But why? Why were sequences left out of logical replication for the better part of a decade? Why let a freshly promoted cluster restart its counters at 1 and collide with the very rows it just spent hours copying over? That turns out to be a surprisingly long story, and one Postgres 19 finally gives a satisfying ending. So let's get into how sequences became the awkward holdout of logical replication, and what changed to bring them along at last.

The Sequence That Time Forgot

There’s a sequence hiding behind every GENERATED AS IDENTITY or SERIAL/BIGSERIAL column ever created in a Postgres cluster. That sequence provides the next value to the related column when no default is supplied. It’s easy to take for granted because it’s always been there and everyone knows how they work.

Any time a write happens in Postgres, the new row contents get written to the WAL. Logical replication works by essentially decoding the raw WAL tuple into an equivalent INSERT or UPDATE statement and replaying it on the subscriber. When a row with id = 9000001 arrives on the subscriber, that's the value that gets written. Meanwhile, the subscriber's own sequence object (probably copied from the publisher at some point) remains unused. That’s great until the subscriber has to generate a value of its own.

Consider the migration including a table that is replicated all the way up to nine million rows. Once we redirect the application to the subscriber node as the new write target, it may be customary to expect the sequence starts at nine million and one. But that's not what happens; the sequence on the subscriber exists as it was initialized, and probably sits at the original value of 1. And that's the value the identity or serial will return, resulting in a cascade of "duplicate key value violates unique constraint" errors.

Not exactly ideal, especially in production.

The official restrictions page has spelled this out plainly for years: sequence data is not replicated. The standard remedy was to fix the sequences manually right before redirecting application and user traffic. Executing something like this on the publisher generates a script of setval() calls. Running the script on the subscriber essentially solves the problem:

COPY (
  SELECT format('SELECT setval(%I.%I, %s);',
           schemaname, sequencename, last_value + 1000
         )
    FROM pg_sequences
) TO '/tmp/set-sequences.sql';

Notice the + 1000. That padding isn't decorative. Even with a highly ambitious activity drain on the old cluster, there's probably a short window where some pending writes could bump the sequence on the publisher. So we inject a 1000 value "buffer" to absorb any of those errant writes until we promote the subscriber to be the new permanent write target. But it's time sensitive: forget to run it, or run it at the wrong moment, and we're back to duplicate keys.

There had to be a better way. For a long time, there was exactly one.

pglogical to the Rescue?

Before native logical replication existed at all, the pglogical extension from 2ndQuadrant was how serious users replicated Postgres data with a publish/subscribe model. Unlike the in-core feature that eventually supplanted it, pglogical learned to handle sequences from the beginning.

The mechanism is a set of functions called on the provider to enroll sequences into a replication set. It's possible to add them one at a time:

SELECT pglogical.replication_set_add_sequence(
  set_name := 'default',
  relation := 'public.orders_id_seq',
  synchronize_data := true
);

Or include everything in a schema at once:

SELECT pglogical.replication_set_add_all_sequences(
  set_name := 'default',
  schema_names := ARRAY['public'],
  synchronize_data := true
);

When enabled, the synchronize_data flag pushes the current value across immediately. There's also a pglogical.synchronize_sequence() function which can force a re-sync on every subscriber, handy after a big data load or partway through an online upgrade. But not even pglogical ever replicated sequences in real-time. Here's what the documentation has to say:

That's basically our + 1000 trick. Pglogical maintains our handy safety buffer automatically with the help of a periodic sequence bump through a background worker process. There’s a reason it works this way, even when handled by an extension.

How we Got Here

So why couldn't native logical replication just decode sequence changes the way it decodes inserts and updates? Why didn’t pglogical? The reality is that sequences break one of the foundations logical decoding is built on.

Sequences are non-transactional. Every generated value, even when rolled back, is permanently consumed. One superpower of sequences is that they're essentially asynchronous, but this comes at the cost of gaps. This allows multiple simultaneous transactions to obtain sequence values without waiting, but it also means any transaction can abandon whatever value it received.

By contrast, logical decoding lives and breathes transactions. It reorders the WAL into neat transactional bundles and replays them in commit order. Sequences don't fit this model at all, making it essentially impossible to add them to a standard logical replication stream.

This wasn't for lack of trying. Tomas Vondra spent years on a patch for logical decoding of sequences that would include sequence advances as part of the decode stream. It actually got committed during the Postgres 16 development cycle. And then it was reverted before the final release.

Why rip out a feature that was already committed? The trouble was reconciling the transactional and non-transactional halves of sequence behavior, and it wasn't clear the design could be salvaged without substantial effort. An alternative idea to log sequence state at commit time was floated and then abandoned too, weighed down by overhead, tricky ordering between concurrent transactions, and reliability concerns.

That's a sobering amount of effort to end up back at square one, but that’s what happened. Sequences became a permanent fly in the logical replication ointment, and everyone just kind of accepted it.

A Sync By Any Other Name

So how do you replicate a counter that isn't possible to cleanly decode? That's the really neat part: you don't! The breakthrough in Postgres 19, led by Vignesh C with Tomas Vondra, was to stop fighting the non-transactional nature of sequences altogether. Instead of streaming every increment through the decode pipeline, the new feature synchronizes sequence values at well-defined moments. Hopefully this is already starting to sound familiar. 

We never really needed the subscriber's sequences to track the publisher increment for increment. That gives us incredibly clean invocation points. The release notes lay out the three moments when this synchronization kicks in:

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

Creating a subscription now pulls sequence values across alongside the initial table data. Then REFRESH PUBLICATION reconciles which sequences exist on the subscriber to match the publisher, updating their values while it's at it. The third command just does what we want outright, directly updating sequence values on the subscriber.

This is not continuous or even periodic replication. Once a sequence value is synchronized, it goes stale the instant the publisher hands out another value. Applications that keep writing to the old primary means the subscriber drifts behind again until the next refresh. But that also dramatically simplifies matters: sync the sequences as the last step before promoting the subscriber.

Let's see what that might look like.

Sequence Sync in Action

On the publisher side, Postgres 19 adds an ALL SEQUENCES clause to CREATE PUBLICATION, mirroring the long-standing ALL TABLES:

CREATE PUBLICATION migration_pub FOR ALL TABLES, ALL SEQUENCES;

That single statement now publishes both the table data and every sequence in the database, including sequences created later. It's also possible to create publications purely for sequences. But take note: the clause is ALL SEQUENCES. There's no syntax for cherry-picking individual sequences into a publication the way you can with tables. This is an all or nothing proposition.

On the subscriber, creating the subscription does the initial work for us:

CREATE SUBSCRIPTION migration_sub
  CONNECTION 'host=oldprimary dbname=app user=repl'
  PUBLICATION migration_pub;

At this point the tables begin their initial copy and the sequence values are pulled across as part of the same process. Nothing should be surprising here. The interesting part comes at the time of the cutover. The initial sync may have happened hours or days ago, and sequences are probably vastly different from their initial state after the sync.

Right before it's time to promote, give sequences one final bump with the new ALTER SUBSCRIPTION command:

ALTER SUBSCRIPTION migration_sub REFRESH SEQUENCES;

That re-reads every sequence value from the publisher and stamps it onto the subscriber without any further fanfare. If there are any new sequences on the publisher since the subscription was created, it's probably a good idea to use REFRESH PUBLICATION instead, since that reconciles existence as well as values.

Compare that to where we started. The entire ritual from the top of this article collapses into one command that the database understands natively.

Peeking Under The Hood

Naturally, it's probably a good idea to validate the values that actually made it across the divide. Postgres 19 provides the new pg_get_sequence_data() function to inspect the synchronized state of a sequence directly:

SELECT last_value FROM pg_get_sequence_data('public.orders_id_seq');

 last_value 
------------
 9000001

We can use that to verify all sequences on both the publisher and subscriber prior to committing to the transition. 

Errors during sequence synchronization get first-class tracking too. The pg_stat_subscription_stats view gains a sync_seq_error_count column to spot sequence problems. This necessitated renaming the old sync_error_count to sync_table_error_count to distinguish the two states. Monitoring dashboards using this view may also need to be updated, so be careful.

It's always a good idea to get a picture of the subscription before taking any drastic actions:

SELECT subname, sync_table_error_count, sync_seq_error_count
  FROM pg_stat_subscription_stats;

If everything in this view looks good, feel free to continue.

Finally in Sequence

It's a little ironic that the solution which finally shipped is the same one pglogical leveraged since its inception, and the same one we've all been faking by hand with setval() scripts. Synchronize the value with a minor safety buffer when requested—how anti-climactic. Then again, it's hard to beat the KISS principle.

This removes one of the last manual gotchas from logical replication for anyone running cluster migrations or major-version upgrades. Given those are two of the biggest use-cases for logical replication, that's a major advancement. More real-time use-cases probably fall under Multi-Master territory, and are better served by UUIDs or Snowflake sequences anyway.

In any case, Postgres 19 is only a few months away. We encourage you to experiment with the betas and release candidates when you get a chance. Try the new FOR ALL SEQUENCES publication syntax. Fiddle with REFRESH SEQUENCES and see what happens. You've been waiting for the opportunity for years, after all!