Re: Transaction Snapshots and Hot Standby

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:

> On Thu, 2008-09-11 at 11:11 +0100, Richard Huxton wrote:
>
>> I have to say I agree with Heikki here. Blocking the master based on
>> what the slave is doing seems to make host standby less useful than warm.
>
> I agree also, that why I flagged it up for discussion.

So as far as I can see there are basically two option here. Either

a) transactions with live snapshots on the slave prevent the master from being
able to vacuum tuples (which defeats the purpose of having a live standby
server for some users).

or

b) vacuum on the server which cleans up a tuple the slave has in scope has to
block WAL reply on the slave (which I suppose defeats the purpose of having
a live standby for users concerned more with fail-over latency).

Is there any middle ground or brilliant ways to get the best of both worlds?

If not it seems to me the latter is preferable since at least the consequence
of having a long-running query on the slave occurs on the same machine running
the query. And the work-around -- killing the long-running query -- requires
taking action on the same machine as the consequences. Also, when you take
action it fixes the problem immediately as WAL reply can recommence which
seems like a better deal than a bloated database.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!