Particionamento de Tabelas no postgres – Detalhes

Este artigo foi escrito por telles

Bom, até agora já discutimos QUANDO e COMO particionar tabelas. Apesar se já ser possível trabalhar com as nossas tabelas particionadas com o que vimos até agora, na prática precisamos ver mais alguns detalhes. Sim, ainda temos alguns deles para discutir aqui.

E o particionamento de uma tabela com FK para outra tabela particionada?

Sim, ficamos devendo, mas não esqueci. Não existe mistério aqui. O particionamento da tabela PEDIDO_DETALHE pode ocorrer sem problemas, uma vez que: a chave ANO_PEDIDO é idêntica em ambas as tabelas e vamos particionar ela com o mesmo tipo de critério e para os mesmos valores: uma para 2008, 2009, 2010, 2011, 2012 e 2013. Isso tem de cassar perfeitamente. Na verdade, sempre que você for criar uma nova partição na tabela PEDIDO, já tem de criar uma partição com os mesmos critérios na tabela PEDIDO_DETALHE. Se você fizer assim, não terá nenhum problema:

CREATE TABLE app.pedido_detalhe_2008 (LIKE app.pedido_detalhe INCLUDING ALL) INHERITS (app.pedido_detalhe); 
CREATE TABLE app.pedido_detalhe_2009 (LIKE app.pedido_detalhe INCLUDING ALL) INHERITS (app.pedido_detalhe); 
CREATE TABLE app.pedido_detalhe_2010 (LIKE app.pedido_detalhe INCLUDING ALL) INHERITS (app.pedido_detalhe);
CREATE TABLE app.pedido_detalhe_2011 (LIKE app.pedido_detalhe INCLUDING ALL) INHERITS (app.pedido_detalhe);
CREATE TABLE app.pedido_detalhe_2012 (LIKE app.pedido_detalhe INCLUDING ALL) INHERITS (app.pedido_detalhe);
CREATE TABLE app.pedido_detalhe_2013 (LIKE app.pedido_detalhe INCLUDING ALL) INHERITS (app.pedido_detalhe);

Vou pular aqui alguns detalhes que mostramos antes como parâmetros de storage, comentários etc. Vamos direto ao que interessa: ajustar as FKs das partições que criamos agora:

ALTER TABLE app.pedido_detalhe_2008 ADD CONSTRAINT pedido_cliente_fk_2008  FOREIGN KEY (id_pedido, ano_pedido) 
    REFERENCES app.pedido(id_pedido, ano_pedido);
ALTER TABLE app.pedido_detalhe_2009 ADD CONSTRAINT pedido_cliente_fk_2009  FOREIGN KEY (id_pedido, ano_pedido) 
    REFERENCES app.pedido(id_pedido, ano_pedido);
ALTER TABLE app.pedido_detalhe_2010 ADD CONSTRAINT pedido_cliente_fk_2010  FOREIGN KEY (id_pedido, ano_pedido) 
    REFERENCES app.pedido(id_pedido, ano_pedido);
ALTER TABLE app.pedido_detalhe_2011 ADD CONSTRAINT pedido_cliente_fk_2011  FOREIGN KEY (id_pedido, ano_pedido) 
    REFERENCES app.pedido(id_pedido, ano_pedido);
ALTER TABLE app.pedido_detalhe_2012 ADD CONSTRAINT pedido_cliente_fk_2012  FOREIGN KEY (id_pedido, ano_pedido) 
    REFERENCES app.pedido(id_pedido, ano_pedido);
ALTER TABLE app.pedido_detalhe_2013 ADD CONSTRAINT pedido_cliente_fk_2013  FOREIGN KEY (id_pedido, ano_pedido) 
    REFERENCES app.pedido(id_pedido, ano_pedido);

Ou seja, a partição PEDIDO_DETALHE_2008, tem uma FK que aponta para a partição PEDIDO_2008. Pronto, é só isso.

Posso inserir registros na tabela mãe?

