PostgreSQL 19 Beta 3: Detecting Logical Replication Sequence Regressions

Introduction

Logical replication is one of those PostgreSQL features that developers may not think about until a system starts using more than one database.

A typical application might have a primary PostgreSQL database and one or more replicated databases for reporting, migrations, regional workloads, or other operational purposes.

A simplified architecture looks like this:

Application
    |
    v
Primary PostgreSQL
    |
    | Logical Replication
    v
Subscriber Database

This sounds straightforward, but sequence values can introduce subtle problems.

PostgreSQL sequences are commonly used to generate values for identity or auto-incrementing columns. When data is replicated logically, the rows and the sequence state are related but are not necessarily synchronized in exactly the same way.

PostgreSQL 19 Beta 3 is particularly interesting for testing because the release includes fixes related to logical replication sequence synchronization and REFRESH SEQUENCES.

For developers and database engineers evaluating PostgreSQL 19, this makes sequence behavior an important area to test before relying on logical replication in production.

What Is Logical Replication?

Logical replication copies changes from a publisher database to a subscriber database.

The basic architecture is:

Publisher
    |
    | INSERT / UPDATE / DELETE
    v
Logical Replication
    |
    v
Subscriber

Unlike physical replication, logical replication works at the logical change level.

For example, when an application executes:

INSERT INTO customers (name)
VALUES ('John Smith');

the logical replication system can replicate the resulting row change to the subscriber.

A simplified setup looks like:

PostgreSQL Publisher
        |
        v
Publication
        |
        v
Logical Replication
        |
        v
Subscription
        |
        v
PostgreSQL Subscriber

This provides flexibility for selective table replication and other logical replication scenarios.

What Is a PostgreSQL Sequence?

A sequence is a database object that generates numeric values.

For example:

CREATE SEQUENCE customer_id_seq
    START WITH 1
    INCREMENT BY 1;

The application can request the next value:

SELECT nextval('customer_id_seq');

which produces values such as:

Sequences are commonly used with identity-style primary keys.

For example:

