Compreendendo e Implementando o Particionamento Declarativo de Tabelas no PostgreSQL 14+

O particionamento no PostgreSQL vale a pena ser considerado quando uma tabela se torna difícil de consultar, executar vácuo, arquivar ou excluir. O exemplo usual é uma tabela de eventos que recebe milhões de linhas por dia e é quase sempre consultada por intervalo de tempo. Sem particionamento, mesmo índices bons podem deixar você com uma tabela cara de manter e dolorosa de envelhecer.

O particionamento declarativo permite que uma tabela lógica direcione linhas para tabelas físicas menores chamadas partições. O PostgreSQL 10 introduziu a sintaxe nativa, e versões posteriores melhoraram o planejamento, a poda, a indexação e o comportamento de manutenção. O PostgreSQL 14+ é maduro o suficiente para que muitas equipes possam usar o particionamento sem esquemas de herança baseados em gatilhos, mas ainda recompensa um design cuidadoso. Uma chave de partição ruim pode tornar o sistema mais complicado sem torná-lo mais rápido.

O que é Particionamento Declarativo de Tabelas?

O particionamento declarativo é um recurso de banco de dados que permite dividir uma única tabela lógica (a tabela pai ou particionada) em várias tabelas físicas (tabelas filhas ou partições) com base em um conjunto definido de regras. Cada partição contém um subconjunto dos dados da tabela pai. A chave de particionamento determina a qual partição uma linha pertence.

Os principais benefícios do particionamento declarativo incluem:

• Melhor Desempenho de Consulta: Consultas que filtram pela chave de particionamento podem ser mais rápidas porque o PostgreSQL pode podar partições que não podem conter linhas correspondentes.
• Gerenciamento de Dados Mais Fácil: Operações como excluir dados antigos ou arquivar podem ser realizadas de forma muito mais eficiente, desanexando ou descartando partições individuais, em vez de realizar operações massivas de DELETE em uma única tabela grande.
• Manutenção Simplificada: Indexação e vácuo podem ser gerenciados por partição, reduzindo o impacto em toda a tabela.
• Unidades de Manutenção Menores: Índices em nível de partição, operações de desanexação e vácuo direcionado podem reduzir o raio de explosão da manutenção de rotina.

Tipos de Particionamento Declarativo

O PostgreSQL suporta vários métodos para particionamento declarativo, cada um adequado a diferentes padrões de distribuição de dados:

1. Particionamento por Intervalo (Range)

O particionamento por intervalo divide os dados com base em um intervalo contínuo de valores em uma coluna específica (por exemplo, datas, números).

Caso de Uso: Ideal para dados de séries temporais, como logs, dados de eventos ou registros de vendas, onde você consulta frequentemente dados dentro de intervalos de data ou numéricos específicos.

Exemplo: Particionando uma tabela vendas pela coluna data_venda.

Criando uma Tabela Particionada por Intervalo

Primeiro, crie a tabela pai, especificando o método e a chave de particionamento:

CREATE TABLE vendas (
    venda_id SERIAL,
    nome_produto VARCHAR(100),
    valor_venda NUMERIC(10, 2),
    data_venda DATE NOT NULL
)
PARTITION BY RANGE (data_venda);

Em seguida, crie as partições individuais. Cada partição é definida com uma cláusula FOR VALUES especificando o intervalo que ela conterá.

-- Partição para vendas em janeiro de 2023.
-- O limite superior é exclusivo, então isso inclui 31 de janeiro.
CREATE TABLE vendas_2023_01
    PARTITION OF vendas ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- Partição para vendas em fevereiro de 2023
CREATE TABLE vendas_2023_02
    PARTITION OF vendas ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- Partição para vendas em março de 2023
CREATE TABLE vendas_2023_03
    PARTITION OF vendas ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

Dica: Ao definir intervalos, certifique-se de que sejam contíguos e cubram todos os valores possíveis. Evite intervalos sobrepostos. O valor TO é exclusivo.

2. Particionamento por Lista (List)

O particionamento por lista divide os dados com base em uma lista discreta de valores em uma coluna.

Caso de Uso: Adequado para colunas com um conjunto fixo e conhecido de valores, como regiões geográficas, códigos de status ou categorias de produtos.

Exemplo: Particionando uma tabela pedidos pela coluna regiao.

Criando uma Tabela Particionada por Lista

Defina a tabela pai com PARTITION BY LIST:

CREATE TABLE pedidos (
    pedido_id SERIAL,
    nome_cliente VARCHAR(100),
    total_pedido NUMERIC(10, 2),
    regiao VARCHAR(50) NOT NULL
)
PARTITION BY LIST (regiao);

Crie partições para regiões específicas:

-- Partição para pedidos na 'América do Norte'
CREATE TABLE pedidos_america_norte
    PARTITION OF pedidos ()
    FOR VALUES IN ('América do Norte');

-- Partição para pedidos na 'Europa'
CREATE TABLE pedidos_europa
    PARTITION OF pedidos ()
    FOR VALUES IN ('Europa');

-- Partição para pedidos na 'Ásia'
CREATE TABLE pedidos_asia
    PARTITION OF pedidos ()
    FOR VALUES IN ('Ásia');

Importante: Se você inserir um valor para regiao que não corresponda à lista IN de nenhuma partição existente e não houver uma partição DEFAULT, a inserção falhará. Você pode criar uma partição DEFAULT para capturar todos os outros valores.

Criando uma Partição Padrão

-- Partição padrão para qualquer região não listada explicitamente
CREATE TABLE pedidos_outros
    PARTITION OF pedidos ()
    DEFAULT;

3. Particionamento por Hash

O particionamento por hash distribui os dados por um número de partições com base em um valor hash da chave de particionamento.

Caso de Uso: Útil quando você tem um grande volume de dados e deseja distribuí-los uniformemente entre as partições sem uma distribuição clara baseada em intervalo ou lista. É bom para balanceamento de carga.

Exemplo: Particionando uma tabela usuarios por usuario_id.

Criando uma Tabela Particionada por Hash

Defina a tabela pai com PARTITION BY HASH e especifique o número de partições:

CREATE TABLE usuarios (
    usuario_id BIGSERIAL,
    nome_usuario VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (usuario_id);

O PostgreSQL criará automaticamente partições para você se não as especificar, mas geralmente é recomendado criá-las explicitamente, especialmente quando você deseja controle sobre o número e a nomenclatura das partições.

Criando Partições Hash Explícitas

-- Criar 4 partições hash
CREATE TABLE usuarios_p0
    PARTITION OF usuarios
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE usuarios_p1
    PARTITION OF usuarios
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE usuarios_p2
    PARTITION OF usuarios
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE usuarios_p3
    PARTITION OF usuarios
    FOR VALUES WITH (modulus 4, remainder 3);

Nota: Ao usar particionamento por hash, você precisa especificar o modulus (o número total de partições) e o remainder (qual partição é esta).

Implementando o Particionamento Declarativo: Melhores Práticas

• Escolha a Chave de Particionamento Correta: A chave de particionamento deve estar alinhada com seus filtros de consulta mais frequentes e operações de gerenciamento de dados. Uma boa chave melhora significativamente o desempenho.
• Considere o Número de Partições: Poucas partições podem não fornecer benefício suficiente, enquanto muitas podem aumentar a sobrecarga. Procure um número que equilibre gerenciabilidade e desempenho. Para particionamento por intervalo, considere sua taxa de crescimento de dados e políticas de retenção.
• Use pg_partman para Automação: Para particionamento por intervalo, especialmente com dados de séries temporais, considere usar extensões como pg_partman. Ela automatiza a criação de novas partições e o arquivamento/descarte das antigas, reduzindo significativamente o esforço manual.
• Indexe Estrategicamente: Os índices são armazenados fisicamente por partição. Criar um índice na tabela pai cria índices de partição correspondentes, mas você ainda deve verificar se cada partição precisa do mesmo padrão de índice.
• Poda de Partição: Certifique-se de que suas consultas sejam escritas para aproveitar a poda de partição, incluindo a chave de particionamento nas cláusulas WHERE. O comando EXPLAIN pode mostrar se a poda está ocorrendo.
• Partições DEFAULT: Para particionamento por lista, uma partição DEFAULT é crucial para evitar erros de inserção se novos valores aparecerem inesperadamente.
• Restrições Únicas: Uma restrição única ou chave primária em uma tabela particionada geralmente deve incluir todas as colunas da chave de partição. Isso pega muitos designs de primeira viagem de surpresa.
• Tipos de Dados: Certifique-se de que o tipo de dados da chave de particionamento seja apropriado e consistente entre as tabelas pai e filha.

Gerenciando Partições

Anexando e Desanexando Partições

Embora as partições sejam criadas diretamente via CREATE TABLE ... PARTITION OF ..., você também pode desanexar e anexar tabelas existentes como partições. Isso é útil para migrar dados ou gerenciar grandes conjuntos de dados.

Desanexando uma Partição: Desanexar transforma a partição em uma tabela regular, mantendo seus dados.

-- Desanexar a partição vendas_2023_01
ALTER TABLE vendas DETACH PARTITION vendas_2023_01;

Anexando uma Tabela como Partição: Você pode anexar uma tabela regular que esteja em conformidade com o esquema da tabela pai e tenha dados que se encaixem nos limites da partição.

-- Suponha que vendas_2022_12 seja uma tabela regular com as mesmas colunas que vendas
-- e apenas linhas de dezembro de 2022.
ALTER TABLE vendas ATTACH PARTITION vendas_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

Antes de anexar uma tabela grande, adicione primeiro uma restrição CHECK correspondente. O PostgreSQL pode usar essa restrição para evitar escanear toda a tabela para provar que as linhas se encaixam nos limites da partição.

Descartando Partições

Descartar uma partição é uma operação rápida, pois apenas remove a tabela de partição, não os dados dentro dela (a menos que especificado explicitamente). Isso é muito mais rápido que DELETE.

-- Para descartar uma partição, você pode simplesmente descartar a tabela filha
DROP TABLE vendas_2023_01;

Exemplo: Melhorando o Desempenho de Consultas com Poda de Partição

Considere a tabela vendas particionada por data_venda conforme mostrado anteriormente.

Consulta sem poda de partição (hipotética em uma tabela não particionada):

SELECT SUM(valor_venda)
FROM vendas
WHERE data_venda >= '2023-01-15' AND data_venda < '2023-01-20';

Se vendas fosse uma tabela enorme e não particionada, esta consulta escanearia a tabela inteira. No entanto, com o particionamento declarativo:

-- Esta consulta escaneará apenas a partição vendas_2023_01
SELECT SUM(valor_venda)
FROM vendas
WHERE data_venda >= '2023-01-15' AND data_venda < '2023-01-20';

O planejador de consultas do PostgreSQL reconhece que data_venda é a chave de particionamento e que o intervalo especificado cai inteiramente dentro da partição vendas_2023_01. Portanto, ele escaneará apenas essa partição, reduzindo drasticamente a E/S e melhorando o desempenho.

Para verificar isso, use EXPLAIN:

EXPLAIN SELECT SUM(valor_venda) FROM vendas WHERE data_venda >= '2023-01-15' AND data_venda < '2023-01-20';

A saída deve mostrar apenas a partição relevante, ou pode mostrar subplanos removidos, dependendo da versão do PostgreSQL e da forma do plano. O sinal importante é que partições não relacionadas não são escaneadas.

Uma Lista de Verificação de Design Prática

Particione apenas quando você puder nomear o ganho operacional. "A tabela é grande" não é suficiente por si só. Uma tabela grande com consultas pontuais bem indexadas pode ser suficiente. O particionamento faz mais sentido quando a maioria das consultas inclui a chave de partição, quando os dados antigos são arquivados ou descartados regularmente, ou quando a manutenção em uma tabela enorme já está causando dor.

Para tabelas de séries temporais, escolha tamanhos de partição que correspondam aos seus padrões de consulta e retenção. Partições diárias são úteis para ingestão muito alta e retenção curta. Partições mensais são frequentemente mais fáceis de gerenciar para volume de eventos moderado. Muitas partições minúsculas podem desacelerar o planejamento e tornar a manutenção ruidosa; poucas partições gigantes podem não resolver o problema original.

Planeje as inserções antes de implantar. Se as linhas podem chegar atrasadas, mantenha as partições mais antigas disponíveis por tempo suficiente para recebê-las. Se a chave de partição pode conter valores inesperados, crie uma partição DEFAULT e monitore-a. Uma partição padrão deve ser uma rede de segurança, não um lugar onde dados esquecidos se acumulam silenciosamente por meses.

Finalmente, teste com formas de consulta reais. A poda de partição funciona melhor quando a cláusula WHERE expõe a chave de partição claramente, como data_venda >= '2023-01-01' AND data_venda < '2023-02-01'. Encapsular a chave em funções pode dificultar a poda:

-- Menos amigável para poda
WHERE date_trunc('month', data_venda) = DATE '2023-01-01';

-- Mais fácil para o planejador
WHERE data_venda >= DATE '2023-01-01'
  AND data_venda <  DATE '2023-02-01';

O particionamento declarativo é uma ferramenta de manutenção tanto quanto uma ferramenta de consulta. Bem usado, torna os dados antigos baratos de remover e os dados quentes mais fáceis de escanear. Usado casualmente, adiciona mais tabelas, mais índices e mais casos extremos. Comece com o padrão de acesso, escolha a chave de partição a partir desse padrão e verifique o plano antes de considerar o design finalizado.

Para uma tabela grande existente, não planeje uma conversão arriscada de uma só vez durante o pico de tráfego. Um caminho de migração comum é criar uma nova tabela particionada, copiar dados em blocos, manter novas gravações fluindo através da lógica do aplicativo ou de um gatilho cuidadosamente testado e, em seguida, trocar os nomes durante uma janela de manutenção curta. A abordagem exata depende do volume de gravação e da tolerância ao tempo de inatividade, mas o princípio é o mesmo: prove a cópia, prove as restrições e ensaie a troca antes de tocar na produção.