Particionamento de tabelas no postgres – Como?

ParticionamentoBom, já contei um pouco sobre quando particionar tabelas no postgres. Agora vamos explicar direito como. Se você nunca leu a documentação do PostgreSQL sobre particionamento ou se você leu e não entendeu muito bem… este post é para você. Mas é claro que isto não substitui a documentação oficial. Não vou cobrir alguns pontos da documentação e outros eu vou detalhar um pouco mais. Então, a máxima prevalece: RTFM.

Bom, para tornar o nosso caso um pouco mais realista, vamos particionar duas tabelas e vamos colocar uns penduricalhos que são comuns de aparecer em ambientes mais críticos. Tablespaces e parâmetros de armazenamento serão utilizados aqui para reproduzir alguns detalhes que só aparecem quando você utiliza eles. Estamos falando de detalhes um pouco mais complexos. Uma visita a documentação sempre cai bem para quem esqueceu algumas coisas ou não está mesmo familiarizado com estes detalhes.

Tablespaces

Vamos começar criando 4 tablespaces:

  • 1 para a tabela PEDIDO
  • 1 para os índices da tabela PEDIDO
  • 1 para a tabela PEDIDO_DETALHE
  • 1 para os índices da tabela PEDIDO_DETALHE

Durante a criação das tabelas e índices você verá a clausula TABLESPACE especificando exatamente o local onde cada objeto será armazenado. Note que eu não estou sugerindo que você organize seus tablespaces assim. Está OK para a maioria das bases não precisar mexer com isso. Estou aqui criando 4 tablespaces somente para mostrar como as coisas poderiam ficar. É claro que tabelas enormes costumam ter tratamento diferenciado na aplicação. É justamente isso que estamos fazendo aqui.

Vou supor aqui que você usa Unix. Se não usa, se vira aí… não há nada tão complexo aqui. Vamos lá, usando o shell:

Agora usando psql mesmo:

Usuários e permissões

Outro detalhe pouco lembrado é a questão das permissões. Vamos aqui criar um esquema APP para colocar nossas tabelas e um usuário APP para ser o dono das nossas tabelas. Adicionalmente vamos criar um segundo usuário chamado CLIENT para ser o usuário que a nossa aplicação utilizaria para acessar nossas tabelas particionadas. Vou simplificar isso ao máximo pois eu sei o quanto as pessoas adoram mexer com a parte de segurança…

Criando as tabelas mãe

Muito bom, agora vamos criar nossas 2 tabelas que serão particionadas e também 2 tabelas que serão FK da tabela principal mas não serão particionadas. Veja, não ligue para a modelagem delas aqui. Não estou sugerindo que você faça desta forma. Estou apenas demonstrando como o particionamento pode ser feito com o maior número de variáveis envolvidas:

Bom, vamos com calma aqui. São 2 tabelas a serem particionadas, certo? Temos a tabela PEDIDO e a tabela PEDIDO_DETALHE. Para facilitar a nossa vida no particionamento, a tabela pedido tem uma PK composta de dois campos: ID_PEDIDO e ANO_PEDIDO. Assim, todo novo ano, a numeração do ID_PEDIDO é reiniciada pela aplicação. A tabela PEDIDO_DETALHE também herda estes dois campos e adiciona um terceiro na sua PK.  Como você pode imaginar, o campo ANO_PEDIDO está lá para servir de chave do nosso particionamento.

CHECK (….) NO INHERIT

Outro detalhe importante: você notou este CHECK?

Note que o campo ANO_PEDIDO faz parte da PK e não pode ser nulo nunca. Ao mesmo tempo temos uma restrição para que ele seja nulo. Resultado: não dá para inserir nenhum registro nesta tabela. Como esta é a tabela mãe, não queremos mesmo que isso  ocorra, pois só vamos inserir nas tabelas filhas. Por isso aparece a cláusula NO INHERIT no final, para que as tabelas filhas não herdem esta propriedade. Você poderia usar isto de outra forma também. Imagine que você já tem uma tabela populada com vários anos anteriores e decide que só vai particionar os registros a partir de 2010. Então você poderia manter os registros de 2009 para trás na tabela mãe e deixar a restrição assim:

Parâmetros de storage

Um bom DBA costuma olhar com atenção especial para tabelas enormes como as que costumamos particionar. Então é comum ajustar alguns parâmetros de armazenamento como os que aparecem aqui, depois da cláusula WITH. Este ajuste fino é importante para o desempenho e melhor uso do espaço em disco. Está fora do nosso escopo discuti-los aqui, mas novamente o que queremos é ver o que vai ocorrer com estes parâmetros quando criarmos as nossas partições.

Criando as tabelas filhas

Bom, como fazemos para que várias partições se comportem como uma só para a aplicação? No PostgreSQL isto pode ser feito de 2 formas, utilizando uma visão com UNION como é demonstrado na documentação ou utilizando a herança de tabelas. Utilizar herança de tabelas é mais prático e oferece alguns recursos adicionais. A herança de tabelas é algo que nasceu com o postgres quando se aventou a ideia do OBJETO-RELACIONAL. Pouca gente usa a herança de tabelas do PostgreSQL para outra coisa que não seja o particionamento de tabelas. Para criar uma tabela filha, você só precisa usar a cláusula INHERITS:

Aqui criamos a tabela PEDIDO_2008 que é filha da tabela PEDIDO. Note a última linha da descrição da tabela que diz:

Isto mostra a relação de herança, onde a maioria das propriedades da tabela PEDIDO foram herdadas. Mas faltaram algumas coisas… A tabela PEDIDO_2008 não herdou todas as propriedades. Faltaram os índices, as restrições, os tablespaces e os parâmetros de storage. Para isso vamos usar mais uma cláusula, o LIKE … INCLUDING:

Bom, agora sim temos o pacote todo. Você verá uma série de informações extras durante a criação, se as suas configurações estiverem habilitadas para isso. Mas se você olhar com atenção, muita atenção, verá que nem tudo veio como você esperava…

Você pode achar o parâmetro (LIKE INCLUDING …) é meio exagerado. Criaram logo um atalho para incluir os 5 tipos de INCLUDING existentes. Deixei aqui assim só para vocês verem, mas na prática podemos utilizar algo como:

Diferenças na herança do CHECK

Agora vamos a algo bem estranho. Lembra que falamos agora a pouco sobre o NO INHERIT na nossa restrição CHECK. Então, ela veio!!! Não devia ter vindo mais veio. Bom, eu não acho que ela deveria vir, ao menos. Veja, deixei duas restrições do tipo CHECK na tabela PEDIDO. Uma tem o NO INHERIT e outra não. O que vamos ver é que você pode apagar uma restrição, mas a outra não:

Diferenças na herança de FKs

Note que as FKs para as tabelas VENDEDOR e CLIENTE não foram importadas. Você terá que importá-las na mão. Isso pode lhe ajudar:

Diferenças na herança de índices

Os índices na tables PEDIDO_2008 estão OK. Vieram com o tablespace e parâmetros de storage corretos. Note no entanto que o nome dos índices não são os mesmos. Antes tínhamos PEDIDO_PK e PEDIDO_DATA_PEDIDO_INDEX, agora temos os novos nomes: PEDIDO_2008_PKEY e PEDIDO_2008_DATA_PEDIDO_IDX.  Claro que o ’2008′ deveria aparecer no nome, afinal o nome da tabela também mudou. No entanto o sufixo não é o mesmo.

Eu já tentei ter conversas amigáveis com um Administrador de Dados preocupado em como montar o DER com todas as partições. No final convenci a figura a ignorar as partições no DER totalmente. Se você conseguir fazer isso, você não vai se importar com o postgres adicionando nomes para os índices de acordo com o padrão dele – que é um padrão bem razoável por sinal. Tem gente que é muito apegada ao seu padrão de nomenclatura de objetos – nada contra. Para esses você tem dois jeitos de resolver: Use algo como ALTER INDEX pedido_2008_pkey RENAME TO pedido_2008_pk ou não utilize o INCLUDING INDEXES  e crie os índices na mão com os nomes corretos.

Diferenças na herança de parâmetros de storage

Se você prestar atenção, verá que os parâmetros “autovacuum_vacuum_scale_factor=0.1,fillfactor=70″ da tabela PEDIDO, não apareceram na tabela PEDIDO_2008. Ocorre que a cláusula (LIKE INCLUDING STORAGE) só se refere aos parâmetros aplicados nas colunas e não na tabela. Ou seja, você vai ter de copiar estes parâmetros na mão como em:

