Re: Reduce pinning in btree indexes

On Mon, Mar 16, 2015 at 8:48 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> 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?

Oh, gosh. Maybe we could invent a whole policy language for this.
Or, if we want to do something less painful, we could fire nail-guns
into our eyeballs.

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.

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, 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.

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

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