PostgreSQL: descrease de desempenho devido ao índice de bloatper

Question

Estou executando um PgSQL 8.1 em um sistema CentOS 4.4 (não atualizável, infelizmente). Há um aplicativo Java rodando sobre o daemon do PgSQL e nós temos que reindexar o banco de dados a cada dois meses ou mais. Também é importante: o banco de dados não está crescendo.

Parece que o inchaço está chegando mais rápido do que antes e isso tende a aumentar.
Minha configuração está disponível aqui, o daemon autovacuum está ativado e em execução com bastante frequência: pastebin.com/RytNj7dK

Você também pode encontrar a saída desta consulta wiki.postgresql.org/wiki/Show_database_bloat

3 horas depois de executar a reindex: link
72 horas depois de executar a reindex: link

Alguém tem alguma idéia do que devo ajustar para me livrar desse inchaço crescente?

Obrigado pela sua ajuda

PS: devido ao sistema de prevenção antispam, eu tive que remover os primeiros 2 http: // prefixos para meus dois primeiros links.

    

Answer 1

Infelizmente, se você tiver um banco de dados ativo (um com muitas inserções / atualizações / exclusões), você experimentará o índice de inchaço do índice - é apenas um fato da vida do banco de dados. O melhor que você pode fazer é esperar retardar o inchaço até o ponto em que seus intervalos de reindexação sejam razoáveis.

O melhor conselho que posso dar a você é atualizar para uma versão mais recente do Postgres (8.3 ou posterior): Foi quando o Postgres introduziu Tuplas somente de heap support.
Agora mesmo em seu (8.1) sistema QUALQUER atualização para uma linha é o equivalente a uma exclusão / inserção no que diz respeito ao índice, daí o índice inchar. 8.3 e posterior não toque no índice a menos que seja necessário ("Se a linha ainda couber na página em que está").

Após a atualização para uma versão do Postgres com suporte a HOT, você ainda pode experimentar o índice inchado se seus UPDATE s estiverem tocando em colunas indexadas ou se seu UPDATE s aumentar substancialmente o tamanho de uma linha para que seja necessário ser movido para uma nova página, mas essas situações devem ser relativamente raras, desde que sua estratégia de indexação seja sã e suas linhas sejam relativamente estáticas em tamanho, portanto, o problema de índice de inchaço deve ser um problema menor.

Algumas estratégias gerais adicionais para lidar com o aumento de índice:

1. Índices de chave primária
   Você está muito sem sorte aqui - você precisa REINDEX e pegar a trava da mesa.
2. Outros índices
   • Opção 1: DROP e re- CREATE Índices não críticos
     Isso tem a vantagem de não bloquear a tabela, mas a desvantagem de remover o índice enquanto ele está sendo reconstruído.
   • Opção 2: Index-Shuffle para índices críticos
     Em vez do procedimento Drop / Create acima, primeiro crie um índice novo , então, quando isso for feito, solte o antigo e renomeie o novo para substituí-lo.
     Isso tem a vantagem de não bloquear a tabela e a vantagem de deixar o índice original funcionando (embora inchado). A principal desvantagem é que você precisa renomear um índice para manter as convenções de nomenclatura atualizadas - uma etapa manual extra.