O PostgreSQL 10 foi lançado no início de outubro de 2017, quase um ano atrás.
Um dos novos "recursos" mais interessantes é o particionamento incondicionalmente declarativo. Mas e se você não tiver pressa para atualizar para 10k? A Amazon, por exemplo, não tem pressa e
introduziu o suporte ao PostgreSQL 10 apenas nos últimos dias de fevereiro de 2018.
Então, o bom e velho particionamento por herança vem em socorro. Como sou arquiteto de software do departamento financeiro de uma empresa de táxi, todos os exemplos serão relacionados a viajar de uma maneira ou de outra (deixaremos os problemas financeiros em outro momento).
Desde que começamos a reescrever nosso sistema financeiro em 2015, quando eu entrei na empresa, não se falava em particionamento declarativo. Até hoje, a técnica descrita abaixo foi usada com sucesso.
O motivo original para escrever este artigo foi que a maioria dos exemplos de particionamento no PostgreSQL que encontrei eram muito básicos. Aqui está uma tabela, aqui está uma coluna que estamos vendo, e talvez até saibamos antecipadamente quais valores estão nela. Parece que tudo é simples. Mas a vida real faz seus próprios ajustes.
No nosso caso, particionamos tabelas em duas colunas, uma das quais contém datas de viagem. É este o caso que consideraremos.
Vamos começar com a aparência da nossa tabela:
create table rides ( id bigserial not null primary key, tenant_id varchar(20) not null, ride_id varchar(36) not null, created_at timestamp with time zone not null, metadata jsonb
Para cada inquilino, a tabela contém milhões de linhas por mês. Felizmente, os dados entre os inquilinos nunca se cruzam e as solicitações mais difíceis são feitas no corte de um ou dois meses.
Para aqueles que não se aprofundaram no funcionamento das partições do PostgreSQL (sorte da Oracle, olá!), Descreverei brevemente o processo.
O PostgreSQL conta com dois de seus "recursos" para isso: a capacidade de herdar tabelas, a herança de tabelas e as condições verificadas.
Vamos começar com a herança. Usando a palavra-chave INHERITS, indicamos que a tabela que criamos herda todos os campos da tabela herdada. Isso também cria um relacionamento entre as duas tabelas: fazendo uma consulta do pai, também obtemos todos os dados dos filhos.
As condições verificadas complementam a imagem, garantindo que os dados não se cruzem. Assim, o otimizador do PostgreSQL pode cortar parte das tabelas filho, confiando nos dados da consulta.
A primeira armadilha dessa abordagem parece bastante óbvia: qualquer solicitação deve conter tenant_id. E, no entanto, se você não se lembrar disso constantemente, mais cedo ou mais tarde, você mesmo escreverá um SQL personalizado no qual se esquecerá de especificar esse tenant_id. Como resultado, uma varredura de todas as partições e um banco de dados que não está funcionando.
Mas voltando ao que queremos alcançar. No nível do aplicativo, eu gostaria de transparência - sempre escrevemos na mesma tabela e o banco de dados já escolhe onde exatamente colocar esses dados.
Para fazer isso, usamos o seguinte procedimento armazenado:
CREATE OR REPLACE FUNCTION insert_row() RETURNS TRIGGER AS $BODY$ DECLARE partition_env TEXT; partition_date TIMESTAMP; partition_name TEXT; sql TEXT; BEGIN
A primeira coisa que você deve prestar atenção é o uso do TG_TABLE_NAME. Como isso é um gatilho, o PostgreSQL preenche algumas variáveis para nós que podemos acessar. A lista completa pode ser
encontrada aqui .
No nosso caso, queremos obter o nome do pai da tabela na qual o gatilho funcionou. No nosso caso, serão passeios. Utilizamos uma abordagem semelhante em vários microsserviços, e essa parte pode ser transferida praticamente sem alterações.
PERFORM é útil se quisermos chamar uma função que não retorna nada. Normalmente, em exemplos, eles tentam colocar toda a lógica em uma função, mas tentamos ter cuidado.
USING NEW indica que nesta consulta usamos os valores da string que tentamos adicionar.
$1.* expandirá todos os valores da nova linha. De fato, isso pode ser traduzido para
NEW.* . O que se traduz em
NEW.ID, NEW.TENANT_ID, …O procedimento a seguir, que chamamos de
PERFORM , criará uma nova partição, se ela ainda não existir. Isso acontecerá uma vez por período para cada inquilino.
CREATE OR REPLACE FUNCTION create_new_partition(parent_table_name text, env text, partition_date timestamp, partition_name text) RETURNS VOID AS $BODY$ DECLARE sql text; BEGIN
Como descrito anteriormente, usamos
INHERITS para criar uma tabela semelhante ao pai e
CHECK para determinar quais dados devem ir para lá.
RAISE NOTICE apenas imprime uma string no console. Se agora rodarmos
INSERT partir do psql, podemos ver se a partição foi criada.
Temos um novo problema.
INHERITS não herda índices. Para fazer isso, temos duas soluções:
Crie índices usando herança:
Use
CREATE TABLE LIKE e
ALTER TABLE INHERITSOu crie índices proceduralmente:
CREATE OR REPLACE FUNCTION index_partition(partition_name text) RETURNS VOID AS $BODY$ BEGIN
É muito importante não esquecer a indexação de tabelas filho, porque mesmo após o particionamento, cada uma delas terá milhões de linhas. Os índices no pai não são necessários no nosso caso, pois o pai sempre permanecerá vazio.
Por fim, criamos um gatilho que será chamado quando uma nova linha for criada:
CREATE TRIGGER before_insert_row_trigger BEFORE INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE insert_row();
Há outra sutileza na qual raramente prestamos atenção. O particionamento é melhor em colunas em que os dados nunca são alterados. No nosso caso, isso funciona: a viagem nunca muda tenant_id e created_at. O problema que surgir, se não for o caso, é que o PostreSQL não retornará parte dos dados para nós. Prometemos-lhe, então, VERIFICAR que todos os dados são válidos.
Existem várias soluções (exceto o óbvio - não modifique os dados pelos quais estamos particionando):
Em vez de
UPDATE sempre executamos
DELETE+INSERT no nível do aplicativo
Adicionamos mais um gatilho em
UPDATE que transferirá dados para a partição correta
Outra ressalva que vale a pena considerar é como indexar corretamente as colunas que contêm datas. Se usarmos AT TIME ZONE em consultas, não devemos esquecer que, na verdade, é uma chamada de função. E isso significa que nosso índice deve ser baseado em funções. Eu esqueci Como resultado, a base está morta novamente da carga.
O último aspecto que vale a pena considerar é como as partições interagem com várias estruturas ORM, seja ActiveRecord no Ruby ou GORM in Go.
Partições no PostgreSQL dependem do fato de que a tabela pai sempre estará vazia. Se você não usar o ORM, poderá retornar com segurança ao primeiro procedimento armazenado e alterar RETURN NEW; em RETURN NULL; Em seguida, a linha na tabela pai simplesmente não será adicionada, exatamente o que queremos.
Mas o fato é que a maioria dos ORMs usa a cláusula RETURNING com INSERT. Se retornarmos NULL do nosso gatilho, o ORM entrará em pânico, acreditando que a linha não foi adicionada. É adicionado, mas não para onde o ORM está olhando.
Existem várias maneiras de contornar isso:
- Não use ORM para INSERTs
- Patch ORM (que às vezes é recomendado no caso do ActiveRecord)
- Adicione outro gatilho, que removerá a linha do pai.
A última opção é indesejável, porque para cada operação executaremos três. No entanto, às vezes é inevitável, porque o consideraremos separadamente:
CREATE OR REPLACE FUNCTION delete_parent_row() RETURNS TRIGGER AS $BODY$ DECLARE BEGIN delete from only rides where id = NEW.ID; RETURN null; END; $BODY$ LANGUAGE plpgsql;
CREATE TRIGGER after_insert_row_trigger AFTER INSERT ON rides FOR EACH ROW EXECUTE PROCEDURE delete_parent_row();
A última coisa que precisamos fazer é testar nossa solução. Para fazer isso, geramos um certo número de linhas:
DO $script$ DECLARE year_start_epoch bigint := extract(epoch from '20170101'::timestamptz at time zone 'UTC'); delta bigint := extract(epoch from '20171231 23:59:59'::timestamptz at time zone 'UTC') - year_start_epoch; tenant varchar; tenants varchar[] := array['tenant_a', 'tenant_b', 'tenant_c', 'tenant_d']; BEGIN FOREACH tenant IN ARRAY tenants LOOP FOR i IN 1..100000 LOOP insert into rides (tenant_id, created_at, ride_id) values (tenant, to_timestamp(random() * delta + year_start_epoch) at time zone 'UTC', i); END LOOP; END LOOP; END $script$;
E vamos ver como o banco de dados se comporta:
explain select * from rides where tenant_id = 'tenant_a' and created_at AT TIME ZONE 'UTC' > '20171102' and created_at AT TIME ZONE 'UTC' <= '20171103';
Se tudo desse certo, veríamos o seguinte resultado:
Append (cost=0.00..4803.76 rows=4 width=196) -> Seq Scan on rides (cost=0.00..4795.46 rows=3 width=196) Filter: (((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone) AND ((tenant_id)::text = 'tenant_a'::text)) -> Index Scan using rides_tenant_a_2017_11_tenant_timezone_idx on rides_tenant_a_2017_11 (cost=0.28..8.30 rows=1 width=196) Index Cond: (((tenant_id)::text = 'tenant_a'::text) AND ((created_at)::timestamp without time zone > '2017-11-02 00:00:00'::timestamp without time zone) AND ((created_at)::timestamp without time zone <= '2017-11-03 00:00:00'::timestamp without time zone)) (5 rows)
Apesar de cada inquilino ter centenas de milhares de linhas, apenas selecionamos a fatia de dados desejada. Sucesso!
Espero que este artigo seja interessante para aqueles que ainda não estão familiarizados com o que é o particionamento e como ele é implementado no PostgreSQL. Mas aqueles para quem esse tópico não é mais novo, aprenderam alguns truques interessantes.
UPD:
Como o
bigtrot observou corretamente, toda essa mágica de rua não funcionará se a configuração
CONSTRAINT_EXCLUSION estiver desativada.
Você pode verificar isso usando o comando
show CONSTRAINT_EXCLUSION
A configuração possui três valores: ativado, desativado e particionado
A configuração da partição é mais ideal se você de repente usar o CHECK CONSTRAINTS, não apenas para partições, mas também para normalização de dados.