A princípio não. Se você quiser inserir registros na tabela mãe, o primeiro problema que você vai esbarrar é o gatilho. Lá a gente já criou uma proteção para não permitir inserir registros fora das partições existentes:

ELSE
        RAISE EXCEPTION 'Data fora do intervalo permitido.';

Ok, podemos retirar esta trava com algo como:

ELSE
        INSERT INTO app.pedido VALUES (NEW.*);

Se você fizer isso, ao inserir na tabela PEDIDO, você vai entrar num laço infinito:

teste=# INSERT INTO app.pedido VALUES (2007,1,'2007-01-08',1,1);
ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT:  PL/pgSQL function app.pedido_trigger() line 3 at IF
	SQL statement "INSERT INTO app.pedido VALUES (NEW.*)"
	PL/pgSQL function app.pedido_trigger() line 16 at SQL statement

Ou seja, o postgres tenta fazer um INSERT na tabela PEDIDO, mas o gatilho desvia para um outro INSERT na tabela PEDIDO novamente, que dispara o gatilho e começa tudo novamente. Isso se chama referência circular e não deve nunca ocorrer numa função dentro de um banco de dados. Claro, existe uma alternativa simples, que é retornar os valores novos ao invés de NULL:

ELSE
        RETURN NEW;

Agora sim o INSERT na tabela PEDIDO, vai funcionar para valores de ANO_PEDIDO fora do intervalo das partições.

Outro detalhe é o CHECK constraint:

CONSTRAINT pedido_null_ck   CHECK (ano_pedido IS NULL) NO INHERIT

Você teria de editar ele para algo como:

CONSTRAINT pedido_null_ck   CHECK (ano_pedido < 2008) NO INHERIT

Isto pode ser útil para manter informações históricas antigas que não são utilizadas com frequência. Mas você não vai poder usar um tablespace diferente para ele se você herdar os tablespaces durante a criação das tabelas filha. Vale a pena lembrar que quando falamos de bases realmente volumosas o uso de tablespaces costuma ser mais comum e exige sempre um planejamento cuidadoso.

Mantendo um grande número de partições

Como disse antes, deixar um número muito grande de partições não é uma boa prática no PostgreSQL. O mecanismo de CONSTRAINT_EXCLUSION e o gatilho/regra começam a ficar enormes, e isso significa perda de desempenho. Particularmente tabelas de auditoria tem a mania de crescer muito e podem ter muitas partições, dependendo da aplicação. Existe uma forma de manter os dados e diminuir o número de partições. Para isto basta fazer com que a partição deixe de ser uma tabela filha, utilizando o NO INHERIT:

ALTER TABLE app.pedido_2008 NO INHERIT app.pedido;

Os dados da tabela PEDIDO_2008 continuam acessíveis, mas você não conseguirá mais acessar eles a partir da tabela PEDIDO. Isso vai exigir alterações na sua aplicação, claro.

Cargas em lote planejadas

Independente de você ter escolhido usar gatilho ou regra como método para desviar o INSERT para as partições, isto impõe um overhead, que em algumas situações não é despresível. Além disso, índices e restrições também diminuem a velocidade da carga. Se você observar um dump feito pelo pg_dump vai observar uma ordem ótima para carga de dados: cria-se a tabela sem índices ou restrições, depois importamos os dados com o COPY e por último criamos os índices e restrições. Esta é a forma mais rápida de importar um grande volume de dados. No nosso caso, a única diferença seria um comando extra no final para filiar a tabela:

ALTER TABLE app.pedido_2008 INHERIT app.pedido;

Gatilho de UPDATE

Todo mundo sabe como é chato ter de atualizar a PK de um registro, principalmente se você estiver mexendo numa tabela central do seu sistema. Mexer no valor da chave de um registro numa tabela particionada é pior. Se você tentar vai ver algo como:

teste=# UPDATE app.pedido SET ano_pedido = 2009 WHERE data_pedido = '2008-01-01 01:00:00';
ERROR:  new row for relation "pedido_2008" violates check constraint "pedido_check_2008"
DETAIL:  Failing row contains (2009, 262836, 2008-01-01 01:00:00, 1, 1, null, null).
STATEMENT:  UPDATE app.pedido SET ano_pedido = 2009 WHERE data_pedido = '2008-01-01 01:00:00';

O seu UPDATE está ocorrendo na verdade na partição APP.PEDIDO_2008 o registro não vai mudar automaticamente para a tabela APP.PEDIDO_2009. Você precisa de um gatilho de UPDATE para conseguir fazer isso, tornando a operação ainda mais cara para o postgres. O detalhe é que não adianta um gatilho na tabela APP.PEDIDO. Se você o fizer, a restrição CHECK vai levantar a mesma excessão que você viu acima, antes mesmo do gatilho ser disparado. Então o que deve ser feito é criar um gatilho para cada partição, aumentando um pouco mais a complexidade do nosso sistema:

CREATE OR REPLACE FUNCTION app.pedido_trigger_upd_2008()
RETURNS TRIGGER AS $$
BEGIN

    IF NEW.ano_pedido != OLD.ano_pedido THEN 
        RAISE NOTICE 'Migrando registro para a partição %', NEW.ano_pedido;
        DELETE FROM app.pedido_2008 WHERE ano_pedido = OLD.ano_pedido AND id_pedido = OLD.id_pedido;
        IF NEW.ano_pedido = 2009 THEN 
            INSERT INTO app.pedido_2009 VALUES (NEW.*);
        ELSIF NEW.ano_pedido = 2010 THEN 
            INSERT INTO app.pedido_2010 VALUES (NEW.*);
        ELSIF NEW.ano_pedido = 2011 THEN 
            INSERT INTO app.pedido_2011 VALUES (NEW.*);
        ELSIF NEW.ano_pedido = 2012 THEN 
            INSERT INTO app.pedido_2012 VALUES (NEW.*);
        ELSIF NEW.ano_pedido = 2013 THEN 
            INSERT INTO app.pedido_2013 VALUES (NEW.*);
        ELSE
            RAISE EXCEPTION 'Data fora do intervalo permitido.';
        END IF;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_pedido_trigger_2008
    BEFORE UPDATE OF ano_pedido ON app.pedido_2008
    FOR EACH ROW EXECUTE PROCEDURE app.pedido_trigger_upd_2008();

Aqui mostrei o gatilho apenas para a partição PEDIDO_2008. Você terá de fazer o mesmo para as partições 2009, 2010, 2011, 2012 e 2013. Veja o funcionamento:

teste=# UPDATE app.pedido SET ano_pedido = 2009 WHERE data_pedido = '2008-01-01 01:00:00';
NOTICE:  Migrando registro para a partição 2009
UPDATE 0

Claro, que você pode simplificar um pouco as coisas, criando uma função de gatilho mais genérica. No entanto, tenha em mente que você vai continuar tendo que criar um gatilho para cada tabela:

CREATE OR REPLACE FUNCTION app.pedido_trigger_upd()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.ano_pedido != OLD.ano_pedido THEN 
        RAISE NOTICE 'Migrando registro para a partição %', NEW.ano_pedido;
        EXECUTE $a$DELETE FROM app.pedido_$a$ || OLD.ano_pedido || 
            $b$ WHERE ano_pedido = $b$ || OLD.ano_pedido || 
            $c$ AND id_pedido = $c$ || OLD.id_pedido;
        INSERT INTO app.pedido VALUES (NEW.*);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_pedido_trigger_2008
    BEFORE UPDATE OF ano_pedido ON app.pedido_2008
    FOR EACH ROW EXECUTE PROCEDURE app.pedido_trigger_upd();

 Estamos quase encerrando o assunto…

Bom, este é o 3º post sobre o assunto, no 4º e último da série estarei demonstrando uma função para automatizar a criação de partições.

O artigo Particionamento de Tabelas no postgres – Detalhes apareceu primeiro em Savepoint.

Particionamento de Tabelas no postgres – Quando?

Este artigo foi escrito por telles

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:

• Desempenho em leitura:
  • Se você particionar uma tabela, o tamanho do índice será menor, logo a profundidade da árvore será menor e você terá um acesso mais rápido.
  • Se você tem uma partição mais utilizada que as demais, como no caso em que apenas uma partição concentra as informações mais recentes, a chance do índice caber na memória é maior;
  • Se você tem uma partição mais utilizada que as demais, pode criar um TABLE SPACE que utilize os discos/raid/storage mais rápidos para esta partição deixando as partições menos utilizadas (com dados históricos por exemplo) em discos mais lentos;
• Desempenho em INSERT e UPDATE: Se você tiver meios físicos de gravação independentes, como dois RAIDs distintos, você pode fazer com que várias gravações concorrentes se dividam em várias partições utilizando diferentes TABLE SPACES em diferentes discos. É um caso bem extremo que depende muito da arquitetura de discos e utiliza um tipo específico de particionamento, conhecido como particionamento por HASH.
• Expurgo de dados. Fazer DELETE em uma quantidade grande de registros custa muito caro. Muitos sistemas tem rotinas de expurgo realizadas mensalmente, semanalmente e até diariamente. Poder fazer um simples TRUNCATE TABLE, um DROP TABLE ou um ALTER TABLE NO INHERIT, é muito mais simples e não fragmenta a tabela.
• Rotinas de manutenção. Rodar um VACCUM, um ANALYZE, um REINDEX ou um CLUSTER numa partição, é muito mais rápido do que fazer isso em toda a tabela. Às vezes um REINDEX numa única tabela pode levar varias horas. Se uma tabela é mais utilizada do que outra, você pode rodar suas rotinas de manutenção apenas nesta tabela;

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:

• Tabela: pedidos
  • Partição 1: pedidos_2008
  • Partição 2: pedidos_2009
  • Partição 3: pedidos_2010
  • Partição 4: pedidos_2011
  • Partição 5: pedidos_2012
  • Partição 6: pedidos_2013

É 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:

• Tabela: pedidos
  • Partição 1: pedidos_a2012_m10
  • Partição 2: pedidos_a2012_m11
  • Partição 3: pedidos_a2012_m12
  • Partição 4: pedidos_a2013_m01
  • Partição 5: pedidos_a2013_m02

É 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:

• Por data:
  • data_pedido BETWEEN DATE’2011-01-01′ AND DATE’2011-12-31′
  • data_pedido BETWEEN DATE’2012-01-01′ AND DATE’2012-12-31′
  • data_pedido BETWEEN DATE’2013-01-01′ AND DATE’2013-12-31′
• Por localização geográfica:
  • uf_pedido = ‘SP’
  • uf_pedido IN (‘AM’, ‘AC’, ‘RO’, ‘PA)
  • uf_pedido IN (‘RS’, ‘PR’, ‘SC’)
• Por loja:
  • cod_loja = 1
  • cod_loja = 2
  • cod_loja IN (3, 4, 5)
• Por ano fiscal:
  • cod_ano_fis = 2011
  • cod_ano_fis = 2012
  • cod_ano_fis = 2013

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:

• O custo do particionamento não se justifica para tabelas pequenas;
• A tabela que será particionada precisa ter um campo que possa ser a chave do particionamento;
• Nenhuma tabela não particionada pode ter uma FK apontada para uma tabela particionada;
• O número de partições de uma tabela não deve ser muito grande. Em geral um número de até 50 partições é algo tolerável. Aumentar este número aumenta o custo das operações sobre a tabela particionada;
• Ao realizar operações de INSERT numa tabela particionada, o código de retorno não reflete o número correto de linhas inseridas, retorna sempre zero;
• Ao realizar operações de UPDATE numa tabela particionada, o valor alterado não deve alterar a partição em que o registro deve pertencer. Se isto for inevitável, então um gatilho deverá fazer esta migração.
• Ao realizar um SELECT você deve utilizar na cláusula WHERE uma restrição idêntica a utilizada na restrição que divide as partições. Esta restrição também não pode estar parametrizada ou ser enviada como parâmetro de um PREPARED STATEMENT.

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.

• 9.2: 
  • Permite que restrições do tipo CHECK sejam marcadas como NO INHERIT, fazendo com que estas restrições só se apliquem a tabela pai e não sejam herdadas nas tabelas filhas. Útil para bloquear INSERT na tabela pai.
  • Permite que uma restrição do tipo CHECK seja marcada como NOT VALID;
  • Corrige o CREATE TABLE (LIKE … ) para evitar conflito de nomes em comentários de índices;
• 9.1:
  • Melhoria na otimização de consultas de tabelas particionadas que utilizam ORDER BY, LIMIT e MIM/MAX;
• 9.0
  • Melhoria na velocidade de seleção de tabelas filhas;
  • ANALYZE automático em tabelas filhas;
  • Quando uma consulta é realizada numa tabela mãe, não checa novamente as permissões nas tabelas filhas, conforme padrão SQL;
  • Permite herdar comentários e parâmetros de storage da tabela pai ao rodar um CREATE TABLE pedido_2013 (LIKE pedido INCLUDING INDEXES INCLUDING STORAGE). Cria também uma atalho para incluir todos os tipos de atributos: comentários, índices, restrições, parâmetros de storage e valores padrão: CREATE TABLE pedido_2013 (LIKE pedido INCLUIDNG ALL)
• 8.4
  • Força as tabelas filhas a herdarem as restrições CHECK das tabelas mães, conforme padrão SQL;
  • Criação valor ‘partition’ para o parâmetro CONTRAINT_EXCLUSION. Assim a busca em tabelas filhas se torna padrão em tabelas particionadas e em consultas UNION ALL;
• 8.3
  • Corrige a herança de restrições NULL;
  • Permite herdar índices da tabela pai ao rodar um CREATE TABLE pedido_2013 (LIKE pedido INCLUDING INDEXES)
  • Melhoria no desempenho de consultas com exclusão de muitas partições;
• 8.2
  • Criação da cláusula NO INHERIT no comando ALTER TABLE;
  • Permite herdar restrições da tabela pai ao rodar um CREATE TABLE pedido_2013 (LIKE pedido INCLUDING CONSTRAINTS)
  • Permite a exclusão de partições em operações de UPDATE e DELETE;
• 8.1
  • Criação do parâmetro CONSTRAINT_EXCLUSION e a possibilidade de particionamento a partir da herança entre tabelas.

Alternativas

Alternativas ao particionamento de tabelas:

• Índices parciais. Quem usa não esquece mais. Resolve problemas difíceis de forma elegante. Nada mais é do que a possibilidade de se colocar uma cláusula WHERE na definição de um índice. Você pode achar que não é a coisa mais elegante do mundo, mas funciona muito bem e é rápido e simples de implementar. Se você precisa de índices mais seletivos e enxutos, essa é uma boa opção.
• Visões materializadas. Criar tabelas resumo com informações condensadas é a melhor forma de lidar com relatórios monstruosos. Você atualiza uma vez por dia a tabela resumo e simplifica uma série de consultas complexas em consultas bem mais simples e rápidas. Claro que isso envolve em trabalhar com informações mais desatualizadas. O mundo não é fácil e em muitos casos é preciso negociar os requisitos, particularmente de relatórios mais pesados. Infelizmente o PostgreSQL também não tem um mecanismo pronto para criar visões materializadas. Mas com um pouco de PL/pgSQL você pode criar algo que funciona muito bem. Senão, pode buscar algum módulo pronto no pgFoundry ou no PGXN.
• Trocar DELETEs por INSERTs. Se você tem que excluir um grande volume de registros, mas não pode criar uma partição para simplesmente trunca-la, então uma alternativa ao desastroso DELETE é o INSERT.

Na sequência

• Particionamento de tabelas no postgres – Como?
• Particionamento de Tabelas no postgres – Detalhes
• Particionamento de Tabelas no postgres – Automatizando

O artigo Particionamento de Tabelas no postgres – Quando? apareceu primeiro em Savepoint.