Twitter Instagram Linkedin Github

Particionamento de Tabelas no postgres – Quando?

Já faz tempo que eu quero falar sobre particionamento de tabelas no PostgreSQL. Quando você lida com bases realmente grandes, o particionamento de tabelas é quase um item obrigatório. Lidar com uma tabela com mais de 500GB é um verdadeiro inferno.

Mas afinal, o que é o particionamento de tabelas em banco de dados. O particionamento consiste em dividir uma tabela grande em pedaços menores, de forma que juntando todos os pedaços você forme a tabela inteira. A aplicação jamais vê as partições individuais de uma tabela, o processo deve sempre ser transparente para a aplicação. O que muda é como os dados são armazenados fisicamente na base. Mesmo como todas seus pedaços físicos, logicamente a tabela não muda. O otimizador do banco de dados percebe quando uma consulta é realizada numa tabela particionada e automaticamente desvia a consulta para a partição específica. Se a consulta precisa manipular registros que estão em mais de uma partição, o banco de dados faz a consulta em todas as partições necessárias para satisfazer o critério da consulta.

Vantagens

Então vejamos os reais motivos que fazem alguém são passar pela empreitada de particionar uma tabela no PostgreSQL:

Avaliando o tamanho da tabela

Ok, você acha que está sofrendo na sua base de dados e que algumas tabelas enormes vão se beneficiar do particionamento. Primeiro você deve avaliar se realmente vale à pena. Tabelas pequenas não vão se beneficiar do particionamento. Ao contrário, você está inserindo uma camada a mais de abstração que tem um custo no processamento. A documentação fala que vale à pena particionar tabelas cujo tamanho superam a quantidade de memória física do servidor. Claro, se você tem apenas 2GB de memória no servidor e tem uma tabela com 4GB, talvez seja mais fácil comprar mais memória, que tal 8GB? Gosto de pensar em tabelas que tem pelo menos seus 10GB. Claro, se você prevê que a tabela vai crescer muito no futuro, é bom ir se preparando…

-- Tabelas com mais de 1GB de tamanho
SELECT n.nspname as "Schema",
  c.relname as "Tabela",
  pg_catalog.pg_size_pretty(pg_table_size(c.oid)) as "Tamanho",
  pg_catalog.pg_size_pretty(pg_total_relation_size(c.oid)) as "Tamanho total"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
      AND pg_catalog.pg_table_is_visible(c.oid)
      AND pg_table_size(c.oid) > 1073741824 -- >1GB
ORDER BY pg_table_size(c.oid) DESC,1,2;

Escolhendo uma chave

Outra questão importante é olhar com cuidado para a modelagem da tabela que você quer particionar. Você deve escolher um critério de particionamento. Você escolhe em geral uma coluna da tabela e define o critério. Por exemplo, uma tabela com vendas de uma cadeia de lojas. Você pode dividir a tabela por loja física. Pode também dividir a tabela por setor da loja ou por tipo de produto. Pode dividir por N critérios, mas o mais comum que você vai encontrar é dividir por Datas ou épocas, por exemplo:

É importante que cada partição tenha um nome que seja composto pelo nome da tabela original e a chave que você está utilizando para particionar a tabela. Não é obrigatório, mas torna a sua vida mais simples. Você poderia particionar por mês também:

É importante ter em mente que no PostgreSQL e em outros SGDBs, ter um número muito grande de partições não é saudável, pois o mecanismo que decide em qual partição que a consulta vai ser realizada começa a consumir muito recurso. Digamos que ter até umas 50 partições numa única tabela seja tolerável. Mas também não faz sentido criar partições que sejam muito grandes. Pensar em dividir uma tabela em pelo menos 3 ou 4 partições é algo que faz sentido para mim. Qualquer coisa acima de 20 ou 30 partições deve ser pensado com muito cuidado.

Mas para escolher o seu critério, você deve ter como selecionar ele de forma simples a partir de uma coluna na sua tabela, ex:

Como você pode ver, você precisa ter um campo como data_pedido, uf_pedido, cod_loja, cod_ano_fis para particionar a sua tabela. Quando o particionamento não é uma opção, é uma necessidade e você não tem um campo bom para usar num critério de particionamento, você acabará tendo de remodelar sua tabela para que o particionamento seja viável. Eu sei, isso não é tão fácil como parece. É por isso que aplicações de grande porte precisam de um planejamento a mais, para se pensar em como serão as coisas quando a aplicação estiver lidando com um grande volume de dados. Uma alternativa é sempre comprar um novo sistema…

Imagine a seginte situação:

CREATE TABLE pedido (
    id_pedido   SERIAL,
    data_pedido TIMESTAMP(2) NOT NULL DEFAULT now(),
    id_cliente  INTEGER      NOT NULL,
    id_vendedor INTEGER      NOT NULL,
    CONSTRAINT pedido_pk PRIMARY KEY (id_pedido)
);

CREATE TABLE pedido_detalhe (
    id_pedido      INTEGER,
    cod_produto    INTEGER,
    valor_unidade  NUMERIC(10,2),
    desconto       NUMERIC(10,2),
    quantidade     INTEGER,
    CONSTRAINT pedido_detalhe_pk PRIMARY KEY (id_pedido, cod_produto),
    CONSTRAINT pedido_detalhe_fk FOREIGN KEY (id_pedido) REFERENCES pedido (id_pedido)
);

Neste caso, para cada pedido, teremos vários detalhes do pedido. Se a tabela ‘pedido_detalhe’ precisar ser particionada por data, você terá um problema, pois a coluna data não está nesta tabela. É o momento onde você precisa de uma licença poética em relação a normalização para tornar o particionamento possível.

Cenário ideal: PK composta

Um único adendo final: sistemas que trabalham com grande volume de registros em geral usam uma PK composta. Pelo menos nas suas tabelas centrais. Imagine por exemplo que cada aluno numa universidade receba um número como “2013-00112”, ou seja, o ano do primeiro ingresso e uma sequência. Assim, o ano pode ser uma boa chave para particionar. Sistemas bancários utilizam um número único para cada dia útil. Assim cada operação bancária tem uma chave composta novamente com o número do dia fiscal e o número da operação. Sistemas grandes como os bancários tem sérios problemas de performance, logo uma modelagem mais cuidadosa como esta traz benefícios imediatos.

Já sistemas modelados olhando pelo prisma de um ORM com zilhões de chaves artificiais… bom, estes tem sérios problemas.  É praticamente impossível particionar uma base que utiliza chaves artificiais nas suas maiores tabelas. É claro que se você tiver um sistema com grande volume de dados modelado assim, o particionamento será o menor dos seus problemas.

O problema das FKs

Bom, fora isso, você terá uma nova preocupação que são as chaves estrangeiras (vamos aqui abreviar como FK, de foreign key). O problema é como o PostgreSQL implementa o recurso de particionamento de tabelas. Ele utiliza a herança de tabelas, onde a tabela PAI é a tabela original, sem nenhum registro nela e as tabelas FILHAS são as partições contendo os registros. Então o que ocorre é que quando você faz uma consulta na tabela mãe… o PostgreSQL olha para a cláusula WHERE da sua consulta e decide em qual partição a consulta será feita.

Imagine que particionamos a tabela PEDIDOS citada aqui. Temos a tabela mãe PEDIDO e as tabelas filhas PEDIDO_2011, PEDIDO_2012 e PEDIDO_2013. Agora imagine que não particionamos a tabela PEDIDO_DETALHE. Inserimos um registro na tabela PEDIDO e depois vamos inserir um na tabela DETALHE_PEDIDO:

teste=# INSERT INTO pedido VALUES (1,'2011-01-08',5,6);
INSERT 0 0
teste=# SELECT * FROM pedido;
 id_pedido | data_pedido | id_cliente | id_vendedor
-----------+-------------+------------+-------------
         1 | 2011-01-08  |          5 |           6
(1 row)

teste=# SELECT * FROM pedido_2011;
 id_pedido | data_pedido | id_cliente | id_vendedor
-----------+-------------+------------+-------------
         1 | 2011-01-08  |          5 |           6
(1 row)

teste=# INSERT INTO pedido_detalhe VALUES (1, 5, '15.5'::NUMERIC, '2.5'::NUMERIC, 3);
ERROR:  insert or update on table "pedido_detalhe" violates foreign key constraint "pedido_detalhe_fk"
DETAIL:  Key (id_pedido)=(1) is not present in table "pedido".

Como você pode ver, fizemos um INSERT na tabela PEDIDO, mas na verdade o INSERT foi realizado na tabela PEDIDO_2011, uma vez que a DATA_PEDIDO é do ano de 2011. Note o detalhe do retorno do INSERT: ”

INSERT 0 0

Detalhe importante. Isto significa que você pediu um INSERT na tabela PEDIDO, mas não houve retorno de nenhum registro nela, por isso o valor zero exibido como retorno. Isso vai nos causar problemas com algumas ferramentas de ORM como o Hibernate que espera um retorno diferente. Isso é facilmente contornável desativando a checagem dos códigos de retorno, mas é mais um ponto de atenção.

Enfim o erro:

ERROR:  insert or update on table "pedido_detalhe" violates foreign key constraint "pedido_detalhe_fk"
DETAIL:  Key (id_pedido)=(1) is not present in table "pedido".

Aqui vemos que mesmo inserindo um registro que sabemos que respeita a FK, pois existe o registro com o ID_PEDIDO = 1, a mensagem de erro teima em aparecer. O problema é que a FK aponta para a tabela PEDIDO e não para PEDIDO_2011 onde o registro se encontra. Desta forma, não é possível utilizar a FK da forma que estamos acostumados.

Desta forma temos uma limitação clara aqui: uma tabela não particionada não pode ter uma FK apontando para uma tabela particionada. Isto não costuma ser um enorme problema, pois você verá que as tabelas mais volumosas que são escolhidas para particionamento são em geral tabelas que estão na ponta da modelagem, portanto não é comum ver uma FK apontando para ela. No nosso caso em particular, uma alternativa seria particionar não apenas a tabela PEDIDO, mas também a tabela PEDIDO_DETALHE. Se as partições das duas tabelas utilizarem a mesma chave, ou seja a coluna DATA_PEDIDO, então cada partição de PEDIDO_DETALHE pode ter uma FK apontando para a partição PEDIDO correspondente. Claro, teremos de ter o campo DATA_PEDIDO em ambas as tabelas para que isso funcione…

Ajustando seus SELECTs

Aqui, para separar as partições utilizamos algumas constraints:

ALTER TABLE pedido_2011 ADD CONSTRAINT pedido_check_2011 CHECK (extract(YEAR FROM data_pedido) = 2011);
ALTER TABLE pedido_2012 ADD CONSTRAINT pedido_check_2012 CHECK (extract(YEAR FROM data_pedido) = 2012);
ALTER TABLE pedido_2013 ADD CONSTRAINT pedido_check_2013 CHECK (extract(YEAR FROM data_pedido) = 2013);

Vamos carregar aqui alguns dados antes de mais nada:

teste=# TRUNCATE TABLE pedido_2011;
TRUNCATE TABLE
teste=# TRUNCATE TABLE pedido_2012;
TRUNCATE TABLE
teste=# TRUNCATE TABLE pedido_2013;
TRUNCATE TABLE
teste=# INSERT INTO pedido SELECT nextval('pedido_id_pedido_seq'),s.a,1,1 FROM generate_series('2011-01-01'::timestamp, '2013-12-31'::timestamp, '2 minutes') AS s(a);
INSERT 0 0

teste=# analyze pedido_2011;
ANALYZE
teste=# analyze pedido_2012;
ANALYZE
teste=# analyze pedido_2013;
ANALYZE

Detalhe importante. Note que aqui rodamos um ANALYZE para que o otimizador tenha as informações mais atualizadas sobre os registros que acabamos de carregar. Note que rodamos o comando individualmente em cada partição. Se você rodasse apenas um ‘ANALYZE pedido;’ isto não provocaria efeito algum, pois não há registros nesta tabela. Então, tarefas de manutenção como ANALYZE, VACUUM e CLUSTER devem ser executados diretamente sobre as partições ou tabelas filhas.

Bom, vamos agora rodar algumas consultas. O que desejamos é consultar dados de uma partição específica, sem ter que dizer em qual partição está a informação. Ou seja, fazemos uma consulta na tabela PEDIDO e o PostgreSQL deverá buscar os registros apenas nas partições certas. Então vamos buscar informações apenas de 2011. Para saber o que o PostgreSQL está fazendo internamente, vamos sempre usar o EXPLAIN:

teste=# EXPLAIN SELECT * FROM pedido WHERE extract(YEAR FROM data_pedido) = 2011;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Result  (cost=0.00..1674.00 rows=2 width=20)
   ->  Append  (cost=0.00..1674.00 rows=2 width=20)
         ->  Seq Scan on pedido  (cost=0.00..0.00 rows=1 width=20)
               Filter: (date_part('year'::text, data_pedido) = 2011::double precision)
         ->  Seq Scan on pedido_2011 pedido  (cost=0.00..1674.00 rows=1 width=20)
               Filter: (date_part('year'::text, data_pedido) = 2011::double precision)
(6 rows)

Note que houve um ‘Seq Scan’ na tabela PEDIDO_2011 e outro na tabela PEDIDO. A passagem na tabela PEDIDO, praticamente não consome tempo, veja que o custo é zero aqui. As partições PEDIDO_2012 e PEDIDO_2013 não fazem parte do plano de execução. No entanto se mudarmos um pouco a consulta pegando novamente os registros de 2011, isso não vai mais funcionar:

teste=# EXPLAIN SELECT * FROM pedido WHERE date_trunc('year',data_pedido) = '2011-01-01';
                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.00..16849.01 rows=3943 width=20)
   ->  Append  (cost=0.00..16849.01 rows=3943 width=20)
         ->  Seq Scan on pedido  (cost=0.00..0.00 rows=1 width=20)
               Filter: (date_trunc('year'::text, data_pedido) = '2011-01-01 00:00:00'::timestamp without time zone)
         ->  Seq Scan on pedido_2011 pedido  (cost=0.00..5616.00 rows=1314 width=20)
               Filter: (date_trunc('year'::text, data_pedido) = '2011-01-01 00:00:00'::timestamp without time zone)
         ->  Seq Scan on pedido_2012 pedido  (cost=0.00..5631.80 rows=1318 width=20)
               Filter: (date_trunc('year'::text, data_pedido) = '2011-01-01 00:00:00'::timestamp without time zone)
         ->  Seq Scan on pedido_2013 pedido  (cost=0.00..5601.22 rows=1310 width=20)
               Filter: (date_trunc('year'::text, data_pedido) = '2011-01-01 00:00:00'::timestamp without time zone)
(10 rows)

Note que a consulta agora passa por todas as partições o custo final subiu consideravelmente. Isto significa que devemos usar a mesma função que utilizamos no CHECK CONSTRAINT que criamos. Vejamos agora o que ocorre se fazemos a mesma consulta retornando apenas um único registro de 2011:

teste=# EXPLAIN SELECT * FROM pedido WHERE data_pedido = '2011_01_01 00:50:00';
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Result  (cost=0.00..14878.01 rows=4 width=20)
   ->  Append  (cost=0.00..14878.01 rows=4 width=20)
         ->  Seq Scan on pedido  (cost=0.00..0.00 rows=1 width=20)
               Filter: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
         ->  Seq Scan on pedido_2011 pedido  (cost=0.00..4959.00 rows=1 width=20)
               Filter: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
         ->  Seq Scan on pedido_2012 pedido  (cost=0.00..4973.00 rows=1 width=20)
               Filter: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
         ->  Seq Scan on pedido_2013 pedido  (cost=0.00..4946.01 rows=1 width=20)
               Filter: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
(10 rows)

Bom, e agora… um custo tão alto para trazer uma única linha. Bom, deveríamos criar um índice para as nossas partições na coluna DATA_PEDIDO:

teste=# create index on pedido_2011 (data_pedido);
CREATE INDEX
teste=# create index on pedido_2012 (data_pedido);
CREATE INDEX
teste=# create index on pedido_2013 (data_pedido);
CREATE INDEX

teste=# EXPLAIN ANALYZE SELECT * FROM pedido WHERE data_pedido = '2011_01_01 00:50:00';
                                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
