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(at)2ndQuadrant(dot)com www.2ndQuadrant.us