Vailati
Voltar

Criar partições novas sem travar a tabela raiz

O comando óbvio do PostgreSQL segura um ACCESS EXCLUSIVE na tabela inteira até o commit. Existe um caminho que não segura nada que importe, e ele é dois comandos em vez de um.

Contexto

Eu precisava criar um conjunto novo de partições em três tabelas particionadas que recebem milhões de linhas por dia, sem que nenhuma consulta ou escrita em andamento ficasse esperando. A decisão foi trocar CREATE TABLE … PARTITION OF por CREATE TABLE (LIKE …) seguido de ATTACH PARTITION, que troca um lock exclusivo na raiz por um lock que não conflita com leitura nem escrita. O custo foi um script mais longo, uma validação a mais e uma regra operacional: não rodar junto com ANALYZE na raiz.

O cenário é um ledger de pontos de uma plataforma de fidelidade. A tabela principal, que vou chamar de ledger_entry, é particionada em dois níveis. O primeiro nível é LIST por account_group, um inteiro derivado do id da conta: cada grupo reúne dois milhões de contas. O segundo nível é RANGE por created_at, uma partição por ano. Duas tabelas irmãs, credit e debit, seguem o mesmo desenho. Toda consulta nessas tabelas carrega account_group e created_at no WHERE, senão vira varredura de dezenas de folhas.

O grupo é uma coluna gerada:

account_group integer GENERATED ALWAYS AS (((id - 1) / 2000000) + 1) STORED

Isso significa que, quando a base de contas cresce, um dia o INSERT de uma conta nova cai num grupo que ainda não tem partição. Sem partição DEFAULT, o INSERT falha. Então as partições do próximo grupo precisam existir antes de o grupo nascer, e criá-las é uma tarefa recorrente, feita com a aplicação no ar.

Problema

A forma natural de criar o subnível de um grupo novo é esta:

CREATE TABLE ledger_entry_g15
    PARTITION OF ledger_entry FOR VALUES IN (15)
    PARTITION BY RANGE (created_at);

E depois as folhas anuais:

CREATE TABLE ledger_entry_g15_2026
    PARTITION OF ledger_entry_g15
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

É o que a documentação mostra primeiro, é o que o EF Core gera numa migration, e é o que eu tinha num script que criava seis grupos de uma vez, cada grupo numa transação. Funciona. O problema é o que acontece enquanto a transação está aberta.

CREATE TABLE … PARTITION OF precisa de ACCESS EXCLUSIVE na tabela mãe. Esse é o lock mais forte do PostgreSQL: conflita com tudo, inclusive com SELECT. E o lock dura até o COMMIT, não até o fim do comando. Num script em que cada grupo é uma transação com vários comandos, a raiz ledger_entry fica inacessível do primeiro CREATE do grupo até o COMMIT daquele grupo. Toda leitura e toda escrita em qualquer partição existente entra na fila.

Com a raiz vazia, isso é milissegundos. Com a raiz recebendo centenas de requisições por segundo, uma fila de alguns segundos vira timeout na aplicação, e timeout na aplicação vira erro para quem chamou. Meu critério era explícito: a partição nova pode ficar indisponível, ninguém usa ela ainda. As partições já em uso não podem ficar inacessíveis nem por instantes.

Há um agravante que eu não tinha considerado. Mesmo que o CREATE seja rápido, ele precisa primeiro obter o lock. Se alguma sessão está segurando um lock qualquer na raiz, o CREATE espera. E enquanto o CREATE espera na fila, tudo que chega depois dele também espera, porque o PostgreSQL não deixa requisições novas furarem a fila de um ACCESS EXCLUSIVE pendente. Um ANALYZE de uma hora na raiz, disparado por um job diário, transforma um CREATE de milissegundos num bloqueio de uma hora.

O que medi

Medi em PostgreSQL 18.6, numa cópia local com o DDL de produção, olhando pg_locks de uma segunda sessão enquanto a transação do script ficava aberta antes do COMMIT.

Com CREATE TABLE … PARTITION OF na raiz:

RelaçãoLock mantido pela transação do script
ledger_entry (raiz)AccessExclusiveLock
credit (raiz)AccessExclusiveLock
debit (raiz)AccessExclusiveLock

Nesse estado, um SELECT count(*) FROM ledger_entry WHERE account_group = 3 AND created_at >= '2026-01-01' numa terceira sessão fica em wait_event = relation até o commit. Não importa que o grupo 3 não tenha nada a ver com o grupo 15: o lock é na raiz, e a consulta precisa passar pela raiz.

Com a versão em dois comandos, que mostro na próxima seção:

RelaçãoLock mantido pela transação do script
ledger_entry (raiz)AccessShareLock, ShareUpdateExclusiveLock
credit (raiz)AccessShareLock, ShareUpdateExclusiveLock
debit (raiz)AccessShareLock, ShareUpdateExclusiveLock
ledger_entry_g15 (nova)AccessExclusiveLock

