Re: Proposal: "Causal reads" mode for load balancing reads without stale data

On 11/11/2015 10:23 AM, Simon Riggs wrote:
> On 11 November 2015 at 05:37, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
> wrote:
>
> Many sites use hot standby servers to spread read-heavy workloads over more
>> hardware, or at least would like to. This works well today if your
>> application can tolerate some time lag on standbys. The problem is that
>> there is no guarantee of when a particular commit will become visible for
>> clients connected to standbys. The existing synchronous commit feature is
>> no help here because it guarantees only that the WAL has been flushed on
>> another server before commit returns. It says nothing about whether it has
>> been applied or whether it has been applied on the standby that you happen
>> to be talking to.
>
> Thanks for working on this issue.

+1.

>> 3. Commit on the primary with "causal_reads = on" waits for all
>> 'available' standbys either to apply the commit record, or to cease to be
>> 'available' and begin raising the error if they are still alive (because
>> their authorizations have expired).
>>
>
> This causes every writer to wait.
>
> What we want is to isolate the wait only to people performing a write-read
> sequence, so I think it should be readers that wait. Let's have that debate
> up front before we start reviewing the patch.

Agreed. And in the write-read sequence, you don't need to wait at the
write either, it's enough that you wait just before you start doing the
read. An application might do a lot of other things between the two, so
that in most cases, there would in fact be no waiting as the record is
already applied when you perform the read.

I'm thinking the client should get some kind of a token back from the
commit, and it could use the token on the standby, to wait for that
commit to be applied. The token could be just the XID, or the LSN of the
commit record. Or the application could generate the token and pass it
to the server in the commit, similar to how 2PC works. So the
interaction would be something like:

In master:
BEGIN;
INSERT INTO FOO ...;
COMMIT;
Server returns: COMMITted with token 1234

Later, in standby:
BEGIN WAIT FOR COMMIT 1234 TO BE VISIBLE;
SELECT * FROM foo;
...

- Heikki