Particionamento de tabelas no postgres – Como?

Bom, já contei um pouco sobre quando particionar tabelas no postgres. Agora vamos explicar direito como. Se você nunca leu a documentação do PostgreSQL sobre particionamento ou se você leu e não entendeu muito bem… este post é para você. Mas é claro que isto não substitui a documentação oficial. Não vou cobrir alguns pontos da documentação e outros eu vou detalhar um pouco mais. Então, a máxima prevalece: RTFM.

Bom, para tornar o nosso caso um pouco mais realista, vamos particionar duas tabelas e vamos colocar uns penduricalhos que são comuns de aparecer em ambientes mais críticos. Tablespaces e parâmetros de armazenamento serão utilizados aqui para reproduzir alguns detalhes que só aparecem quando você utiliza eles. Estamos falando de detalhes um pouco mais complexos. Uma visita a documentação sempre cai bem para quem esqueceu algumas coisas ou não está mesmo familiarizado com estes detalhes.

Tablespaces

Vamos começar criando 4 tablespaces:

  • 1 para a tabela PEDIDO
  • 1 para os índices da tabela PEDIDO
  • 1 para a tabela PEDIDO_DETALHE
  • 1 para os índices da tabela PEDIDO_DETALHE

Durante a criação das tabelas e índices você verá a clausula TABLESPACE especificando exatamente o local onde cada objeto será armazenado. Note que eu não estou sugerindo que você organize seus tablespaces assim. Está OK para a maioria das bases não precisar mexer com isso. Estou aqui criando 4 tablespaces somente para mostrar como as coisas poderiam ficar. É claro que tabelas enormes costumam ter tratamento diferenciado na aplicação. É justamente isso que estamos fazendo aqui.

Vou supor aqui que você usa Unix. Se não usa, se vira aí… não há nada tão complexo aqui. Vamos lá, usando o shell:

# Criando diretórios para novos TABLESPACEs
mkdir -p /data/pedido
mkdir -p /data/pedido_detalhe
mkdir -p /index/pedido
mkdir -p /index/pedido_detalhe
chown -R postgres: /data
chown -R postgres: /index

Agora usando psql mesmo:

-- Tablespaces novos nos diretórios criados anteriormente
CREATE TABLESPACE pedido LOCATION '/data/pedido';
CREATE TABLESPACE pedido_detalhe LOCATION '/data/pedido_detalhe';
CREATE TABLESPACE pedido_idx LOCATION '/index/pedido';
CREATE TABLESPACE pedido_detalhe_idx LOCATION '/index/pedido_detalhe';

Usuários e permissões

Outro detalhe pouco lembrado é a questão das permissões. Vamos aqui criar um esquema APP para colocar nossas tabelas e um usuário APP para ser o dono das nossas tabelas. Adicionalmente vamos criar um segundo usuário chamado CLIENT para ser o usuário que a nossa aplicação utilizaria para acessar nossas tabelas particionadas. Vou simplificar isso ao máximo pois eu sei o quanto as pessoas adoram mexer com a parte de segurança…

-- Criação de usuários e esquema 
CREATE ROLE app PASSWORD 'abizi' LOGIN;
CREATE SCHEMA app;
GRANT ALL ON SCHEMA app TO app;

CREATE ROLE client PASSWORD 'abizi' LOGIN;
GRANT USAGE ON SCHEMA app TO client;

Criando as tabelas mãe

Muito bom, agora vamos criar nossas 2 tabelas que serão particionadas e também 2 tabelas que serão FK da tabela principal mas não serão particionadas. Veja, não ligue para a modelagem delas aqui. Não estou sugerindo que você faça desta forma. Estou apenas demonstrando como o particionamento pode ser feito com o maior número de variáveis envolvidas:

-- Tabelas não particionadas
CREATE TABLE app.cliente (
    id_cliente INTEGER,
    nome_cliente VARCHAR(100),
    CONSTRAINT cliente_pk PRIMARY KEY (id_cliente)
);
ALTER TABLE app.cliente OWNER TO app;

CREATE TABLE app.vendedor (
    id_vendedor   INTEGER,
    nome_vendedor VARCHAR(100),
    CONSTRAINT vendedor_pk PRIMARY KEY (id_vendedor)
);
ALTER TABLE app.vendedor OWNER TO app;

