Particionamento de Tabelas no postgres – Detalhes

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.