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

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