pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: vacuum full 100 mins plus?
Date: Thu, 15 Jul 2004 00:36:43 -0400
Message-ID: <16532.1089866203@sss.pgh.pa.us> (raw)
In-Reply-To: <m33c3uhuxi.fsf@wolfe.cbbrowne.com>
References: <OF3581B09F.CDD98B1C-ON88256ED1.007318CA-88256ED1.0076E70C@fds.com>
	<OFE37D67EB.C42A1B3B-ON88256ED1.007C0430-88256ED1.007D08AB@fds.com>
	<m33c3uhuxi.fsf@wolfe.cbbrowne.com>

Christopher Browne <cbbrowne@acm.org> writes:
> A long time ago, in a galaxy far, far away, PHatcher@macys.com (Patrick Hatcher) wrote:
>> Answered my own question.  I gave up the vacuum full after 150 mins.  I was
>> able to export to a file, vacuum full the empty table, and reimport in less
>> than 10 mins.  I suspect the empty item pointers and the sheer number of
>> removable rows was causing an issue.

> In that case, you'd be a little further better off if the steps were:
>  - drop indices;
>  - copy table to file (perhaps via pg_dump -t my_table);
>  - truncate the table, or drop-and-recreate, both of which make
>    it unnecessary to do _any_ vacuum of the result;
>  - recreate indices, probably with SORT_MEM set high, to minimize
>    paging to disk
>  - analyze the table (no need to vacuum if you haven't created any
>    dead tuples)
>  - cut SORT_MEM back down to "normal" sizes

Rather than doing all this manually, you can just CLUSTER on any handy
index.  In 7.5, another possibility is to issue one of the forms of
ALTER TABLE that force a table rewrite.

The range of usefulness of VACUUM FULL is really looking narrower and
narrower to me.  I can foresee a day when we'll abandon it completely.

			regards, tom lane



view thread (6+ messages)  latest in thread

Message-ID: <16532.1089866203@sss.pgh.pa.us>
Permalink:  ../16532.1089866203@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16532.1089866203@sss.pgh.pa.us

 · 

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-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, cbbrowne@acm.org
  Subject: Re: vacuum full 100 mins plus?
  In-Reply-To: <16532.1089866203@sss.pgh.pa.us>

* 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