Re: max_standby_delay considered harmful

Tom Lane wrote:
> 1. The timestamps we are reading from the log might be historical,
> if we are replaying from archive rather than reading a live SR stream.
> In the current implementation that means zero grace period for standby
> queries. Now if your only interest is catching up as fast as possible,
> that could be a sane behavior, but this is clearly not the only possible
> interest --- in fact, if that's all you care about, why did you allow
> standby queries at all?
>

If the standby is not current, you may not want people to execute
queries against it. In some situations, returning results against
obsolete data is worse than not letting the query execute at all. As I
see it, the current max_standby_delay implementation includes the
expectation that the results you are getting are no more than
max_standby_delay behind the master, presuming that new data is still
coming in. If the standby has really fallen further behind than that,
there are situations where you don't want it doing anything but catching
up until that is no longer the case, and you especially don't want it
returning stale query data.

The fact that tuning in that direction could mean the standby never
actually executes any queries is something you need to monitor for--it
suggests the standby isn't powerful/well connected to the master enough
to keep up--but that's not necessarily the wrong behavior. Saying "I
only want the standby to execute queries if it's not too far behind the
master" is the answer to "why did you allow standby queries at all?"
when tuning for that use case.

> 2. There could be clock skew between the master and slave servers.
>

Not the database's problem to worry about. Document that time should be
carefully sync'd and move on. I'll add that.

> 3. There could be significant propagation delay from master to slave,
> if the WAL stream is being transmitted with pg_standby or some such.
> Again this results in cutting into the standby queries' grace period,
> for no defensible reason.
>

Then people should adjust their max_standby_delay upwards to account for
that. For high availability purposes, it's vital that the delay number
be referenced to the commit records on the master. If lag is eating a
portion of that, again it's something people should be monitoring for,
but not something we can correct. The whole idea here is that
max_standby_delay is an upper bound on how stale the data on the standby
can be, and whether or not lag is a component to that doesn't impact how
the database is being asked to act.

> In addition to these fundamental problems there's a fatal implementation
> problem: the actual comparison is not to the master's current clock
> reading, but to the latest commit, abort, or checkpoint timestamp read
> from the WAL.
Right; this has been documented for months at
http://wiki.postgresql.org/wiki/Hot_Standby_TODO and on the list before
that, i.e. "If there's little activity in the master, that can lead to
surprising results." The suggested long-term fix has been adding
keepalive timestamps into SR, which seems to get reinvented every time
somebody plays with this for a bit. The HS documentation improvements
I'm working on will suggest that you make sure this doesn't happen, that
people have some sort of keepalive WAL-generating activity on the
master regularly, if they expect max_standby_delay to work reasonably in
the face of an idle master. It's not ideal, but it's straightforward to
work around in user space.

> I'm inclined to think that we should throw away all this logic and just
> have the slave cancel competing queries if the replay process waits
> more than max_standby_delay seconds to acquire a lock. This is simple,
> understandable, and behaves the same whether we're reading live data or
> not.

I don't consider something that allows queries to execute when not
playing recent "live" data is necessarily a step forward, from the
perspective of implementations preferring high-availability. It's
reasonable for some people to request that the last thing a standby
that's not current (<max_standby_delay behind the master, based on the
last thing received) should be doing is answering any queries, when it
doesn't have current data and it should be working on catchup instead.

Discussion here obviously has wandered past your fundamental objections
here and onto implementation trivia, but I didn't think the difference
between what you expected and what's actually committed already was
properly addressed before doing that.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us