Partição de tabelas no PostgreSQL

david23.7

O artigo explica como particionar tabelas no PostgreSQL, o que pode ser útil para otimização do desempenho.

[ Hits: 14.537 ]

Por: David Augusto em 30/07/2012 | Blog: http://pedreirosdosoftware.wordpress.com

0 0

Denuncie Favoritos Indicar Impressora

Teoria e criação da estrutura

A princípio, este artigo publiquei em meu blog pessoal Pedreiros Do Software, mas para deixar o artigo com maior visibilidade, resolvi postar aqui e abandonar um pouco o blog, já que o intuito é compartilhar conhecimento.

Uma das melhores estratégias para diminuir o tempo de acesso à grandes volumes de dados é o particionamento de tabelas no PostgreSQL, o funcionamento é muito simples e bastante funcional.

Pensem que, como em um HD, você pode fazer várias partições, ou trabalhar com vários discos, organizando para que arquivos de A à D estejam na partição 1, arquivos de E à H estejam na partição 2, e assim por diante.

Desta forma, você sabe onde estão seus arquivos, e provavelmente, irá diretamente onde está o arquivo que procura. Com o Banco de Dados não é diferente, particionando as tabelas de acordo com o valor de um índice, ele irá para o local correto, trazendo dados mais rapidamente.

* Aviso: Questões referentes à modelagem além do particionamento, não fazem parte do artigo, nem se CPF deve ou não deve chave primária, nem mesmo o tipo de dado escolhido, é somente para tornar palpável o exemplo, nem mesmo qual a convenção utilizada para nomear tabela se case sensitive ou não e blá blá blá.

Considerem o seguinte modelo:

Linux:
Partição de tabelas no PostgreSQL

Temos nossa tabela "cliente" que é a pai (servindo apenas como um padrão) para as demais tabelas, ou seja, aquele famoso conceito de herança.

As tabelas "cliente_part_01', "cliente_part_02", "cliente_part_03"... Receberão os dados direcionados de acordo com o índice, que no caso é CPF.

Eis as SQLs:

CREATE TABLE cliente (
cpf numeric(11,0) NOT NULL,
nome character varying(20),
CONSTRAINT cliente_pkey PRIMARY KEY (cpf)
);

CREATE TABLE cliente_part_01() INHERITS(cliente);

CREATE TABLE cliente_part_02() INHERITS(cliente);

CREATE TABLE cliente_part_03() INHERITS(cliente);

Particionamento

Tabelas criadas, partimos para a mágica do particionamento, o primeiro ponto é criar constraints para evitar que um registro seja adicionado fora de sua devida partição.

No caso, vamos considerar que:

cliente_part_01 receberá CPFs do número 000.000.000-00 até o número 300.000.000-00
cliente_part_02 receberá CPFs do número 300.000.000-01 até o número 600.000.000-00
cliente_part_03 receberá CPFs do número 600.000.000-01 até o número 999.999.999-99

Eis a SQL para criação disso:

ALTER TABLE cliente_part_01 ADD CHECK(cpf BETWEEN 0 AND 30000000000);
ALTER TABLE cliente_part_02 ADD CHECK(cpf BETWEEN 30000000001 AND 60000000000);
ALTER TABLE cliente_part_03 ADD CHECK(cpf BETWEEN 60000000001 AND 99999999999);

O segundo passo é criar índices nas chaves primárias, para que o banco saiba onde deve procurar com mais rapidez.

* Nota: CPF foi criado como 'numeric(11,0)", índices podem não funcionar tão bem com esse tipo como com números inteiros, mas estou sendo apenas conceitual.

CREATE INDEX indice_cliente_part_01 ON cliente_part_01(cpf);
CREATE INDEX indice_cliente_part_02 ON cliente_part_02(cpf);
CREATE INDEX indice_cliente_part_03 ON cliente_part_03(cpf);

O próximo passo, é criar uma função que direcione a inserção para sua devida tabela:

CREATE OR REPLACE FUNCTION insere_cliente()
RETURNS TRIGGER AS $$
BEGIN
IF(NEW.cpf BETWEEN 0 AND 30000000000) THEN
INSERT INTO cliente_part_01 VALUES(NEW.cpf, NEW.nome);
ELSIF(NEW.cpf BETWEEN 30000000001 AND 60000000000) THEN
INSERT INTO cliente_part_02 VALUES(NEW.cpf, NEW.nome);
ELSIF(NEW.cpf BETWEEN 60000000001 AND 99999999999) THEN
INSERT INTO cliente_part_03 VALUES(NEW.cpf, NEW.nome);
END IF;
RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

E por fim, a sua trigger:

CREATE TRIGGER cliente_particionamento
BEFORE INSERT ON cliente FOR EACH
ROW EXECUTE PROCEDURE insere_cliente();

Testando

Para testar?

Insira alguns registros, tipo:

INSERT INTO cliente(cpf, nome) VALUES (11111111111, 'Alberto Roberto');
INSERT INTO cliente(cpf, nome) VALUES (55555555555, 'Jonas o Motoqueiro');
INSERT INTO cliente(cpf, nome) VALUES (88888888888, 'Ruth Le-le-lemos');

Caso esteja utilizando o pgAdmin, basta olhar nas tabelas de partição, os dados estarão em seus devidos lugares e para a seleção, basta um:

SELECT * FROM clientes WHERE ...

Prático e funcional!

E existem várias estratégias para utilização de particionamento, até a utilização de tablespaces.

* Nota: Até a versão 8.4, para otimização, é recomendado alterar o parâmetro "constraint_exclusion" para "on", a partir do PostgreSQL 9.1, o valor "on" é default.

Fonte: ddl-partitioning « www.postgresql.org

Páginas do artigo

1. Teoria e criação da estrutura

Outros artigos deste autor

Nenhum artigo encontrado.

Leitura recomendada

Como migrar banco de dados MySQL para PostgreSQL

Monitorando Banco de Dados Postgres com ZABBIX

Checklist de performance do PostgreSQL 8.0

Automação comercial livre no Slackware 12

Programando PostgreSQL + PHP

Comentários

[1] Comentário enviado por rony_souza em 30/07/2012 - 15:32h

Olá David, muito interessante a publicação desse tutorial, pois estou estudando na construção de banco de dados.

0 0

[2] Comentário enviado por rai3mb em 31/07/2012 - 02:21h

Pra mim seu artigo vai ajudar muito, tenho algumas bases que tendem ao infinito, e com esse tipo de fragmentação dar pra otimizar e muito as consultas. Favoritado!

0 0

[3] Comentário enviado por leonanlindu em 14/03/2013 - 08:55h

Obrigado pela ajuda, Deus os abençoe e de tudo na vida para vocês

0 0

[4] Comentário enviado por antoniomarcos_23 em 02/10/2015 - 17:55h

Prezado Obrigado, sou muito iniciante em postgre e com seu post consegui particionar. Mas tenho um problema, não consegui inserir os dados da tabela mãe para as filhas... o que estou errando?
Grato.
Antonio

0 0