Particionamento de tabelas

1. Conceitos Fundamentais de Particionamento

Particionamento de tabelas é uma técnica de design de banco de dados que divide logicamente uma tabela grande em partes menores e mais gerenciáveis, chamadas partições, mantendo uma única interface lógica para consultas e manipulações. Cada partição armazena um subconjunto dos dados com base em uma chave de particionamento, como data, região geográfica ou faixa de valores.

Diferença entre Particionamento Horizontal e Vertical

  • Particionamento horizontal: Divide as linhas da tabela em diferentes partições. Cada partição contém um subconjunto de linhas com a mesma estrutura de colunas. É o tipo mais comum e será o foco deste artigo.
  • Particionamento vertical: Divide as colunas da tabela, separando atributos frequentemente acessados daqueles raramente usados. Cada partição vertical contém um subconjunto de colunas, mantendo a mesma chave primária.

Benefícios do Particionamento

  • Melhoria de desempenho em consultas: O otimizador pode ignorar partições inteiras (pruning) quando a condição WHERE filtra pela chave de particionamento, reduzindo drasticamente a quantidade de dados varridos.
  • Manutenção eficiente: Operações como exclusão em massa, backup e restore podem ser realizadas em partições individuais, evitando bloqueios prolongados na tabela inteira.
  • Gerenciamento de dados volumosos: Facilita o arquivamento de dados antigos e a gestão do ciclo de vida dos dados, removendo partições inteiras em vez de executar DELETE em milhões de linhas.

2. Tipos de Particionamento no PostgreSQL

O PostgreSQL oferece três métodos nativos de particionamento:

Particionamento por Intervalo (RANGE)

Divide os dados com base em faixas contínuas de valores, como datas ou IDs numéricos.

CREATE TABLE vendas (
    id_venda SERIAL,
    data_venda DATE NOT NULL,
    valor DECIMAL(10,2)
) PARTITION BY RANGE (data_venda);

Cenário ideal: Dados históricos, logs de eventos, séries temporais.

Particionamento por Lista (LIST)

Agrupa dados com base em valores específicos de uma coluna.

CREATE TABLE clientes (
    id_cliente SERIAL,
    regiao TEXT NOT NULL,
    nome TEXT
) PARTITION BY LIST (regiao);

Cenário ideal: Dados categorizados por região geográfica, status, tipo de produto.

Particionamento por Hash (HASH)

Distribui dados uniformemente entre partições usando uma função hash.

CREATE TABLE transacoes (
    id_transacao SERIAL,
    hash_key INT
) PARTITION BY HASH (hash_key);

Cenário ideal: Distribuição uniforme de carga, tabelas muito grandes sem chave natural óbvia.

Comparação entre os Tipos

Tipo Vantagens Desvantagens Uso Típico
RANGE Pruning eficiente por intervalo, fácil manutenção temporal Pode criar partições desbalanceadas se a distribuição não for uniforme Séries temporais
LIST Agrupamento lógico claro Número limitado de partições, pode gerar partições muito grandes Categorias fixas
HASH Distribuição uniforme Sem pruning significativo, difícil manutenção semântica Balanceamento de carga

3. Criando Tabelas Particionadas na Prática

Sintaxe Básica

Primeiro, definimos a tabela mestre com a cláusula PARTITION BY:

CREATE TABLE logs_acesso (
    id_log BIGSERIAL,
    usuario_id INT NOT NULL,
    data_acesso TIMESTAMP NOT NULL,
    pagina TEXT,
    ip_acesso INET
) PARTITION BY RANGE (data_acesso);

Criação de Partições Filhas

Em seguida, criamos as partições individuais:

CREATE TABLE logs_acesso_2024_01 PARTITION OF logs_acesso
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_acesso_2024_02 PARTITION OF logs_acesso
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE logs_acesso_2024_03 PARTITION OF logs_acesso
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

Exemplo Completo: Particionamento por Mês/Ano

-- Tabela mestre
CREATE TABLE pedidos (
    id_pedido SERIAL,
    data_pedido DATE NOT NULL,
    cliente_id INT,
    valor_total DECIMAL(12,2),
    status TEXT
) PARTITION BY RANGE (data_pedido);

-- Partições mensais para 2024
CREATE TABLE pedidos_2024_01 PARTITION OF pedidos
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE pedidos_2024_02 PARTITION OF pedidos
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE pedidos_2024_03 PARTITION OF pedidos
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE pedidos_2024_04 PARTITION OF pedidos
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

4. Gerenciamento de Partições

Adicionando Novas Partições Dinamicamente

-- Adicionar partição para maio de 2024
CREATE TABLE pedidos_2024_05 PARTITION OF pedidos
    FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');

Removendo Partições Antigas

-- Remover dados de janeiro de 2023 (mais eficiente que DELETE)
DROP TABLE pedidos_2023_01;

Anexando e Desanexando Partições

-- Desanexar uma partição existente (torna-se tabela independente)
ALTER TABLE pedidos DETACH PARTITION pedidos_2024_01;

-- Anexar uma tabela existente como partição
ALTER TABLE pedidos ATTACH PARTITION pedidos_2024_06
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

