Thread: Time for Vacuum vs. Vacuum Full

Time for Vacuum vs. Vacuum Full

From
Brad Nicholson
Date:
I have a couple of database clusters that need a vacuum full, and I
would like to estimate how long it will take, as it will need to be in a
maintenance window.  I have the times that it takes to to do a regular
vacuum on the clusters, will vacuum full take longer?
--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.


Re: Time for Vacuum vs. Vacuum Full

From
Vivek Khera
Date:
On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote:

> I have the times that it takes to to do a regular
> vacuum on the clusters, will vacuum full take longer?

almost certainly it will, since it has to move data to compact pages
rather than just tagging the rows as reusable.

you can speed things up by dropping your indexes first, then running
vacuum full, then re-creating your indexes.  this will make for
better (more compact) indexes too.

as for how much longer, I don't know how to estimate that.


Re: Time for Vacuum vs. Vacuum Full

From
Decibel!
Date:
On Thu, Aug 09, 2007 at 10:22:57AM -0400, Vivek Khera wrote:
>
> On Aug 9, 2007, at 9:38 AM, Brad Nicholson wrote:
>
> >I have the times that it takes to to do a regular
> >vacuum on the clusters, will vacuum full take longer?
>
> almost certainly it will, since it has to move data to compact pages
> rather than just tagging the rows as reusable.
>
> you can speed things up by dropping your indexes first, then running
> vacuum full, then re-creating your indexes.  this will make for
> better (more compact) indexes too.
>
> as for how much longer, I don't know how to estimate that.

A generally easier approach would be to cluster the tables on an
appropriate index. That does re-write the table from scratch, but in
cases of bad bloat that can actually be a lot faster.

One thing you can do to test this out is to setup another copy of the
database using PITR or some other file-based copy mechanism and try
running VACUUM FULL vs CLUSTER. Note that a copy obtained via pg_dump
obviously won't work for this. :)
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Attachment