Vailati
Voltar

Particionar um ledger em dois níveis: grupo de conta e ano

LIST por grupo de conta, RANGE por ano, chave primária composta e uma regra que ninguém pode esquecer: toda consulta leva as duas chaves.

Contexto

Eu precisava que três tabelas de um ledger de pontos continuassem rápidas com bilhões de linhas e milhões de inserções por dia, sem reescrever a aplicação. A decisão foi particionar cada uma em dois níveis, primeiro por um grupo derivado do id da conta e depois por ano, e aceitar o custo que vem junto: chave primária composta, duas colunas a mais em cada linha filha, uma tabela de lookup para buscas por número e uma disciplina de consulta que o banco não impõe sozinho.

O cenário é uma plataforma de fidelidade com dezenas de milhões de contas. A tabela ledger_entry guarda cada lançamento; credit e debit guardam o detalhe de cada crédito e cada consumo, com chave estrangeira para o lançamento. As três crescem na mesma proporção, e as consultas mais frequentes são sempre “os lançamentos desta conta num período” e “o saldo disponível desta conta”. Quase nunca alguém pergunta algo que atravessa contas.

Problema

Uma tabela única com bilhões de linhas em PostgreSQL tem três dores que não se resolvem com índice.

A primeira é manutenção. VACUUM, ANALYZE e REINDEX numa tabela de terabytes levam horas e disputam I/O com a aplicação. O anti-wraparound de XID numa tabela única é um evento de madrugada que ninguém quer.

A segunda é a leitura. Mesmo com índice por conta, o heap da conta está espalhado por toda a tabela. Um extrato de doze meses vira centenas de leituras aleatórias de página.

A terceira é o ciclo de vida. Lançamentos de anos antigos raramente são lidos, mas continuam ocupando espaço nos índices quentes e participando de cada varredura de manutenção. Sem partição por tempo, não há como descartar ou arquivar um ano inteiro com um comando.

Particionar só por data resolve a terceira dor e ajuda na primeira, mas não melhora a localidade por conta. Particionar só por conta melhora a localidade e a manutenção, mas não dá ciclo de vida. Eu queria as duas coisas.

O que medi

Antes de escolher o desenho, a conta foi sobre tamanho de partição, não sobre tempo de consulta. O id da conta é um inteiro sequencial. Agrupando dois milhões de contas por partição, o volume histórico que já existia cabia em dez grupos, e o ritmo de cadastro, medido pela série diária de ids, era de pouco mais de oito mil contas por dia. Isso dá um grupo novo a cada sete ou oito meses. Com partição anual dentro de cada grupo, nenhuma folha passa de algumas dezenas de GB, que é o tamanho em que VACUUM e REINDEX ainda são operações de minutos.

Depois de particionar, o que medi foi o custo de esquecer a regra. Num job de lote, uma junção entre ledger_entry e credit usava o id do lançamento, mas não as colunas de partição. O planejador sondou todas as 70 folhas de credit a cada chamada. Mediana de 21 ms por consulta, picos de 1 s, e cerca de meio segundo só de planejamento com cache frio, porque planejar sobre 70 relações custa. A mesma consulta com as duas chaves de partição toca uma folha e cai para a casa de poucos milissegundos.

Também medi o que o PostgreSQL faz com chaves estrangeiras entre tabelas particionadas, porque o DDL exportado assusta: aparece uma constraint por par de partições, quase seiscentas, e é fácil ler isso como redundância a ser apagada. No catálogo, pg_constraint com conparentid = 0 mostrou quatro chaves estrangeiras reais, todas de raiz para raiz. As outras 588 eram clones internos, filhas das quatro, que o PostgreSQL cria para implementar a FK partição a partição. O custo de cada INSERT é uma verificação, não seiscentas. E o DROP das clones falharia, porque constraint herdada não se apaga sozinha. A lição ficou registrada: DDL exportado por ferramenta não distingue constraint declarada de clone interno; conparentid distingue.

Solução

O primeiro nível é LIST por account_group, um inteiro derivado do id da conta. O segundo é RANGE por created_at, uma partição por ano. Os anos anteriores ao sistema ficaram numa partição só.

CREATE TABLE ledger_entry (
    id             uuid        NOT NULL,
    account_id     integer     NOT NULL,
    account_group  integer     NOT NULL,
    created_at     timestamptz NOT NULL,
    -- demais colunas
    PRIMARY KEY (id, account_group, created_at)
) PARTITION BY LIST (account_group);

CREATE TABLE ledger_entry_g1 PARTITION OF ledger_entry
    FOR VALUES IN (1) PARTITION BY RANGE (created_at);

CREATE TABLE ledger_entry_g1_2020_2023 PARTITION OF ledger_entry_g1
    FOR VALUES FROM ('2020-01-01') TO ('2024-01-01');
