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

Re: 8.2rc1: vacuum full fills up disk space

From: "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
To: "Thomas H(dot)" <me(at)alternize(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 17:35:48
Message-ID: 456B21F4.6090301@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Thomas H. wrote:
>>> this somehow sounds buggy:
>>
>> vacuum full absolutely *will* bloat your index, if run on a
>> heavily-modified table.  I do not think it will bloat pg_xlog by itself
>> however; are you sure you don't have some other open transactions?
> 
> 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.

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.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

In response to

Responses

pgsql-bugs by date

Next:From: Patrick HayesDate: 2006-11-27 18:04:05
Subject: BUG #2785: Exception Issue
Previous:From: Jeremy HaileDate: 2006-11-27 17:14:00
Subject: Re: fsync and semctl errors with 8.1.5/win32

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