Re: Reduce pinning in btree indexes

On 16 March 2015 at 12:48, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>> On 13 March 2015 at 15:41, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>>
>>> The feedback was generally fairly positive except for the fact that
>>> snapshot "age" (for purposes of being too old) was measured in
>>> transaction IDs assigned. There seemed to be a pretty universal
>>> feeling that this needed to be changed to a time-based setting.
>>
>> -1 for a time based setting.
>>
>> After years of consideration, bloat is now controllable by altering
>> the size of the undo tablespace.
>>
>> I think PostgreSQL needs something size-based also. It would need some
>> estimation to get it to work like that, true, but it is actually the
>> size of the bloat we care about, not the time. So we should be
>> thinking in terms of limits that we actually care about.
>
> Are you thinking, then, that WAL volume generated (as determined by
> LSN) would be the appropriate unit of measure for this? (We would
> still need to map that back to transaction IDs for vacuuming, of
> course.) If we did that we could allow the "size" units of
> measure, like '5GB' and similar. Or are you thinking of something
> else?

It's probably the closest and easiest measure, and the most
meaningful. We can easily accumulate that in a data structure in clog,
like async commit LSN. For next release though, since it will take a
little bit of thought to interpret that.

With commit timestamp enabled in 9.5, we can easily judge time limit,
but it is less useful because its not a measure of bloat.

As I've said, I'd be happy with just an xid limit for 9.5, if that was
the only thing we had. But I think timestamp is just as easy.

> Given that there seems to be disagreement on what is the more
> useful metric, do we want to consider allowing more than one? If
> so, would it be when *all* conditions are met or when *any*
> conditions are met?

Yours was the first reply to my idea, so I think its too early to
describe that as disagreement.

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