Re: Default autovacuum settings too conservative

Hi, Christopher,

Christopher Browne wrote:

> Right. And part of the trouble is that you lose certainty that you
> have covered off transaction wraparound.

Yes. Vacuum (full) serve at least four purposes:

- TID wraparound prevention
- obsolete row removal
- table compaction
- giving space back to the OS by truncating files

While the first one needs full table sweeps, the others don't. And from
my personal experience, at least the obsolete row removal is needed much
more frequently than TID wraparound prevention.

>>When tables are tracked individually for wraparound, the longest
>>transaction required for vacuuming will be one to vacuum one
>>table. With delete-map and other functions, the time for that
>>transaction may be reduced. Partial vacuum of large tables is an
>>option, but again requires some real smarts in the autovac code to
>>track wraparound issues.
>
> Unfortunately, "delete-map" *doesn't* help you with the wraparound
> problem. The point of the "delete map" or "vacuum space map" is to
> allow the VACUUM to only touch the pages known to need vacuuming.
>
> At some point, you still need to walk through the whole table (touched
> parts and untouched) in order to make sure that the old tuples are
> frozen.

Preventing transaction ID wraparound needs a guaranteed full table sweep
during a vacuum run, but not necessarily in a single transaction. It
should be possible to divide this full table sweep into smaller chunks,
each of them in its own transaction.

It will certainly be necessary to block e. G. simultaneous VACUUMs,
CLUSTERs or other maintainance commands for the whole VACUUM run, but
normal SELECT, INSERT and UPDATE statement should be able to interleave
with the VACUUM transaction.

Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org