Checking for stale reads on hot standby

From: Yang Zhang <yanghatespam(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Checking for stale reads on hot standby
Date: 2010-09-26 23:51:38
Message-ID: AANLkTi=c75UAZ3u787fY2LKHQK4tJszWsm1xv0ceQ+uq@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Say you have an application using PG asynchronous streaming
replication to some hot standbys, to distribute the read load. The
application itself is a typical web application consisting of multiple
servers, serving a number of sessions (perhaps belonging to different
users), and the workload is OLTP-ish, with each session continually
issuing a bunch of transactions. To guarantee session timeline
consistency for clients of the application, you want to make sure that
they can read data that's at least as new as anything they've
read/written previously, never traveling back in time.

With asynchronous replication, after seeing a new version of the data
from one standby, you may see an older version from a subsequent query
to another standby. The question: what are some ways to provide this
form of consistency in the context of PG asynchronous replication?

Is the standard/recommended approach to use a sequence representing
the global database version? Here, the application is responsible for
incrementing this from update transactions. In read transactions,
check that the sequence value is >= the session's highest-seen-value,
and raise the latter if necessary.

Thanks in advance.
--
Yang Zhang
http://yz.mit.edu/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gurjeet Singh 2010-09-27 00:09:31 Re: Checking for stale reads on hot standby
Previous Message Heine Ferreira 2010-09-26 23:29:11 Postgresql 9.0 and desktop heap and Windows