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.534 ]

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

psql - Conheça o básico

PostgreSQL - Instalação inicial no Debian Wheezy 64 bits

Instalando PostgreSQL 8.1 com extensão para dados espaciais (PostGis) e interface de gerenciamento (PgAdmin3)

Pool de Conexões Transparentes no Postgres usando o pgpool

PostgreSQL 9.4 - Replicação com slots

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