Re: Feedback on getting rid of VACUUM FULL

On Wed, 2009-09-16 at 13:20 -0500, Kevin Grittner wrote:
> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> > a) To date, I have yet to hear a single person bring up an actual
> > real-life use-case where VACUUM FULL was desireable and REWRITE
> > would not be.
>
> Would rewrite have handled this?:
>
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php

If REWRITE is just a CLUSTER using seqscan, then no

If it is a sequence of

1. ordinary VACUUM (it can't run out of FSM anymore, no?)
2. a process moving live tuples from end (using reverse seqscan) to free
space found scanning in first-to-last direction, either one tuple at a
time or one page at a time, until the two scans meet
3. another ordinary VACUUM to actually reclaim the free space

4. repeat a few times so that tuples at the end of relation (for
whatever reason) added while doing 1-3 are also moved towards beginning

then yes, it would have taken some time, but it would have definitely
helped

It would still have caused index bloat, so to get full benefit of it,
one should have finished it up with an equivalent of CONCURRENT REINDEX.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training