Re: Turning off HOT/Cleanup sometimes

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com>
Cc: Greg Stark <stark(at)mit(dot)edu>, Simon Riggs <simon(at)2ndquadrant(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Andres Freund <andres(at)anarazel(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: Turning off HOT/Cleanup sometimes
Date: 2015-04-16 13:20:20
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.

Of course, as Greg says there will be some additional scans of the HOT
chain by read-only processes.

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Abhijit Menon-Sen 2015-04-16 13:24:59 Re: initdb -S and tablespaces
Previous Message Heikki Linnakangas 2015-04-16 11:57:03 Re: Streaming replication and WAL archive interactions