PostgreSQL > Avançado > Particionamento de tabelas gigantes

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.