CREATE TABLE customers
(
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

The database can generate the id value automatically when a row is inserted.

Why Sequences Matter During Replication

Consider a publisher:

Publisher
Customers
IDs:
1
2
3
4
5

and a subscriber:

Subscriber
Customers
IDs:
1
2
3
4
5

Now imagine the publisher generates:

6
7
8

The rows may be replicated successfully.

But sequence state is a separate concern.

A useful mental model is:

Row Data
   +
Sequence State
   =
Consistent Replicated Database

If those two pieces become inconsistent, future inserts can produce unexpected results.

A Simple Sequence Problem

Suppose the subscriber contains:

Customer IDs:
1
2
3
4
5

but its local sequence still expects to generate:

A new local insert could attempt to reuse an existing identifier.

That can result in a primary-key conflict.

For example:

ERROR:
duplicate key value violates unique constraint

The exact error depends on the schema and operation, but the underlying issue is the same: generated key state and existing data are out of alignment.

Why PostgreSQL 19 Beta 3 Is Worth Testing

PostgreSQL 19 Beta 3 includes fixes related to logical replication sequence synchronization and REFRESH SEQUENCES.

That makes this release a useful testing target for teams that depend on logical replication.

The important point is not simply:

"PostgreSQL 19 has a sequence feature."

The more useful question is:

"Does our replication workflow keep sequence state
consistent under the operations our application performs?"

That requires an actual test.

Understanding `REFRESH SEQUENCES`

PostgreSQL provides mechanisms for refreshing sequence state in logical replication scenarios.

A simplified conceptual workflow is:

Publisher
    |
    v
Sequence Changes
    |
    v
Replication
    |
    v
Subscriber
    |
    v
Refresh Sequence State

The exact commands and behavior should be tested against the PostgreSQL version and logical replication configuration being used.

For PostgreSQL 19 Beta 3, sequence-related changes are particularly relevant because the release notes identify fixes around sequence synchronization and REFRESH SEQUENCES.

Build a Reproducible Test Environment

Before testing PostgreSQL 19 Beta 3, create two environments:

Publisher
PostgreSQL 19 Beta 3
       |
       v
Subscriber
PostgreSQL 19 Beta 3

For application testing, you can use a simple customer table:

CREATE TABLE customers
(
    id BIGINT GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

    CONSTRAINT pk_customers
        PRIMARY KEY (id)
);

The important part is that the table has an automatically generated identifier.

Insert Initial Data

Start with several rows:

INSERT INTO customers (name)
VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie'),
    ('David'),
    ('Emma');

Verify the data:

SELECT *
FROM customers
ORDER BY id;

You should see sequential identifiers.

The exact values depend on the starting sequence state.

Check Sequence State

For an identity column, PostgreSQL provides metadata that can be inspected to understand the associated sequence.

For a separately named sequence, you can use:

SELECT last_value
FROM customer_id_seq;

This gives you a useful baseline before replication testing.

The important thing is to record both:

Current table data
+
Current sequence state

Configure the Publication

A simplified logical replication publication can be created with:

CREATE PUBLICATION customer_publication
FOR TABLE customers;

This tells PostgreSQL which table changes should be published.

The subscriber then creates a subscription pointing to the publisher.

The exact connection details depend on the environment.

For example:

CREATE SUBSCRIPTION customer_subscription
CONNECTION 'host=publisher-host dbname=appdb user=replicator password=...'
PUBLICATION customer_publication;

In production, credentials should be handled securely rather than embedded in scripts or source control.

Test Normal Inserts

The first test should be simple.

Insert a new row on the publisher:

INSERT INTO customers (name)
VALUES ('Frank');

Then verify the subscriber:

SELECT *
FROM customers
ORDER BY id;

The row should appear after replication catches up.

This establishes that the basic logical replication workflow is functioning.

Test Multiple Inserts

Next, generate a larger sequence of values:

INSERT INTO customers (name)
SELECT 'Customer ' || generate_series(1, 100);

Then compare the publisher and subscriber.

Check:

SELECT COUNT(*)
FROM customers;

on both databases.

Also check:

SELECT MIN(id), MAX(id)
FROM customers;

The goal is to verify that replicated rows and generated identifiers remain consistent.

Test Sequence Advancement

Now perform another insert on the publisher:

INSERT INTO customers (name)
VALUES ('New Customer')
RETURNING id;

Record the generated identifier.

Then verify the subscriber data.

This test answers an important question:

Does the subscriber have sequence state that is consistent with the replicated data?

Do not stop after confirming that the row arrived.

Check the next generated value as well.

Test a Local Subscriber Insert

This is an important edge case.

Depending on the logical replication architecture, the subscriber may be read-only from an application perspective or may also accept local writes.

If local writes are allowed, test them explicitly.

For example:

INSERT INTO customers (name)
VALUES ('Subscriber Customer')
RETURNING id;

Now verify:

Does the generated ID conflict?
Does it overlap with replicated values?
Does the sequence remain usable?

The answer depends on the architecture and how sequences are managed.

This is exactly the type of scenario that should be tested rather than assumed.

Test Sequence Refresh

If your replication workflow uses sequence refresh operations, test them independently.

The test should record:

Sequence state before refresh
Sequence state after refresh
Table maximum ID
Next generated ID

For example:

SELECT MAX(id)
FROM customers;

Then inspect the sequence state using the appropriate PostgreSQL sequence inspection method.

The goal is to establish whether:

Sequence State

matches:

Replicated Data

after the refresh operation.

PostgreSQL 19 Beta 3 specifically includes sequence-related logical replication fixes, so this is one of the areas worth including in a regression suite.

Test Replication Lag

Replication is not necessarily instantaneous.

Therefore, avoid writing a test that assumes:

INSERT
  |
  v
Immediate SELECT on subscriber

always sees the new row.

Instead:

Publisher Insert
      |
      v
Replication
      |
      v
Wait / Poll
      |
      v
Subscriber Validation

Your test should have a reasonable timeout.

For example, an integration test could repeatedly check for the expected row instead of immediately declaring failure.

Test Large Sequence Values

Sequence bugs are not limited to small numbers.

Test values near realistic production ranges.

For example:

1,000
10,000
1,000,000

and, where relevant, much larger values.

Also test the actual data type:

BIGINT

rather than assuming INT is sufficient.

The important point is to test the range your application actually expects.

Test Rollback Scenarios

Consider:

BEGIN;

INSERT INTO customers (name)
VALUES ('Temporary');

ROLLBACK;

Sequence behavior can be different from transaction behavior because sequence operations are not treated exactly like ordinary transactional row changes.

This is an important reason not to assume:

Rollback
=
Sequence Value Returned

Sequence allocation and transaction rollback should be tested separately.

Test Gaps in Sequence Values

Sequences can contain gaps.

For example:

The missing value does not automatically indicate corruption.

Applications should generally not rely on generated identifiers being perfectly consecutive.

A benchmark should therefore distinguish:

Gap

from:

Incorrect Sequence State

These are different problems.

Test Replication After Restart

A useful production-style test is restarting the subscriber.

The sequence workflow becomes:

Publisher
    |
    v
Replication
    |
    v
Subscriber
    |
    v
Restart
    |
    v
Replication Resumes
    |
    v
Sequence Validation

After restart, verify:

Rows
Replication Status
Sequence State
New Inserts

This can reveal issues that are not visible during continuous operation.

Test Subscription Refresh

If a subscription is refreshed or reconfigured, validate sequence behavior afterward.

The test should include:

Initial Data
      |
      v
Replication
      |
      v
Refresh / Reconfiguration
      |
      v
New Publisher Inserts
      |
      v
Sequence Validation

Do not assume that a refresh operation affects only row replication.

Sequence state should be explicitly checked.

Test Schema Changes Carefully

Schema changes can make replication testing more complicated.

For example:

ALTER TABLE customers
ADD COLUMN email TEXT;

Then test:

Existing Rows
New Rows
Sequence Generation
Replication

Keep schema changes separate from sequence tests where possible.

This makes failures easier to diagnose.

Monitoring Replication

A production system should monitor logical replication rather than assuming it is healthy.

Useful checks include:

Replication Lag
Subscription State
Worker Errors
Last Applied Transaction
Table Synchronization
Sequence State

The exact monitoring queries depend on the PostgreSQL version and replication architecture.

A useful operational dashboard might look like:

Publisher
   |
   +--> WAL Generation
   |
   v
Replication
   |
   +--> Lag
   +--> Errors
   |
   v
Subscriber
   |
   +--> Apply Status
   +--> Sequence State

PostgreSQL and .NET Applications

For a .NET application using PostgreSQL through an ORM such as Entity Framework Core, sequence behavior can become visible through generated identifiers.

A typical entity might look like:

public class Customer
{
    public long Id { get; set; }

    public string Name { get; set; } = string.Empty;

    public DateTime CreatedAt { get; set; }
}

The application may simply perform:

var customer = new Customer
{
    Name = "Alice",
    CreatedAt = DateTime.UtcNow
};

db.Customers.Add(customer);

await db.SaveChangesAsync();

The database generates the identifier.

From the application's perspective, everything looks simple.

But in a replicated environment, database-level sequence behavior still matters.

What to Validate in EF Core

After replication tests, validate the application layer.

Check:

Insert
Update
Query
SaveChanges
Generated ID
Transaction behavior
Retry behavior

For example:

var customer = new Customer
{
    Name = "Test Customer"
};

db.Customers.Add(customer);

await db.SaveChangesAsync();

Console.WriteLine(customer.Id);

Then verify that the generated ID does not conflict with existing data.

Benchmark Matrix

A useful regression matrix can look like this:

Test	Publisher	Subscriber	Expected
Initial insert	Pass	Replicated	Matching row
Multiple inserts	Pass	Replicated	Matching data
Sequence state	Record	Record	Consistent
Sequence refresh	Execute	Validate	Expected state
Subscriber restart	Restart	Resume	Replication continues
Large IDs	Test	Validate	No conflict
Transaction rollback	Test	Validate	Expected behavior
Subscriber insert	Test if supported	Validate	No unexpected collision
EF Core insert	Pass	Validate	Generated ID valid
Replication lag	Measure	Measure	Within expected range

This matrix can become part of an automated regression suite.

Common Mistakes

Mistake 1: Checking Only Row Replication

Rows arriving successfully does not automatically prove sequence state is correct.

Mistake 2: Assuming Sequences Are Ordinary Rows

Sequence objects have their own behavior and lifecycle.

Mistake 3: Treating Gaps as Errors

Sequence gaps can be normal.

Mistake 4: Testing Only Small Datasets

Sequence issues can become more important as identifiers grow.

Mistake 5: Ignoring Subscriber Writes

If the subscriber accepts local writes, sequence allocation requires careful testing.

Mistake 6: Testing Without Replication Lag

Immediately querying the subscriber can produce false failures.

Mistake 7: Mixing Several Variables

Do not combine schema changes, failover, sequence refresh, and application changes in one test unless that combined scenario is specifically what you are testing.

Troubleshooting

Problem	What to Check
Duplicate key error	Sequence state and existing maximum ID
Rows replicated but inserts fail	Subscriber sequence alignment
Sequence values appear unexpected	Sequence state and transaction behavior
Subscriber is behind	Replication lag and worker status
Refresh produces unexpected state	Sequence refresh behavior and version
Application insert fails	Generated key and database constraints
Tests intermittently fail	Replication timing and asynchronous apply
Restart causes problems	Subscription state and sequence validation
Large IDs behave unexpectedly	Sequence data type and range

Best Practices

Treat Sequence State as Production Data

Monitor it just like other important database state.

Test the Full Replication Workflow

Do not test only the initial configuration.

Include Sequence Refresh Scenarios

This is particularly relevant when evaluating PostgreSQL 19 Beta 3.

Test With Realistic Data

Use representative identifier ranges and table sizes.

Separate Data Validation From Performance Testing

First establish correctness, then measure performance.

Test Subscriber Writes Explicitly

Only if your architecture allows them.

Account for Replication Lag

Use polling or an appropriate synchronization mechanism in integration tests.

Automate Regression Tests

Sequence-related problems are ideal candidates for repeatable database tests.

Validate With the Actual PostgreSQL Version

Beta behavior should be tested against the exact PostgreSQL build intended for evaluation.

Advantages of Testing Sequence Synchronization

Finds Subtle Replication Problems

Sequence problems may remain hidden until a new insert occurs.

Protects Against Duplicate Keys

Early testing can identify identifier collisions before production.

Improves Upgrade Confidence

Teams evaluating PostgreSQL 19 can test sequence behavior before adoption.

Helps .NET Teams

Applications using generated database identifiers can validate the complete application-to-database workflow.

Creates Repeatable Regression Tests

Once the scenarios are automated, future PostgreSQL upgrades can be tested more easily.

Limitations and Challenges

Replication Is Asynchronous

Tests must account for replication delay.

Sequence Behavior Is Easy to Misinterpret

Gaps do not necessarily indicate a problem.

Production Topologies Differ

A simple publisher/subscriber test may not represent a complex production environment.

Beta Software Requires Additional Validation

A beta release should not be treated as equivalent to a mature production release without appropriate testing.

Database-Level Testing Is Necessary

Application tests alone may not expose replication-specific sequence problems.

A Practical PostgreSQL 19 Regression Workflow

For teams evaluating PostgreSQL 19 Beta 3, a useful sequence test can follow this workflow:

Create Publisher
       |
       v
Create Subscriber
       |
       v
Create Tables and Sequences
       |
       v
Insert Baseline Data
       |
       v
Configure Logical Replication
       |
       v
Verify Initial State
       |
       v
Generate New IDs
       |
       v
Verify Replicated Rows
       |
       v
Validate Sequence State
       |
       v
Test REFRESH SEQUENCES
       |
       v
Restart Subscriber
       |
       v
Generate More IDs
       |
       v
Run EF Core Integration Tests
       |
       v
Record Results

The exact commands should be adapted to the replication topology and PostgreSQL configuration being tested.

What Developers Should Record

For every regression test, record:

PostgreSQL version
Publisher configuration
Subscriber configuration
Table schema
Sequence configuration
Initial sequence state
Number of rows
Replication lag
Refresh operation
Final sequence state
Generated IDs
Application result

This makes failures reproducible.

Conclusion

Logical replication can make PostgreSQL much more flexible, but sequence behavior deserves its own testing because replicated row data and sequence state are closely related without being exactly the same thing. PostgreSQL 19 Beta 3 includes fixes around logical replication sequence synchronization and REFRESH SEQUENCES, making this an important area for teams evaluating the release. The safest approach is to create a controlled publisher and subscriber environment, establish the initial sequence state, generate realistic inserts, validate replicated rows, test sequence refresh operations, restart components, and finally verify the behavior through the actual .NET application and EF Core workflow. Do not treat every sequence gap as an error, and do not assume that successful row replication proves that future inserts will always work correctly. A small, repeatable sequence regression suite can provide much more confidence when upgrading PostgreSQL or introducing logical replication into a production system.

Introduction

What Is Logical Replication?

What Is a PostgreSQL Sequence?

Why Sequences Matter During Replication

A Simple Sequence Problem

Why PostgreSQL 19 Beta 3 Is Worth Testing

Understanding REFRESH SEQUENCES

Build a Reproducible Test Environment

Insert Initial Data

Check Sequence State

Configure the Publication

Test Normal Inserts

Test Multiple Inserts

Test Sequence Advancement

Test a Local Subscriber Insert

Test Sequence Refresh

Test Replication Lag

Test Large Sequence Values

Test Rollback Scenarios

Test Gaps in Sequence Values

Test Replication After Restart

Test Subscription Refresh

Test Schema Changes Carefully

Monitoring Replication

PostgreSQL and .NET Applications

What to Validate in EF Core

Benchmark Matrix

Common Mistakes

Mistake 1: Checking Only Row Replication

Mistake 2: Assuming Sequences Are Ordinary Rows

Mistake 3: Treating Gaps as Errors

Mistake 4: Testing Only Small Datasets

Mistake 5: Ignoring Subscriber Writes

Mistake 6: Testing Without Replication Lag

Mistake 7: Mixing Several Variables

Troubleshooting

Best Practices

Treat Sequence State as Production Data

Test the Full Replication Workflow

Include Sequence Refresh Scenarios

Test With Realistic Data

Separate Data Validation From Performance Testing

Test Subscriber Writes Explicitly

Account for Replication Lag

Automate Regression Tests

Validate With the Actual PostgreSQL Version

Advantages of Testing Sequence Synchronization

Finds Subtle Replication Problems

Protects Against Duplicate Keys

Improves Upgrade Confidence

Helps .NET Teams

Creates Repeatable Regression Tests

Limitations and Challenges

Replication Is Asynchronous

Sequence Behavior Is Easy to Misinterpret

Production Topologies Differ

Beta Software Requires Additional Validation

Database-Level Testing Is Necessary

A Practical PostgreSQL 19 Regression Workflow

What Developers Should Record

Conclusion

Understanding `REFRESH SEQUENCES`