Re: Allow "snapshot too old" error, to prevent bloat

Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> On 02/19/2015 09:44 AM, Kevin Grittner wrote:

> I understand why this make people nervous. I wonder if it might be more
> palatable if there were a per-table setting that could enable it? If we
> could ensure that this was only applied to high churn queue tables, say,
> while tables touched by the report writer would not have it applied,
> that would calm a lot of my fears.

That's an interesting idea. I think I should switch the unit of
measure for "too old" to a time-based value first, since there
seems to be universal agreement that it would be better than number
of transactions. Once I've cleared that hurdle I'll see what this
would take.

> I'm also interested in handling the case Stephen Frost described, where
> a tuple is effectively dead but we don't currently have the means of
> discovering the fact, because there is an older long running transaction
> which is never in fact going to be able to see the tuple.

Absolutely. That's one of several other issues that I've been
looking at over the last few weeks. It sounds like people are
already working on that one, which is great. My personal priority
list included that, but after the two I submitted here and a patch
to allow combining near-empty btree pages so that btree bloat is
constrained without having to reindex periodically for the cases
where index tuples are added in index order (at one or a few
insertion points) and most-but-not-all are deleted. You can
currently wind up with a worst-case of one index tuple per page
with no action to reduce that bloat by vacuum processes.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company