Commit Sequence Numbers and Visibility

On Wed, 3 Jun 2026 at 01:33, Jeff Davis <pgsql@j-davis.com> wrote:

At pgconf.dev, we had an unconference session on $SUBJECT:

https://wiki.postgresql.org/wiki/PGConf.dev_2026_Developer_Unconference#Commit_Sequence_Numbers

CSN visibility semantics are quite simple in one sense, but raise at
least two important questions that I'd like to resolve in this thread:

1. When we acquire a snapshot, exactly what CSN do we choose?
2. Given a specific CSN, what durability requirements must
transactions included in the snapshot meet before the snapshot can
be used?

Those questions are easy to answer on the replica (just use the
last-applied record LSN), so the discussion below is mostly about what
to do on the primary.

I disagree with this framing; it assumes that the snapshot determines
the durability requirements for the transactions, and that is not a
decision that the snapshot mechanism necessarily must or should make.
Our current snapshot mechanism allows it to be a decision that the
committing transaction makes (+ edge cases related to cancellation
etc.); this means that snapshot performance isn't meaningfully
impacted by concurrent sessions' durability requirements.

The previous discussion at [1] did come up with some answers to those
questions, but I couldn't find much explanation about those topics
specifically. The discussion mostly focused on the mapping of an xid
to its commit LSN. The discussion at [2] didn't need to resolve either
question.

Definitions:

Current Procarray-based snapshot: Changes from a transaction are
visible if it has committed and is not in the procarray.

Proposed CSN-based snapshot: Changes from a transaction are visible if
the transaction's commit record LSN is at or before the CSN.

I'm not so stoked about this specifically, see below for details.

Motivation:

In either case, basic isolation semantics are preserved.

The problems with the Procarray-based snapshot definition are:

* it is based on the contents of procarray, which is hard to observe
and reason about externally;
* some snapshots on the primary are impossible on the replica; and
* it's inherently more expensive to acquire a new snapshot.

This is mostly a "global" expense. I suspect that the local cost of
acquiring and using a snapshot is not so significantly different
between CSN and the procarray approach; I suspect CSNs may even be
more CPU-expensive (locally) than our PgProc approach if we need to
populate the [xmin, xmax] range of commit-is-visible checks from our
own backend through SLRU lookups.

Using CSN-based snapshots solves these problems and also fits nicely
with our generally WAL-centric architecture.

Constraints on CSN choice for new snapshots:

a. CSN must be greater than or equal to the CSN of any previously
acquired snapshot.

... any previously acquired snapshot which was published before we
started acquiring a snapshot.
It should be fine to piggy-back on another backend's snapshot
acquisition as long as nobody else has published that they know of a
newer snapshot before we started getting that new snapshot. But that's
talking optimizations.

[...]

Safety of using a CSN-based snapshot:

The commit record is written before CLOG is updated, so there's a
window where we could assign a CSN and the CLOG hasn't been
updated. We either need to wait until the CLOG update happens before
using the snapshot, or change the way CLOG and/or visibility checks
work to avoid that problem. Existing implementations in [1] do the
latter.

Proposal for Question 1:

I propose that we choose the CSN to be the minimum CSN that satisfies
constraint (c): the highest commit LSN of all the transactions that
have released locks. That offers a useful guarantee: because the last
transaction in the snapshot has already released locks, it has met its
own WAL durability requirement, and all other transactions in the
snapshot have met the same durability requirement (though not
necessarily their *own* durability requirement).

True, but this means that the effects of one transaction with a higher
durability requirements are (or, can be) published ahead of those
requirements actually being met. Or, that transactions with a lesser
durability expectation will start to have to wait for transaction with
a higher durability expectation to become durable. I don't think
either of these options is a good one.

It'll also make transaction-local GUC values for
synchronous_transaction much less safe.

In the discussion at [1], some proposals used the WAL insert pointer
to assign the CSN, but that doesn't seem to have any advantage, and
makes it harder to come up with a satisfactory answer for question #2.

Proposal for Question #2:

Currently, for the procarray-based snapshots, a transaction will only
be included in the snapshot if it meets the durability requirement of
the transaction *writing the changes*. So, a sync rep transaction can
see the effects of a sync=local transaction before it's replicated,
and a sync=local transaction can see the effects of an async
transaction before it's flushed. (Arguably this is a bug.)

Instead, I propose that we 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*. To
me, that would be the least-surprising behavior.

