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.