SHARE UPDATE EXCLUSIVE não conflita com ROW EXCLUSIVE, que é o que INSERT, UPDATE e DELETE pedem, nem com ACCESS SHARE, que é o que SELECT pede. A mesma consulta no grupo 3 executa normalmente com a transação do script aberta. O ACCESS EXCLUSIVE ficou só na tabela nova, que ninguém usa.

Também medi o que acontece quando algo conflita de verdade. Abri uma sessão com BEGIN; ANALYZE credit; sem commit, que segura SHARE UPDATE EXCLUSIVE na raiz de credit, e rodei o script com lock_timeout = '3s'. Resultado: o script falhou em quatro segundos no primeiro comando que tocou credit, com zero commits e zero relações soltas, porque o grupo inteiro é uma transação. Liberei a sessão do ANALYZE, reexecutei o mesmo script sem mudar nada, e ele criou as 18 relações. A consulta de validação, que lista grupos ausentes ou incompletos, ficou vazia.

Por fim, subi a aplicação apontando para a cópia e criei um lançamento para uma conta do grupo 15 pela API. A linha caiu em ledger_entry_g15_2026, com o identity gerado, e o estorno dela caiu em debit_g15_2026. Isso prova que o ATTACH propagou tudo que o roteamento precisa, não só a tabela.

Solução

A ideia é separar “criar a tabela” de “ligar a tabela à raiz”. O CREATE TABLE com LIKE não toca a raiz além de ler a definição dela. O ATTACH PARTITION, desde o PostgreSQL 12, pede só SHARE UPDATE EXCLUSIVE na mãe.

\set ON_ERROR_STOP on
SET lock_timeout = '3s';

BEGIN;

-- 1. subnível do grupo: cópia da definição da raiz, ainda solta
CREATE TABLE IF NOT EXISTS ledger_entry_g15
    (LIKE ledger_entry
        INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING GENERATED
        EXCLUDING IDENTITY EXCLUDING INDEXES)
    PARTITION BY RANGE (created_at);

-- 2. ligar à raiz: SHARE UPDATE EXCLUSIVE, não conflita com DML
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_inherits
                   WHERE inhrelid = 'ledger_entry_g15'::regclass) THEN
        EXECUTE 'ALTER TABLE ledger_entry ATTACH PARTITION ledger_entry_g15
                 FOR VALUES IN (15)';
    END IF;
END $$;

-- 3. folhas anuais: PARTITION OF é aceitável aqui, o lock é na g15, que está vazia
CREATE TABLE IF NOT EXISTS ledger_entry_g15_2026
    PARTITION OF ledger_entry_g15
    FOR VALUES FROM ('2026-01-01') TO ('2027-01-01')
    WITH (autovacuum_vacuum_scale_factor = 0.02,
          autovacuum_analyze_scale_factor = 0.01,
          autovacuum_vacuum_threshold = 1000);

CREATE TABLE IF NOT EXISTS ledger_entry_g15_2027
    PARTITION OF ledger_entry_g15
    FOR VALUES FROM ('2027-01-01') TO ('2028-01-01')
    WITH (autovacuum_vacuum_scale_factor = 0.02,
          autovacuum_analyze_scale_factor = 0.01,
          autovacuum_vacuum_threshold = 1000);

COMMIT;

Quatro detalhes que fazem diferença.

EXCLUDING INDEXES e EXCLUDING IDENTITY são de propósito. O ATTACH cria na partição nova os índices equivalentes aos índices particionados da raiz e os anexa a eles. Se o LIKE já tivesse copiado os índices, o ATTACH encontraria índices com a mesma definição e os reaproveitaria, o que também funciona, mas a nomenclatura fica diferente da que o ATTACH gera sozinho e a validação depois fica mais chata. Com IDENTITY é parecido: a coluna identity da raiz é propagada pelo ATTACH; copiá-la no LIKE cria uma sequence própria para a partição, que não é o que eu quero. As restrições NOT NULL, a chave primária e as chaves estrangeiras vêm pelo INCLUDING CONSTRAINTS e pelo próprio ATTACH.

O ATTACH não varre nada porque a partição é nova e está vazia. Quando você anexa uma tabela com dados, o PostgreSQL precisa verificar que todas as linhas cabem no intervalo, e isso é uma varredura completa com o lock seguro. Partição recém-criada não tem linhas, e a verificação é imediata. Da mesma forma, se existisse uma partição DEFAULT na raiz, o ATTACH precisaria varrê-la para garantir que nenhuma linha do grupo 15 foi parar lá. Eu não tenho DEFAULT justamente por isso.