-- Criação da tabela PEDIDO
CREATE TABLE app.pedido (
    ano_pedido  SMALLINT,
    id_pedido   INTEGER,
    data_pedido TIMESTAMP(2) NOT NULL DEFAULT now(),
    id_cliente  INTEGER      NOT NULL,
    id_vendedor INTEGER      NOT NULL,
    status      CHAR(1),
    observacao  TEXT,
    CONSTRAINT pedido_pk PRIMARY KEY (ano_pedido, id_pedido) 
        WITH (fillfactor=100) USING INDEX TABLESPACE pedido_idx,
    CONSTRAINT pedido_cliente_fk FOREIGN KEY (id_cliente)
        REFERENCES app.cliente(id_cliente),
    CONSTRAINT pedido_vendedor_fk FOREIGN KEY (id_vendedor)
        REFERENCES app.vendedor(id_vendedor),
    CONSTRAINT pedido_status_ck  CHECK (status IN ('A', 'B', 'I', 'Z')),
    CONSTRAINT pedido_null_ck   CHECK (ano_pedido IS NULL) NO INHERIT -- Só funciona a partir do PG 9.2
) WITH (autovacuum_vacuum_scale_factor=0.1,fillfactor=70)
TABLESPACE pedido;

CREATE INDEX pedido_data_pedido_index ON app.pedido (data_pedido) 
    WITH (fillfactor=95) TABLESPACE pedido_idx;
CREATE INDEX pedido_id_cliente_index ON app.pedido (id_cliente)  
    WITH (fillfactor=95) TABLESPACE pedido_idx;
CREATE INDEX pedido_id_vendedor_index ON app.pedido (id_vendedor) 
    WITH (fillfactor=95) TABLESPACE pedido_idx;

COMMENT ON TABLE app.pedido IS 'Tabela de pedidos foo';
COMMENT ON COLUMN app.pedido.ano_pedido IS 'Campo chave do particionamento';
COMMENT ON CONSTRAINT pedido_null_ck ON app.pedido IS 'Restrição para impedir registros na tabela mãe';
COMMENT ON INDEX pedido_data_pedido_index IS 'Índice from hell';

ALTER TABLE app.pedido OWNER TO app;
GRANT SELECT, INSERT, UPDATE ON TABLE app.pedido TO client;

-- Criação da tabela PEDIDO_DETALHE
CREATE TABLE app.pedido_detalhe (   
    ano_pedido     SMALLINT,
    id_pedido      INTEGER,
    id_produto     INTEGER,
    valor_unidade  NUMERIC(10,2),
    desconto       NUMERIC(10,2),
    quantidade     INTEGER,
    CONSTRAINT pedido_detalhe_pk PRIMARY KEY (id_pedido, ano_pedido, id_produto)
        WITH (fillfactor = 95) USING INDEX TABLESPACE pedido_detalhe_idx,
    CONSTRAINT pedido_detalhe_fk FOREIGN KEY (id_pedido, ano_pedido) 
        REFERENCES app.pedido (id_pedido, ano_pedido)
) WITH (fillfactor = 100)
TABLESPACE pedido_detalhe;

COMMENT ON TABLE app.pedido_detalhe IS 'Itens do pedido';
CREATE INDEX pedido_detalhe_id_produto_index ON app.pedido_detalhe (id_produto) 
    WITH (fillfactor = 95)TABLESPACE pedido_detalhe_idx;

ALTER TABLE app.pedido_detalhe OWNER TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE app.pedido_detalhe TO client;

Bom, vamos com calma aqui. São 2 tabelas a serem particionadas, certo? Temos a tabela PEDIDO e a tabela PEDIDO_DETALHE. Para facilitar a nossa vida no particionamento, a tabela pedido tem uma PK composta de dois campos: ID_PEDIDO e ANO_PEDIDO. Assim, todo novo ano, a numeração do ID_PEDIDO é reiniciada pela aplicação. A tabela PEDIDO_DETALHE também herda estes dois campos e adiciona um terceiro na sua PK.  Como você pode imaginar, o campo ANO_PEDIDO está lá para servir de chave do nosso particionamento.

CHECK (….) NO INHERIT

Outro detalhe importante: você notou este CHECK?

    CHECK (ano_pedido IS NULL) NO INHERIT

Note que o campo ANO_PEDIDO faz parte da PK e não pode ser nulo nunca. Ao mesmo tempo temos uma restrição para que ele seja nulo. Resultado: não dá para inserir nenhum registro nesta tabela. Como esta é a tabela mãe, não queremos mesmo que isso  ocorra, pois só vamos inserir nas tabelas filhas. Por isso aparece a cláusula NO INHERIT no final, para que as tabelas filhas não herdem esta propriedade. Você poderia usar isto de outra forma também. Imagine que você já tem uma tabela populada com vários anos anteriores e decide que só vai particionar os registros a partir de 2010. Então você poderia manter os registros de 2009 para trás na tabela mãe e deixar a restrição assim:

    CHECK (ano_pedido < 2010) NO INHERIT

