Re: Allow "snapshot too old" error, to prevent bloat

On 17 February 2015 at 06:35, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>
> On Feb 17, 2015 12:26 AM, "Andres Freund" <andres(at)2ndquadrant(dot)com> wrote:
>>
>> On 2015-02-16 16:35:46 -0500, Bruce Momjian wrote:
>> > It seems we already have a mechanism in place that allows tuning of
>> > query cancel on standbys vs. preventing standby queries from seeing old
>> > data, specifically
>> > max_standby_streaming_delay/max_standby_archive_delay. We obsessed
>> > about how users were going to react to these odd variables, but there
>> > has been little negative feedback.
>>
>> FWIW, I think that's a somewhat skewed perception. I think it was right to
>> introduce those, because we didn't really have any alternatives.
>>
>> But max_standby_streaming_delay, max_standby_archive_delay and
>> hot_standby_feedback are among the most frequent triggers for questions
>> and complaints that I/we see.
>>
>
> Agreed.
>
> And a really bad one used to be vacuum_defer_cleanup_age, because of
> confusing units amongst other things. Which in terms seems fairly close to
> Kevins suggestions, unfortunately.

I agree with Bruce that we already have a mechanism similar to this
for Hot Standby, so it should therefore be OK to have it for normal
running when users desire it. The key difference here is that in this
patch we use the LSN to look for changed data blocks, allowing queries
to proceed for longer than they would do on Hot Standby where they
currently just get blown away regardless. I like the proposal in this
patch but it is more extreme than the mechanism Hot Standby provides.
(If we implement this then I would want to see it work for Hot Standby
also so we can keep the mechanisms in step, I think that is too late
in the day to add that for 9.5.)

I also agree with Andres and Magnus in saying that in the current
definition of the tunable parameter it would be hard to use.

In response to Tom's perspective that it is the single most annoying
feature of Oracle, I agree. It just happens we have a similar problem:
bloat.

Having a parameter to define "maximum acceptable bloat" would be a
very useful thing in PostgreSQL. IIRC other DBMS had a lot of work to
make "snapshot too old" occur in controllable circumstances. So I
would call having a very crap parameter like "old_snapshot_limit" a
good start, but clearly not the end point of an initiative too improve
our control of bloat.

+1 to include this patch in 9.5, as long as there is agreement to
improve this in the future

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services