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

Ühel kenal päeval, N, 2007-03-01 kell 13:14, kirjutas Simon Riggs:
> 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

This still needs some tweaking, so that same-page updates don't happen
for tuples that need moving.

> > 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

Yes, that too.

> > 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.

We would still need to do something for HOT tuples that are placed in
the "dense" part of table, that is below the eventual truncation point.

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

That was the main point, yes.

> I'm on the edge here, want to persuade me some more?

It should be much simpler to do than any of the CLUSTER/COPY variants -
"just" null updates with simple new-version placement .

> Pro
> - we don't need any more space

Actually we may need a little (or a lot), as the moved tuples need index
entries. But likely it is much less than for CLUSTER variant.

> Cons
> - not as fast as dump-to-new-file technique

It depends on table contents and amount/placement of dirty space - it
may be much faster. 

> - will end up with bloated indexes and REINDEX is still not concurrent

If we get plan invalidation, then doing CREATE newindex; DROP oldindex;
would be a good replacement for REINDEX. And it can be done CONCURRENTLY
as well.


Work done on COMPACT TABLE could probably be reused later for some
version of CLUSTER CONCURRENTLY which would do something similar, but
pay more attention on ordering and/or clustering and fill factor

-- 
----------------
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