Re: Reduce pinning in btree indexes

On Mon, Mar 16, 2015 at 12:07 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> 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.

I am sure there are more sophisticated things to be done here, but I
guess my feeling is that time is a good way to go here for a first cut
- lots of people have suggested it, and there's clearly a use case for
it. If the setting turns out to be popular, we can fine-tune it more
once we've got some experience with it. But I'm nervous about trying
to to design something more complicated than that right now,
especially so late in the release cycle. We know that this setting,
with time-based units, will meet the needs of the customer for whom it
was developed, and that is a good-enough reason to think that time is
a reasonable metric for this, even if we eventually have others.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company