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:
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