All,
I've been playing with vacuum_defer_cleanup_age in reference to the
query cancel problem. It really seems to me that this is the way
forward in terms of dealing with query cancel for normal operation
rather than wal_standby_delay, or maybe in combination with it.
As a first test, I set up a deliberately pathological situation with
pgbench and a wal_standby_delay of 1 second. This allowed me to trigger
query cancel on a relatively simple reporting query; in fact, to make it
impossible to complete.
Then I increased vacuum_defer_cleanup_age to 100000, which represents
about 5 minutes of transactions on the test system. This eliminated all
query cancels for the reporting query, which takes an average of 10s.
Next is a database bloat test, but I'll need to do that on a system with
more free space than my laptop.
--Josh Berkus