Re: Postgres DB maintainenance - vacuum and reindex

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(at)uci(dot)cu
Telf: +53 07-837-3737
+53 07-837-3714
Universidad de las Ciencias Informáticas
http://www.uci.cu