If the workload uses a consistent durability requirement, then no
waiting will be required if we assign the CSN as proposed in the
answer to question #1. But it does risk regressions for workloads with
mixed durability requirements.

For instance, let's say that some sync transaction T1 that writes a
commit record with LSN 122 but has not flushed yet. Then there's an
async transaction T2 that writes a commit record with LSN 123, then
finishes and releases locks. Then sync transaction T3 takes a
snapshot, which must include T2 due to constraint (c), and therefore
also includes T1 because the commit LSN is less than that of T2. If T3
uses the snapshot right away, that would mean a sync transaction T3 is
reading the changes of another sync transaction T1 that hasn't flushed
yet, which is broken. The only solution is for T3 to wait at least
until LSN 122 is flushed, which could add latency to the sync reader
transactions.

The only solution, unless visibility is determined (and logged)
separately from durability, in which case it could be made to work by
requiring transactions with higher durability expectations to log
their visibility, or offloading that visibility-logging job to a
background process.

It doesn't even have to be very fancy; including the durability
requirement in the commit record, and then logging the 3 commit
durability horizons every once in a short while for as long as there
are commits waiting for durability (and actually become durable)
should be sufficient - such a "durability horizons" record should be
sufficient as CSN (visibility horizon) for the differently-durable
commits that were logged ahead of their respective durability horizon.
It'd be comparable to the commit_delay system that combines commits'
fsyncs, but with an additional XLog record getting logged.

(With procarray-based snapshots, T1 would simply not be
included in the snapshot, but that's impossible for CSN-based
snapshots.)

We can mitigate that for some cases, such as a mix of async writers
and sync readers, by tracking additional information so that a sync
transaction doesn't wait for a flush if the only unflushed
transactions are async. But there will still be a worst-case workload:
where a steady stream of async writing transactions force
newly-assigned CSNs to be close to the insert pointer, along with
less-frequent sync writing transactions, and frequent sync reading
transactions that need to wait for the flushes to use the
snapshots.

I don't think that trading 'locked snapshot acquisition' for
'synchronous durability waits in snapshot acquisition' is a good
trade.

On average it'll cause transactions in workloads with mixed durability
expectations (s_c values of off/local, off/remote_*, local/remote_*)
to have significantly worse latency than they currently have: In a
(likely) worst case of such a mixed workload, the session with higher
durability requirements will have to wait for the durability of every
snapshot it takes (it's likely the CSN will be closer to the latest
record insert pointer than the durability horizon needed), and will
also have to wait for the full durability of your own commit record;
while on HEAD we only ever wait for the locks on the procarray, and
[ignoring cancellation bugs in SyncRepWait] wait for the durability of
your commit record (because every visible commit's durability is
already guaranteed before they become visible).

That worst-case workload may be impossible to solve in the
CSN world, so hopefully it's not a major problem.

An approach with commit-visibility records would fix this; making
transactions visible using a separate visibility (or horizon) record
whose LSN is the transaction's CSN visibility threshold, which would
allow us to skip the synchronous waits for CSN durability of mixed
sync and async transactions.

[...]

SERIALIZABLE:

I didn't fully analyze the implications for SSI yet, but I believe it's
compatible. Snapshots are still snapshots, and SSI can still detect
conflicts the same way as before. The serializable order can be
different from CSN order, which could be confusing, but that's an
existing problem.

Do you have a reference where I can read up on this issue?

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

On Wed, 2026-06-10 at 14:19 +0200, Matthias van de Meent wrote:

Our current snapshot mechanism allows it to be a decision that the
committing transaction makes (+ edge cases related to cancellation
etc.);

That's true, but I thought that was considered to be incidental and
undesirable behavior.

visibility is determined (and logged)
separately from durability,

That stretches the definition of "CSN". We'd have to redefine "commit"
to mean "when the transaction writes the commit-visibility record". But
crash recovery and failover would be using a different definition?

in which case it could be made to work by
requiring transactions with higher durability expectations to log
their visibility, or offloading that visibility-logging job to a
background process.

It doesn't even have to be very fancy; including the durability
requirement in the commit record, and then logging the 3 commit
durability horizons every once in a short while for as long as there
are commits waiting for durability (and actually become durable)
should be sufficient - such a "durability horizons" record should be
sufficient as CSN (visibility horizon) for the differently-durable
commits that were logged ahead of their respective durability
horizon.
It'd be comparable to the commit_delay system that combines commits'
fsyncs, but with an additional XLog record getting logged.

If I understand your proposal:

Naively, each committing transaction can write an additional commit-
visibility record right before ProcArrayEndTransaction(), but that
could be expensive.

You are suggesting an optimization to infer the visibility LSN of a
given transaction from its commit record (including an extra durability
requirement setting) along with some other records in the WAL that are
written less often than every commit. Can you describe in more detail
what will be logged and how you make the inference?

Also, how do you choose the CSN when acquiring a snapshot? I assume
just the insert pointer?

[...]

SERIALIZABLE:

I didn't fully analyze the implications for SSI yet, but I believe
it's
compatible. Snapshots are still snapshots, and SSI can still detect
conflicts the same way as before. The serializable order can be
different from CSN order, which could be confusing, but that's an
existing problem.

Do you have a reference where I can read up on this issue?

https://arxiv.org/pdf/1208.4179

Regards,
Jeff Davis

On Wed, 10 Jun 2026 at 18:40, Jeff Davis <pgsql@j-davis.com> wrote:

On Wed, 2026-06-10 at 14:19 +0200, Matthias van de Meent wrote:

Our current snapshot mechanism allows it to be a decision that the
committing transaction makes (+ edge cases related to cancellation
etc.);

That's true, but I thought that was considered to be incidental and
undesirable behavior.

I think it's desireable that snapshots don't need to take special care
around the durability of the transactions that are included in their
snapshot.
Async transactions may want to see sync transactions' durable data,
but probably prefer not to have to wait for that durability just
because its session logged its latest commit record after the sync
transaction did.

visibility is determined (and logged)
separately from durability,

That stretches the definition of "CSN". We'd have to redefine "commit"
to mean "when the transaction writes the commit-visibility record". But
crash recovery and failover would be using a different definition?

Visibility would presumably happen after the recovery/replica has made
sure that the durability of the pending-visible transactions is
guaranteed; which presumably could be done with a sync wait at
end-of-recovery.

in which case it could be made to work by
requiring transactions with higher durability expectations to log
their visibility, or offloading that visibility-logging job to a
background process.

It doesn't even have to be very fancy; including the durability
requirement in the commit record, and then logging the 3 commit
durability horizons every once in a short while for as long as there
are commits waiting for durability (and actually become durable)
should be sufficient - such a "durability horizons" record should be
sufficient as CSN (visibility horizon) for the differently-durable
commits that were logged ahead of their respective durability
horizon.
It'd be comparable to the commit_delay system that combines commits'
fsyncs, but with an additional XLog record getting logged.

If I understand your proposal:

Naively, each committing transaction can write an additional commit-
visibility record right before ProcArrayEndTransaction(), but that
could be expensive.

Correct. Not all that much more expensive, but for unlogged workloads
it could double the number of WAL records emitted.

You are suggesting an optimization to infer the visibility LSN of a
given transaction from its commit record (including an extra durability
requirement setting) along with some other records in the WAL that are
written less often than every commit. Can you describe in more detail
what will be logged and how you make the inference?

I've thought about these different approaches:

0.) synchronous_commit=off doesn't care about durability and doesn't
need to wait to release its locks; and is excluded from consideration
in the systems below.
The commit record itself would be used to supply the CSN visibility threshold.

1.) Each commit for themselves, logging their own visibility record
when they achieve durability.
Lowest per-commit durability latency, but higher pressure on WAL
insertions. The visibility record's LSN would be the CSN threshold for
the visibility of this XID, and doesn't need a separate durability
guarantee - recovery has rules to recover, apply, and make visible
those yet-to-become-visible transactions.

1a.) A batched approach to 1, where one backend gathers sibling
committers (like in commit_delay) with the same durability
expectations for a single visibility record that includes the sibling
commits' IDs; they all become visible once that record's logged.
This reduces WAL pressure, at the cost of slightly increased latency
(the oldest xact will have to wait for the newest xact in the batch to
also become durable).

3.) A BGWorker approach, where each backend includes its durability
requirement (sync/remote_write/remote_apply) in the commit record, and
a bgworker logs records every (e.g.) commit_delay that include the
(monotonically increasing) LSNs of the various durability horizons, if
any commits were waiting for that horizon (sync/rw/ra). This limits
WAL overhead to once per commit_delay, at the cost of each commit
having an up to commit_delay latency penalty past their durability
window; and less fine-grained snapshot boundaries.

Also, how do you choose the CSN when acquiring a snapshot? I assume
just the insert pointer?

Yes, that, or the record-end pointer of (e.g.) the most recently
inserted visibility record.

[...]

SERIALIZABLE:

I didn't fully analyze the implications for SSI yet, but I believe
it's
compatible. Snapshots are still snapshots, and SSI can still detect
conflicts the same way as before. The serializable order can be
different from CSN order, which could be confusing, but that's an
existing problem.

Do you have a reference where I can read up on this issue?

https://arxiv.org/pdf/1208.4179

Thanks!

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

On Wed, 2026-06-10 at 21:18 +0200, Matthias van de Meent wrote:

I think it's desireable that snapshots don't need to take special
care
around the durability of the transactions that are included in their
snapshot.
Async transactions may want to see sync transactions' durable data,
but probably prefer not to have to wait for that durability just
because its session logged its latest commit record after the sync
transaction did.

For the problem sequence with CSNs:

1. sync transaction T1 writes commit record
2. async transaction T2 writes commit record
3. T2 releases locks
4. T3 takes a snapshot
5. T1's commit record is flushed
6. T1 releases locks

there are three resolutions:

a. force T3 to wait until T1's commit record is flushed before
using the snapshot, slowing down the sync part of the
workload; or

b. force T2 to wait until all unflushed sync transactions with
an earlier commit LSN are flushed before releasing locks
(thereby making the above seqeunce impossible), slowing down
the async part of the workload; or

c. let T3 use the snapshot immediately, potentially returning
unflushed data to the client.

Perhaps none of those options is great for everyone, but we could allow
users to select the behavior they want. That seems better than today,
when any async transaction can cause sync transactions to start
returning unflushed data to the client, and there's no way to prevent
that.

Visibility would presumably happen after the recovery/replica has
made
sure that the durability of the pending-visible transactions is
guaranteed; which presumably could be done with a sync wait at
end-of-recovery.

That might be fine, but it's different from CSNs as I understand the
meaning (at least in the simplest and most intuitive meaning). Should
we call them "Visibility Sequence Numbers" (VSNs) instead?

For instance, given a sequence like (T1-T3 all sync transactions):

1. T1 writes commit record at LSN 123
2. T2 writes commit record at LSN 124
3. Flush to LSN 124
4. T2 writes commit-visibility record at LSN 125
5. T3 takes a snapshot with VSN=125 and returns data to client that
includes T2's changes but not T1's changes
6. crash

Recovery does not have enough information to know whether T1 or T2
became visible first, so we'd probably need to create a new "multi-
commit-visible" record that makes them all visible at the same LSN.
That would mean that the snapshot taken by T3 (that was externally
observed) could never exist in the recovered system, even though both
T1 and T2 exist.

That might have consequences for PITR. With recovery_target_xid, do you
recover up to its commit record or its commit-visibility record?

Regards,
Jeff Davis

On Thu, 11 Jun 2026 at 02:50, Jeff Davis <pgsql@j-davis.com> wrote:

On Wed, 2026-06-10 at 21:18 +0200, Matthias van de Meent wrote:

I think it's desireable that snapshots don't need to take special
care
around the durability of the transactions that are included in their
snapshot.
Async transactions may want to see sync transactions' durable data,
but probably prefer not to have to wait for that durability just
because its session logged its latest commit record after the sync
transaction did.

For the problem sequence with CSNs:

1. sync transaction T1 writes commit record
2. async transaction T2 writes commit record
3. T2 releases locks
4. T3 takes a snapshot
5. T1's commit record is flushed
6. T1 releases locks

there are three resolutions:

a. force T3 to wait until T1's commit record is flushed before
using the snapshot, slowing down the sync part of the
workload; or

b. force T2 to wait until all unflushed sync transactions with
an earlier commit LSN are flushed before releasing locks
(thereby making the above seqeunce impossible), slowing down
the async part of the workload; or

c. let T3 use the snapshot immediately, potentially returning
unflushed data to the client.

Perhaps none of those options is great for everyone, but we could allow
users to select the behavior they want. That seems better than today,
when any async transaction can cause sync transactions to start
returning unflushed data to the client, and there's no way to prevent
that.

Yes, "wait for data I'm reading to match my own durability
requirement" should be configurable.

But I'm not sure if we want to do that at snapshot acquisition time,
or only when the data is being read that was modified by said
transaction -- the latter would be a very attractive optimization for
workloads with different durability expectations which touch
completely -or, mostly- disjoint datasets in the same database.

