PostgreSQL - Rotina de VACUUM optimizada

   Primeiramente devo agradecer ao Fabrízio Mello(http://fabriziomello.blogspot.com.br/) que trabalhou junto comigo na rotina abaixo.

CENÁRIO: Base 24x7 em uma base de quase 500GB.

PROBLEMA: Optou-se por tornar o auto-vacuum mais agressivo para não impactar no ambiente durante o horário comercial, mas o VACUUM na base inteira ainda que executado no domingo demorava muito, mesmo com alteração dos parâmetros de memória.  

SOLUÇÃO: Em conjunto com o Fabrízio Mello, o qual tem minha indicação incondicional, elaboramos a rotina abaixo através do PgAgent em um job de BATCH:

#!/bin/bash

PGUSER="postgres"
PGPORT="5432"
PGDATABASE="postgres" 
PGHOST="localhost"

export PGUSER PGPORT PGDATABASE PGHOST

START="00:00:00"
END="06:00:00"
LIMIT="20"

SQL="
SELECT 'VACUUM ANALYZE VERBOSE '||quote_ident(schemaname)||'.'||quote_ident(relname)||';'
  FROM pg_stat_user_tables
 WHERE (n_dead_tup+n_live_tup)>0
 ORDER BY (n_dead_tup * 100)/(n_dead_tup+n_live_tup) desc
 LIMIT ${LIMIT};"

echo "Iniciando rotina de vacuum:" > /tmp/vacuum-maintenance-$PGDATABASE-v2.log

psql -Atq -c "$SQL" | while read i
do
 now=$(date +%H%M%S)
 if [[ "$now" == "$START" || "$now" > "$START" &&
   "$now" < "$END" || "$now" == "$END" ]]
 then
  psql -c "$i" >> /tmp/vacuum-maintenance-$PGDATABASE-v2.log 2>&1
 
 fi
done

Em resumo o job realiza VACUUM ANALYZE nas 20(quantidade definida com o parâmetro LIMIT) tabelas que mais tem tupulas mortas. Entretanto a execução ocorre apenas entre o START e o END que for definido no job, caso finalize a tabela 19 e o horário for maior que o definido em END o job é finalizado sem realizar o VACUUM ANALYZE na tabela 20 da query. O resultado podes ver em /tmp/vacuum-maintenance-$PGDATABASE-v2.log onde $PGDATABASE é o nome da base alvo do BATCH.