1

What is the consistency of Postgresql HA cluster with Patroni?

My understanding is that because the fail-over is using a consensus (etc or zookeeper) the system will stay consistent under network partition.

Does this mean that transaction running under the serializable Isolation Level will also provide linearizability.

If not which consistency will I get Sequential Consistency, Causal Consistency .. ?

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
skyde
  • 2,816
  • 4
  • 34
  • 53

2 Answers2

2

You shouldn't mix up consistency between the primary and the replicas and consistency within the database.

A PostgreSQL database running in a Patroni cluster is a normal database with streaming replicas, so it provides the eventual consistency of streaming replication (all replicas will eventually show the same values as the primary).

Serializabiliy guarantees that you can establish an order in the database transactions that ran against the primary such that the outcome of a serialized execution in that order is the same as the workload had in reality.

If I read the definition right, that is just the same as “linearizability”.

Since only one of the nodes in the Patroni cluster can be written to (the primary), this stays true, no matter if the database is in a Patroni cluster or not.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Linearizability has powerful consequences. Once an write operation is complete, every read must see it or some later state. This mean that during change of primary the new primary is not allowed to serve stale to read request. If streaming replication is asynchronous this would not be true. But if streaming replication is setup to be synchronous I assume we this could be. – skyde Feb 12 '18 at 18:46
  • Also serializability say nothing about the invocation and completion times so while all operation should look as if they executed in some sequential order the order dont need to align with physical invocation time. – skyde Feb 12 '18 at 19:00
  • 1
    Only if `synchronous_commit = remote_apply`, changes will immediately be visible on synchronous standbys. But you cannot have all standbys synchronous, else availability will be reduced. Read up on the GAP theorem. – Laurenz Albe Feb 12 '18 at 19:02
  • If I understand you correctly its always possible that the current primary fail and the standby that was not in synchronous mode become the new primary ? Thus the new primary will look like it forgot about some of the writes performed just before the failover? – skyde Feb 12 '18 at 19:12
  • No, if you use a good cluster software, it will promote the synchronous standby to avoid data loss. – Laurenz Albe Feb 13 '18 at 03:16
2

In a distributed context, where we have multiple replicas of an object’s state, A schedule is linearizable if it is as if they were all updated at once at a single point in time.

Once a write completes, all later reads (wall-clock time) from any replica should see the value of that write or the value of a later write.

Since PostgreSQL version 9.6 its possible to have multiple synchronous standy node. This mean if we have 3 server and use num_sync = 2, the primary will always wait for write to be on the 2 standby before doing commit.

This should satisfy the constraint of linearizable schedule even with failover.

Since version 1.2 of Patroni, When synchronous mode is enabled, Patroni will automatically fail over only to a standby that was synchronously replicating at the time of the master failure. This effectively means that no user visible transaction gets lost in such a case.

skyde
  • 2,816
  • 4
  • 34
  • 53