Re: Time for Vacuum vs. Vacuum Full

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(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)