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:

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);

Criando as tabelas filhas:

CREATE TABLE measurement_2019_04 PARTITION OF measurement
FOR VALUES FROM ('2019-04-01') TO (2019-04-30);

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:

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));

Criando tabelas filhas:

CREATE TABLE population_s PARTITION OF population
FOR VALUES IN ('s');

CREATE TABLE population_wxyz PARTITION OF population
FOR VALUES IN ('w','x','y','z');

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:

CREATE TABLE sensor_values (
sensor_id INTEGER NOT NULL,
"time" TIMESTAMP NOT NULL,
"value" NUMERIC
) PARTITION BY HASH (sensor_id);

CREATE TABLE sensor_values_0 PARTITION OF sensor_values
FOR VALUES WITH (MODULUS 4, REMAINDER 0);

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

CREATE TABLE sensor_values_default
PARTITION OF sensor_values DEFAULT;

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 Function
CREATE OR REPLACE FUNCTION create_population_partition()
RETURNS TRIGGER 
LANGUAGE plv8
VOLATILE
AS $$

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 filha
CREATE 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 criada
CREATE INDEX IF NOT EXISTS ${partition_table_name}_${data_input_id}_${primary_key_column }_index
ON ${schema}${partition_table_name}_${partiton_id}
USING BTREE (${primary_key_column });
`;

var res = plv8.execute(query);

return NEW;
$$;

-- Adicionando a trigger à tabela family_names
CREATE TRIGGER create_population_partition_trigger
    AFTER INSERT OR UPDATE 
    ON family_names
    FOR EACH ROW
    EXECUTE PROCEDURE create_population_partition();

Aleatitus