Particionamento de Tabelas no PostgreSQL

Quando temos uma tabela muito grande, ou seja com milhões de linhas a melhor opção é particiona-la para uma melhor performance do banco. Para isso, é necessário algumas regras na tabela principal e criar as tabelas auxiliares:

Veja abaixo.

1 – Crie a tabela principal

CREATE TABLE salario(
funcionario numeric(10) NOT NULL,

centro_custo character varying(10) NOT NULL,
valor_resultado numeric(13,2),
data_evento date,
evento numeric(3),
conta numeric(10),cargo numeric(5)
)
WITH (OIDS=TRUE);

2 – Crie as tabelas particionadas para inserção dos dados, sendo que as mesmas devem herdar as caracteristicas da tabela principal, para isso usamos o () INHERITS

–JANEIRO

CREATE TABLE salario_jan () INHERITS (salario);

–FEVEREIRO

CREATE TABLE salario_fev () INHERITS (salario);

……………………..

–DEZEMBRO

CREATE TABLE salario_dez () INHERITS (salario);

3 – Crie a regra que no caso de uma tabela de pagamentos de funcionarios, Nesse caso, o campo referencia é a data de pagamento tendo base o mês. Sendo assim crie uma rule para cada mês, onde a cada insert feito na tabela mãe … os dados são filtrados e inseridos em cada tabela filha correspondende ao mês.

— JANEIRO

CREATE OR REPLACE RULE insert_jan AS
ON INSERT TO salario

WHERE date_part (‘month’::text, new.data_evento)=1

DO INSTEAD INSERT INTO salario_jan (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);

— FEVEREIRO

CREATE OR REPLACE RULE insert_fev AS
ON INSERT TO salario
WHERE date_part(‘month’::text, new.data_evento)=2
DO INSTEAD INSERT INTO salario_fev (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);

……………………………

— DEZEMBRO

CREATE OR REPLACE RULE insert_dez AS
ON INSERT TO salario
WHERE date_part(‘month’::text, new.data_evento)=12
DO INSTEAD INSERT INTO salario_dez (funcionario, centro_custo, valor_resultado, data_evento, evento, conta, cargo)
VALUES (new.funcionario, new.centro_custo, new.valor_resultado, new.data_evento, new.evento, new.conta, new.cargo);

Explicando …

A função data_part, vai extrair uma determinada parte da data a ser imposta pela regra.

No caso desse particionamento a referencia é o mês. Sendo assim no momento do insert, será verificado o mês em questão e redirecionado para tabela particionada correspondente.

É importante dizer que se um registro não atender a uma determinada regra, ele será inserido na tabela principal.

E que a tabela principal se comportará como uma tabela MERGE, sendo assim ela mostrará todos os registros !!

4 – Criando os Indices ..

Para a busca ser mais rápida é interessante a criação de indice nas tabelas particionadas.

— JANEIRO

CREATE INDEX salario_jan_idx
ON salario_jan
USING btree
(data_evento);

— FEVEREIRO

CREATE INDEX salario_fev_idx
ON salario_fev
USING btree
(data_evento);

………………………..

— DEZEMBRO

CREATE INDEX salario_dez_idx
ON salario_dez
USING btree
(data_evento);


4 – Criando as views …..

As views vão auxiliar na vizualização dos dados particionados por mês, sendo assim, algumas views são interessantes ….

View para cada mês CORRENTE.

CREATE OR REPLACE VIEW vw_salario_jan AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_jan a
WHERE date_part(‘month’::text, data_evento) = 1
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;

CREATE OR REPLACE VIEW vw_salario_fev AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_fev a
WHERE date_part(‘month’::text, data_evento) = 2
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;

—————–

CREATE OR REPLACE VIEW vw_salario_dez AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario_dez a
WHERE date_part(‘month’::text, data_evento) = 12
AND date_part(‘year’::text, data_evento) = now()
ORDER BY a.data_evento;

View de anos anteriores.

Para vizualização dos anos anteriores as seguintes views

CREATE OR REPLACE VIEW vw_salario_2006 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2006
ORDER BY a.data_evento;


CREATE OR REPLACE VIEW vw_salario_2007 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2007
ORDER BY a.data_evento;

CREATE OR REPLACE VIEW vw_salario_2008 AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta, a.cargo
FROM salario a
WHERE date_part(‘year’::text, a.data_evento) = 2008
ORDER BY a.data_evento;

View dos ultimos 6 meses apartir do 1o dia util

CREATE OR REPLACE VIEW vw_6meses_anteriores AS
SELECT a.funcionario, a.centro_custo, a.valor_resultado, a.data_evento, a.evento, a.conta,a.cargo
FROM salario a
WHERE a.data_evento >= (current_date – (date_part( ‘day’ , current_date)::integer -1)) – interval ‘6 months’
ORDER BY a.data_evento;

Bom … apartir dai .. é possivel a criação de varias views para ver os dados …
Espero que seja util para vcs como foi pra mim !!!!

Kenia Milene

16 thoughts on “Particionamento de Tabelas no PostgreSQL

  1. Obrigado.
    Estava correndo atrás de exemplos como esse a muito tempo. Se tiver mais exemplos é só publicar que, pelo menos eu, agradeço imensamente.

  2. primeiro gostei muito do seu site sobre o assunto. Acho q vou precisar dos seus trabalhos profissionais, tipo uma consultoria, caso eu feche um contrato q estou batalhando a um tempo…. se vc pudesse me passar seus contatos. meu e-mail acima tb é msn.(marcio@itarget.com.br)
    agora sobre minha dúvida desta tabela. uso muito trigrer e tem situações q preciso consultar informacoes antigas antes de permitir uma inserção, pensando em dividir a tabela mae em outras como vai funcionar estas consultas, vai procurar automaticamente nas novas tabelas, sem precisar alterar o código. aguardo retorno….

  3. Gostei do seu exemplo, mas não entendi o motivo de criar as views. Seria a mesma coisa de ficar criando tabelas a cada mês.
    Você poderia colocar no seu exemplo, o que a meu ver é o principal motivo de usar uma particionamento, colocar o banco para escolher as tabelas automaticamente, quando for fazer uma consulta na tabela mãe.

  4. Implementei minha base de acordo com seu exemplo. Em algumas situações tive até 100% de melhora no desempenho, em outras 0%. Muito bom.
    A idéia citada acima por Diogo é uma boa. Também não entendi as views.

  5. Uma dúvida: Eu fiz os testes aqui, criei uma estrutura um pouco diferente da tabela, quando rodo os inserts para testes, eu notei que realmente está salvando os dados distribuídos, mas, também são salvos na tabela master. Fica minha pergunta: Qual a vantagem disto no final das contas? Se as consultas são realizadas na tabela master, pra que servem as tabelas filhas distribuidas? Não ficou claro isso pra mim, se alguem souber me explicar, agradeço!

    • Voce deve ter feito alguma coisa errada na montagem do teu cenário, o que deveria acontecer é a tabela pai ficar vazia, e as tabelas filhas conterem as informacoes. No caso que vc comentou rafael, ao inserir as informações ficam duplicadas.

  6. Conforme prometido. Este é um exemplo em que crio uma tabela “mãe” e faça as tabelas “filhas” (particionamento) só quando necessário:

    –============ LAB 2: particionar tabela por ano; de minha parte, criar a tabela em tempo de execução

    — Tabela mãe – a única criada antecipadamente
    create table tb_aluno (
    codigo serial primary key,
    materia varchar(50),
    ano integer);

    — Função para criar dinamicamente a tabela (se ainda não existir) e inserir o registro … UFA! Essa deu trabalho!
    create or replace function fn_insere_aluno_ano(char, integer)
    returns void as $$
    declare
    comandosql text;
    nometabela text;
    tabela record;
    begin
    nometabela := ‘tb_aluno_’ || cast($2 as text);
    — Verifica se a tabela existe
    comandosql := ‘select tablename from pg_tables where tablename = ‘ || quote_literal(nometabela) || ‘ and schemaname = any (current_schemas(true));’;
    execute comandosql into tabela;
    — Se não existe, cria
    if tabela.tablename is null then
    comandosql := ‘create table ‘ || nometabela || ‘ () inherits (tb_aluno);’;
    execute comandosql;
    end if;
    — Insere o registro
    comandosql := ‘insert into ‘ || nometabela || ‘ (materia, ano) values (‘ || quote_literal($1) || ‘, ‘ || cast($2 as text) || ‘);’;
    execute comandosql;
    end;
    $$ language ‘plpgsql’;

    — Regra de inserção
    create or replace rule insercao_geral as
    on insert to tb_aluno
    do instead select fn_insere_aluno_ano(new.materia, new.ano);

    — Alguns registros
    insert into tb_aluno (materia, ano) values (‘Ano 2001, tabela ainda não existe’, 2001);
    insert into tb_aluno (materia, ano) values (‘Ano 2001, tabela já existe 1’, 2001);
    insert into tb_aluno (materia, ano) values (‘Ano 2001, tabela já existe 2’, 2001);
    insert into tb_aluno (materia, ano) values (‘Ano 2001, tabela já existe 3’, 2001);
    insert into tb_aluno (materia, ano) values (‘Ano 2002, tabela ainda não existe’, 2002);
    insert into tb_aluno (materia, ano) values (‘Ano 2002, tabela já existe 1’, 2002);
    insert into tb_aluno (materia, ano) values (‘Ano 2002, tabela já existe 2’, 2002);
    insert into tb_aluno (materia, ano) values (‘Ano 2003, tabela ainda não existe’, 2003);
    insert into tb_aluno (materia, ano) values (‘Ano 2003, tabela já existe 1’, 2003);
    insert into tb_aluno (materia, ano) values (‘Ano 2003, tabela já existe 2’, 2003);

    — Verificação
    select * from tb_aluno;
    select * from only tb_aluno;
    select * from tb_aluno_2001;
    select * from tb_aluno_2002;
    select * from tb_aluno_2003;

    DIVIRTAM-SE!

  7. PRECISO DE UM SOCORRO. VC PODE ME AJUDAR
    dalminha11@yahoo.com.br, se puder me envia um email?
    Para fazer a organização dos grupos e as escolhas dos temas, cada aluno deve participar, neste recurso, optando por participar em um dos temas descritos a seguir. Para cada tema, o grupo de alunos tem liberdade para definir os dados que considerar suficientes para representar as entidades que identificar, desde que respeite o enunciado do tema.
    Grupo 3 – Drogaria:

    A gerência de uma drogaria (fictícia) deseja que o processo de trabalho dos seus vendedores seja registrado em um banco de dados. As ações cujos dados devem ser armazenadas resumem-se ao atendimento de balcão. Isto inclui a identificação do vendedor, o registro do horário de atendimento e os itens (medicamentos ou outros) que foram vendidos em um atendimento. A gerência da drogaria enfatiza a necessidade de construção de consultas que permitam relatar:

    • A média diária do volume de vendas de cada vendedor, dentro de um período determinado, para comparar a eficiência dos vendedores.
    • A média diária do volume de vendas em cada turno, dentro de um período determinado, para comparar a efetividade da drogaria ao longo do dia. Serão considerados três turnos diários, sendo o primeiro de 6h às 11h30, o segundo de 11h30 às 17h, e o terceiro de 17h às 22h30.
    • A média diária do volume de vendas em cada mês do ano, para analisar o impacto que eventos associados a épocas do ano têm nas vendas da drogaria.

Deixe uma Resposta

Preencha os seus detalhes abaixo ou clique num ícone para iniciar sessão:

Logótipo da WordPress.com

Está a comentar usando a sua conta WordPress.com Terminar Sessão / Alterar )

Imagem do Twitter

Está a comentar usando a sua conta Twitter Terminar Sessão / Alterar )

Facebook photo

Está a comentar usando a sua conta Facebook Terminar Sessão / Alterar )

Google+ photo

Está a comentar usando a sua conta Google+ Terminar Sessão / Alterar )

Connecting to %s