Parâmetros de storage

Um bom DBA costuma olhar com atenção especial para tabelas enormes como as que costumamos particionar. Então é comum ajustar alguns parâmetros de armazenamento como os que aparecem aqui, depois da cláusula WITH. Este ajuste fino é importante para o desempenho e melhor uso do espaço em disco. Está fora do nosso escopo discuti-los aqui, mas novamente o que queremos é ver o que vai ocorrer com estes parâmetros quando criarmos as nossas partições.

Criando as tabelas filhas

Bom, como fazemos para que várias partições se comportem como uma só para a aplicação? No PostgreSQL isto pode ser feito de 2 formas, utilizando uma visão com UNION como é demonstrado na documentação ou utilizando a herança de tabelas. Utilizar herança de tabelas é mais prático e oferece alguns recursos adicionais. A herança de tabelas é algo que nasceu com o postgres quando se aventou a ideia do OBJETO-RELACIONAL. Pouca gente usa a herança de tabelas do PostgreSQL para outra coisa que não seja o particionamento de tabelas. Para criar uma tabela filha, você só precisa usar a cláusula INHERITS:

teste=# CREATE TABLE app.pedido_2008 ( ) INHERITS (app.pedido);
CREATE TABLE
teste=# d app.pedido_2008
                        Table "app.pedido_2008"
   Column    |              Type              |       Modifiers        
-------------+--------------------------------+------------------------
 ano_pedido  | smallint                       | not null
 id_pedido   | integer                        | not null
 data_pedido | timestamp(2) without time zone | not null default now()
 id_cliente  | integer                        | not null
 id_vendedor | integer                        | not null
 status      | character(1)                   | 
 observacao  | text                           | 
Check constraints:
    "pedido_status_ck" CHECK (status = ANY (ARRAY['A'::bpchar, 'B'::bpchar, 'I'::bpchar, 'Z'::bpchar]))
Inherits: pedido

Aqui criamos a tabela PEDIDO_2008 que é filha da tabela PEDIDO. Note a última linha da descrição da tabela que diz:

Inherits: pedido

Isto mostra a relação de herança, onde a maioria das propriedades da tabela PEDIDO foram herdadas. Mas faltaram algumas coisas… A tabela PEDIDO_2008 não herdou todas as propriedades. Faltaram os índices, as restrições, os tablespaces e os parâmetros de storage. Para isso vamos usar mais uma cláusula, o LIKE … INCLUDING:

-- INCLUDING COMMENTS     só funciona a partir do PG 9.0
-- INCLUDING STORAGE      só funciona a partir do PG 9.0
-- INCLUDING INDEXES      só funciona a partir do PG 8.3
-- INCLUDING CONSTRAINTS  só funciona a partir do PG 8.2
-- INCLUDING DEFAULT      só funciona a partir do PG 7.4

teste=# CREATE TABLE app.pedido_2008 (LIKE app.pedido INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS) INHERITS (app.pedido); 
NOTICE:  merging column "ano_pedido" with inherited definition
NOTICE:  merging column "id_pedido" with inherited definition
NOTICE:  merging column "data_pedido" with inherited definition
NOTICE:  merging column "id_cliente" with inherited definition
NOTICE:  merging column "id_vendedor" with inherited definition
NOTICE:  merging column "status" with inherited definition
NOTICE:  merging column "observacao" with inherited definition
NOTICE:  merging constraint "pedido_status_ck" with inherited definition
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pedido_2008_pkey" for table "pedido_2008"
CREATE TABLE
teste=# d+ app.pedido_2008
                                                     Table "app.pedido_2008"
   Column    |              Type              |       Modifiers        | Storage  | Stats target |          Description           
-------------+--------------------------------+------------------------+----------+--------------+--------------------------------
 ano_pedido  | smallint                       | not null               | plain    |              | Campo chave do particionamento
 id_pedido   | integer                        | not null               | plain    |              | 
 data_pedido | timestamp(2) without time zone | not null default now() | plain    |              | 
 id_cliente  | integer                        | not null               | plain    |              | 
 id_vendedor | integer                        | not null               | plain    |              | 
 status      | character(1)                   |                        | extended |              | 
 observacao  | text                           |                        | extended |              | 
Indexes:
    "pedido_2008_pkey" PRIMARY KEY, btree (ano_pedido, id_pedido) WITH (fillfactor=100), tablespace "pedido_idx"
    "pedido_2008_data_pedido_idx" btree (data_pedido) WITH (fillfactor=95), tablespace "pedido_idx"
    "pedido_2008_id_cliente_idx" btree (id_cliente) WITH (fillfactor=95), tablespace "pedido_idx"
    "pedido_2008_id_vendedor_idx" btree (id_vendedor) WITH (fillfactor=95), tablespace "pedido_idx"
Check constraints:
    "pedido_null_ck" CHECK (ano_pedido IS NULL)
    "pedido_status_ck" CHECK (status = ANY (ARRAY['A'::bpchar, 'B'::bpchar, 'I'::bpchar, 'Z'::bpchar]))
Inherits: pedido
Has OIDs: no

Bom, agora sim temos o pacote todo. Você verá uma série de informações extras durante a criação, se as suas configurações estiverem habilitadas para isso. Mas se você olhar com atenção, muita atenção, verá que nem tudo veio como você esperava…

Você pode achar o parâmetro (LIKE INCLUDING …) é meio exagerado. Criaram logo um atalho para incluir os 5 tipos de INCLUDING existentes. Deixei aqui assim só para vocês verem, mas na prática podemos utilizar algo como:

CREATE TABLE app.pedido_2008 (LIKE app.pedido INCLUDING ALL ) INHERITS (app.pedido);

Diferenças na herança do CHECK

Agora vamos a algo bem estranho. Lembra que falamos agora a pouco sobre o NO INHERIT na nossa restrição CHECK. Então, ela veio!!! Não devia ter vindo mais veio. Bom, eu não acho que ela deveria vir, ao menos. Veja, deixei duas restrições do tipo CHECK na tabela PEDIDO. Uma tem o NO INHERIT e outra não. O que vamos ver é que você pode apagar uma restrição, mas a outra não:

teste=# ALTER TABLE app.pedido_2008 DROP CONSTRAINT pedido_null_ck;
ALTER TABLE
teste=# ALTER TABLE app.pedido_2008 DROP CONSTRAINT pedido_status_ck;
ERROR:  cannot drop inherited constraint "pedido_status_ck" of relation "pedido_2008"
STATEMENT:  ALTER TABLE app.pedido_2008 DROP CONSTRAINT pedido_status_ck;

Diferenças na herança de FKs

Note que as FKs para as tabelas VENDEDOR e CLIENTE não foram importadas. Você terá que importá-las na mão. Isso pode lhe ajudar:

SELECT 'ALTER TABLE app.pedido_2008 ADD CONSTRAINT ' || conname || '_2008 ' || pg_get_constraintdef(tc.oid) || ';' AS alter_c
FROM 
    pg_constraint AS tc,
    pg_class      AS c,
    pg_class      AS r,
    pg_namespace  AS nc,
    pg_namespace  AS nr
WHERE
        tc.contype   = 'f'
    AND nc.nspname   = 'app'
    AND tc.conrelid  = c.oid
    AND c.relname    = 'pedido'
    AND nc.oid       = c.relnamespace
    AND tc.confrelid = r.oid
    AND nr.oid       = r.relnamespace

Diferenças na herança de índices

Os índices na tables PEDIDO_2008 estão OK. Vieram com o tablespace e parâmetros de storage corretos. Note no entanto que o nome dos índices não são os mesmos. Antes tínhamos PEDIDO_PK e PEDIDO_DATA_PEDIDO_INDEX, agora temos os novos nomes: PEDIDO_2008_PKEY e PEDIDO_2008_DATA_PEDIDO_IDX.  Claro que o ‘2008’ deveria aparecer no nome, afinal o nome da tabela também mudou. No entanto o sufixo não é o mesmo.

Eu já tentei ter conversas amigáveis com um Administrador de Dados preocupado em como montar o DER com todas as partições. No final convenci a figura a ignorar as partições no DER totalmente. Se você conseguir fazer isso, você não vai se importar com o postgres adicionando nomes para os índices de acordo com o padrão dele – que é um padrão bem razoável por sinal. Tem gente que é muito apegada ao seu padrão de nomenclatura de objetos – nada contra. Para esses você tem dois jeitos de resolver: Use algo como ALTER INDEX pedido_2008_pkey RENAME TO pedido_2008_pk ou não utilize o INCLUDING INDEXES  e crie os índices na mão com os nomes corretos.

Diferenças na herança de parâmetros de storage

