Message-ID: <4B9FBFEB.5060503@uci.cu>
Date: Tue, 16 Mar 2010 13:29:15 -0400
From: "Ing. Marcos Ortiz Valmaseda" <mlortiz@uci.cu>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
CC: pgsql-performance@postgresql.org
Subject: Re: Postgres DB maintainenance - vacuum and reindex
References: <27913694.post@talk.nabble.com>
In-Reply-To: <27913694.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Meena_Ramkumar escribió:
> How to run vacuumdb and reindex for Postgres DB in a non-stop server? Will it
> be made without shutting the server? If so, then what will be performance
> degradation percentage?
>   
To execute vacuum, you can´t stop the server, is another process of it.
If you are using a recent version of PostgreSQL, you can use autovacuum 
on the server and this process is charged of this or to use VACUUM with 
the right schedule. You should avoid to use VACUUM FULL, because is very 
slow and it requires exclusive locks of the tables that you are 
executing this, and it reduces the table size on the disc but It doesn´t 
reduce the index size, but iit can make indexes larger.

With autovacuum = on, you can avoid to use VACUUM frecuently

The performance degradation depends of the quantity of tables and 
databases that you have on your server.

REINDEX is another task that you can execute periodicly on you server, 
but if you don´t want to affect the production task, the best thing yo 
do is to drop the index and reissue the CREATE INDEX CONCURRENTLY command.

Regards


-- 
-------------------------------------------------------- 
-- Ing. Marcos Luís Ortíz Valmaseda                   --
-- Twitter: http://twitter.com/@marcosluis2186        --
-- FreeBSD Fan/User                                   --
-- http://www.freebsd.org/es                          --
-- Linux User # 418229                                --
-- Database Architect/Administrator                   --
-- PostgreSQL RDBMS                                   --
-- http://www.postgresql.org                          --
-- http://planetpostgresql.org                        --
-- http://www.postgresql-es.org                       --
--------------------------------------------------------
-- Data WareHouse -- Business Intelligence Apprentice --
-- http://www.tdwi.org                                --
-------------------------------------------------------- 
-- Ruby on Rails Fan/Developer                        --
-- http://rubyonrails.org                             --
--------------------------------------------------------

Comunidad Técnica Cubana de PostgreSQL
http://postgresql.uci.cu
http://personas.grm.uci.cu/+marcos    

Centro de Tecnologías de Gestión de Datos (DATEC) 
Contacto:                                                             
        Correo: centalad@uci.cu                                        
        Telf: +53 07-837-3737                                        
              +53 07-837-3714                                        
Universidad de las Ciencias Informáticas        
http://www.uci.cu