Visibility would presumably happen after the recovery/replica has
made
sure that the durability of the pending-visible transactions is
guaranteed; which presumably could be done with a sync wait at
end-of-recovery.

That might be fine, but it's different from CSNs as I understand the
meaning (at least in the simplest and most intuitive meaning). Should
we call them "Visibility Sequence Numbers" (VSNs) instead?

I think that'd help the difference between durability and visibility, yes.

For instance, given a sequence like (T1-T3 all sync transactions):

1. T1 writes commit record at LSN 123
2. T2 writes commit record at LSN 124
3. Flush to LSN 124
4. T2 writes commit-visibility record at LSN 125
5. T3 takes a snapshot with VSN=125 and returns data to client that
includes T2's changes but not T1's changes
6. crash

Recovery does not have enough information to know whether T1 or T2
became visible first, so we'd probably need to create a new "multi-
commit-visible" record that makes them all visible at the same LSN.
That would mean that the snapshot taken by T3 (that was externally
observed) could never exist in the recovered system, even though both
T1 and T2 exist.

Correct, but are we also targeting true snapshot transferability from
primary to replicas? I don't think we need to be able to recreate all
snapshots that could exist on a primary on the replica (we'd have to
consider CommandIDs), as long as all snapshots that can be created on
a replica can also have been created on the primary; which I think is
what this VSN approach is able to guarantee.

Note that I believe that there is no meaningful difference from a
consistency standpoint between T1 and T2 both becoming visible at the
same time (with the same VSN) during recovery, and the system not
having any snapshot acquired between their VSNs -- which is something
that could happen on replicas. Side effects of a transaction won't
appear in WAL before the VSN of that transaction, so there should be
no opportunity for ordering issues here.

That might have consequences for PITR. With recovery_target_xid, do you
recover up to its commit record or its commit-visibility record?

Good point.

I suspect we'd have to make that a configurable option, but VSN (which
would be end of WAL if no VSN was logged for that commit) would
probably make the most sense, as it is least likely to cause snapshot
issues if further wal may be replayed afterward.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

On Thu, 2026-06-11 at 10:28 +0200, Matthias van de Meent wrote:

Yes, "wait for data I'm reading to match my own durability
requirement" should be configurable.

But I'm not sure if we want to do that at snapshot acquisition time,
or only when the data is being read that was modified by said
transaction -- the latter would be a very attractive optimization for
workloads with different durability expectations which touch
completely -or, mostly- disjoint datasets in the same database.

Agreed, there are several options here and I think it's an area where
configuration makes sense.

That might be fine, but it's different from CSNs as I understand
the
meaning (at least in the simplest and most intuitive meaning).
Should
we call them "Visibility Sequence Numbers" (VSNs) instead?

I think that'd help the difference between durability and visibility,
yes.

WAL is such a central part of our overall architecture that I think a
simpler visibility foundation (i.e. real CSNs) will pay off in a big
way.

I see "Visibility Sequence Numbers" as a somewhat-unfortunate
compromise. VSNs get the right information in WAL to keep the primary
and replica consistent, which is certainly good; but they leave us with
visibility semantics that don't match the commit order, which is a
source of confusion.

If we have the right set of configuration knobs as suggested above (to
handle mixed async/sync workloads), do you still think we should pursue
VSNs over CSNs?

Correct, but are we also targeting true snapshot transferability from
primary to replicas?

That would be a significant benefit of CSNs over VSNs. There would
still be some details to work out about timelines, and maybe other
things I haven't considered, but CSNs get us a lot closer.

Note that I believe that there is no meaningful difference from a
consistency standpoint between T1 and T2 both becoming visible at the
same time (with the same VSN) during recovery, and the system not
having any snapshot acquired between their VSNs -- which is something
that could happen on replicas. Side effects of a transaction won't
appear in WAL before the VSN of that transaction, so there should be
no opportunity for ordering issues here.

You're correct that VSNs eliminate inconsistencies between the primary
and the replica (or failed-over/recovered system). If there's some
snapshot on the primary with T2 but not T1, there won't be a snapshot
somewhere else with T1 but not T2.

But we will have collapsed T1 and T2 into a single visibility event,
which is technically a loss of information, and we'd need to sort
through the nuances and implications.

That might have consequences for PITR. With recovery_target_xid, do
you
recover up to its commit record or its commit-visibility record?

Good point.

I suspect we'd have to make that a configurable option, but VSN
(which
would be end of WAL if no VSN was logged for that commit) would
probably make the most sense, as it is least likely to cause snapshot
issues if further wal may be replayed afterward.

