Re: Turning off HOT/Cleanup sometimes

On 2014-09-19 17:29:08 -0400, Robert Haas wrote:
> > I generally have serious doubts about disabling it generally for
> > read workloads. I imagine it e.g. will significantly penalize
> > workloads where its likely that a cleanup lock can't be acquired
> > every time...
>
> I share that doubt. But I understand why Simon wants to do something,
> too, because the current situation is not great either.

Right, I totally agree. I doubt a simple approach like this will work in
the general case, but I think something needs to be done.

I think limiting the amount of HOT cleanup for readonly queries is a
good idea, but I think it has to be gradual. Say after a single cleaned
up page at least another 500 pages need to have been touched till the
next hot cleanup. That way a single query won't be penalized with
cleaning up everything, but there'll be some progress.

The other thing I think might be quite worthwile would be to abort hot
cleanup when the gain is only minimal. If e.g. only 1 small tuple is
removed from a half full page it's not worth the cost of the wal logging
et al.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services