Dividindo e Mesclando Partições

Para dividir uma partição, é necessário desanexá-la, criar novas partições e migrar os dados:

-- Dividir partição de janeiro em duas quinzenas
ALTER TABLE pedidos DETACH PARTITION pedidos_2024_01;

CREATE TABLE pedidos_2024_01a PARTITION OF pedidos
    FOR VALUES FROM ('2024-01-01') TO ('2024-01-16');
CREATE TABLE pedidos_2024_01b PARTITION OF pedidos
    FOR VALUES FROM ('2024-01-16') TO ('2024-02-01');

INSERT INTO pedidos_2024_01a SELECT * FROM pedidos_2024_01
    WHERE data_pedido < '2024-01-16';
INSERT INTO pedidos_2024_01b SELECT * FROM pedidos_2024_01
    WHERE data_pedido >= '2024-01-16';

DROP TABLE pedidos_2024_01;

5. Índices, Constraints e Chaves em Tabelas Particionadas

Índices Locais vs. Globais

No PostgreSQL, os índices são criados em cada partição individualmente:

-- Índice local em cada partição
CREATE INDEX idx_pedidos_data ON pedidos_2024_01 (data_pedido);
CREATE INDEX idx_pedidos_data ON pedidos_2024_02 (data_pedido);

-- Alternativa: criar índice na tabela mestre (propaga para todas as partições)
CREATE INDEX idx_pedidos_data ON pedidos (data_pedido);

Constraints de Chave Primária e Unicidade

A chave primária deve incluir a coluna de particionamento:

CREATE TABLE pedidos (
    id_pedido SERIAL,
    data_pedido DATE NOT NULL,
    cliente_id INT,
    PRIMARY KEY (id_pedido, data_pedido)
) PARTITION BY RANGE (data_pedido);

Foreign Keys

Tabelas particionadas podem referenciar outras tabelas, mas não podem ser referenciadas por foreign keys de outras tabelas:

CREATE TABLE itens_pedido (
    id_item SERIAL,
    pedido_id INT,
    data_pedido DATE,
    FOREIGN KEY (pedido_id, data_pedido) REFERENCES pedidos (id_pedido, data_pedido)
);

6. Consultas e Performance em Tabelas Particionadas

Como o Planner Utiliza o Pruning

O PostgreSQL analisa a condição WHERE para eliminar partições desnecessárias:

EXPLAIN SELECT * FROM pedidos WHERE data_pedido = '2024-02-15';
-- Saída esperada: apenas a partição pedidos_2024_02 será escaneada

Exemplo com e sem Particionamento

Sem particionamento (tabela com 100 milhões de linhas):

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE data_pedido BETWEEN '2024-01-01' AND '2024-01-31';
-- Seq Scan on pedidos (cost=0.00..2000000.00 rows=1000000 width=100)
-- Tempo: 4500 ms

Com particionamento (partições mensais de ~8 milhões de linhas):

EXPLAIN ANALYZE SELECT * FROM pedidos WHERE data_pedido BETWEEN '2024-01-01' AND '2024-01-31';
-- Seq Scan on pedidos_2024_01 (cost=0.00..150000.00 rows=800000 width=100)
-- Tempo: 350 ms

Estratégias para Otimizar Consultas

  • Sempre incluir a chave de particionamento nas condições WHERE
  • Evitar funções na coluna de particionamento: WHERE DATE_TRUNC('month', data_pedido) = '2024-01-01' impede pruning
  • Usar índices compostos que incluam a chave de particionamento

Limitações

  • Joins complexos podem não se beneficiar totalmente do pruning
  • Subconsultas correlacionadas podem escanear todas as partições
  • UPDATE/DELETE que alteram a chave de particionamento são proibidos

7. Manutenção e Boas Práticas

Estratégias de Backup e Restore

-- Backup de partições específicas
pg_dump -t pedidos_2024_01 -t pedidos_2024_02 meu_banco > backup_pedidos.sql

-- Restore
psql meu_banco < backup_pedidos.sql

Automação com pg_partman

A extensão pg_partman automatiza a criação e manutenção de partições:

CREATE EXTENSION pg_partman;

SELECT partman.create_parent(
    p_parent_table := 'public.pedidos',
    p_control := 'data_pedido',
    p_type := 'native',
    p_interval := '1 month',
    p_premake := 3
);

Monitoramento de Tamanho

SELECT
    relname AS particao,
    pg_size_pretty(pg_total_relation_size(relid)) AS tamanho
FROM pg_catalog.pg_statio_user_tables
WHERE relname LIKE 'pedidos_%'
ORDER BY relname;

Armadilhas Comuns

  • Chave de particionamento mal escolhida: Colunas com baixa cardinalidade ou distribuição desigual (ex.: status com 3 valores) criam partições desbalanceadas
  • Partições muito pequenas: Centenas de partições com poucas linhas degradam performance
  • Esquecer de criar partições futuras: Consultas para datas sem partição correspondente falham
  • Índices ausentes: Cada partição precisa de seus próprios índices para consultas eficientes

Referências