Particionamento de Tabelas no postgres – Detalhes

ParticionamentoBom, até agora já discutimos QUANDO e COMO particionar tabelas. Apesar se já ser possível trabalhar com as nossas tabelas particionadas com o que vimos até agora, na prática precisamos ver mais alguns detalhes. Sim, ainda temos alguns deles para discutir aqui.

E o particionamento de uma tabela com FK para outra tabela particionada?

Sim, ficamos devendo, mas não esqueci. Não existe mistério aqui. O particionamento da tabela PEDIDO_DETALHE pode ocorrer sem problemas, uma vez que: a chave ANO_PEDIDO é idêntica em ambas as tabelas e vamos particionar ela com o mesmo tipo de critério e para os mesmos valores: uma para 2008, 2009, 2010, 2011, 2012 e 2013. Isso tem de cassar perfeitamente. Na verdade, sempre que você for criar uma nova partição na tabela PEDIDO, já tem de criar uma partição com os mesmos critérios na tabela PEDIDO_DETALHE. Se você fizer assim, não terá nenhum problema:

Vou pular aqui alguns detalhes que mostramos antes como parâmetros de storage, comentários etc. Vamos direto ao que interessa: ajustar as FKs das partições que criamos agora:

Ou seja, a partição PEDIDO_DETALHE_2008, tem uma FK que aponta para a partição PEDIDO_2008. Pronto, é só isso.

Posso inserir registros na tabela mãe?

A princípio não. Se você quiser inserir registros na tabela mãe, o primeiro problema que você vai esbarrar é o gatilho. Lá a gente já criou uma proteção para não permitir inserir registros fora das partições existentes:

Ok, podemos retirar esta trava com algo como:

Se você fizer isso, ao inserir na tabela PEDIDO, você vai entrar num laço infinito:

Ou seja, o postgres tenta fazer um INSERT na tabela PEDIDO, mas o gatilho desvia para um outro INSERT na tabela PEDIDO novamente, que dispara o gatilho e começa tudo novamente. Isso se chama referência circular e não deve nunca ocorrer numa função dentro de um banco de dados. Claro, existe uma alternativa simples, que é retornar os valores novos ao invés de NULL:

Agora sim o INSERT na tabela PEDIDO, vai funcionar para valores de ANO_PEDIDO fora do intervalo das partições.

Outro detalhe é o CHECK constraint:

Você teria de editar ele para algo como:

Isto pode ser útil para manter informações históricas antigas que não são utilizadas com frequência. Mas você não vai poder usar um tablespace diferente para ele se você herdar os tablespaces durante a criação das tabelas filha. Vale a pena lembrar que quando falamos de bases realmente volumosas o uso de tablespaces costuma ser mais comum e exige sempre um planejamento cuidadoso.

Mantendo um grande número de partições

Como disse antes, deixar um número muito grande de partições não é uma boa prática no PostgreSQL. O mecanismo de CONSTRAINT_EXCLUSION e o gatilho/regra começam a ficar enormes, e isso significa perda de desempenho. Particularmente tabelas de auditoria tem a mania de crescer muito e podem ter muitas partições, dependendo da aplicação. Existe uma forma de manter os dados e diminuir o número de partições. Para isto basta fazer com que a partição deixe de ser uma tabela filha, utilizando o NO INHERIT:

Os dados da tabela PEDIDO_2008 continuam acessíveis, mas você não conseguirá mais acessar eles a partir da tabela PEDIDO. Isso vai exigir alterações na sua aplicação, claro.

Cargas em lote planejadas

Independente de você ter escolhido usar gatilho ou regra como método para desviar o INSERT para as partições, isto impõe um overhead, que em algumas situações não é despresível. Além disso, índices e restrições também diminuem a velocidade da carga. Se você observar um dump feito pelo pg_dump vai observar uma ordem ótima para carga de dados: cria-se a tabela sem índices ou restrições, depois importamos os dados com o COPY e por último criamos os índices e restrições. Esta é a forma mais rápida de importar um grande volume de dados. No nosso caso, a única diferença seria um comando extra no final para filiar a tabela:

Gatilho de UPDATE

Todo mundo sabe como é chato ter de atualizar a PK de um registro, principalmente se você estiver mexendo numa tabela central do seu sistema. Mexer no valor da chave de um registro numa tabela particionada é pior. Se você tentar vai ver algo como:

O seu UPDATE está ocorrendo na verdade na partição APP.PEDIDO_2008 o registro não vai mudar automaticamente para a tabela APP.PEDIDO_2009. Você precisa de um gatilho de UPDATE para conseguir fazer isso, tornando a operação ainda mais cara para o postgres. O detalhe é que não adianta um gatilho na tabela APP.PEDIDO. Se você o fizer, a restrição CHECK vai levantar a mesma excessão que você viu acima, antes mesmo do gatilho ser disparado. Então o que deve ser feito é criar um gatilho para cada partição, aumentando um pouco mais a complexidade do nosso sistema:

Aqui mostrei o gatilho apenas para a partição PEDIDO_2008. Você terá de fazer o mesmo para as partições 2009, 2010, 2011, 2012 e 2013. Veja o funcionamento:

Claro, que você pode simplificar um pouco as coisas, criando uma função de gatilho mais genérica. No entanto, tenha em mente que você vai continuar tendo que criar um gatilho para cada tabela:

 Estamos quase encerrando o assunto…

Bom, este é o 3º post sobre o assunto, no 4º e último da série estarei demonstrando uma função para automatizar a criação de partições.

3 comentários sobre “Particionamento de Tabelas no postgres – Detalhes

Dúvidas, sugestões, críticas, comentários e cervejas são bem vindos!!!