Re: max_standby_delay considered harmful

Heikki Linnakangas wrote:
> Let's rip out the concept of a delay altogether, and make it a boolean.
> If you really want your query to finish, set it to -1 (using the current
> max_standby_delay nomenclature). If recovery is important to you, set it
> to 0.
>   

So the only user options would be "allow long-running queries to block 
WAL application forever" and "always cancel queries on conflict?"  That 
would be taking away the behavior I was going to suggest as the default 
to many customers I work with.  I expect a non-trivial subset of people 
using this feature will set max_standby_delay to is some small number of 
minutes, similarly to how archive_timeout is sized now.  Enough time to 
get reasonably sized queries executed, not so long as to allow something 
that might try to run for hours on the standby to increase failover 
catchup time very much.

The way the behavior works is admittedly limited, and certainly some 
people are going to want to set it to either 0 or -1.  But taking it 
away altogether is going to cripple one category of potential Hot 
Standby use in the field.  Consider this for a second:  do you really 
think that Simon would have waded into this coding mess, or that I would 
have spent as much energy as I have highlighting issues with its use, if 
there wasn't demand for it?  If it wouldn't hurt the usefulness of 
PostgreSQL 9.0 significantly to cut it, I'd have suggested that myself 
two months ago and saved everyone (especially myself) a lot of trouble.

> If you have the monitoring in place to sensibly monitor the delay
> between primary and standby, and you want a limit on that, you can put
> together a script to flip the switch in postgresql.conf if the standby
> falls too much behind.
>   

There's a couple of things you should do in order for max_standby_delay 
to working as well as it can.  Watching clock sync and forcing periodic 
activity are two of them that always come up.  Those are both trivial to 
script for, and something I wouldn't expect any admin to object to.

If you need a script that involves changing a server setting to do 
something, that translates into "you can't do that" for a typical DBA.  
The idea of a program regularly changing a server configuration setting 
on a production system is one you just can't sell.  That makes this idea 
incredibly more difficult to use in the field than any of the 
workarounds that cope with the known max_standby_delay issues.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us