Skip site navigation (1) Skip section navigation (2)

Re: 8.2rc1: vacuum full fills up disk space

From: "Thomas H(dot)" <me(at)alternize(dot)com>
To: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>,<pgsql-bugs(at)postgresql(dot)org>
Subject: Re: 8.2rc1: vacuum full fills up disk space
Date: 2006-11-27 18:04:38
Message-ID: 0faa01c7124e$81d09cb0$0201a8c0@iwing (view raw or flat)
Thread:
Lists: pgsql-bugs
>> well yes, as the system is "live", users are browsing the website. but 
>> all queries that try to access the table in question are stalled at the 
>> moment. when querying server status i'm seeing lots of queries that are 
>> waiting for access to the table.
>>
>> would vacuum freeze be faster?
>
> Vacuum freeze won't move tuples so it won't reclaim any more space than a 
> normal vacuum. Cluster, however, rewrites the whole table and compacts the 
> space, and runs faster than vacuum full on a badly bloated table. It will 
> also recreate all indexes.

will give it a try later on, thanks!

>
> In the future, instead of updating a whole table with UPDATE, you should 
> consider doing a SELECT INTO to create a new table, dropping the old table 
> and renaming the new one in place of the old one.

the problem is: the table was far from being bloated, IMO. it was 2 days 
old, every record at most 2-3 times updated. the space needed for the table 
dropped from 400mb to roughly 200mb after the 1.5hr vacuum full...

i've never had such a long vacuuming time before, even on tables that are 
much larger and contains more dead rows. the table uses tsearch2 and a 
gin-index, could that be the problem? the gin faq says a drop/create index 
would be much faster than a reindex. maybe this is also true when vacuuming 
a table with a gin-index?

- thomas 



In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-11-27 18:48:25
Subject: Re: multiple SRFs in SELECT clause.
Previous:From: Patrick HayesDate: 2006-11-27 18:04:05
Subject: BUG #2785: Exception Issue

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group