Re: vacuum full table - internals in 8.3

Thanks Kevin. That confirms what I've seen on 8.3.

Could you explain what causes index bloat when running vacuum full? I've
read that index bloat can occur, but no quite sure what is going on internally.

Kieren

> Date: Tue, 5 Oct 2010 10:24:26 -0500
> From: Kevin(dot)Grittner(at)wicourts(dot)gov
> To: kierenscott(at)hotmail(dot)com; pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] vacuum full table - internals in 8.3
>
> Kieren Scott <kierenscott(at)hotmail(dot)com> wrote:
>
> > I'm trying to understand what is going on internally when doing a
> > VACUUM FULL on a table in 8.3.
> >
> > I have a table that is 1GB in size, 500M is used, and 500M is free
> > space. When I do a vacuum full on this table, will it either: -
> >
> > 1) Compact all of the used tuples into free space within the
> > existing disk file and then shrink the file to 500M. Therefore
> > simply freeing up 500M in the disk file.
>
> On 8.3, this is what it will do, although it can take a very long
> time. I've given up on this before completion (sometimes after
> leaving it cranking away for a couple days) every time I've tried it
> on a table with more than a few GB and any significant bloat. I
> don't know that I've ever tried it on a table as small as you
> describe, but I would bet that CLUSTER is going to be much faster if
> you have the half a GB free space. Another issue with VACUUM FULL
> is that it bloats indexes; so you generally need to follow it with a
> REINDEX on the table.
>
> -Kevin
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin