From: Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu>
To: Meena_Ramkumar <winmeena_ramkumar@yahoo.co.in>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres DB maintainenance - vacuum and reindex
Date: Tue, 16 Mar 2010 13:29:15 -0400
Message-ID: <4B9FBFEB.5060503@uci.cu> (raw)
In-Reply-To: <27913694.post@talk.nabble.com>
References: <27913694.post@talk.nabble.com>
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.cuhttp://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
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: mlortiz@uci.cu, winmeena_ramkumar@yahoo.co.in
Subject: Re: Postgres DB maintainenance - vacuum and reindex
In-Reply-To: <4B9FBFEB.5060503@uci.cu>
* 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