Re: Turning off HOT/Cleanup sometimes

On Thu, Apr 16, 2015 at 6:50 PM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
wrote:

> Pavan Deolasee wrote:
> > On Thu, Apr 16, 2015 at 2:47 PM, Greg Stark <stark(at)mit(dot)edu> wrote:
>
> > > From a holistic point of view the question is how many times is a given
> > > hit chain going to need to be followed before it's pruned. Or to put it
> > > another way, how expensive is creating a hot chain. Does it cause a
> single
> > > prune? a fixed number of chain readers followed by a prune? Does the
> amount
> > > of work depend on the workload or is it consistent?
> >
> > IMO the size or traversal of the HOT chain is not that expensive compared
> > to the cost of either pruning too frequently, which generates WAL as well
> > as makes buffers dirty. OTOH cost of less frequent pruning could also be
> > very high. It can cause severe table bloat which may just stay for a very
> > long time. Even if dead space is recovered within a page, truncating a
> > bloated heap is not always possible.
>
> I think you're failing to consider that in the patch there is a
> distinction between read-only page accesses and page updates. During a
> page update, HOT cleanup is always done even with the patch, so there
> won't be any additional bloat that would not be there without the patch.
> It's only the read-only accesses to the patch that skip the HOT pruning.
>
>
Ah, Ok. I'd not read the patch. But now that I do, I feel much more
comfortable with the change. In fact, I wonder if its just enough to either
do full HOT prune for target relations and not at all for all other
relations involved in the query. My apologies if this is done based on
discussions upthread. I haven't read the entire thread yet.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee