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.
