Re: Feedback on getting rid of VACUUM FULL

On Wed, 2009-09-16 at 11:48 -0700, Josh Berkus wrote:
> Hannu,
>
> > The only case is when you are out of disk space and can't afford to
> > write out a full set of live rows.
>
> Well, it's actually rather specific. You need to have:
>
> a) *Some* free disk space (FULL requires extra disk) but not enough to
> copy one entire table and its indexes.
>
> b) be already down or willing to accept the long downtime which comes
> with FULL more than you're willing to go out and get some extra disk or
> move your database to a new share.
>
> There's no question that this combination is fairly circumstantial and
> represents a minority of potential vacuum cases. Unfortunately, it does
> seem to represent some real-life ones, so we have to take those into
> account.

Agreed.

> > What I'd like to propose is VACUUM FULL CONCURRENTLY, which similar to
> > VACUUM CONCURRENTLY, would actually do the compaction phase, that is,
> > move simultaneously from two directions, from start, to find empty space
> > and from end to find tuples. for each sufficiently large empty space the
> > forward scan finds it would take one or more tuples from the reverse
> > scan and then "null update" those to the empty space found by the
> > free-space-scan beginning. it should do that in small chunks, say one
> > page at a time, so it will minimally interfere with OLTP loads.
>
> How would this work with HS?

Exactly the same as just doing a lot of UPDATE's which move tuples
around between pages.

It actually _is_ a lots of updates, just with extra condition that tuple
is always moved to lowest available free slot.

> --
> Josh Berkus
> PostgreSQL Experts Inc.
> www.pgexperts.com
>
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training