CREATE TABLE ledger_entry_g1_2024 PARTITION OF ledger_entry_g1
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
-- … um por ano, e o mesmo para credit e debit

O grupo vem de uma coluna gerada na tabela de contas, e é copiado para cada linha filha no momento da escrita:

ALTER TABLE account
    ADD COLUMN account_group integer
    GENERATED ALWAYS AS (((id - 1) / 2000000) + 1) STORED;

Cinco consequências desse desenho merecem explicação.

A chave primária é composta porque o PostgreSQL exige. Toda chave primária ou única numa tabela particionada precisa conter as colunas de partição, nos dois níveis. Então a chave de ledger_entry é (id, account_group, created_at), e o mesmo vale para credit e debit. A consequência prática é que as chaves estrangeiras entre elas também são compostas, e o ORM precisa mapear as três colunas em cada relacionamento.

Toda consulta leva as duas chaves. É a regra mais importante e a que o banco não impõe. Um WHERE account_id = $1 sozinho usa índice dentro de cada folha, mas visita todas as folhas. Com account_group o planejador poda para um grupo; com created_at poda para os anos do período. O extrato de uma conta fica assim:

SELECT …
FROM ledger_entry e
WHERE e.account_id = $1
  AND e.account_group = $2
  AND e.created_at BETWEEN $3 AND $4
ORDER BY e.created_at DESC
LIMIT $5 OFFSET $6;

O account_group da consulta vem da linha da conta, que a aplicação já leu para validar o chamador. Não custa nada carregar. No ORM, até um UPDATE por id leva as três colunas no WHERE, senão o EF Core gera um comando que o planejador precisa rotear por todas as folhas.

Buscar por um número de negócio exige uma tabela de lookup. Lançamentos têm um número sequencial de negócio, e sistemas externos consultam por ele, sem conta nem data. Esse número não é chave de partição e não pode ser único na tabela particionada sem incluir as chaves. A solução é uma tabela pequena, não particionada:

CREATE TABLE ledger_lookup (
    entry_number  bigint      NOT NULL UNIQUE,
    created_at    timestamptz NOT NULL,
    account_id    integer     NOT NULL,
    account_group integer     NOT NULL
);

A busca por número lê o lookup primeiro, descobre grupo e data, e só então vai à partição certa. A mesma tabela resolve idempotência: ela é inserida antes do lançamento, na mesma transação, e a violação da unicidade é o sinal de que aquele número já foi processado.

Estatísticas da raiz são responsabilidade sua. O autovacuum vacuuma e analisa as folhas, mas não roda ANALYZE na tabela particionada mãe. Sem estatísticas na raiz, consultas que o planejador precisa estimar no nível da raiz, como junções entre as três tabelas, ganham planos ruins. Um job diário roda ANALYZE nas três raízes, em horário de baixo tráfego, com timeout generoso.

Chaves estrangeiras funcionam, e o catálogo mente no export. Declare as quatro FKs entre as raízes e deixe o PostgreSQL criar os clones. Não tente “otimizar” apagando o que o export mostra.

flowchart LR
    Q[Consulta com account_id,<br/>account_group e created_at] --> R[ledger_entry raiz]
    R -->|LIST account_group| G[ledger_entry_g7]
    G -->|RANGE created_at| Y[ledger_entry_g7_2026]
    Y --> I[índice por account_id<br/>dentro da folha]
    Q2[Consulta só com account_id] --> R2[ledger_entry raiz]
    R2 -.->|Append sobre 70 folhas| X[todas as folhas]

O que faria diferente

Eu colocaria a disciplina de consulta num lugar só. Hoje ela está espalhada em cada repositório e em cada endpoint, e um comentário no guia do projeto avisa que esquecer as chaves derruba produção. Funciona porque o time sabe. Eu preferiria um teste de integração que rode EXPLAIN nas consultas críticas e falhe se o plano tiver Append sobre mais de uma folha por grupo, e um helper único de filtro que receba a conta e o período e emita as três condições. O banco não impõe a regra; o teste pode.

Eu criaria as partições por automação desde o início. A migration inicial criou os grupos e os anos até um horizonte fixo, e a partir dali a criação virou tarefa manual com script, validação e janela. O risco de uma partição de ano ou de grupo faltar é indisponibilidade total de escrita. Um job que mantém sempre o próximo ano e o próximo grupo criados, com antecedência, é pequeno e tira o item da lista de coisas para lembrar.

Eu teria medido o lock de criação de partição antes de precisar. Isso virou um artigo próprio.

Referências

Compartilhe este artigo:
Compartilhar no LinkedinCompartilhar no WhatsappCompartilhar no XCompartilhar por e-mail
Artigo anterior
Criar partições novas sem travar a tabela raiz
Próximo artigo
Trace distribuído que atravessa o outbox e a fila em memória