Partições no PostgreSQL

A partir do PostgreSQL 10, o banco passou a suportar partições nativamente. Partições são formas de quebrar uma grande tabela em subtabelas. Fazer partições em uma tabela pode melhorar performance em inserts e selects. Para que uma tabela seja particionada, ela precisa ter uma regra que dirá, com base no valor de alguma coluna, em qual partição uma linha irá ser colocada.

Formas de particionar uma tabela

Partição por Range

Criando a tabela mãe:

[code language="sql"] CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate); [/code]

Criando as tabelas filhas:

[code language="sql"] CREATE TABLE measurement_2019_04 PARTITION OF measurement FOR VALUES FROM ('2019-04-01') TO (2019-04-30); [/code]

Partição por List

A partição por List usa um índice BTree (Árvore B) para localizar em qual partição queremos acessar em uma consulta.

Criando tabela mãe:

[code language="sql"] CREATE TABLE population ( user_id BIGINT NOT NULL, family_name TEXT NOT NULL, first_name TEXT NOT NULL, age INTEGER ) PARTITION BY LIST (left(lower(family_name), 1)); [/code]

Criando tabelas filhas:

[code language="sql"] CREATE TABLE population_s PARTITION OF population FOR VALUES IN ('s'); [/code]

[code language="sql"] CREATE TABLE population_wxyz PARTITION OF population FOR VALUES IN ('w','x','y','z'); [/code]

Partição por Hash

Funciona apenas a partir do PostgreSQL 11.
A partição por Hash usa uma função Hash para dividir as partições. Para usar a partição hash você precisa saber de antemão quantas partições você deseja criar no total. É preciso informar um número chamado de MODULUS e outro número chamado de REMAINDER. O MODULUS é o número usado para dividir a coluna da partição. O REMAINDER é o resto, resultado da divisão inteira do valor da coluna da partição pelo MODULUS. Se temos um MODULUS 5, teremos no máximo 5 partições: as de REMAINDER 0, 1, 2, 3, 4.

Criando tabela mãe:

[code language="sql"] CREATE TABLE sensor_values ( sensor_id INTEGER NOT NULL, "time" TIMESTAMP NOT NULL, "value" NUMERIC ) PARTITION BY HASH (sensor_id); [/code]

[code language="sql"] CREATE TABLE sensor_values_0 PARTITION OF sensor_values FOR VALUES WITH (MODULUS 4, REMAINDER 0); [/code]

Criando uma partição Default

Se ao inserir uma nova linha numa tabela particionada, a linha não se encaixar em nenhum lugar, a inserção poderá lançar um erro.

Para que você consiga inserir dados que não se encaixem nas regras, você pode criar uma partição default

[code language="sql"] CREATE TABLE sensor_values_default PARTITION OF sensor_values DEFAULT; [/code]

Referências

Escalando partições no PostgreSQL 10
https://blog.timescale.com/scaling-partitioning-data-postgresql-10-explained-cd48a712a9a1

Documentação oficial sobre partições no PostgreSQL
https://www.postgresql.org/docs/current/static/ddl-partitioning.html

Ótimo tutorial sobre partições no PostgreSQL 10
https://blog.2ndquadrant.com/scaling-iot-time-series-data-postgres-bdr/

Extensão partman que gerencia partições automaticamente
https://github.com/pgpartman/pg_partman
https://pgxn.org/dist/pg_partman/doc/pg_partman.html

Possibilidades na sintaxe do CREATE TABLE com partições
https://www.postgresql.org/docs/current/static/sql-createtable.html

Melhorias no sistema de partições do PostgreSQL 10 para o PostgreSQL 11
https://severalnines.com/blog/how-take-advantage-new-partitioning-features-postgresql-11
http://www.pateldenish.com/2018/11/postgres-11-partitioning.html

Criando partições dinamicamente via triggers
https://read.acloud.guru/how-to-partition-dynamically-in-postgresql-ce3acbaef66c

Acessando tabelas onde as partições são criadas no PostgreSQL
https://dba.stackexchange.com/questions/223327/how-to-identify-the-column-used-to-partition-a-table-from-the-postgres-system-ca

Usando o pg_partman, extensão para gerenciar partições automaticamente
https://blog.heroku.com/handling-very-large-tables-in-postgres-using-partitioning

Criando partições depois que uma partição DEFAULT é criada
https://www.enterprisedb.com/blog/default-partition-adopting-odds

Exemplos práticos

Criando uma partição com uma tabela default

CREATE TABLE IF NOT EXISTS pd.pd_1_1_1            (                sensor_id integer NOT NULL,                "start" timestamptz NOT NULL,                "end" timestamptz,                "int" integer,                "double" double precision,                "text" text,                "json" jsonb,                PRIMARY KEY ("sensor_id", "start")			) PARTITION BY RANGE ("start")            ;CREATE TABLE IF NOT EXISTS pd.pd_1_1_1_defalt PARTITION OF pd.pd_1_1_1 DEFAULT-- FOR VALUES FROM x to y;

Criando partições automaticamente via trigger

Imagine o caso em que você tem uma tabela que conterá uma lista com todas as partições (nesse caso chamada family_names). Cada insert ou update nessa tabela criará uma nova partição. Para isso funcionar automaticamente é preciso criar uma trigger function e uma trigger para essa tabela.

-- Criando a Trigger FunctionCREATE OR REPLACE FUNCTION create_population_partition()RETURNS TRIGGER LANGUAGE plv8VOLATILEAS $$var partition_table_name = "population";var schema = "your_schema.";var partition_id = NEW.family_name;var primary_key_column = "user_id";var query = `-- Criando a tabela mãe caso ela não exista.CREATE TABLE IT NOT EXISTS ${your_schema}${partition_table_name}(user_id BIGINT NOT NULL,family_name TEXT NOT NULL,first_name TEXT NOT NULL,age INTEGER) PARTITION BY LIST (family_name);-- Criando a tabela filhaCREATE TABLE IF NOT EXISTS ${schema}${partition_table_name}_${partiton_id}PARTITION OF ${schema}${partition_table_name}FOR VALUES IN (${partiton_id});-- Adicionando uma chave primária na tabela filha (ela será única apenas dentro da partição)ALTER TABLE ${schema}${partition_table_name}_${partiton_id}ADD PRIMARY KEY (${primary_key_column});-- Adicionando um índice na partição recém criada e à chave primária recém criadaCREATE INDEX IF NOT EXISTS ${partition_table_name}_${data_input_id}_${primary_key_column }_indexON ${schema}${partition_table_name}_${partiton_id}USING BTREE (${primary_key_column });`;var res = plv8.execute(query);return NEW;$$;-- Adicionando a trigger à tabela family_namesCREATE TRIGGER create_population_partition_trigger    AFTER INSERT OR UPDATE     ON family_names    FOR EACH ROW    EXECUTE PROCEDURE create_population_partition();