Date: Sun, 9 Apr 2006 14:37:47 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Tomas Vondra <tv@fuzzy.cz>
Cc: pgsql-performance@postgresql.org
Subject: Re: serious problems with vacuuming databases
Message-ID: <20060409183747.GB15094@surnet.cl>
Mail-Followup-To: Tomas Vondra <tv@fuzzy.cz>, pgsql-performance@postgresql.org
References: <443950FB.4050707@fuzzy.cz>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <443950FB.4050707@fuzzy.cz>
User-Agent: Mutt/1.5.11+cvs20060330

Tomas Vondra wrote:

Hi,

> Then
> these data were deleted from A, C, D and tables A_old, C_old and D_old
> were dumped, truncated and all the tables were vacuumed (with FULL
> ANALYZE options). So the procedure was this
> 
> 1) drop, create and fill table B (aggregated data from A, C, D)
> 2) copy 'old' data from A, C and D to A_old, C_old a D_old
> 3) delete old data from A, C, D
> 4) dump data from A_old, C_old and D_old
> 5) truncate tables A, C, D
> 6) vacuum full analyze tables A, C, D, A_old, C_old and D_old
> 
> So the dump of the fatabase has about 1.2 GB of data, from which about
> 1 GB is in the B table (the one rebuilt in step 1). This was done yesterday.
> 
> The problem is this - today, we run a scheduled VACUUM FULL ANALYZE for
> the whole database, and it runs for about 10 hours already, which is
> much more than usual (and it is still running).

Probably the indexes are bloated after the vacuum full.  I think the
best way to get rid of the "fat" is to recreate both tables and indexes
anew.  For this the best tool would be to CLUSTER the tables on some
index, probably the primary key.  This will be much faster than
VACUUMing the tables, and the indexes will be much smaller as result.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.