Re: Reduce pinning in btree indexes

On 16 March 2015 at 13:02, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> I'm not sure what the value of basing this on WAL volume is, and I
> think we should talk about that a bit more first. The value of doing
> this by rate of XID consumption is easy to understand: it's simple to
> implement. The value of doing this by time is also simple to
> understand: if you set the setting to 20 minutes, then you can be sure
> that queries will not get cancelled unless they run for more than 20
> minutes. This clearly makes the system easier to reason about. I
> don't see what the benefit of doing this by WAL volume would be.

I think we can assess that more clearly as the amount of now-dead
space left by an action. So we can include Updates and Deletes, or in
the case of aborted transactions, the total WAL written. I assumed
that was what Kevin meant.

> What Simon actually proposed was by *bloat*; that is, if the overall
> amount of bloat in the system exceeds some metric, start whacking old
> queries in order to control it. The appeal of that is obvious,

Agreed

> but I
> think it would be very hard to make work, because canceling queries
> won't get rid of the bloat you've already introduced, and so you'd
> tend to cancel nothing until you hit some threshold, and then start
> canceling everything.

That would just be an unhelpful way of using that info. There are many
possible designs that wouldn't need to work that way.

We have many cases where we begin a cleanup action before we run out
of space for other server resources. VACUUM is itself a good example,
but there are many others.

The main point is that if we blindly decide things based upon xid age
or time then it will be wrong in many cases, since apps with uniform
write rates are rare. We need to work out how to limit bloat itself.
If we can't do that easily at a macro level, then we'll have to do
that more precisely at the table level, for example having VACUUM
decide where to put the limit if we find too much unremovable/recently
dead data.

> If we want this feature, let's try to keep the spec simple enough that
> we actually get it.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, RemoteDBA, Training & Services