PostgreSQL synchronous replication consistency

Question

If we compare multiple types of replication (Single-leader, Multi-leader or Leaderless), Single-leader replication has the possibility to be Linearizable. In my understanding, Linearizability means that once a write completes, all later reads should return that value, or of a later write. Or said in other words, there should be an impression if there is only one database, but no more. So i guess, no stale reads.

PostgreSQL in his streaming replication, has the ability to make all it's replicas synchronous using the synchronous_standby_names and it also has the ability to fine tune with the synchronous_commit option, where it can be set to remote_apply, so the leader waits until the transaction is replayed on the standby (making it visible to queries). In the documentation, in the paragraph where it talks about the remote_apply option, it states that this allows load-balancing in simple cases with causal consistency.

Few pages back, it says this:

,,Some solutions are synchronous, meaning that a data-modifying transaction is not considered committed until all servers have committed the transaction. This guarantees that a failover will not lose any data and that all load-balanced servers will return consistent results no matter which server is queried,,

So i'm struggling to understand what can there be guaranteed, and what anomalies can happen if we load-balance read queries to the read replicas. Can still there be stale reads? Can it happen when i query different replicas to get different results even no write after happend on the leader? My impression is yes, but i'm not really sure. If no, how PostgreSQL prevents stale reads? I did not find anything with more details how it fully works under the hood. Does it use two-phase commit, or some modification of it, or it uses some other algorithm to prevent stale reads?

If it does not provide option of no stale reads, is there a way to accomplish that? I saw, PgPool has to option to load-balance to the replicas that are behind no more than a defined threshold, but i did not understand if it could be defined to load-balance to replicas that are up with the leader.

It's really confusing to me to really understand if there anomalies can happen in a fully synchronous replication in PostgreSQL.

I understand that setup like this has problems with availability, but that is now not a concern.

Sailesh

Answer 1

If you use synchronous replication with synchronous_commit = remote_apply, you can be certain that you will see the modified data on the standby as soon as the modifying transaction has committed.

Synchronous replication does not use two-phase commit, the primary server first commits locally and then simply waits for the feedback of the synchronous standby server before COMMIT returns. So the following is possible:

• An observer will see the modified data on the primary before COMMIT returns, and before the data are propagated to the standby.

• An observer will see the modified data on the standby before the COMMIT on the primary returns.

• If the committing transaction is interrupted on the primary at the proper moment before COMMIT returns, the transaction will already be committed only on the primary. There is always a certain time window between the time the commit happens on the server and the time it is reported to the client, but that window increases considerably with streaming replication.

  Eventually, though, the data modification will always make it to the standby.

Levulose

It's not to hard to reason about that when you get the response, then every further request should see the data. I tried to simulate some network delay using the tc tool on a cluster that uses a pg_pool in front, and what i observed is that the data can be queried on the replica even the master did not return. My understanding is that if the replication uses a 2pc, the commits on the replicas are executed on different times, so in a small interval, load balancing could produce a up to date read and then a stale read. Am i correct? – Millicent

I have added some detail to my too short answer. – Levulose