Particionamento de tabelas gigantes

<< Click to Display Table of Contents >>

Navigation:  PostgreSQL > Avançado >

Particionamento de tabelas gigantes

Previous pageReturn to chapter overviewNext page

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 tb_saldo( 

                      cd_produto int not null,

                      vl_saldo   numeric(13,2),

                      dt_data    date

                     )

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

Uma para cada mês

CREATE TABLE tb_saldo_01 () INHERITS (tb_saldo);

CREATE TABLE tb_saldo_02 () INHERITS (tb_saldo);

 

... e assim até dezembro...

CREATE TABLE tb_saldo_12 () INHERITS (tb_saldo);

 

3 – Crie as regras

 

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.

CREATE OR REPLACE RULE insert_01 AS 

  ON INSERT TO tb_saldo WHERE date_part ('month'::text, new.dt_data)=1 DO 

  INSTEAD INSERT INTO tb_saldo_01(cd_produto, vl_saldo, dt_data) VALUES (new.cd_produto, new.vl_saldo, new.dt_data);

 

CREATE OR REPLACE RULE insert_02 AS 

  ON INSERT TO tb_saldo WHERE date_part ('month'::text, new.dt_data)=2 DO 

  INSTEAD INSERT INTO tb_saldo_02(cd_produto, vl_saldo, dt_data) VALUES (new.cd_produto, new.vl_saldo, new.dt_data);
... e assim até dezembro...

CREATE OR REPLACE RULE insert_12 AS 

  ON INSERT TO tb_saldo WHERE date_part ('month'::text, new.dt_data)=12 DO 

  INSTEAD INSERT INTO tb_saldo_12(cd_produto, vl_saldo, dt_data) VALUES (new.cd_produto, new.vl_saldo, new.dt_data);

 

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.

CREATE INDEX tb_saldo_01_idx ON tb_saldo_01 USING btree (dt_data);

CREATE INDEX tb_saldo_02_idx ON tb_saldo_02 USING btree (dt_data);

 

... e assim até dezembro...

 

CREATE INDEX tb_saldo_12_idx ON tb_saldo_12 USING btree (dt_data);

 

5-Testando, insira valores de 3 ou mais meses

 

insert into tb_saldo values (1, 10.2, '01/01/2008');

insert into tb_saldo values (2, 11.2, '01/01/2008');

insert into tb_saldo values (3, 10.3, '01/01/2008');

 

insert into tb_saldo values (1, 30.2, '02/01/2008');

insert into tb_saldo values (2, 21.2, '02/02/2008');

insert into tb_saldo values (3, 13.3, '02/02/2008');

 

insert into tb_saldo values (1, 30.2, '03/01/2008');

insert into tb_saldo values (2, 31.2, '03/02/2008');

insert into tb_saldo values (3, 33.3, '03/02/2008');

 

6-Selects

 

Este trás tudo:

select * from tb_saldo;

 

 cd_produto | vl_saldo |  dt_data

------------+----------+------------

          1 |    10.20 | 2008-01-01

          2 |    11.20 | 2008-01-01

          3 |    10.30 | 2008-01-01

          1 |    30.20 | 2008-02-01

          2 |    21.20 | 2008-02-02

          3 |    13.30 | 2008-02-02

          1 |    30.20 | 2008-03-01

          2 |    31.20 | 2008-03-02

          3 |    33.30 | 2008-03-02

(9 rows)

 

Estes trazem apenas do seus meses respectivos:

select * from tb_saldo_01;

 

 cd_produto | vl_saldo |  dt_data

------------+----------+------------

          1 |    10.20 | 2008-01-01

          2 |    11.20 | 2008-01-01

          3 |    10.30 | 2008-01-01

(3 rows)

 

 

select * from tb_saldo_02;

 

 cd_produto | vl_saldo |  dt_data

------------+----------+------------

          1 |    30.20 | 2008-02-01

          2 |    21.20 | 2008-02-02

          3 |    13.30 | 2008-02-02

(3 rows)

 

7-Conclusão

 

Você ainda pode criar triggers para trazer os resultados. As possibilidades são infinitas, é só usar a cachola.