Particionamento no PostgreSQL com Laravel: como manter a tabela de transações rápida

Particionar a tabela de transações por mês no PostgreSQL, criar partições pelo Laravel e lidar com chave primária, unicidade e retenção sem DELETE.

Quando a tabela de transações de uma conta digital cresce a ponto de os índices não caberem mais em memória e o autovacuum passar o dia brigando com ela, particionar por intervalo de data no PostgreSQL costuma ser a saída mais limpa. Cada mês vira uma tabela física separada. As consultas por período leem só as partições relevantes, e tirar dado antigo do caminho vira um DETACH em vez de um DELETE que demora horas. O preço é real: a chave primária e qualquer restrição única passam a incluir a data, e alguém precisa garantir que a partição do mês seguinte exista antes de o mês começar.

Vou contar como eu estruturo isso em projetos Laravel, onde o Schema Builder não ajuda e onde a pegadinha está nos detalhes.

Quando particionar a tabela de transações faz sentido?

Particionamento declarativo é o recurso do PostgreSQL (versão 10 em diante, maduro a partir da 12) em que você define uma tabela "mãe" sem dados próprios e várias partições filhas, cada uma responsável por um intervalo de valores de uma coluna, a chave de partição. Para quem escreve a query, continua sendo uma tabela só.

Não é otimização para tabela pequena. Se os índices dão conta e o volume cabe confortavelmente, particionar só adiciona complexidade. Os sinais que me fazem considerar são estes:

Quase toda consulta filtra por período. Extrato do mês, conciliação do dia, relatório do trimestre. Se o acesso é temporal, o PostgreSQL consegue descartar partições inteiras.
Os dados antigos quase não são lidos, mas precisam existir. Em fintech você não apaga transação por capricho, mas a de três anos atrás raramente aparece numa tela.
Manutenção ficou cara. VACUUM, REINDEX e criação de índice em tabela gigante viram janelas de manutenção. Numa partição de um mês, são operações pequenas.
Retenção é um requisito. Logs de webhook, eventos brutos de provedor e payloads de auditoria têm prazo para morrer. Derrubar uma partição é instantâneo.

Se a dor é outra, como query mal escrita, índice faltando ou N+1 no Eloquent, particionar não resolve nada.

Como criar uma tabela particionada por mês numa migration do Laravel?

O Schema Builder do Laravel não tem suporte a PARTITION BY, então a migration vai em SQL puro. Prefiro isso a um pacote, porque o SQL é curto e fica explícito o que está acontecendo.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement(<<<'SQL'
            CREATE TABLE transactions (
                id BIGSERIAL,
                account_id BIGINT NOT NULL,
                type VARCHAR(32) NOT NULL,
                amount_cents BIGINT NOT NULL,
                provider_reference VARCHAR(100),
                created_at TIMESTAMPTZ NOT NULL,
                updated_at TIMESTAMPTZ,
                PRIMARY KEY (id, created_at)
            ) PARTITION BY RANGE (created_at)
        SQL);

        DB::statement(
            'CREATE INDEX transactions_account_created_idx '
            . 'ON transactions (account_id, created_at)'
        );
    }

    public function down(): void
    {
        DB::statement('DROP TABLE transactions');
    }
};

Alguns pontos que valem atenção:

Índice criado na mãe se propaga. Desde o PostgreSQL 11, um índice na tabela particionada é criado automaticamente em cada partição, inclusive nas futuras.
BIGSERIAL funciona bem. A sequência fica na mãe e o default é aplicado no insert pela tabela pai. Colunas IDENTITY em tabelas particionadas só passaram a ser suportadas de forma completa em versões recentes do PostgreSQL, então confira a sua antes de usar.
Fuso horário dos limites. Com TIMESTAMPTZ, eu defino os limites das partições em UTC. Uma consulta do mês em horário de Brasília vai tocar duas partições nas bordas, o que é irrelevante. O que importa é ser consistente e nunca misturar.

O model Eloquent aponta para transactions normalmente. O PostgreSQL roteia cada insert para a partição certa.

Por que a chave primária precisa incluir a data?

Essa é a primeira surpresa. O PostgreSQL não tem índice global: cada partição tem o próprio índice, e ele só consegue garantir unicidade se a restrição contiver a chave de partição. Por isso a PK é (id, created_at) e não só id.

Na prática, o id continua único, porque vem de uma sequência. Mas o banco não garante isso por você. E isso tem consequências:

Foreign keys que apontam para transactions (suportadas desde o PostgreSQL 12) precisam referenciar (id, created_at). A tabela filha passa a carregar transaction_created_at junto.
Transaction::find($id) gera WHERE id = ? sem a data. O planner não sabe em qual partição procurar e consulta o índice de todas. Com poucas dezenas de partições é aceitável; com centenas, vira custo perceptível.
Route model binding sofre do mesmo problema, já que resolve por id.

Uma saída elegante é usar um identificador público que já carrega o tempo, como UUIDv7 ou ULID. Dá para extrair o timestamp do ID e adicionar o filtro de data na busca. Quando não dá, eu escrevo um escopo explícito e uso nos lugares quentes:

public function scopeLocate(Builder $query, int $id, CarbonInterface $createdAt): Builder
{
    return $query->where('id', $id)->where('created_at', $createdAt);
}

Como garantir unicidade de idempotency key entre partições?

Aqui está o problema que mais me preocupa em sistema financeiro. Toda transação originada de um provedor tem uma referência externa ou uma idempotency key, que é um identificador que garante que o mesmo evento não gere dois lançamentos. Numa tabela comum, um UNIQUE (provider_reference) resolve. Numa tabela particionada por created_at, você não consegue criar esse índice sem incluir a data. E UNIQUE (provider_reference, created_at) não protege nada: o mesmo webhook reenviado um segundo depois tem outro created_at.

A solução que uso é uma tabela pequena, não particionada, só para as chaves. O insert nela acontece na mesma transação do lançamento:

DB::transaction(function () use ($event) {
    $inserted = DB::table('transaction_keys')->insertOrIgnore([
        'idempotency_key' => $event->idempotencyKey,
        'created_at' => now(),
    ]);

    if ($inserted === 0) {
        return; // evento já processado
    }

    Transaction::create([
        'account_id' => $event->accountId,
        'type' => $event->type,
        'amount_cents' => $event->amountCents,
        'provider_reference' => $event->idempotencyKey,
        'created_at' => now(),
    ]);
});

Com UNIQUE (idempotency_key) em transaction_keys, duas requisições concorrentes com a mesma chave não passam juntas. A segunda espera o commit da primeira e cai no ON CONFLICT DO NOTHING que o insertOrIgnore gera. Essa tabela cresce também, mas cada linha é minúscula e o índice é um só. Se ela incomodar um dia, dá para aplicar retenção, porque nenhum provedor reenvia um evento de anos atrás. É o mesmo princípio que descrevi em como processar webhooks de pagamento duplicados e fora de ordem uma única vez, só que aqui o particionamento obriga a separar a garantia de unicidade da tabela principal.

Quem cria as partições do próximo mês?

Se chegar um insert com created_at para o qual não existe partição, o PostgreSQL rejeita com erro. Em tabela de transações, isso significa pagamento falhando à meia-noite do dia primeiro. Existem três formas de evitar:

Abordagem	Vantagem	Desvantagem
Command agendado no Laravel	Código no repositório, testável, sem extensão	Você mantém a lógica e o monitoramento
pg_partman + pg_cron	Maduro, cuida de criação e retenção	Depende de extensões, nem todo banco gerenciado oferece as duas
Partição `DEFAULT` como rede de segurança	Nenhum insert falha	Esconde o problema e atrapalha operações depois

Eu fico com o command agendado, criando partições com alguns meses de antecedência:

class EnsureTransactionPartitions extends Command
{
    protected $signature = 'partitions:ensure {--months=3}';

    public function handle(): int
    {
        $start = CarbonImmutable::now('UTC')->startOfMonth();

        for ($i = 0; $i <= (int) $this->option('months'); $i++) {
            $from = $start->addMonths($i);
            $to = $from->addMonth();
            $name = 'transactions_' . $from->format('Y_m');

            if (DB::scalar('SELECT to_regclass(?) IS NOT NULL', [$name])) {
                continue;
            }

            DB::transaction(function () use ($name, $from, $to) {
                DB::statement("SET LOCAL lock_timeout = '5s'");
                DB::statement(sprintf(
                    "CREATE TABLE %s PARTITION OF transactions FOR VALUES FROM ('%s') TO ('%s')",
                    $name,
                    $from->toIso8601String(),
                    $to->toIso8601String(),
                ));
            });

            $this->info("Partição {$name} criada");
        }

        return self::SUCCESS;
    }
}

E no routes/console.php:

Schedule::command('partitions:ensure')->daily()->onOneServer();

O lock_timeout não está ali por enfeite. CREATE TABLE ... PARTITION OF precisa de um lock forte na tabela mãe. Se houver uma query longa segurando a tabela, o comando fica na fila e todo insert que chega depois fica preso atrás dele. Com o timeout, ele falha rápido e tenta de novo no dia seguinte, e por isso a antecedência de meses. Rodar diariamente também deixa o command idempotente na prática: se ele falhar, a próxima execução cobre.

Sobre a partição DEFAULT: já usei e deixei de usar. Ela recebe qualquer linha fora dos intervalos definidos, o que parece seguro. O problema aparece depois. Criar uma partição cujo intervalo tenha linhas na default obriga o PostgreSQL a varrer a default e falhar se houver conflito. E o DETACH PARTITION ... CONCURRENTLY não é permitido quando existe partição default. Prefiro um alerta barulhento a uma rede de segurança que complica a operação. Se você usar a default, monitore para que ela fique sempre vazia.

