Partição de Tabelas no PostgreSQL

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 você 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 dever 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 bla bla bla.



Considerem o seguinte modelo:

Temos nossa tabela cliente que é a pai(servindo apenas como um “padrão”) para as demais tabelas, cliente_part_01, cliente_part_02, cliente_part_03 , estas receberam 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);

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 recebera cpfs do número 000.000.000-00 até o número 300.000.000-00
  • cliente_part_02 recebera cpfs do número 300.000.000-01 até o número 600.000.000-00
  • cliente_part_03 recebera 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, índices valem um outro tópico.
Nota: CPF foi criado como numeric(11,0), índices podem não funcionar tão bem 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();

Aí está!
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(que renderão um outro artigo), enfim é isso!

Nota: Até a versão 8.4, para otimização é recomendado alterar o parametro constraint_exclusion para on, a partir do PostgreSQL 9.1  o valor “on” é default.

Fonte: http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>