On 2013-08-31 23:02:46 -0700, Peter Geoghegan wrote:
> On Sat, Aug 31, 2013 at 7:53 PM, Peter Geoghegan <pg@heroku.com> wrote:
> > With a table with many unique indexes and many reasons to decide
> > to reject a tuple proposed for insertion by INSERT...ON DUPLICATE KEY
> > IGNORE, it isn't hard to imagine them all becoming heavily bloated
> > very quickly.
>
> There may be no index tuple directly corresponding to some heap tuple
> where you might expect that, due to transactions aborting (as with a
> traditional unique constraint violations) or because of HOT, but,
> prior to now, never the other way around (heap tuples are created
> first and physically killed last). That is an assumption that has
> existed since the Berkeley days, and it is something that may be
> treated as an invariant in some places. Autovacuum is one such place,
> I'd say. Isn't it the case that the autovacuum launcher daemon might
> not hear about all of this index-only bloat, even if it was extremely
> serious? Isn't the threshold it follows only in respect of heap tuples
> (even if the statistics collector does collect index tuple stats
> separately)?
I don't think the amount of bloat should get that big since we should
immediately kill the promises when we've detected that we conflict on a
following unique index. The reason vacuum needs to care at all is only
that we might have crashed after inserting the promise but before
deleting it and thus haven't cleaned up. That itself isn't too bad since
that should happen infrequently but if we then get a xid wraparound
later we could be waiting on the wrong transaction.
Sure, there would need to be some changes to that code. I think we can
trigger that fairly easy by just triggering relation vacuums via
relfrozenxid and enforce a index bulkdelete on full table vacuums even
if there are no dead tuples.
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services