Nos testes, a migration cria a tabela vazia, sem partições. Rodo partitions:ensure no setUp da classe base ou num seeder de teste, senão o primeiro Transaction::create quebra.

Como o Eloquent se comporta com partition pruning?

Partition pruning é a capacidade do planner de excluir partições que não podem conter as linhas buscadas, com base no WHERE. É daí que vem o ganho de desempenho, e ele depende de você filtrar pela coluna crua:

// Faz pruning: lê só as partições do período
Transaction::where('account_id', $accountId)
    ->where('created_at', '>=', $from)
    ->where('created_at', '<', $to)
    ->get();

// Não faz pruning: a função esconde a coluna do planner
Transaction::where('account_id', $accountId)
    ->whereDate('created_at', $day)
    ->get();

O whereDate gera created_at::date = ?, e aí o planner não consegue relacionar o filtro com os limites das partições. Troco por intervalo semiaberto (>= início, < fim) em todo lugar. Como o Laravel usa prepared statements, o pruning acontece em tempo de execução, suportado desde o PostgreSQL 11. Para conferir, rode um EXPLAIN e veja quantas partições aparecem no plano.

Esse cuidado conversa direto com a rotina de conciliação diária entre o extrato do provedor e o seu banco: ela é inteiramente orientada a janela de data, então se beneficia muito do pruning. Basta que as queries respeitem o formato.

Como arquivar ou apagar partições antigas sem DELETE?

O segundo grande ganho é a retenção. Em vez de DELETE FROM webhook_events WHERE created_at < ..., que gera bloat, carga de WAL e horas de autovacuum, você desanexa a partição:

ALTER TABLE webhook_events DETACH PARTITION webhook_events_2023_01 CONCURRENTLY;

O CONCURRENTLY (PostgreSQL 14 em diante) evita bloquear leituras e escritas na tabela mãe, mas não pode rodar dentro de um bloco de transação. Num command do Laravel, chame DB::statement fora de DB::transaction. Se for numa migration, desligue a transação dela com public $withinTransaction = false;. Depois de desanexada, a partição é uma tabela comum: você faz pg_dump para armazenamento frio e só então dá DROP.

Aqui separo bem dois tipos de tabela. Logs de webhook e payloads brutos têm prazo e podem ser derrubados. Lançamentos financeiros, não. Se você modela saldo como um ledger de partidas dobradas, o histórico é a fonte da verdade e precisa ser preservado pelo prazo legal. Nesse caso, o particionamento serve para manutenção e desempenho, e a partição antiga no máximo vai para um tablespace mais barato ou um banco de arquivo, mas nunca some. Também vale guardar saldos consolidados por período, para que nenhuma tela precise somar desde o primeiro lançamento.

Como migrar uma tabela grande que já existe para particionada?

Não existe ALTER TABLE ... PARTITION BY. As opções que já avaliei:

Tabela nova e cópia em lotes. Cria a transactions_new particionada, copia por faixas de id num job, sincroniza o delta com dupla escrita ou trigger e troca os nomes numa transação curta. É a mais trabalhosa e a mais segura para dados financeiros.
Anexar a tabela antiga como uma partição histórica. Renomeia a tabela atual, cria a mãe particionada e faz ATTACH PARTITION da antiga com um intervalo que cubra todo o passado. Para o attach não varrer a tabela inteira sob lock, crie antes uma CHECK constraint equivalente ao intervalo com NOT VALID e valide em separado. O PostgreSQL reaproveita a constraint validada. O passado fica numa partição gorda, e os meses novos já nascem particionados.
Começar só nas tabelas novas. Muitas vezes a dor real está em webhook_events ou em logs de integração, não no ledger. Particionar essas primeiro dá a maior parte do ganho com risco mínimo.

A opção 2 é a minha preferida quando o objetivo principal é parar de crescer o problema. Ela não resolve o passado, mas troca uma migração de dias por uma operação de minutos.

O que eu faria amanhã

Se a tabela de transações ou de eventos está começando a pesar, este é o caminho que eu seguiria:

Confirme com EXPLAIN e métricas que o gargalo é volume e acesso temporal, não índice faltando.
Comece pela tabela com retenção clara, geralmente a de eventos brutos de webhook.
Particione por mês com limites em UTC e PK (id, created_at).
Tire a unicidade de referência externa para uma tabela de chaves não particionada.
Agende o partitions:ensure com meses de antecedência e lock_timeout, e alerte se ele falhar.
Revise as queries quentes: troque whereDate por intervalo e find($id) por busca com data.
Só depois leve o ledger para o mesmo modelo, com a estratégia de anexar a tabela antiga como partição histórica.

O particionamento não deixa query ruim rápida. Ele tira de cada query boa o peso dos meses que ela não precisa ler, e transforma a limpeza de dados antigos em uma operação de metadados. O preço são três disciplinas: data em toda chave, data em todo filtro e partição criada antes de ser necessária. Com elas, a tabela que mais cresce no sistema deixa de ser a mais temida.