Particionamento / Partitioning No Postgres

Published: 2018-09-15, Updated: 2018-12-23

Tipos de particionamento

Declarative (A partir do postgres 10)

É o modelo mais simples, você define a chave da partição e o postgresql joga pra participação correta, você tem que criar indexes em cada partição que desejar, as partições tem que ter as exatamente as mesmas colunas da tabela mãe

Inheritance

Permite que as partições tenham mais colunas, força que as filhas tenham as mesmas constraints da Mae, voce pode fazer uma chave de partição baseada em regras mais customizadas do que apenas chave

Quando particionar

Referência 1 de quando particionar

Diz para particionar nos meados dos 100M, que seria uma tabela entre 5G e 100G

Em outros vi dizendo

A partir de 50M de registros começa a fazer sentido criar partições para reduzir o tempo de inserção, alteração, seleção

Dicas gerais de como particionar

https://medium.com/@StreamBright/creating-partitions-automatically-in-postgresql-7006d68c0fbb
https://stablekernel.com/storing-historical-data-with-postgresql-and-automatic-partitions/
https://www.postgresql.org/docs/10/static/ddl-partitioning.html
https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1
https://github.com/derkan/pg_party/blob/master/pg_party.sh

Passos

Crie a tabela
Crie uma função que cria uma partição
Crie uma trigger que chama a função que cria partição antes de inserir o registro, assim as partições serão criadas sob demanda

Particionar usando modelo de herança / Inheritance Partitioning

