Re: Revitalising VACUUM FULL for 8.3

On Thu, 2007-03-01 at 14:42 +0200, Hannu Krosing wrote:
> Ühel kenal päeval, N, 2007-03-01 kell 14:32, kirjutas Hannu Krosing:
>
> > 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
>
> This means that
>
> VACUUM FULL mytable;
>
> would translate to:
>
> VACUUM mytable; -- make free space
-- stop here if nothing further to do
-- stop when freespace >= remaining space in table
-- update the FSM, so that concurrent inserts/updates
put their new versions at start of table
> COMPACT mytable; -- move tuples in a bunch of small transactions
> -- might have a GUC for max trx length
-- which => always perform COLD UPDATEs, never HOT ones
> VACUUM mytable; -- free the tuples at the end and give space back to fs
-- start the VACUUM from the first non-filled block

So if we do this, we wouldn't need to worry about HOT tuples at all, nor
would we need to wait until all transactions are gone.

This would also mean that VACUUM FULL could be achieved concurrently.

I'm on the edge here, want to persuade me some more?
Pro
- we don't need any more space

Cons
- not as fast as dump-to-new-file technique
- will end up with bloated indexes and REINDEX is still not concurrent

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com