Re: Revitalising VACUUM FULL for 8.3

Ühel kenal päeval, N, 2007-03-01 kell 12:02, kirjutas Simon Riggs:
> Use case for VACUUM FULL is very low these days. VACUUM does the most
> important part of what VACUUM FULL offers, yet does it concurrently
> rather than with a full table lock. VACUUM FULL also
> - has very long execution time
> - generates lots of WAL traffic
> - uses lots of memory while it runs
> - isn't as good at compacting a relation as CLUSTER
> - sometimes requires multiple runs to properly compact data

Yet another way to achieve VACUUM FULL-like results would be a COMPACT
TABLE command, which would do the following:

1 - start a forward sequential scan to find free space

2 - start a backwards seqscan to find live tuples

move live tuples to free space by doing a null update (UPDATE without
changing any field values) with new version being placed to lowest
possible age until the two scans intersect.

This, together with ordinary VACUUM would achieve almost the same
results as VACUUM FULL with much lower overhead. And it can also be done
in lots of smaller transactions instead of one big one if required.

If we can trust FSM, the whole process just becomes the backward scan
and null updates until the null update does not move tuple to a lower
page. Also, for the duration of COMPACT TABLE the updated tuple should
always be placed in lowes available slot, that is no same-page updates
should be tied before going to FSM.

This has some downsides :

1 - the original xmin will be lost

2 - as with any updates, it may block/abort other concurrent updates, so
it could be a good thing to teach the update mechanism about null
updates.

Still I think that this would be the chepest way to get VACUUM FULL
behaviour without locking the whole table for long time

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com