Re: Transaction Snapshots and Hot Standby

On Mon, Sep 15, 2008 at 8:40 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Mon, 2008-09-15 at 13:13 +0100, Gregory Stark wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>>
>> > So passing xl_xmin from master to standby seems not necessary to me. The
>> > standby's OldestXmin needs to be passed through to the master, not the
>> > other way around so that WAL records for tuple removal are not
>> > generated.
>>
>> I think most people were pretty leery of doing it that way because it means
>> activity on the standby database can cause the master to bloat. The consensus
>> seemed to be headed towards having WAL replay on the standby stall if it meets
>> a tuple removal record for a tuple that's visible to a query running on it.
>> Probably with a mechanism to configure a maximum amount of time it can be
>> stalled before shooting those queries.
>
> Well, my impression from all inputs is there is no single preferred
> route. Any one of the approaches seems to have a possible objection,
> depending upon the needs of the user. So I'm going to give options.
>
> In any case it's not just a two horse race. There are other options,
> favoured by some people, that you've not personally commented on in any
> of your summaries (thats up to you, of course).
>
> And "Standby causing master to bloat" is not such a big problem. It's no
> different to running queries directly on the master. But please don't
> take it that I don't see the problem or think it the best solution in
> all cases.

It's not a problem, but a relative disadvantage. What makes warm
standby really attractive relative to other data transfer solutions is
that there are no side effects on the master outside of setting the
archive command...communication is one way. Any 'master bloat' style
approach seems to be increasingly fragile if you want to increase the
number of standby servers, if it's even possible to do that.

I'm not saying it should be the only way to do it (and it may not even
be possible)...but it would be very attractive to be able to run a hot
standby much the same as a warm standby is running today...I could,
for example easily script a second standby but keep it a week behind
for example.

merlin