Обсуждение: Synchronous Replication: Where is data visible first?
Hello,
While discussing with the development team, an interesting question came up: in a synchronous streaming replication scenario, with synchronous_commit = remote_apply, will the change be first visible on Standby (replica)? Primary will wait till the change is applied and committed on standby, and hence logically this looks to be correct. But I couldn't find this mentioned explicitly anywhere and hence seeking comments from the community.
Assuming that the answer is yes, potentially the same can happen even with synchronous_commit = remote_write | ON because on the way back to Primary, if the network is slow, there is a chance that Standby may complete applying the change and make it visible to the users.
Would sincerely appreciate if someone can share a documentation/blog link that explains this, if there is one.
Many Thanks!
On Wednesday, January 10, 2024, P C <puravc@gmail.com> wrote:
Hello,While discussing with the development team, an interesting question came up: in a synchronous streaming replication scenario, with synchronous_commit = remote_apply, will the change be first visible on Standby (replica)? Primary will wait till the change is applied and committed on standby, and hence logically this looks to be correct. But I couldn't find this mentioned explicitly anywhere and hence seeking comments from the community.
IIUC, no, the standbys can never reflect a newer state than what would be seen on the primary. The state of a given transaction, including most importantly the locking surrounding it, exists first on the primary and then is replicated to the secondary. Either the locking will prevent a dirty read or the dirty read will see the unconfirmed but committed data on the primary. The locking will be removed on the primary strictly before the standbys.
David J.
On Wed, Jan 10, 2024 at 11:43:50PM -0700, David G. Johnston wrote:
> On Wednesday, January 10, 2024, P C <puravc@gmail.com> wrote:
>
> Hello,
>
> While discussing with the development team, an interesting question came
> up: in a synchronous streaming replication scenario, with
> synchronous_commit = remote_apply, will the change be first visible on
> Standby (replica)? Primary will wait till the change is applied and
> committed on standby, and hence logically this looks to be correct. But I
> couldn't find this mentioned explicitly anywhere and hence seeking comments
> from the community.
>
>
> IIUC, no, the standbys can never reflect a newer state than what would be seen
> on the primary. The state of a given transaction, including most importantly
> the locking surrounding it, exists first on the primary and then is replicated
> to the secondary. Either the locking will prevent a dirty read or the dirty
> read will see the unconfirmed but committed data on the primary. The locking
> will be removed on the primary strictly before the standbys.
Uh, my research contradicts that, see:
https://momjian.us/main/blogs/pgblog/2020.html#June_3_2020
1 Write commit record to the write-ahead log
2 Flush the write-ahead log to durable storage
3 Update the pg_xact (slide 57)
4 Transfer to replicas
-> 5 Mark the commit as visible to other sessions (ProcArrayEndTransaction() updates PGPROC)
6 Communicate commit to the client
While the transaction will be visible on the primary to the creating
transaction session, it might not be visible to other sessions before it
appears on the replica.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.