As folhas podem usar PARTITION OF porque o lock delas é na g15. O ACCESS EXCLUSIVE do CREATE TABLE … PARTITION OF ledger_entry_g15 é na ledger_entry_g15, que acabou de nascer e não tem leitores. A raiz não é tocada. Eu poderia repetir o LIKE + ATTACH nas folhas, mas seria verbosidade sem ganho.

O WITH (autovacuum_…) precisa estar em cada folha. Parâmetros de armazenamento não são herdados. Pior: uma tabela particionada rejeita WITH de autovacuum, com o erro cannot specify storage parameters for a partitioned table. Então não existe “configurar na raiz e deixar descer”. Se as partições em uso têm autovacuum agressivo, cada folha nova precisa nascer com ele, senão a tabela de maior escrita do sistema fica com o padrão de 20 % e o primeiro VACUUM dela é o anti-wraparound.

O fluxo de locks, lado a lado:

sequenceDiagram
    participant S as Script
    participant R as ledger_entry (raiz)
    participant A as Aplicação
    Note over S,A: Versão 1: PARTITION OF na raiz
    S->>R: CREATE TABLE g15 PARTITION OF raiz
    Note over R: ACCESS EXCLUSIVE até o COMMIT
    A-->>R: SELECT grupo 3 (espera)
    S->>R: COMMIT
    R-->>A: resultado (atrasado)
    Note over S,A: Versão 2: LIKE + ATTACH
    S->>S: CREATE TABLE g15 (LIKE raiz) PARTITION BY RANGE
    S->>R: ALTER TABLE raiz ATTACH PARTITION g15
    Note over R: SHARE UPDATE EXCLUSIVE
    A->>R: SELECT grupo 3 (executa)
    S->>R: COMMIT

Idempotência e falha

Cada grupo é uma transação. Se qualquer comando falhar, o grupo não existe em parte alguma e o script pode ser rodado de novo inteiro. Os IF NOT EXISTS e o teste em pg_inherits garantem que grupos já criados são pulados. Isso importa porque lock_timeout vai disparar um dia: basta o job de ANALYZE das raízes estar rodando. A resposta certa não é aumentar o timeout, é falhar rápido, esperar o concorrente acabar e reexecutar.

Dois tipos de sessão conflitam com o ATTACH: ANALYZE na raiz, que também pede SHARE UPDATE EXCLUSIVE, e qualquer DDL na raiz. Autovacuum não conflita, porque autovacuum trabalha nas folhas, nunca numa tabela particionada. Antes de rodar, eu consulto pg_locks para as três raízes e pg_stat_progress_analyze; se houver algo além de AccessShareLock, espero.

O lock_timeout de três segundos foi escolhido abaixo do menor timeout da aplicação, que é de dez segundos nas consultas. No pior caso, uma requisição que chegou enquanto o ATTACH esperava o lock atrasa três segundos e segue; nunca atinge o timeout dela.

Validação

Depois do commit, uma consulta sobre pg_inherits e pg_class lista, por raiz, os grupos esperados que estão ausentes ou incompletos, e relações _g15 que existem sem estar anexadas. Esperado: zero linhas. Outra compara reloptions de cada folha nova com o perfil de autovacuum da raiz dela. E uma terceira confere os índices particionados: cada índice da raiz precisa ter um filho válido na g15, e nenhum índice da família pode estar indisvalid = false.

O teste final é funcional, pela aplicação: um lançamento para uma conta do grupo novo, lido de volta da folha certa. Se o roteamento funciona de ponta a ponta, o ATTACH fez tudo que tinha que fazer.

O que faria diferente

Eu teria medido o lock antes de escrever o primeiro script, não depois. O script com PARTITION OF passou em todos os testes funcionais e em todas as validações de estrutura. Ele estava certo em tudo, menos no que acontece com quem está usando o banco durante a execução. Teste de DDL em banco vazio não mostra isso. pg_locks numa segunda sessão, com a transação aberta, é o teste que faltava, e leva um minuto.

Eu automatizaria a criação das partições anuais. O problema do grupo novo é raro. O problema da partição do ano seguinte é anual, previsível, e toda _2027 do sistema precisa existir antes de 1.º de janeiro. Um job que cria a próxima folha de cada subnível com antecedência, com o mesmo padrão de LIKE + ATTACH quando for o caso, tira isso da lista de coisas que alguém precisa lembrar em dezembro.

Eu não deixaria o ORM gerar essa migration. O EF Core gera CREATE TABLE … PARTITION OF sem pensar em lock, e a migration roda na pipeline de deploy, que não é o lugar para segurar a raiz. Partição é operação de banco, com janela, pré-voo e validação. A migration do ORM, nesse caso, só registra que a estrutura existe.

Referências

Compartilhe este artigo:
Compartilhar no LinkedinCompartilhar no WhatsappCompartilhar no XCompartilhar por e-mail
Próximo artigo
Particionar um ledger em dois níveis: grupo de conta e ano