Thread: vacuum full table - internals in 8.3

Hi,

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.

2) Rewrite the table data to a new disk file with no free space. Once it has finished, then removes
the old copy of the table. I took this from http://developer.postgresql.org/pgdocs/postgres/sql-vacuum.html
, but I don't know whether this is how it worked prior to version 9.

In the case of 2) I would therefore need at least 500M free space in the filesystem whist it writes out
the new table, and once complete, 1GB would then be freed up when the old copy of the table is
deleted.

Regards

Kieren

Kieren Scott <kierenscott@hotmail.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

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.Grittner@wicourts.gov
> To: kierenscott@hotmail.com; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] vacuum full table - internals in 8.3
>
> Kieren Scott <kierenscott@hotmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin

Kieren Scott <kierenscott@hotmail.com> wrote:

> Could you explain what causes index bloat when running vacuum
> full?

To collapse the space, it copies tuples to locations closer to the
front of the table.  The index needs to contain references to the
old and new tuple copies until the VACUUM FULL commits or rolls
back.  VACUUM FULL doesn't attempt to reorganize the index or free
index space, so the space previously held by index entries pointing
to the old tuples (if you commit) or the new tuples (if you roll
back) will be dead space.  It may be reused by later index
insertions, but the empty space is likely to cause a lot of
partially-filled blocks to bog down performance unless there is a
REINDEX.

-Kevin

Kieren Scott wrote:

I'm trying to understand what is going on internally when doing a VACUUM FULL on a table in 8.3.

The info you've gotten from Kevin is all correct, but you may find some of the additional trivia in this area collected at http://wiki.postgresql.org/wiki/VACUUM_FULL interesting as well.

-- 
Greg Smith, 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support  www.2ndQuadrant.us
Author, "PostgreSQL 9.0 High Performance"    Pre-ordering at:
https://www.packtpub.com/postgresql-9-0-high-performance/book