Se você prestar atenção, verá que os parâmetros “autovacuum_vacuum_scale_factor=0.1,fillfactor=70” da tabela PEDIDO, não apareceram na tabela PEDIDO_2008. Ocorre que a cláusula (LIKE INCLUDING STORAGE) só se refere aos parâmetros aplicados nas colunas e não na tabela. Ou seja, você vai ter de copiar estes parâmetros na mão como em:

SELECT 'ALTER TABLE app.pedido_2008 SET (' || array_to_string(c.reloptions,',') || ');' AS sql
FROM 
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE
    c.relname = 'pedido' AND 
    n.nspname = 'app';

Diferenças na herança dos comentários

Aqui ocorre algo semelhante ao que ocorre nos parâmetros de storage. Os comentários de coluna são herdados, mas os comentários de tabela não. Você não vê os comentários de tabela no comando do psql d+, mas vê no dt+ :

teste=# dt+ app.pedido*
                                List of relations
 Schema |      Name      | Type  |  Owner   |    Size    |      Description      
--------+----------------+-------+----------+------------+-----------------------
 app    | pedido         | table | app      | 8192 bytes | Tabela de pedidos foo
 app    | pedido_2008    | table | postgres | 8192 bytes |

Para dizer a verdade eu acho que importar comentários em partições algo que só serve para entulhar a sua base, mas se você realmente quer fazer isso, você pode rodar:

SELECT $$COMMENT ON TABLE app.pedido_2008 IS '$$ || description || $$';$$
FROM 
    pg_class c
    JOIN pg_namespace n   ON c.relnamespace = n.oid
    JOIN pg_description d ON c.oid = d.objoid
WHERE
    c.relname = 'pedido' AND 
    n.nspname = 'app'    AND
    d.objsubid = 0;

Diferenças na herança de permissões

Aqui o postgres ignora totalmente as permissões e não herda nada. Vamos ver como ficaram as permissões no psql com o comando dp:

teste=> dp app.pedido*
                                    Access privileges
 Schema |         Name         |   Type   | Access privileges | Column access privileges 
--------+----------------------+----------+-------------------+--------------------------
 app    | pedido               | table    | app=arwdDxt/app  +| 
        |                      |          | client=arw/app    | 
 app    | pedido_2008          | table    |                   |

Se bem que isto não chega a ser um problema, pelo menos não a partir da versão 9.0. Do PostgreSQL 9.0 em diante o comportamento padrão SQL passa a ser seguido: se você tem permissão na tabela PEDIDO, ao fazer uma operação nesta, as permissões não serão checadas novamente nas tabelas filhas, veja:

$ psql -U client teste
psql (9.2.2)

teste=> select * from app.pedido limit 1;
 ano_pedido | id_pedido |     data_pedido     | id_cliente | id_vendedor | status | observacao 
------------+-----------+---------------------+------------+-------------+--------+------------
       2008 |         5 | 2008-01-01 00:00:00 |          1 |           1 | A      | asdf
(1 row)

teste=> select * from app.pedido_2008 limit 1;
ERROR:  permission denied for relation pedido_2008
STATEMENT:  select * from app.pedido_2008 limit 1;

Ou seja, você não precisa dar permissão nas tabelas filhas, pois elas herdam as permissões da tabela mãe, mas somente se você fizer a operação na tabela mãe. Se tentar direto na tabela filha, a operação falha. Por outro lado, o usuário APP não é mais dono das partições. Se você precisar fazer algumas operações administrativas com as partições, precisará do superusuário. Então eu recomendo que você apenas troque o dono das tabelas filhas, como em:

SELECT 'ALTER TABLE app.pedido_2008 OWNER TO ' || a.rolname || ';'
FROM 
    pg_class c 
    JOIN pg_namespace n ON c.relnamespace = n.oid
    JOIN pg_authid a ON c.relowner = a.oid
WHERE
    n.nspname = 'app' AND 
    c.relname = 'pedido';

Claro, se você realmente quiser conceder todas as permissões da tabela PEDIDO diretamente nas tabelas filhas, você pode rodar algo como:

SELECT 'GRANT ' || privilege_type || ' ON app.pedido_2008 TO ' || grantee || 
    CASE is_grantable WHEN 'YES' THEN ' WITH GRANT OPTION' ELSE '' END || ';' sql
FROM information_schema.table_privileges 
WHERE 
    table_schema = 'app' AND
    table_name = 'pedido';

 Agora sim, vamos criar todas as partições

-- INCLUDING ALL só funciona a partir do PG 9.0
CREATE TABLE app.pedido_2008 (LIKE app.pedido INCLUDING ALL) INHERITS (app.pedido); 
CREATE TABLE app.pedido_2009 (LIKE app.pedido INCLUDING ALL) INHERITS (app.pedido); 
CREATE TABLE app.pedido_2010 (LIKE app.pedido INCLUDING ALL) INHERITS (app.pedido);
CREATE TABLE app.pedido_2011 (LIKE app.pedido INCLUDING ALL) INHERITS (app.pedido);
CREATE TABLE app.pedido_2012 (LIKE app.pedido INCLUDING ALL) INHERITS (app.pedido);
CREATE TABLE app.pedido_2013 (LIKE app.pedido INCLUDING ALL) INHERITS (app.pedido);

ALTER TABLE app.pedido_2008 DROP CONSTRAINT pedido_null_ck;
ALTER TABLE app.pedido_2009 DROP CONSTRAINT pedido_null_ck;
ALTER TABLE app.pedido_2010 DROP CONSTRAINT pedido_null_ck;
ALTER TABLE app.pedido_2011 DROP CONSTRAINT pedido_null_ck;
ALTER TABLE app.pedido_2012 DROP CONSTRAINT pedido_null_ck;
ALTER TABLE app.pedido_2013 DROP CONSTRAINT pedido_null_ck;

ALTER TABLE app.pedido_2008 ADD CONSTRAINT pedido_cliente_fk_2008  FOREIGN KEY (id_cliente) REFERENCES app.cliente(id_cliente);
ALTER TABLE app.pedido_2009 ADD CONSTRAINT pedido_cliente_fk_2009  FOREIGN KEY (id_cliente) REFERENCES app.cliente(id_cliente);
ALTER TABLE app.pedido_2010 ADD CONSTRAINT pedido_cliente_fk_2010  FOREIGN KEY (id_cliente) REFERENCES app.cliente(id_cliente);
ALTER TABLE app.pedido_2011 ADD CONSTRAINT pedido_cliente_fk_2011  FOREIGN KEY (id_cliente) REFERENCES app.cliente(id_cliente);
ALTER TABLE app.pedido_2012 ADD CONSTRAINT pedido_cliente_fk_2012  FOREIGN KEY (id_cliente) REFERENCES app.cliente(id_cliente);
ALTER TABLE app.pedido_2013 ADD CONSTRAINT pedido_cliente_fk_2013  FOREIGN KEY (id_cliente) REFERENCES app.cliente(id_cliente);

ALTER TABLE app.pedido_2008 ADD CONSTRAINT pedido_vendedor_fk_2008 FOREIGN KEY (id_vendedor) REFERENCES app.vendedor(id_vendedor);
ALTER TABLE app.pedido_2009 ADD CONSTRAINT pedido_vendedor_fk_2009 FOREIGN KEY (id_vendedor) REFERENCES app.vendedor(id_vendedor);
ALTER TABLE app.pedido_2010 ADD CONSTRAINT pedido_vendedor_fk_2010 FOREIGN KEY (id_vendedor) REFERENCES app.vendedor(id_vendedor);
ALTER TABLE app.pedido_2011 ADD CONSTRAINT pedido_vendedor_fk_2011 FOREIGN KEY (id_vendedor) REFERENCES app.vendedor(id_vendedor);
ALTER TABLE app.pedido_2012 ADD CONSTRAINT pedido_vendedor_fk_2012 FOREIGN KEY (id_vendedor) REFERENCES app.vendedor(id_vendedor);
ALTER TABLE app.pedido_2013 ADD CONSTRAINT pedido_vendedor_fk_2013 FOREIGN KEY (id_vendedor) REFERENCES app.vendedor(id_vendedor);

ALTER TABLE app.pedido_2008 SET (autovacuum_vacuum_scale_factor=0.1,fillfactor=70);
ALTER TABLE app.pedido_2009 SET (autovacuum_vacuum_scale_factor=0.1,fillfactor=70);
ALTER TABLE app.pedido_2010 SET (autovacuum_vacuum_scale_factor=0.1,fillfactor=70);
ALTER TABLE app.pedido_2011 SET (autovacuum_vacuum_scale_factor=0.1,fillfactor=70);
ALTER TABLE app.pedido_2012 SET (autovacuum_vacuum_scale_factor=0.1,fillfactor=70);
ALTER TABLE app.pedido_2013 SET (autovacuum_vacuum_scale_factor=0.1,fillfactor=70);

ALTER TABLE app.pedido_2008 OWNER TO app;
ALTER TABLE app.pedido_2009 OWNER TO app;
ALTER TABLE app.pedido_2010 OWNER TO app;
ALTER TABLE app.pedido_2011 OWNER TO app;
ALTER TABLE app.pedido_2012 OWNER TO app;
ALTER TABLE app.pedido_2013 OWNER TO app;

COMMENT ON TABLE app.pedido_2008 IS 'Tabela de pedidos foo';
COMMENT ON TABLE app.pedido_2009 IS 'Tabela de pedidos foo';
COMMENT ON TABLE app.pedido_2010 IS 'Tabela de pedidos foo';
COMMENT ON TABLE app.pedido_2011 IS 'Tabela de pedidos foo';
COMMENT ON TABLE app.pedido_2012 IS 'Tabela de pedidos foo';
COMMENT ON TABLE app.pedido_2013 IS 'Tabela de pedidos foo';

Ainda falta criar uma restrição que coloque os registros dos anos correspondentes em cada tabela filha:

ALTER TABLE app.pedido_2008 ADD CONSTRAINT pedido_check_2008 CHECK (ano_pedido = 2008);
ALTER TABLE app.pedido_2009 ADD CONSTRAINT pedido_check_2009 CHECK (ano_pedido = 2009);
ALTER TABLE app.pedido_2010 ADD CONSTRAINT pedido_check_2010 CHECK (ano_pedido = 2010);
ALTER TABLE app.pedido_2011 ADD CONSTRAINT pedido_check_2011 CHECK (ano_pedido = 2011);
ALTER TABLE app.pedido_2012 ADD CONSTRAINT pedido_check_2012 CHECK (ano_pedido = 2012);
ALTER TABLE app.pedido_2013 ADD CONSTRAINT pedido_check_2013 CHECK (ano_pedido = 2013);

Pronto, agora nossas tabelas filhas são partições da tabela PEDIDO. Se você realizar um SELECT na tabela PEDIDO, os registros das tabelas filhas serão lidos direitinho. Como demonstramos no post anterior, se você precisar dos registros que estão apenas em algumas partições, o PostgreSQL irá buscar as informações apenas nas partições correspondentes. Isso aumenta muito o desempenho da consulta. Claro, o parâmetro CONSTRAINT_EXCLUSION, precisa estar habilitado para isso ocorrer. Caso contrário a consulta irá sempre varrer todas as partições. No entanto, apenas operações de SELECT, UPDATE e DELETE estão prontas para serem utilizadas. Para inserir na tabela PEDIDO e gravar nas partições corretas, precisamos de um recurso adicional de desvio. Este pode ser implementado com um gatilho ou com uma regra.

Gatilho de INSERT

Aqui a fórmula é simples, criamos uma função para o gatilho onde desviamos o INSERT para a partição correta:

CREATE OR REPLACE FUNCTION app.pedido_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.ano_pedido = 2008 THEN 
        INSERT INTO app.pedido_2008 VALUES (NEW.*);
    ELSIF 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;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_pedido_trigger
    BEFORE INSERT ON app.pedido
    FOR EACH ROW EXECUTE PROCEDURE pedido_trigger();

 Regra de INSERT

Outra forma de fazer a mesma coisa é utilizando as regras do postgres. É um recurso pouco conhecido, mas pode ser utilizado para obter o mesmo efeito:

CREATE RULE pedido_2008_insert AS ON INSERT TO pedido 
    WHERE ( ano_pedido = 2008 )
    DO INSTEAD INSERT INTO app.pedido_2008 VALUES (NEW.*);

CREATE RULE pedido_2009_insert AS ON INSERT TO pedido 
    WHERE ( ano_pedido = 2009 )
    DO INSTEAD INSERT INTO app.pedido_2009 VALUES (NEW.*);

CREATE RULE pedido_2010_insert AS ON INSERT TO pedido 
    WHERE ( ano_pedido = 2010 )
    DO INSTEAD INSERT INTO app.pedido_2010 VALUES (NEW.*);

CREATE RULE pedido_2011_insert AS ON INSERT TO pedido 
    WHERE ( ano_pedido = 2011 )
    DO INSTEAD INSERT INTO app.pedido_2011 VALUES (NEW.*);

CREATE RULE pedido_2012_insert AS ON INSERT TO pedido 
    WHERE ( ano_pedido = 2012 )
    DO INSTEAD INSERT INTO app.pedido_2012 VALUES (NEW.*);

CREATE RULE pedido_2013_insert AS ON INSERT TO pedido 
    WHERE ( ano_pedido = 2013 )
    DO INSTEAD INSERT INTO app.pedido_2013 VALUES (NEW.*);

 Regra X gatilho

Qual a melhor forma de implementar, gatilho ou regra? A resposta para variar é depende. Vamos agora usar a função timing do psql para ver quem é mais rápido. Primeiro vamos testar com  um único INSERT carregando vários registros de uma vez só:

-- Utilizando REGRA
teste=# timing 
Timing is on.
teste=# INSERT INTO pedido SELECT 2008, nextval('pedido_id_pedido_seq'), s.a,1,1 FROM generate_series('2008-01-01'::timestamp, '2008-12-31'::timestamp, '2 minutes') AS s(a);
INSERT 0 0
Time: 3819,447 ms

-- Utilizando GATILHO
teste=# INSERT INTO pedido SELECT 2008, nextval('pedido_id_pedido_seq'), s.a,1,1 FROM generate_series('2008-01-01'::timestamp, '2008-12-31'::timestamp, '2 minutes') AS s(a);
INSERT 0 0
Time: 10869,557 ms

Aqui o que vemos é a REGRA ganhando de lavada de 3,8 segundos contra 10,9 segundos. Agora vamos ver o custo de inserir um único registro:

-- Utilizando REGRA
teste=# INSERT INTO pedido_2008 VALUES (2008,1,'2008-01-01',1,1);
INSERT 0 1
Time: 28,950 ms

-- Utilizando GATILHO
teste=# INSERT INTO pedido_2008 VALUES (2008,1,'2008-01-01',1,1);
INSERT 0 1
Time: 22,400 ms

Aqui a relação se inverte e o gatilho é ligeiramente mais rápido. Embora seja mais comum o uso do gatilho em particionamento, se você tem uma tabela que recebe mais cargas em lote (mas não com um único INSERT para cada registro), então o uso de regra lhe será vantajoso.

Outro detalhe é que quando você utiliza regras você precisa de uma regra para cada partição e o plano de execução sempre varre todas as regras. Desta forma, se você utilizar muitas consultas, o uso de regras começa a se tornar muito caro. Veja o plano de execução de um único registro para ver como isso acontece:

teste=# EXPLAIN ANALYZE INSERT INTO pedido VALUES (2008,3,'2008-01-01',1,1);
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Insert on pedido  (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
         One-Time Filter: false
 Total runtime: 0.065 ms

 Insert on pedido_2008  (cost=0.00..0.01 rows=1 width=0) (actual time=0.121..0.122 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.159 ms

 Insert on pedido_2009  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
         One-Time Filter: false
 Total runtime: 0.037 ms

 Insert on pedido_2010  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
         One-Time Filter: false
 Total runtime: 0.034 ms

 Insert on pedido_2011  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)
         One-Time Filter: false
 Total runtime: 0.032 ms

 Insert on pedido_2012  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
         One-Time Filter: false
 Total runtime: 0.034 ms

 Insert on pedido_2013  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)
         One-Time Filter: false
 Total runtime: 0.033 ms
(33 rows)

Embora a maior parte do tempo seja gasto com o INSERT na tabela PEDIDO_2008, o postgres perde tempo passando pelas demais tabelas também.

No próximo post vamos falar sobre mais detalhes da implementação do particionamento no PostgreSQL e vamos particionar também a tabela PEDIDO_DETALHE. Na sequência também vou demonstrar como automatizar a criação de partições para várias tabelas de uma vez só. Até a próxima.

6 comentários sobre “Particionamento de tabelas no postgres – Como?

    • Olá Marcelo, excelente o seu post.
      Tenho uma dúvida, na verdade uma pergunta.
      Pois na minha necessidade eu tenho uma carga muito grande para fazer.
      São 24 arquivos distribuídos por mês e ano, entre duas tabelas.
      Item_jan2012 – com aproximadamente 200 milhões de registros
      ItemPedido_jan2012 – com aproximadamente 1,2 bilhão de registros

      essa estrutura segue os meses seguintes: fev2012, mar_2012, abr_2012 e assim por diante.

      a minha pergunta é: Qual a melhor forma de eu particionar? por dia ou mês, uma vez que cada arquivo texto que usarei para popular as respectivas tabelas, já são por mês?

      Desde já agradeço.

      Ivan

      Curtir

      • Olá Ivan, parece que o melhor é particionar por mês mesmo. Veja que ter um volume muito grande de partições pequenas atrapalha ao invés de ajudar. Imagine que partições com até 50GB cada é um volume bastante razoável.

        Curtir

  1. Pingback: Particionamento de Tabelas no postgres – Quando? | Savepoint

  2. Pingback: Particionamento de Tabelas no postgres – Detalhes. | Savepoint

  3. Pingback: Particionamento de Tabelas no postgres – automatizando - Savepoint

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s