Diferenças na herança dos comentários

Aqui ocorre algo semelhante ao que ocorre nos parâmetros de storage. Os comentários de coluna são herdados, mas os comentários de tabela não. Você não vê os comentários de tabela no comando do psql \d+, mas vê no \dt+ :

Para dizer a verdade eu acho que importar comentários em partições algo que só serve para entulhar a sua base, mas se você realmente quer fazer isso, você pode rodar:

Diferenças na herança de permissões

Aqui o postgres ignora totalmente as permissões e não herda nada. Vamos ver como ficaram as permissões no psql com o comando \dp:

Se bem que isto não chega a ser um problema, pelo menos não a partir da versão 9.0. Do PostgreSQL 9.0 em diante o comportamento padrão SQL passa a ser seguido: se você tem permissão na tabela PEDIDO, ao fazer uma operação nesta, as permissões não serão checadas novamente nas tabelas filhas, veja:

Ou seja, você não precisa dar permissão nas tabelas filhas, pois elas herdam as permissões da tabela mãe, mas somente se você fizer a operação na tabela mãe. Se tentar direto na tabela filha, a operação falha. Por outro lado, o usuário APP não é mais dono das partições. Se você precisar fazer algumas operações administrativas com as partições, precisará do superusuário. Então eu recomendo que você apenas troque o dono das tabelas filhas, como em:

Claro, se você realmente quiser conceder todas as permissões da tabela PEDIDO diretamente nas tabelas filhas, você pode rodar algo como:

 Agora sim, vamos criar todas as partições

Ainda falta criar uma restrição que coloque os registros dos anos correspondentes em cada tabela filha:

Pronto, agora nossas tabelas filhas são partições da tabela PEDIDO. Se você realizar um SELECT na tabela PEDIDO, os registros das tabelas filhas serão lidos direitinho. Como demonstramos no post anterior, se você precisar dos registros que estão apenas em algumas partições, o PostgreSQL irá buscar as informações apenas nas partições correspondentes. Isso aumenta muito o desempenho da consulta. Claro, o parâmetro CONSTRAINT_EXCLUSION, precisa estar habilitado para isso ocorrer. Caso contrário a consulta irá sempre varrer todas as partições. No entanto, apenas operações de SELECT, UPDATE e DELETE estão prontas para serem utilizadas. Para inserir na tabela PEDIDO e gravar nas partições corretas, precisamos de um recurso adicional de desvio. Este pode ser implementado com um gatilho ou com uma regra.

Gatilho de INSERT

Aqui a fórmula é simples, criamos uma função para o gatilho onde desviamos o INSERT para a partição correta:

 Regra de INSERT

Outra forma de fazer a mesma coisa é utilizando as regras do postgres. É um recurso pouco conhecido, mas pode ser utilizado para obter o mesmo efeito:

 Regra X gatilho

Qual a melhor forma de implementar, gatilho ou regra? A resposta para variar é depende. Vamos agora usar a função \timing do psql para ver quem é mais rápido. Primeiro vamos testar com  um único INSERT carregando vários registros de uma vez só:

Aqui o que vemos é a REGRA ganhando de lavada de 3,8 segundos contra 10,9 segundos. Agora vamos ver o custo de inserir um único registro:

Aqui a relação se inverte e o gatilho é ligeiramente mais rápido. Embora seja mais comum o uso do gatilho em particionamento, se você tem uma tabela que recebe mais cargas em lote (mas não com um único INSERT para cada registro), então o uso de regra lhe será vantajoso.

Outro detalhe é que quando você utiliza regras você precisa de uma regra para cada partição e o plano de execução sempre varre todas as regras. Desta forma, se você utilizar muitas consultas, o uso de regras começa a se tornar muito caro. Veja o plano de execução de um único registro para ver como isso acontece:

Embora a maior parte do tempo seja gasto com o INSERT na tabela PEDIDO_2008, o postgres perde tempo passando pelas demais tabelas também.

No próximo post vamos falar sobre mais detalhes da implementação do particionamento no PostgreSQL e vamos particionar também a tabela PEDIDO_DETALHE. Na sequência também vou demonstrar como automatizar a criação de partições para várias tabelas de uma vez só. Até a próxima.

4 comentários sobre “Particionamento de tabelas no postgres – Como?

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