Re: VACCUM FULL ANALYZE PROBLEM

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Michael Ryan S(dot) Puncia" <mpuncia(at)census(dot)gov(dot)ph>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: VACCUM FULL ANALYZE PROBLEM
Date: 2005-02-15 05:58:16
Message-ID: 001701c51323$5b1c1f80$7201a8c0@mst1x5r347kymb
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

OK, that's interesting. So the original assumption that vacuum full was
needed was completely wrong anyway.

If table re-organisation isn't required a plain vacuum would be fastest. I
will take a guess that the next best alternative is to do the "create table
newtable as select ... order by ..." thing and then create the indexes and
stuff. This would reorganize the table completely. After that you have the
cluster command, and coming in last place is vacuum full. Sound about right?

Michael, you said that a vacuum that runs for 3 days is too long, but hasn't
given any specific requirements or limitations. Hopefully you can find
something suitable in the alternatives listed above.

regards
Iain

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Iain" <iain(at)mst(dot)co(dot)jp>
Cc: "Michael Ryan S. Puncia" <mpuncia(at)census(dot)gov(dot)ph>;
<pgsql-performance(at)postgresql(dot)org>
Sent: Tuesday, February 15, 2005 2:30 PM
Subject: Re: [PERFORM] VACCUM FULL ANALYZE PROBLEM

> "Iain" <iain(at)mst(dot)co(dot)jp> writes:
>>> another way to speed up full vacuum?
>
>> Hmmm... a full vacuum may help to re-organize the structure of modified
>> tables, but whether this is significant or not is another matter.
>
> Actually, VACUUM FULL is designed to work nicely for the situation where
> a table has say 10% wasted space and you want the wasted space all
> compressed out. When there is a lot of wasted space, so that nearly all
> the rows have to be moved to complete the compaction operation, VACUUM
> FULL is not a very good choice. And it simply moves rows around, it
> doesn't modify the rows internally; so it does nothing at all to reclaim
> space that would have been freed up by DROP COLUMN operations.
>
> CLUSTER is actually a better bet if you want to repack a table that's
> suffered a lot of updates or deletions. In PG 8.0 you might also
> consider one of the rewriting variants of ALTER TABLE.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Iain 2005-02-15 06:55:02 Re: seq scan cache vs. index cache smackdown
Previous Message Tom Lane 2005-02-15 05:30:30 Re: VACCUM FULL ANALYZE PROBLEM