Let recovery_target_xid=T1 and let the WAL contents be:

LSN 122 TL1: commit record for T0
LSN 123 TL1: commit record for T1
LSN 124 TL1: commit record for T2
LSN 125 TL1: commit-visible record for T1
LSN 126 TL1: commit-visible record for T0
LSN 127 TL1: whatever
LSN 128 TL1: commit-visible record for T2

If you recover to the commit-visible record (LSN=125), then T0's and
T2's commit records have been replayed, but not their commit-visible
record, so you must write the multi-visible record:

LSN 126 TL2: multi-visible record for {T0, T2}

before startup. That means you have effectively recovered to T2, not
T1. That's wrong, therefore we must define recovery based on commit
records.

If you recover to the commit record (LSN=123) instead, then T0's and
T1's commit records have been replayed, but not their commit-visible
records, so you must write a multi-visible record:

LSN 124 TL2: multi-visible record for {T0, T1}

which loses the information that T1 became visible before T0. That
might be acceptable, but CSNs just seem a lot simpler.

Regards,
Jeff Davis

On Fri, 12 Jun 2026 at 00:24, Jeff Davis <pgsql@j-davis.com> wrote:

I see "Visibility Sequence Numbers" as a somewhat-unfortunate
compromise. VSNs get the right information in WAL to keep the primary
and replica consistent, which is certainly good; but they leave us with
visibility semantics that don't match the commit order, which is a
source of confusion.

If we have the right set of configuration knobs as suggested above (to
handle mixed async/sync workloads), do you still think we should pursue
VSNs over CSNs?

Maybe we should add VSNs as fourth option in the list, as it keeps
PG's current visibility ordering mostly intact - except in the case of
recovery where all transactions might become visible all at once.
Which is not something I'm particularly worried about, for the reasons
I mentioned in my mail yesterday.

Current snapshot semantics in PG are quite nice [ignoring cancellation
bugs, and replicas] for workloads with mixed durability requirements,
in that visibility == transaction's configured durability, and no
snapshot has to spend its own time waiting for a transaction's
durability expectation, unless it is explicitly blocked by that
transaction's locks.

Correct, but are we also targeting true snapshot transferability from
primary to replicas?

That would be a significant benefit of CSNs over VSNs. There would
still be some details to work out about timelines, and maybe other
things I haven't considered, but CSNs get us a lot closer.

CommandIds are a big issue with full RW snapshot transfers. RO
snapshot transfers would be easy with both CSN and VSN, in both cases
assuming that both systems are using the same timeline; it's just that
for those transfers you may have to add some not-yet-durable ordering
data (or wait for the VSN to arrive on the replica).

Note that I believe that there is no meaningful difference from a
consistency standpoint between T1 and T2 both becoming visible at the
same time (with the same VSN) during recovery, and the system not
having any snapshot acquired between their VSNs -- which is something
that could happen on replicas. Side effects of a transaction won't
appear in WAL before the VSN of that transaction, so there should be
no opportunity for ordering issues here.

You're correct that VSNs eliminate inconsistencies between the primary
and the replica (or failed-over/recovered system). If there's some
snapshot on the primary with T2 but not T1, there won't be a snapshot
somewhere else with T1 but not T2.

But we will have collapsed T1 and T2 into a single visibility event,
which is technically a loss of information, and we'd need to sort
through the nuances and implications.

I don't think there are many nuances. If the replica/recovery doesn't
have the VSN of certain transactions yet, then there also can't be any
effects in the database that depend on the VSN ordering of those
transactions: locks are only released after the VSN is assigned, so
any persistent VSN ordering dependencies can only appear after the
VSN.

And, for external readers, at worst, they won't be able to see or know
the intermediate snapshot ordering they could've seen on the primary
before the server crash/failover/recovery point, but after the last
durable WAL was logged. I don't think we should break our minds over
that: every transaction with its (sufficiently high) durability
guarantee satisfied that was visible on the primary will be visible
after recovery/promotion.

Let recovery_target_xid=T1 and let the WAL contents be:

LSN 122 TL1: commit record for T0
LSN 123 TL1: commit record for T1
LSN 124 TL1: commit record for T2
LSN 125 TL1: commit-visible record for T1
LSN 126 TL1: commit-visible record for T0
LSN 127 TL1: whatever
LSN 128 TL1: commit-visible record for T2

If you recover to the commit-visible record (LSN=125), then T0's and
T2's commit records have been replayed, but not their commit-visible
record, so you must write the multi-visible record:

LSN 126 TL2: multi-visible record for {T0, T2}

before startup. That means you have effectively recovered to T2, not
T1. That's wrong, therefore we must define recovery based on commit
records.

If you recover to the commit record (LSN=123) instead, then T0's and
T1's commit records have been replayed, but not their commit-visible
records, so you must write a multi-visible record:

LSN 124 TL2: multi-visible record for {T0, T1}

which loses the information that T1 became visible before T0. That
might be acceptable, but CSNs just seem a lot simpler.

It's probably simpler, yes, but the VSN approach does resolve my concerns about

i.) commit throughput in light of the waits introduced to every
backend by options (a) and (b), and
ii.) the lack of visible data's commit durability guarantee in (c).

Adjusting (c) with durability waits when the snapshot encounters data
modified by not-yet-fully-durable transactions would just add this
option to the 'newly introduced waits' concern list, too.

None of that would be a concern if we only allowed a global
synchronous_commit setting, but that's not a world we live in, nor a
world we're planning to live in (I think), so I do worry about these
concerns.

Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

On Fri, 2026-06-12 at 12:03 +0200, Matthias van de Meent wrote:

But we will have collapsed T1 and T2 into a single visibility
event,
which is technically a loss of information, and we'd need to sort
through the nuances and implications.

I don't think there are many nuances.

How would remote_apply work? The docs say:

"When set to remote_apply, commits will wait until replies from the
current synchronous standby(s) indicate they have received the commit
record of the transaction and applied it, so that it has become visible
to queries on the standby(s), and also written to durable storage on
the standbys."

For it to have become visible remotely, the commit-visible record must
also have been applied remotely. But the commit-visible record can't
even be written locally until we've met the transaction's durability
requirement. If remote_apply counts as a "durability requirement", then
we have a cycle.

We also might need to look more carefully at recovery_target_time,
which could be a more surprising manifestation of the
recovery_target_xid issue.

And when does logical decoding emit the transaction, at commit or
commit-visible? What about multi-visible records? Logical decoding
needs to come up with some serial order, but (after a crash) might not
have enough information to recreate the same visible order as the
primary.

Perhaps these are not real problems or perhaps they have some
resolution. Regardless, my main point is that we want to get away from
the need to reason through this complexity. CSNs are simple to reason
about, and that simplicity will pay off across all the dependent
subsystems: replication, PITR, etc.

Adjusting (c) with durability waits when the snapshot encounters data
modified by not-yet-fully-durable transactions would just add this
option to the 'newly introduced waits' concern list, too.

I'll think about this some more, it might eliminate most of the waits
in practical cases.

Regards,
Jeff Davis

On Sat, 13 Jun 2026 at 02:25, Jeff Davis <pgsql@j-davis.com> wrote:

On Fri, 2026-06-12 at 12:03 +0200, Matthias van de Meent wrote:

But we will have collapsed T1 and T2 into a single visibility
event,
which is technically a loss of information, and we'd need to sort
through the nuances and implications.

I don't think there are many nuances.

How would remote_apply work? The docs say:

"When set to remote_apply, commits will wait until replies from the
current synchronous standby(s) indicate they have received the commit
record of the transaction and applied it, so that it has become visible
to queries on the standby(s), and also written to durable storage on
the standbys."

For it to have become visible remotely, the commit-visible record must
also have been applied remotely. But the commit-visible record can't
even be written locally until we've met the transaction's durability
requirement. If remote_apply counts as a "durability requirement", then
we have a cycle.

Yeah, that is something I hadn't yet taken into consideration. That
does disqualify remote_apply from being a requirement for VSN logging,
at least in its current form.

[various problems]
Perhaps these are not real problems or perhaps they have some
resolution. Regardless, my main point is that we want to get away from
the need to reason through this complexity. CSNs are simple to reason
about, and that simplicity will pay off across all the dependent
subsystems: replication, PITR, etc.

That is a good point. I agree CSNs are easier to reason about in those cases.

Adjusting (c) with durability waits when the snapshot encounters data
modified by not-yet-fully-durable transactions would just add this
option to the 'newly introduced waits' concern list, too.

I'll think about this some more, it might eliminate most of the waits
in practical cases.

I'm not sure it'll be sufficient for specific cases, but many cases,
probably, yes.
I think it's not weird to use async xacts for frequently updated rows
(counters), whose effects are then used to affect blindly-issued sync
xacts. It's not entirely unlike how sequences work right now, except
that sequences don't have MVCC.

