Re: Commit Sequence Numbers and Visibility

Subject: Re: Commit Sequence Numbers and Visibility

Hi Jeff, Matthias,

On Tue, Jun 2, 2026, Jeff Davis wrote:
> some snapshots on the primary are impossible on the replica; and
> it's inherently more expensive to acquire a new snapshot.

I think it would help to keep three related goals separate here:

1. making snapshot acquisition scale better than copying xip[];
2. making primary and standby snapshots prefixes of one common
visibility order;
3. defining durability guarantees for the data admitted by a snapshot.

My original interest in CSNs was the second problem, commonly called
Long Fork[0]. The invariant I would like to get from this work is that
every snapshot observable on a primary or physical standby is a prefix
of one common transaction visibility order.

I attached a deterministic standalone TAP reproducer. T1 writes the
earlier commit record and waits for remote_apply on a paused synchronous
standby. T2 then commits asynchronously and becomes visible on the
primary. A second standby applies T1 but is paused before T2. HEAD can
then simultaneously return (T1 invisible, T2 visible) on the primary
and (T1 visible, T2 invisible) on the standby. The test is not proposed
for the recovery test schedule at this point.

This is also a concrete instance of the mixed-durability sequence
discussed above. I think we need to distinguish two guarantees:

> wait until the CSN is flushed to the point that it meets the
> durability requirements of the transaction using the snapshot rather
> than the transaction writing the changes.

- writer-selected durability says how persistent a transaction's changes
must be before its COMMIT may return;
- reader-selected durability says how persistent all data admitted by a
snapshot must be before the reader may consume it.

Neither guarantee by itself defines the visibility order. A commit-LSN
CSN gives us the common prefix needed to prevent Long Fork, but exposes
the hard case directly: a prefix containing T2 also contains T1 while
T1 is still waiting for remote_apply.

The Vancouver notes answer the question of a synchronous reader
seeing an asynchronous commit by making the reader wait, but leave the
reverse question unanswered: should an asynchronous reader see a
synchronous commit that is still waiting? The reproducer makes the
consequence of that choice concrete across a primary and standby.

Can we first agree on the common visibility order, and treat the
durability required before a reader may consume a prefix as a separate
policy? More specifically, may a reader with a weak durability
requirement consume T1 while T1's COMMIT is still waiting for
remote_apply? If not, does T1 keep T2 invisible despite T2 having
already completed?

The same distinction matters when a synchronous replication wait is
canceled after local commit, and when a recovered primary starts
accepting clients before its end-of-recovery LSN reaches the synchronous
standbys. Those are the two cases I have been working on in the "Small
fixes needed by high-availability tools" thread[1].

On SERIALIZABLE, predicate-lock state is not WAL-logged, and
SERIALIZABLE transactions are not supported on hot standbys today.
Distributed SSI seems like a separate and much larger project, rather
than a prerequisite for CSN visibility. I would be interested in
helping with it, though its prospects look uncertain while the existing
series of SSI bug fixes is still struggling to attract committer
attention.

Thank you!

Best regards, Andrey Borodin.

[0] https://aws.amazon.com/blogs/database/understanding-transaction-visibility-in-postgresql-clusters-with-read-replicas/
[1] https://www.postgresql.org/message-id/flat/0B44E464-BA62-4056-9465-3320DD2D0302%40yandex-team.ru