Re: Revitalising VACUUM FULL for 8.3

On Thu, 2007-03-01 at 14:32 +0200, Hannu Krosing wrote:
> Ü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.

Thats almost exactly what VACUUM FULL does now, just using two passes of
the table. 1st scan builds the free block list and tuples to move list,
then it starts from back, moving rows until it gets to the front. This
does sound like it would save some I/O by avoiding the second half of
the first scan. In most other respects its the same thing, AFAICS.

CLUSTER would still achieve better compaction and you'll still need to
write lots of WAL doing this.

ISTM a radical approach is needed, so I'm very open to discussion about
this and how we cope.

If we break down the main thoughts into a few parts:

1. would like a way to CLUSTER/VACUUM FULL where we don't have to move
all of the tuple versions, just the current ones.

2. would like a way to compact a table more efficiently

Your idea does (2) in a concurrent manner, which is very good.

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