- Matthias

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]https://aws.amazon.com/blogs/database/understanding-transaction-visibility-in-postgresql-clusters-with-read-replicas/. 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]/messages/by-id/0B44E464-BA62-4056-9465-3320DD2D0302@yandex-team.ru.

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]: /messages/by-id/0B44E464-BA62-4056-9465-3320DD2D0302@yandex-team.ru

On Thu, 2026-08-27 at 14:21 +0500, Andrey Borodin wrote:

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.

That's the problem I'm most interested in, as well. Postgres is not a
single-instance system, it has replicas, PITRs, etc. WAL is central to
all of that, and if we make a single point in WAL for both durability
and visibility, that greatly simplifies the overall system.

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. 

...

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.

I'm not clear on the use case where the reader needs to obey the
writer's durability setting. Why wouldn't the reader just care about
its own durability setting?

I can see how the writer has more of a sense of the importance of what
they are writing, but (at least for me) this is a theoretical argument.

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?

Yes, assuming that the sync commit is in the reader's CSN snapshot. As
I proposed in the original email:

"wait until the CSN is flushed to the point that it meets the
durability requirements of the transaction using the snapshot"

In this case the sync transaction has already met the durability
requirements of the reader.

I'm open to the idea of a GUC that can control whether the reader waits
in that case, but that wouldn't affect visibility.

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?

If by "consume T1", you mean: "processes a query that includes T1's
changes and returns results to the client", then agreed, those are
separate concepts.

More specifically, may a reader with a weak durability
requirement consume T1 while T1's COMMIT is still waiting for
remote_apply?

The reader can follow its own policy on whether to do so immediately or
wait for some durability requirement to be met first. If all
transactions have the same durability setting, then the durability
requirement has already been met by the time the CSN is assigned, so
waiting is unnecessary.

  If not, does T1 keep T2 invisible despite T2 having
already completed?

T1 will never keep T2 invisible; the visibility is defined only by the
CSN and commit LSNs of the transactions. If T2 commits and releases
locks, then any snapshot taken afterward will see both T2 and T1
(constraint (c)).

Waiting to use a snapshot is not the same as a transaction being
invisible to that snapshot.

Distributed SSI seems like a separate and much larger project, rather
than a prerequisite for CSN visibility.

Agreed.

  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.

Perhaps CSN will simplify SSI in some ways, and make it easier to work
on going forward.

Regards,
Jeff Davis

On Fri, Aug 28, 2026, Jeff Davis wrote:

I'm not clear on the use case where the reader needs to obey the
writer's durability setting. Why wouldn't the reader just care about
its own durability setting?

I agree. I was not proposing that the reader should obey the writer's
setting. I wanted to make explicit that, with commit-record LSNs as the
visibility order, a reader with a weaker policy may consume T1 while
T1's COMMIT is still waiting for its writer-selected durability.

Waiting to use a snapshot is not the same as a transaction being
invisible to that snapshot.

This distinction makes sense to me. Once T2 releases its locks, a new
snapshot includes both T1 and T2. The reader's durability policy only
determines whether it may use that snapshot immediately. This also
gives the common-prefix property needed to prevent Long Fork.

One implementation question still seems open to me. At Vancouver,
Andres was concerned that eliminating Long Fork might require coupling
WAL insertion with ProcArrayLock: the point where a transaction joins
snapshot visibility must have a position in WAL, while no snapshot may
pass that point without including the transaction.

Is maxTransactionFinishedPtr intended to avoid that interlock by making
its atomic update the snapshot linearization point? For example, if T1
has inserted its commit record at LSN 1 but has not yet made its CSN
state readable, and T2 advances maxTransactionFinishedPtr to LSN 2, a
snapshot at LSN 2 must include T1 rather than treat it as in progress.
Is the intended solution for that snapshot to wait on T1's commit-in-
progress state, or do we need another WAL-visible visibility marker?

Is there any further design input you would like before implementation?
Are you already planning to post a prototype? If not, I can prepare
one based on Heikki's latest "CSN snapshots in hot standby" patch. Even
if the interlock above is not fully settled, a prototype would make it
concrete and let us measure its performance impact rather than reason
about it only in the abstract. I would initially focus on the common
visibility order, the Long Fork reproducer, and the relevant concurrent
workloads, leaving the reader durability policy as a separate step.

Thank you!

Best regards, Andrey Borodin.