Re: autovacuum not prioritising for-wraparound tables

On 24.01.2013 23:57, Alvaro Herrera wrote:
> I have a bug pending that autovacuum fails to give priority to
> for-wraparound tables. When xid consumption rate is high and dead tuple
> creation is also high, it is possible that some tables are waiting for
> for-wraparound vacuums that don't complete in time because the workers
> are busy processing other tables that have accumulated dead tuples; the
> system is then down because it's too near the Xid wraparound horizon.
> Apparently this is particularly notorious in connection with TOAST
> tables, because those are always put in the tables-to-process list after
> regular tables.
>
> (As far as I recall, this was already reported elsewhere, but so far I
> have been unable to find the discussion in the archives. Pointers
> appreciated.)
>
> So here's a small, backpatchable patch that sorts the list of tables to
> process (not all that much tested yet). Tables which have the
> wraparound flag set are processed before those that are not. Other
> than this criterion, the order is not defined.
>
> Now we could implement this differently, and maybe more simply (say by
> keeping two lists of tables to process, one with for-wraparound tables
> and one with the rest) but this way it is simpler to add additional
> sorting criteria later: say within each category we could first process
> smaller tables that have more dead tuples.
>
> My intention is to clean this up and backpatch to all live branches.
> Comments?

Backpatching sounds a bit scary. It's not a clear-cut bug, it's just
that autovacuum could be smarter about its priorities. There are other
ways you can still bump into the xid-wraparound issue, even with this patch.

- Heikki