Re: Proposal: "Causal reads" mode for load balancing reads without stale data - Mailing list pgsql-hackers

1.  Reader waits with exposed LSNs, as Heikki suggests.  This is what BerkeleyDB does in "read-your-writes" mode.  It means that application developers have the responsibility for correctly identifying transactions with causal dependencies and dealing with LSNs (or whatever equivalent tokens), potentially even passing them to other processes where the transactions are causally dependent but run by multiple communicating clients (for example, communicating microservices).  This makes it difficult to retrofit load balancing to pre-existing applications and (like anything involving concurrency) difficult to reason about as applications grow in size and complexity.  It is efficient if done correctly, but it is a tax on application complexity.

2.  Reader waits for a conservatively chosen LSN.  This is roughly what MySQL derivatives do in their "causal_reads = on" and "wsrep_sync_wait = 1" modes.  Read transactions would start off by finding the current end of WAL on the primary, since that must be later than any commit that already completed, and then waiting for that to apply locally.  That means every read transaction waits for a complete replication lag period, potentially unnecessarily.  This is tax on readers with unnecessary waiting.

3.  Writer waits, as proposed.  In this model, there is no tax on readers (they have zero overhead, aside from the added complexity of dealing with the possibility of transactions being rejected when a standby falls behind and is dropped from 'available' status; but database clients must already deal with certain types of rare rejected queries/failures such as deadlocks, serialization failures, server restarts etc).  This is a tax on writers.