pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Nick Urbanik <nicku@nicku.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: How overcome wait for vacuum full?
Date: Tue, 15 May 2007 18:53:13 -0400
Message-ID: <20070515225313.GV12731@alvh.no-ip.org> (raw)
In-Reply-To: <20070515221356.GG5264@nicku.org>
References: <20070515221356.GG5264@nicku.org>

Nick Urbanik wrote:
> Dear Folks,
> 
> I am running a full vacuum on a database.  It's taking longer than I
> hoped.  In particular, the vacuum still hasn't reached the table that
> will benefit most from the vacuum.
> 
> Can I move the existing table to a backup, make a copy of the table
> back to its original name, restart the application, and run the vacuum
> on the backup?  Or can I reclaim the disk space by dropping the
> original after making a copy?
> 
> If I can, without losing data, what is the best way to do that?
> I am reaching the end of the period when this database application can
> be disabled.
> 
> I have never run vacuum full on such a large database (24G) and am
> desperate to get the application back online.

Instead of waiting a month for the time when you can take the
application offline (thus accumulating a month's worth of dead tuples),
run a non-full vacuum more often (say, once a day or more).  It doesn't
lock the table so the app can continue to be online while it runs.

If you have too many dead tuples and are desperate to get the table in a
reasonable non-bloated state, try CLUSTER instead of VACUUM FULL.  It
might finish faster.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support



view thread (12+ messages)  latest in thread

Message-ID: <20070515225313.GV12731@alvh.no-ip.org>
Permalink:  ../20070515225313.GV12731@alvh.no-ip.org/
Also on:    postgresql.org/message-id/20070515225313.GV12731@alvh.no-ip.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: alvherre@commandprompt.com, nicku@nicku.org
  Subject: Re: How overcome wait for vacuum full?
  In-Reply-To: <20070515225313.GV12731@alvh.no-ip.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox