pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Max Baker <max@warped.org>
To: Rod Taylor <pg@rbt.ca>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Vacuum takes a really long time, vacuum full required
Date: Tue, 19 Oct 2004 14:12:45 -0400
Message-ID: <20041019181245.GJ21258@warped.org> (raw)
In-Reply-To: <1098200416.750.238.camel@home>
References: <20041019153821.GA21258@warped.org>
	<1098200416.750.238.camel@home>

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



view thread (5+ messages)  latest in thread

Message-ID: <20041019181245.GJ21258@warped.org>
Permalink:  ../20041019181245.GJ21258@warped.org/
Also on:    postgresql.org/message-id/20041019181245.GJ21258@warped.org

 · 

reply

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: max@warped.org, pg@rbt.ca
  Subject: Re: Vacuum takes a really long time, vacuum full required
  In-Reply-To: <20041019181245.GJ21258@warped.org>

* 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