From: PFC <lists@boutiquenumerique.com>
To: pgsql-performance@postgresql.org
Subject: Re: VACCUM FULL ANALYZE PROBLEM
Date: Tue, 15 Feb 2005 09:51:22 +0100
Message-ID: <opsl8djwxyth1vuj@musicbox> (raw)
In-Reply-To: <8499.1108445430@sss.pgh.pa.us>
References: <200502150202.j1F22flW029903@mail.census.gov.ph>
<009201c51306$6e9ece80$7201a8c0@mst1x5r347kymb>
<8499.1108445430@sss.pgh.pa.us>
I don't know if this would work, but if you just want to restructure your
rows, your could do this:
UPDATE table SET id = id WHERE id BETWEEN 0 AND 20000;
VACUUM table;
UPDATE table SET id = id WHERE id BETWEEN 20001 AND 40000;
VACUUM table;
wash, rinse, repeat.
The idea is that an update rewrites the rows (in your new format) and
that VACUUM (not FULL) is quite fast when you just modified a part of the
table, and non-locking.
Would this work ?
> "Iain" <iain@mst.co.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
>
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: lists@boutiquenumerique.com
Subject: Re: VACCUM FULL ANALYZE PROBLEM
In-Reply-To: <opsl8djwxyth1vuj@musicbox>
* 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