---------
 Result  (cost=0.00..24.89 rows=4 width=20) (actual time=0.085..16.872 rows=1 loops=1)
   ->  Append  (cost=0.00..24.89 rows=4 width=20) (actual time=0.084..16.870 rows=1 loops=1)
         ->  Seq Scan on pedido  (cost=0.00..0.00 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=1)
               Filter: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
         ->  Index Scan using pedido_2011_data_pedido_idx on pedido_2011 pedido  (cost=0.00..8.30 rows=1 width=20) (actual time=0.083..0.083 rows=1 lo
ops=1)
               Index Cond: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
         ->  Index Scan using pedido_2012_data_pedido_idx on pedido_2012 pedido  (cost=0.00..8.30 rows=1 width=20) (actual time=16.744..16.744 rows=0
loops=1)
               Index Cond: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
         ->  Index Scan using pedido_2013_data_pedido_idx on pedido_2013 pedido  (cost=0.00..8.30 rows=1 width=20) (actual time=0.040..0.040 rows=0 lo
ops=1)
               Index Cond: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
 Total runtime: 16.907 ms
(11 rows)

Melhorou muito, de 14878,01 para 24,89. .. Mas continuamos consultado todas as partições. Temos de fazer isso funcionar direito então faremos assim:

teste=# EXPLAIN SELECT * FROM pedido WHERE data_pedido = '2011_01_01 00:50:00' AND extract(YEAR FROM data_pedido) = 2011;
                                                                           QUERY PLAN                                      ------------------------------------------------------------------------------------------------------------------------------------------------------
----------
 Result  (cost=0.00..8.30 rows=2 width=20)
   ->  Append  (cost=0.00..8.30 rows=2 width=20)
         ->  Seq Scan on pedido  (cost=0.00..0.00 rows=1 width=20)
               Filter: ((data_pedido = '2011-01-01 00:50:00'::timestamp without time zone) AND (date_part('year'::text, data_pedido) = 2011::double pr
ecision))
         ->  Index Scan using pedido_2011_data_pedido_idx on pedido_2011 pedido  (cost=0.00..8.30 rows=1 width=20)
               Index Cond: (data_pedido = '2011-01-01 00:50:00'::timestamp without time zone)
               Filter: (date_part('year'::text, data_pedido) = 2011::double precision)
(7 rows)

Agora sim, um “Index Scan” na PEDIDO_2011 e nada das demais partições. O custo agora caiu para algo razoável, 8,3. A consulta ficou meio estranha, mas é a forma de garantir que o mecanismo de ‘constraint exclusion’ funcione.

Migração de partições

Se você alterar o conteúdo de uma linha de uma tabela particionada, deve ter um cuidado adicional. O valor não pode obrigar a linha a mudar de partição. No nosso exemplo, um registro não pode ter a data mudando de ano. Se isso ocorrer o registro ficará perdido, pois o PostgreSQL não irá retirar a linha de uma partição e inserir ela na partição correta. Se este casso ocorrer, mas for raro, você pode criar um gatilho de UPDATE na tabela particionada para fazer esta migração de partições, mas é um trabalho a mais a ser feito.

Resumindo

Particionar é algo realmente importante. Em bases com tabelas muito grandes é um recurso indispensável. Mas é um recurso que não sai barato. A implementação do PostgreSQL tem restrições consideráveis:

A evolução

Veja que o particionamento ainda tem grandes problemas, mas já foi pior. A partir da versão 8.1 onde foi lançado todas as versões posteriores incluíram algum tipo de melhoria no particionamento.

Alternativas

Alternativas ao particionamento de tabelas:

Na sequência

Com a tag

4 respostas

  1. Pingback: Particionamento de tabelas no postgres – Como? | Savepoint
  2. Pingback: Particionamento de Tabelas no postgres – Detalhes. | Savepoint

  3. Fábio, parabéns pelo artigo, me esclareceu muitas dúvidas sobre particionamento e será muito útil para minha implementação. Curti sua palestra hoje no FISL. Parabéns, Abraço.

    Responder
  4. Pingback: Oracle X PostgreSQL - Parte II: Vantagens do Oracle - Savepoint

Deixe uma resposta

Licença Creative Commons
Este obra está licenciado com uma Licença
Creative Commons Atribuição 4.0 Internacional.

Twitter Instagram Linkedin Github
plugins premium WordPress
%d blogueiros gostam disto: