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

Re: Vacuum takes a really long time, vacuum full required

From: Max Baker <max(at)warped(dot)org>
To: Rod Taylor <pg(at)rbt(dot)ca>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Vacuum takes a really long time, vacuum full required
Date: 2004-10-19 18:12:45
Message-ID: 20041019181245.GJ21258@warped.org (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Rod,

On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote:
> > Whatever the case, the database still slows down to a halt after a month or
> > so, and I have to go in and shut everything down and do a VACUUM FULL by
> > hand.  One index (of many many) takes 2000 seconds to vacuum.  The whole
> > process takes a few hours.
> 
> Do a REINDEX on that table instead, and regular vacuum more frequently.

Great, this is exactly what I think it needs.  Meanwhile, I was checking out

    http://www.postgresql.org/docs/7.3/static/sql-reindex.html

Which suggests I might be able to do a drop/add on each index with the
database 'live'.

However, the DROP INDEX command was taking an awfully long time to complete
and it hung my app in the mean time.   Does anyone know if the DROP INDEX
causes an exclusive lock, or is it just a lengthy process?

> > $ pg_config --version
> >     PostgreSQL 7.3.2
> 
> 7.4.x deals with index growth a little better 7.3 and older did.

Will do.  Meanwhile I'm stuck supporting older 7.x versions, so I'm still
looking for a solution for them.

Thanks!
-m

In response to

pgsql-performance by date

Next:From: Max BakerDate: 2004-10-19 18:43:29
Subject: Re: Free PostgreSQL Training, Philadelphia, Oct 30
Previous:From: Contact AR-SD.NETDate: 2004-10-19 17:49:45
Subject: Re: Index not used in query. Why?

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