PostgreSQL Partition: Trabalhando com Partições no PostgreSQL
Neste artigo veremos como lidar com o particionamento das tabelas no PostgreSQL, o que se torna uma boa prática no momento em que precisamos realizar pesquisas rápidas.
Neste artigo trabalharemos um ponto importante com relação ao SGBD PostgreSQL que é o particionamento, onde abordaremos sua conceituação, qual a necessidade de realizar esta tarefa e apresentaremos exemplos de sua criação, de forma a sanar dúvidas referentes a criação e utilização de particionamento de tabelas.
Começando com as Partições
A primeira questão a ser levantada é: O que seria um particionamento? Particionamento, é na verdade o processo de dividir as tabelas em partes gerenciáveis menores, onde estas partes menores são chamadas de partições. O que ocorre no processo de particionamento é a divisão da lógica de uma grande tabela em múltiplas unidades físicas menores.
Dentre os motivos que nos levam a realizar o particionamento das tabelas, temos o que podemos considerar o mais importante que é para o aumento do desempenho da base de dados, o que é conseguido com o auxílio dos Joins de partição, onde se precisarmos realizar uma série de consultas numa tabela completa, a utilização de partições nos ajudaria limitando o âmbito dessa pesquisa. A segunda razão mais importante é que com o particionamento das tabelas, torna-se muito mais fácil o seu gerenciamento.
Outras vantagens que podemos ver são quando consultas ou atualizações acessam uma grande porcentagem de uma única partição, onde o desempenho pode ser melhorado, tendo como vantagem uma varredura sequencial da partição, ao invés de utilizar índices e acessos aleatórios espalhados por toda a tabela. Operações de exclusão podem ser realizadas pela adição ou mesmo remoção de partições, onde as operações de ALTER TABLE NO INHERIT e DROP TABLE são ambas muito mais rápido do que uma operação em massa. Além de que, com estes comandos temos a possibilidade de evitar a sobrecarga causada pelo VACUUM causado por uma exclusão em massa de registros, dentre outras razões.
Hoje, na versão 9.4 do PostgreSQL, temos suporte ao particionamento através de herança entre tabelas, onde cada partição deverá ser criada como uma tabela filha de uma única tabela pai, a qual é vazia, existindo apenas para representar o conjunto de dados gerados. Para a implementação dos particionamentos no PostgreSQL, temos realiza-los de duas possíveis formas, que são a Range partitioning e a List partitioning, as quais veremos a seguir uma breve descrição.
Range partitioning ("intervalos de particionamento")
Neste processo, temos que a tabela “mestra” é dividida em "intervalos", definidos por uma coluna chave ou por um conjunto de colunas, sem que haja uma sobreposição entre os intervalos dos valores atribuídos a diferentes partições. Quando estamos trabalhando com intervalos de partição, podemos utilizar intervalos de datas ou registros numéricos, por exemplo.
List partitioning (Lista de particionamentos)
Nesta forma, temos que a tabela é dividida em listas que de forma explicita, apresenta os valores chave de cada partição. Com relação a lista de particionamentos, podemos criar, por exemplo, listas de departamentos, como RH e financeiro.
Herança
Antes de darmos seguimento com as partições, precisamos entender primeiro o processo de implementação de herança que ocorre entre as tabelas do PostgreSQL, o que pode ser de grande utilidade ao criarmos nossas bases de dados. A herança é um conceito de bancos de dados orientados a objeto, que abre possibilidades interessantes para os projetos de bancos de dados, onde no PostgreSQL temos que uma tabela pode herdar de nenhuma ou de várias tabelas.
Para exemplificarmos a sua utilização, vejamos a criação de um modelo de dados para cidades e estados, onde cada estado possui várias cidades, mas apenas uma capital para cada cidade. Para recuperarmos mais rápido a capital, podemos criar duas tabelas para acelerar o processo, onde uma é para a capital e a outra para as cidades que não são capitais. Vejamos de acordo com as Listagens 1 e 2 como ficariam nossas tabelas.
CREATE TABLE empresa(
nome_empresa text,
endereco text
);
Listagem 1. Criação da tabela empresa
CREATE TABLE departamento(
nome_dep text,
qtde_funcionarios int
) INHERITS (empresa);
Listagem 2. Criação da tabela de departamentos
Agora que temos as nossas tabelas criadas, adicionaremos alguns dados de teste para vermos o funcionamento, como mostra a Listagem 3.
INSERT INTO empresa(nome_empresa, endereco)
VALUES('Empresa testes', 'Rua dos testes');
INSERT INTO departamento(nome_empresa, endereco , nome_dep, qtde_funcionarios)
VALUES('Empresarial dos testes', 'Rua testador', 'Financeiro', 60);
Listagem 3. Inserção de registros nas tabelas
Neste exemplo, temos que a tabela departamento herda todas as colunas da tabela pai, empresa, a qual possui duas colunas que são quantidade de funcionários e o nome do departamento.
Agora que entendemos um pouco sobre a herança, daremos continuidade a criação das partições, onde precisamos estar cientes de que existem ao todo, cinco passos necessários para a criação das partições no PostgreSQL, que são a criação da tabela principal (ou pai), criação das tabelas filhas, criação dos índices das tabelas, criação de triggers para a inserção dos dados nas tabelas filhas e por último, habilitar a restrição de exclusão.
Intervalos de partição (Range Partitioning)
Quando utilizamos intervalos de partição, temos que este é o tipo de partição onde particionamos a tabela em intervalos menores, definidos por uma coluna única ou várias colunas. Ao definirmos os intervalos, precisamos tomar cuidado para que as tabelas estejam conectadas, não sobrepondo umas às outras. Além disso, os intervalos devem ser definidos usando o operador (<). Para entendermos melhor com relação a partição de intervalos, criaremos agora um exemplo de sua utilização, onde teremos os registros das vendas referentes ao ano de 2014.
Criando a tabela principal
Para começarmos o exemplo, criaremos inicialmente a nossa tabela principal, a qual chamaremos de registros_financeiros, teremos os campos apresentados de acordo com a Listagem 4. Nesta tabela, teremos todos os dados sobre as vendas armazenadas em uma base diária.
CREATE TABLE registros_financeiros
(
cod_registro NUMERIC PRIMARY KEY,
qtde_vendas NUMERIC,
data_venda DATE NOT NULL DEFAULT CURRENT_DATE
);
Listagem 4. Criando a tabela principal
Como apresentado pela Listagem 1, temos a nossa tabela principal, ou pai criada, onde todos os registros que forem inseridos serão movidos para as tabelas filhas com base nos critérios de data das vendas, o que será criado agora.
Criando uma tabela com intervalos de partição
Criada a tabela principal, implementaremos uma partição para a criação das tabelas filhas que herdarão a tabela pai. Adicionaremos uma restrição do tipo CHECK para as datas, o que irá possibilitar que tenhamos os dados corretos para cada partição. Cada partição possuirá dados referentes a um trimestre, onde teremos as tabelas criadas de acordo com as Listagens 5, 6 e 7.
CREATE TABLE registros_financeiros_jan_mar
(
PRIMARY KEY (cod_registro, data_venda),
CHECK (data_venda >= DATE '2015-01-01' AND data_venda < DATE '2015-04-01')
)
INHERITS (registros_financeiros);
Listagem 5. Criando a tabela filho de janeiro a março
CREATE TABLE registros_financeiros_abril_junho
(
PRIMARY KEY (cod_registro, data_venda),
CHECK (data_venda >= DATE '2015-04-01' AND data_venda < DATE '2015-07-01')
)
INHERITS (registros_financeiros);
Listagem 6. Criando a tabela filha dos meses abril a junho
CREATE TABLE registros_financeiros_jul_set
(
PRIMARY KEY (cod_registro, data_venda),
CHECK (data_venda >= DATE '2015-07-01'
AND data_venda < DATE '2015-10-01')
)
INHERITS (registros_financeiros);
Listagem 7. Criando a tabela dos registros de julho a setembro
Criando índices para as tabelas
Agora que temos nossas tabelas criadas, iremos adicionar índices para cada uma das tabelas filhas para que dessa forma possamos agilizar ainda mais as consultas. Os índices serão criados no campo data_venda e servirá para qualquer operação DML (INSERT, SELECT ou UPDATE) referente ao campo de data. Os índices serão criados de acordo com as instruções a seguir:
CREATE INDEX data_venda_jan_mar_idx ON registros_financeiros_jan_mar (data_venda);
CREATE INDEX data_venda_abril_junho_idx ON registros_financeiros_abril_junho (data_venda);
CREATE INDEX data_venda_jul_set_idx ON registros_financeiros_jul_set (data_venda);
Utilização de triggers
Após a realização das tabelas e dos índices, o próximo passo é a criação da trigger que será disparada pela tabela principal, onde as condições devem ser as mesmas aplicadas nas tabelas filhas. Vejamos então de acordo com a Listagem 8 como ficará a nossa trigger.
CREATE OR REPLACE FUNCTION insercao_registros()
RETURNS TRIGGER AS $
BEGIN
IF (NEW.data_venda >= DATE '2015-01-01' AND
NEW.data_venda < DATE '2015-04-01') THEN
INSERT INTO registros_financeiros_jan_mar VALUES (NEW.*);
ELSIF (NEW.data_venda >= DATE '2015-04-01' AND
NEW.data_venda < DATE '2015-06-01') THEN
INSERT INTO registros_financeiros_abril_junho VALUES (NEW.*);
ELSIF (NEW.data_venda >= DATE '2015-06-01' AND
NEW.data_venda < DATE '2015-09-01') THEN
INSERT INTO registros_financeiros_jul_set VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'A data não se encontra nos limites estabelecidos para a inserção...';
END IF;
RETURN NULL;
END;
$ LANGUAGE plpgsql;
Listagem 8. Desenvolvendo a trigger para a tabela principal
O único propósito desta função que acabamos de criar é para que os dados sejam preenchidos nas respectivas tabelas com base na data dos registros financeiros da empresa. Para que a trigger criada na Listagem 8 seja disparada, precisaremos de uma função que irá executar a trigger no momento em que as instruções de INSERT, UPDATE e DELETE forem disparadas. Esta trigger function, que chamaremos de registro_mes_trigger, será definida de acordo com a apresentada pela Listagem 9.
CREATE TRIGGER registro_mes_trigger
BEFORE INSERT ON registros_financeiros
FOR EACH ROW
EXECUTE PROCEDURE insercao_registros();
Listagem 9. Criando a trigger function
Trabalhando com as constraints exclusions
Como última etapa a ser realizada, temos a implementação das restrições de exclusão, ou constraints exclusion, onde esta é uma técnica voltada para a otimização das consultas que buscam melhoria de desempenho para as tabelas particionadas que foram definidas no decorrer do artigo. Para que possamos definir a restrição como ativa, o processo é realizado da seguinte forma:
SET constraint_exclusion = on;
SELECT count(*) FROM registros_financeiros WHERE data_venda >= DATE '2015-03-01';
Caso a constraint_exclusion não tenha sido definida, a consulta será realizada em cada uma das partições da tabela de registros_financeiros. No entanto, quando a restrição é habilitada, as constraints de cada partição serão examinadas, onde será verificada a necessidade de pesquisa apenas nas tabelas que estejam de acordo com a cláusula WHERE da consulta.
Partições e operações DML
No momento em que executamos uma instrução de INSERT na tabela principal, temos que a trigger insercao_registros(), criada anteriormente será acionada e em sequência, chamará a trigger function registro_mes_trigger, onde com base no campo data_venda teremos os dados salvos na tabela filha específica. Para vermos o funcionamento da nossa estrutura, iremos inserir alguns registros na tabela registros_financeiros, para que em seguida, utilizemos o comando SELECT para ver o resultado final da operação. Vejamos então de acordo com a Listagem 10.
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (1, 300, TO_DATE('05/03/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (2, 700, TO_DATE('07/15/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (3, 450, TO_DATE('02/08/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (5, 1300, TO_DATE('05/15/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (6, 900, TO_DATE('02/27/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (7, 3000, TO_DATE('08/07/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (8, 1500, TO_DATE('08/17/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (9, 1000, TO_DATE('01/27/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (10, 4000, TO_DATE('07/06/2015','MM/DD/YYYY'));
INSERT INTO registros_financeiros (cod_registro, qtde_vendas, data_venda) VALUES (11, 3000, TO_DATE('04/20/2015','MM/DD/YYYY'));
Listagem 10. Inserção de registros na tabela principal,
registros_financeiros
Com nossos registros inseridos, realizaremos inicialmente uma consulta na tabela principal para que possamos ver o resultado que nos será apresentado, e em seguida, realizaremos uma consulta em uma das tabelas filhas para verificarmos os registros que foram inseridos. Realizaremos nossas consultas de acordo com as instruções a seguir, seguidas dos resultados apresentados pelas Figuras 1 e 2.
SELECT * FROM registros_financeiros;SELECT * FROM registros_financeiros_abril_junho;Como podemos observar na tabela filha, referente as vendas dos meses de abril a junho, temos apresentados três registros inseridos, sendo que dois foram no mês de maio e um de abril. Com relação as instruções de UPDATE ou DELETE, não precisaremos criar nenhuma nova trigger, pois a trigger de inserção criada anteriormente resolve o nosso problema. Para demonstrarmos isso, realizaremos inicialmente a atualização em um dos registros e verificaremos o impacto realizado sobre a tabela filha, como podemos ver de acordo com a instrução a seguir:
UPDATE registros_financeiros SET data_venda = TO_DATE('03/10/2015','MM/DD/YYYY') WHERE cod_registro = 9;O resultado apresentado ao realizarmos uma consulta sobre a tabela filha de janeiro a março será então apresentado com um novo registro, como mostra a Figura 3, com base na instrução de pesquisa a seguir:
SELECT * FROM registros_financeiros_jan_mar;Por fim, realizaremos agora a exclusão de um dos registros, o qual se aplica da seguinte forma:
DELETE FROM registros_financeiros WHERE data_venda = TO_DATE('03/10/2015','MM/DD/YYYY');List partitioning (Lista de particionamentos)
Ao tratarmos de lista de partições, temos que esta é uma maneira similar ao Range partitioning, com a diferença de que a tabela é dividida listando-se explicitamente os valores-chave que aparecem em cada partição. Neste caso, cada partição é definida e designada com base em um valor de coluna presente em um conjunto de listas de valores, ao contrário de ser com base em um conjunto de faixas de valores adjacentes, o qual será realizado através da definição de cada partição por meio dos valores representados pelo value_list, onde value_list é uma lista de valores separados por vírgulas.
Para nosso propósito, criaremos novas tabelas principal e filhas, as quais conterão os registros financeiros da empresa de vendas em conjunto com as informações das cidades que possuem uma unidade da empresa em questão. Neste caso, teremos a coluna cidade como sendo a base para a criação da nossa lista de partições. Dito isso, criaremos inicialmente a nossa tabela principal, a qual chamaremos de registros_financeiros_listaParticao, como mostra a Listagem 11.
CREATE TABLE registros_financeiros_listaParticao
(
cod_reg NUMERIC primary key,
data_venda date,
qtd_vendas NUMERIC,
cidade_empresa text
);
Listagem 11. Criação da tabela principal
Agora, serão criadas as tabelas filhas, como podemos ver de acordo com as instruções apresentadas pelas Listagens 12 e 13.
CREATE TABLE registros_financeiros_1
(
PRIMARY KEY (cod_reg, didade_empresa),
CHECK (didade_empresa IN ('Recife', 'Caruaru'))
)
INHERITS (registros_financeiros_listaParticao);Listagem 12. Criação da tabela filha registros_financeiros_1
CREATE TABLE registros_financeiros_2
(
PRIMARY KEY (cod_reg, didade_empresa),
CHECK (didade_empresa IN ('Vitória', 'Fortaleza'))
)
INHERITS (registros_financeiros_listaParticao); Listagem 13. Criação da tabela filha registros_financeiros_2
Realizada a criação das tabelas, serão criados os índices para as tabelas geradas, os quais geraremos de acordo com as seguintes instruções:
CREATE INDEX listaParticao1_idx ON registros_financeiros_1(didade_empresa);
CREATE INDEX listaParticao2_idx ON registros_financeiros_2(didade_empresa);Em seguida, criaremos as triggers principal e a trigger function para realizarmos a atualização das tabelas, como mostra asListagens 14 e 15.
CREATE OR REPLACE FUNCTION insere_registros_listaParticao()
RETURNS TRIGGER AS $
BEGIN
IF (NEW.didade_empresa IN ('Recife', 'Caruaru')) THEN
INSERT INTO registros_financeiros_1 VALUES (NEW.*);
ELSIF (NEW.didade_empresa IN ('Vitória', 'Fortaleza')) THEN
INSERT INTO registros_financeiros_2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'A cidade não foi encontrada na listagem especificada...';
END IF;
RETURN NULL;
END;
$ LANGUAGE plpgsql;
Listagem 14. Criação da trigger function
CREATE TRIGGER registros_financ_trigger
BEFORE INSERT ON registros_financeiros_listaParticao
FOR EACH ROW
EXECUTE PROCEDURE insere_registros_listaParticao();
Listagem 15. Criação da trigger auxiliar registros_financ_trigger
Para finalizarmos, realizaremos a inserção de alguns registros para vermos o funcionamento das instruções, como mostra a Listagem 16.
INSERT INTO registros_financeiros_listaparticao(cod_reg, data_venda, qtd_vendas, didade_empresa) VALUES (1, '16-07-2015', 1500, 'Vitória');
INSERT INTO registros_financeiros_listaparticao(cod_reg, data_venda, qtd_vendas, didade_empresa) VALUES (2, '26-03-2015', 3500, 'Fortaleza');
INSERT INTO registros_financeiros_listaparticao(cod_reg, data_venda, qtd_vendas, didade_empresa) VALUES (3, '10-09-2015', 500, 'Caruaru');
INSERT INTO registros_financeiros_listaparticao(cod_reg, data_venda, qtd_vendas, didade_empresa) VALUES (4, '16-12-2015', 1500, 'Recife');
INSERT INTO registros_financeiros_listaparticao(cod_reg, data_venda, qtd_vendas, didade_empresa) VALUES (5, '18-10-2015', 5000, 'Fortaleza');
Listagem 16. Inserção de registros na tabela principal
Com os registros inseridos, vejamos através da instrução SELECT como serão apresentadas as informações em uma das tabelas filhas, como mostrado a seguir:
select * from
registros_financeiros_2;Com base na instrução de seleção, tivemos os dados apresentados como mostra a Figura 4, onde temos que três registros foram inseridos, sendo que dois foram para Fortaleza.
Com isso finalizamos este artigo, no qual foram apresentadas as técnicas de particionamento de tabelas para a base de dados PostgreSQL, onde para isso, precisamos entender conceitos de herança entre as tabelas para que pudéssemos capitar melhor o motivo de tal necessidade. Esperamos que tenham gostado. Até a próxima! =)
Links
DLL Inherit
http://www.postgresql.org/docs/9.4/static/ddl-inherit.html
DLL Partitioning
http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html
Artigos relacionados
-
Artigo
-
Artigo
-
Artigo
-
Artigo
-
Artigo