Constraints na mae refletem para as particoes mesmo que voce tente inserir diretamente na particao
inserts devem ser feitos na particao respectiva caso contrario ele será feita na tabela mae
updates podem ser feitos na tabela mae, ela vai saber direcionar o UPDATE para a tabela respectiva (ou para todas e cada uma atualizará o item que fizer match caso voce nao passe a particao correta`

-- PARTICIONAMENTO USANDO O MODELO DE HERANCA
CREATE TABLE CUSTOMER (
	IDT_CUSTOMER SERIAL, 
	DAT_CREATION DATE,
	NAM_USER TEXT
);

-- TABELAS FILHAS PODEM TER OUTRAS COLUNAS
CREATE TABLE CUSTOMER_2016 (NAM_CITY TEXT) INHERITS (CUSTOMER);

-- AS OUTRAS NAO NECESSARIAMENTE DEVE TER AS MESMAS COLUNAS, 
-- SOH DEVEM TER AS MESMAS COLUNAS QUE A MAE
CREATE TABLE CUSTOMER_2017 () INHERITS (CUSTOMER);

Nesse modelo voce precisa fazer o insert na particao especifica, se fizer o insert na tabela mae, ele ficará inserido nela e NÃO direcionado para a particao

INSERT INTO CUSTOMER (DAT_CREATION, NAM_USER) VALUES (NOW(), 'John'), (NOW(), 'Maria'), (DATE '2017-01-01', 'Miley');
INSERT INTO CUSTOMER_2016 (DAT_CREATION, NAM_USER) VALUES (DATE '2016-01-01', 'Eric');
INSERT INTO CUSTOMER_2017 (DAT_CREATION, NAM_USER) VALUES (DATE '2017-01-01', 'Kyle');

EXPLAIN 
	SELECT * FROM CUSTOMER;
Append  (cost=0.00..42.30 rows=2130 width=40) (actual time=0.012..0.012 rows=0 loops=1)
  ->  Seq Scan on customer  (cost=0.00..2.20 rows=120 width=40) (actual time=0.009..0.009 rows=0 loops=1)
  ->  Seq Scan on customer_2016  (cost=0.00..18.10 rows=810 width=40) (actual time=0.001..0.001 rows=0 loops=1)
  ->  Seq Scan on customer_2017  (cost=0.00..22.00 rows=1200 width=40) (actual time=0.001..0.001 rows=0 loops=1)
Planning time: 0.107 ms
Execution time: 0.035 ms

Mesmo que voce use filtros o postgres vai escanear em todas as particoes enquanto voce nao criar constraints para ensina-lo como fazer isso de uma forma melhor

EXPLAIN 
	SELECT * FROM CUSTOMER WHERE dat_creation = '2018-09-15';
Append  (cost=0.00..47.62 rows=11 width=40)
  ->  Seq Scan on customer  (cost=0.00..2.50 rows=1 width=40)
        Filter: (dat_creation = '2018-09-15'::date)
  ->  Seq Scan on customer_2016  (cost=0.00..20.12 rows=4 width=40)
        Filter: (dat_creation = '2018-09-15'::date)
  ->  Seq Scan on customer_2017  (cost=0.00..25.00 rows=6 width=40)
        Filter: (dat_creation = '2018-09-15'::date)

Depois de criar as constrains o postgres irá escanear apenas as particoes que fizerem match

ALTER TABLE CUSTOMER_2016
	ADD CHECK (DAT_CREATION >= '2016-01-01' AND DAT_CREATION < DATE '2017-01-01');

ALTER TABLE CUSTOMER_2017
	ADD CHECK (DAT_CREATION >= '2017-01-01' AND DAT_CREATION < DATE '2018-01-01');
	
EXPLAIN 
SELECT * FROM CUSTOMER WHERE DAT_CREATION = '2017-01-01';
Append  (cost=0.00..27.50 rows=7 width=40)
  ->  Seq Scan on customer  (cost=0.00..2.50 rows=1 width=40)
        Filter: (dat_creation = '2017-01-01'::date)
  ->  Seq Scan on customer_2017  (cost=0.00..25.00 rows=6 width=40)
        Filter: (dat_creation = '2017-01-01'::date)

Testando o indice em uma particao

CREATE INDEX CUSTOMER_2017_IDX1 ON CUSTOMER_2017(DAT_CREATION);

-- gerando dados para teste
INSERT INTO CUSTOMER_2017 (DAT_CREATION, NAM_USER) 
SELECT  
    (DATE '2017-12-31' - ( random() * INTERVAL '90 days' ))::timestamp,
    md5(random()::text)
  FROM generate_series(1,10000);

-- testando o indice
SELECT * FROM CUSTOMER WHERE DAT_CREATION = DATE '2017-01-01';
Append  (cost=0.00..22.46 rows=5 width=40) (actual time=0.013..0.035 rows=2 loops=1)
  ->  Seq Scan on customer  (cost=0.00..2.50 rows=1 width=40) (actual time=0.012..0.013 rows=1 loops=1)
        Filter: (dat_creation = '2017-01-01'::date)
        Rows Removed by Filter: 2
  ->  Bitmap Heap Scan on customer_2017  (cost=4.45..19.96 rows=4 width=40) (actual time=0.020..0.020 rows=1 loops=1)
        Recheck Cond: (dat_creation = '2017-01-01'::date)
        Heap Blocks: exact=1
        ->  Bitmap Index Scan on customer_2017_idx1  (cost=0.00..4.45 rows=4 width=0) (actual time=0.014..0.014 rows=4 loops=1)
              Index Cond: (dat_creation = '2017-01-01'::date)
Planning time: 0.320 ms
Execution time: 0.082 ms

Deletando a tabela mae

DROP TABLE CUSTOMER;
ERROR: cannot drop table customer because other objects depend on it
  Detail: table customer_2016 depends on table customer
table customer_2017 depends on table customer
  Hint: Use DROP ... CASCADE to drop the dependent objects too.
SQLState:  2BP01

Voce precisa deletar as particoes antes ou fazer um delete cascade

DROP TABLE CUSTOMER CASCADE;

Particionar usando modelo declarativo / Declarative Partitioning

Voce pode particionar usando ranges (datas, numeros), ou valores estaticos pre-definidos (textos identificadores, datas especificas, numeros especificos)
Voce nao pode criar duas particoes para a mesma tabela que se cruzem, ou seja, que façam overlap entre si
Voce pode fazer insert direto na tabela mae que ela irá direcionar o registro para a particao correta
Voce pode tirar e colocar outras ou as mesmas particoes quando quiser
Inserts que nao facam match com as particoes existentes irão tomar erro de insert
Constraints na tabela mae se aplicam para todas as filhas
Constrainsts podem ser criadas em particoes especificas
Os indices devem ser criados particao a particao

CREATE TABLE CUSTOMER (
	IDT_CUSTOMER SERIAL, 
	DAT_CREATION DATE,
	NAM_USER TEXT
) PARTITION BY RANGE (DAT_CREATION);

-- Nesse modelo as particoes nao podem ter colunas diferentes
CREATE TABLE CUSTOMER_2016
	PARTITION OF CUSTOMER FOR VALUES FROM ('2016-01-01') /* inclusivo */ TO ('2017-01-01') /* exclusivo */
;

CREATE TABLE CUSTOMER_2017 
	PARTITION OF CUSTOMER FOR VALUES FROM ('2017-01-01') TO ('2018-01-01')
;

Ao fazer o select o postgres irá parar de procurar assim que ver que a chave da partição não aponta para lugar nenhum, ao inves de fazer seq scan igual ao particionamento por herança

EXPLAIN
SELECT * FROM CUSTOMER WHERE dat_creation = '2018-09-15'
Result  (cost=0.00..0.00 rows=0 width=40) (actual time=0.001..0.001 rows=0 loops=1)
  One-Time Filter: false
Planning time: 0.121 ms
Execution time: 0.012 ms

Quando se faz o select por uma particao que exista, entao ele faz seq dentro dessa particao uma vez que ela nao tem indice

EXPLAIN
	SELECT * FROM CUSTOMER WHERE dat_creation = '2017-09-15'
Append  (cost=0.00..25.00 rows=6 width=40) (actual time=0.003..0.003 rows=0 loops=1)
  ->  Seq Scan on customer_2017  (cost=0.00..25.00 rows=6 width=40) (actual time=0.003..0.003 rows=0 loops=1)
        Filter: (dat_creation = '2017-09-15'::date)
Planning time: 0.123 ms
Execution time: 0.018 ms

Quando se cria o indice tudo passa a funcionar

-- 1 = BRUTO
-- 2 = SUMARIZADO POR DIA
-- 3 = SUMARIZADO POR MES
CREATE TABLE BSK_SKIN_SALE_HISTORY (
	DAT_OCCURRENCE TIMESTAMP NOT NULL,
	IND_TYPE INTEGER NOT NULL,
	NUM_PRICE DECIMAL(23, 4),
	COD_SKIN VARCHAR(255)
) PARTITION BY RANGE(DAT_OCCURRENCE, IND_TYPE)


CREATE TABLE BSK_SKIN_SALE_HISTORY_1_2018 PARTITION OF BSK_SKIN_SALE_HISTORY
	FOR VALUES FROM ('2018-01-01 ', 1) TO ('2018-12-31 ', 1);

CREATE INDEX BSK_SKIN_SALE_HISTORY_1_2018_IDX1 ON BSK_SKIN_SALE_HISTORY_1_2018(DAT_OCCURRENCE, IND_TYPE);

CREATE TABLE BSK_SKIN_SALE_HISTORY_2_2018 PARTITION OF BSK_SKIN_SALE_HISTORY
	FOR VALUES FROM ('2019-01-01 ', 2) TO ('2019-12-31 ', 2);

CREATE INDEX BSK_SKIN_SALE_HISTORY_2_2018_IDX1 ON BSK_SKIN_SALE_HISTORY_2_2018(DAT_OCCURRENCE, IND_TYPE);


INSERT INTO BSK_SKIN_SALE_HISTORY VALUES (DATE_TRUNC('DAY', NOW()), 2, 38.81, 'M4A1-S | Hot Rod (Factory New)');

EXPLAIN analyse
SELECT * FROM BSK_SKIN_SALE_HISTORY BSSH
WHERE BSSH.dat_occurrence < TIMESTAMP '2018-12-31 '
AND BSSH.ind_type = 1

Funcao para criar particoes automaticamente

CREATE OR REPLACE FUNCTION createSkinSalePartition (dateFrom TEXT, dateTo TEXT, type INTEGER)
RETURNS TEXT AS $func$
DECLARE
	partitionName text;
BEGIN
	partitionName := 'BSK_SKIN_SALE_HISTORY_' || type || '_' || REPLACE(dateFrom, '-', '_');
	EXECUTE (
		'CREATE TABLE ' || partitionName || ' PARTITION OF BSK_SKIN_SALE_HISTORY
			FOR VALUES FROM ('''|| dateFrom || ''', ' || type || ') TO (''' || dateTo || ''', ' || type || ');'
	);
	EXECUTE (
		'CREATE INDEX ' || partitionName || '_IDX_1 ON ' || partitionName || '(DAT_OCCURRENCE, IND_TYPE);'
	);
	EXECUTE (
		'CREATE INDEX ' || partitionName || '_IDX2 ON ' || partitionName || '(IDT_BSK_SKIN_SALE_HISTORY);'
	);
	RETURN 'partition created: ' || partitionName;
END;
$func$ LANGUAGE plpgsql;

keywords

particionando no postgres, postgres partitioning data, postgresql commands, postgres commands

Syslog commands Overclocking CPU power limit