Re: Revitalising VACUUM FULL for 8.3 - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Revitalising VACUUM FULL for 8.3
Date
Msg-id 1172754854.3760.1267.camel@silverbirch.site
Whole thread Raw
In response to Re: Revitalising VACUUM FULL for 8.3  (Hannu Krosing <hannu@skype.net>)
Responses Re: Revitalising VACUUM FULL for 8.3  ("Zeugswetter Andreas ADI SD" <ZeugswetterA@spardat.at>)
Re: Revitalising VACUUM FULL for 8.3  (Hannu Krosing <hannu@skype.net>)
List pgsql-hackers
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
spacein 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-filledblock 

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




pgsql-hackers by date:

Previous
From: "Simon Riggs"
Date:
Subject: Re: Revitalising VACUUM FULL for 8.3
Next
From: "Zeugswetter Andreas ADI SD"
Date:
Subject: Re: COMMIT NOWAIT Performance Option