Re: Allow "snapshot too old" error, to prevent bloat - Mailing list pgsql-hackers

Stephen Frost <sfrost@snowman.net> wrote:

> I also agree with the general idea that it makes sense to provide a way
> to control bloat, but I think you've missed what Andres was getting at
> with his suggestion (if I understand correctly, apologies if I don't).
>
> The problem is that we're only looking at the overall xmin / xmax
> horizon when it comes to deciding if a given tuple is dead.  That's
> not quite right- the tuple might actually be dead to all *current*
> transactions by being newer than the oldest transaction but dead for all
> later transactions.  Basically, there exist gaps between our cluster
> wide xmin / xmax where we might find actually dead rows.  Marking those
> rows dead and reusable *would* stop the bloat, not just slow it down.
>
> In the end, with a single long-running transaction, the worst bloat you
> would have is double the size of the system at the time the long-running
> transaction started.

I agree that limiting bloat to one dead tuple for every live one
for each old snapshot is a limit that has value, and it was unfair
of me to characterize that as not being a limit.  Sorry for that.

This possible solution was discussed with the user whose feedback
caused me to write this patch, but there were several reasons they
dismissed that as a viable total solution for them, two of which I
can share:

(1)  They have a pool of connections each of which can have several
long-running cursors, so the limit from that isn't just doubling
the size of their database, it is limiting it to some two-or-three
digit multiple of the necessary size.

(2)  They are already prepared to deal with "snapshot too old"
errors on queries that run more than about 20 minutes and which
access tables which are modified.  They would rather do that than
suffer the bloat beyond that point.

IMO all of these changes people are working are very valuable, and
complement each other.  This particular approach is likely to be
especially appealing to those moving from Oracle because it is a
familiar mechanism, and one which some of them have written their
software to be aware of and deal with gracefully.

> I'm not against having a knob like this, which is defaulted to off,

Thanks!  I'm not sure that amounts to a +1, but at least it doesn't
sound like a -1.  :-)

> but I do think we'd be a lot better off with a system that could
> realize when rows are not visible to any currently running transaction
> and clean them up.

+1

But they are not mutually exclusive; I see them as complementary.

> If this knob's default is off then I don't think
> we'd be likely to get the complaints which are being discussed (or, if
> we did, we could point the individual at the admin who set the knob...).

That's how I see it, too.  I would not suggest making the default
anything other than "off", but there are situations where it would
be a nice tool to have in the toolbox.

Thanks for the feedback!

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