Coluna JSON que virou esquema escondido: quando o campo flexível passa a precisar de índice, validação e migração

A primeira reação ao campo flexível que virou problema é discutir se ele deveria existir. É a discussão errada, porque o campo já tem 40 milhões de linhas e a resposta não muda nada. A pergunta útil é outra: que esquema esses 40 milhões de documentos obedecem de fato? Toda chave que aparece em 90% das linhas é uma coluna que nunca foi declarada. Toda chave que aparece com dois tipos diferentes é uma validação que nunca existiu. Toda chave que aparece em 0,3% das linhas e nunca entra em um WHERE é a única parte do campo que está sendo usada como "flexível" de verdade. Antes de criar índice, constraint ou coluna, é preciso enxergar essa distribuição, e o próprio Postgres a entrega com jsonb_object_keys e jsonb_typeof sobre uma amostra.

-- inventario.sql
-- Inventario das chaves de primeiro nivel do jsonb: presenca e tipos por chave.
-- TABLESAMPLE SYSTEM (1) le cerca de 1% das paginas da tabela: em 40 milhoes de
-- linhas e o bastante para enxergar toda chave que aparece em mais de 0,1% delas,
-- sem varrer a tabela inteira nem segurar lock.
WITH amostra AS (
  SELECT atributos
    FROM clientes TABLESAMPLE SYSTEM (1)
   WHERE jsonb_typeof(atributos) = 'object'   -- array ou escalar no lugar do objeto tambem e achado
),
chaves AS (
  SELECT chave, jsonb_typeof(atributos -> chave) AS tipo
    FROM amostra, LATERAL jsonb_object_keys(atributos) AS chave
)
SELECT chave,
       count(*)                                                       AS ocorrencias,
       round(100.0 * count(*) / (SELECT count(*) FROM amostra), 1)    AS presenca_pct,
       string_agg(DISTINCT tipo, ', ' ORDER BY tipo)                  AS tipos
  FROM chaves
 GROUP BY chave
 ORDER BY ocorrencias DESC;

-- Para cada chave com mais de um tipo, um exemplo de cada forma: e isso que vira
-- a lista de correcoes antes de qualquer indice ou constraint.
SELECT chave, tipo, min(atributos ->> chave) AS exemplo, count(*) AS linhas
  FROM clientes TABLESAMPLE SYSTEM (1), LATERAL jsonb_object_keys(atributos) AS chave,
       LATERAL (SELECT jsonb_typeof(atributos -> chave) AS tipo) t
 WHERE chave IN ('cpf', 'limite_credito', 'endereco')
 GROUP BY chave, tipo
 ORDER BY chave, linhas DESC;

O TABLESAMPLE importa mais do que parece. Rodar jsonb_object_keys em 40 milhões de linhas é uma varredura completa com detoast de cada documento, e um jsonb acima de cerca de 2 KB fica comprimido em uma tabela TOAST separada: ler uma chave é descomprimir o documento inteiro. Em 1% das páginas, a consulta termina em segundos, e a distribuição de chaves que importam, as que aparecem em mais de 1% das linhas, sai com precisão suficiente para decidir. O que a amostra não captura são as chaves raras, e isso é aceitável: chave rara não vai ganhar índice nem coluna. O resultado típico em uma tabela como essa tem a cara da tabela abaixo.

Esse inventário vira a lista de trabalho e a sua ordem. A chave presente em quase todas as linhas e usada em filtro é candidata a coluna. A chave com dois tipos precisa de correção e constraint antes de qualquer índice, porque um índice de expressão com cast falha na primeira linha com tipo errado. A chave com duas formas estruturais, como o endereço, precisa de uma decisão de modelagem, não de um índice. E a cauda longa de chaves raras é a parte do campo que estava certa o tempo todo.

Um jsonb aceita dois tipos de índice com comportamentos muito diferentes, e a escolha errada é comum porque os dois "funcionam" em desenvolvimento. O índice GIN sobre a coluna inteira responde a consultas de contenção, do tipo atributos @> '{"plano": "pro"}', e com a classe de operador jsonb_path_ops fica menor e mais rápido, ao custo de não responder ao operador de existência de chave. O índice B-tree de expressão, criado sobre (atributos->>'plano'), responde a igualdade e a faixas sobre aquela chave específica e, mais importante, faz o ANALYZE coletar estatísticas da expressão, o que dá ao planejador a seletividade real em vez de um chute. Com 40 milhões de linhas e um valor que aparece em 70% delas, essa diferença é a diferença entre o planejador escolher o índice e escolher a varredura.

-- indices.sql
-- Igualdade por uma chave: indice de expressao B-tree. O ANALYZE passa a coletar
-- estatisticas da expressao, e o planejador deixa de chutar a seletividade.
CREATE INDEX CONCURRENTLY clientes_plano_idx
    ON clientes ((atributos ->> 'plano'));

-- Contencao em varias chaves ao mesmo tempo: GIN com jsonb_path_ops. Menor e mais
-- rapido que o operador padrao, mas responde so a @> e a jsonpath (@?, @@);
-- nao responde "a chave existe?" (?), que exige o jsonb_ops padrao.
CREATE INDEX CONCURRENTLY clientes_atributos_gin
    ON clientes USING gin (atributos jsonb_path_ops);

-- Comparacao numerica: o indice precisa da mesma expressao e do mesmo cast da consulta.
-- Se uma unica linha tiver texto nao numerico nessa chave, o CREATE INDEX falha:
-- por isso a validacao vem antes do indice, nunca depois.
CREATE INDEX CONCURRENTLY clientes_limite_credito_idx
    ON clientes (((atributos ->> 'limite_credito')::numeric));

-- A consulta so usa o indice se tiver exatamente a forma da expressao indexada.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM clientes WHERE atributos ->> 'plano' = 'pro';
-- Index Scan using clientes_plano_idx ...

-- Usa o GIN (contencao), nao o B-tree:
--   WHERE atributos @> '{"plano": "pro", "origem": "indicacao"}'
-- Nao usa nenhum dos dois, por mais indices que existam:
--   WHERE atributos ->> 'plano' ILIKE 'pr%'
--   WHERE (atributos -> 'tags') ? 'vip'          -- jsonb_path_ops nao responde ?
--   WHERE atributos ->> 'limite_credito' > '5000' -- comparacao de texto, nao de numero

Há um limite do que o índice conserta. Quando a mesma chave é filtrada, ordenada, agregada e comparada numericamente, cada uma dessas consultas precisa de um índice de expressão diferente, cada um com seu custo de escrita, e o planejador continua sem estatísticas cruzadas entre elas. Nesse ponto o índice está compensando a ausência de uma coluna, e o custo de manter três índices de expressão em uma chave que aparece em 99,8% das linhas supera o custo de promovê-la. O índice é a resposta certa para a chave que entra em um ou dois tipos de filtro e para a contenção em chaves variadas; a coluna é a resposta para a chave que a aplicação trata como campo.

A validação que existe hoje mora em código, e é por isso que 3% das linhas têm CPF numérico: o job de importação não passou pelo mesmo caminho do formulário. Mover a regra para o banco fecha essa porta para todo escritor, mas a forma ingênua, um ALTER TABLE ADD CONSTRAINT CHECK, varre a tabela inteira sob lock exclusivo e falha na primeira linha que viola a regra. Em 40 milhões de linhas isso é minutos de escrita bloqueada e, no fim, um erro. O caminho que funciona em produção tem três passos: adicionar a constraint com NOT VALID, o que a impõe só para linhas novas e atualizadas e devolve em milissegundos; corrigir o legado em lotes; e só então VALIDATE CONSTRAINT, que varre a tabela com um lock compatível com INSERT, UPDATE e DELETE.

-- validacao.sql
-- 1. A regra passa a valer para toda linha nova ou atualizada imediatamente.
--    NOT VALID pula a varredura das linhas antigas: o ALTER segura um lock curto
--    e devolve em milissegundos, mesmo com 40 milhoes de linhas.
ALTER TABLE clientes
  ADD CONSTRAINT clientes_atributos_plano_chk
  CHECK (
    jsonb_typeof(atributos -> 'plano') IS NULL      -- chave ausente continua permitida
    OR (
      jsonb_typeof(atributos -> 'plano') = 'string'
      AND atributos ->> 'plano' IN ('free', 'pro', 'enterprise')
    )
  ) NOT VALID;

-- 2. O legado que violaria a regra. Repare que {"plano": null} tem tipo 'null',
--    nao e chave ausente: entra aqui e precisa ser normalizado removendo a chave.
SELECT id, atributos -> 'plano' AS valor
  FROM clientes
 WHERE NOT (
   jsonb_typeof(atributos -> 'plano') IS NULL
   OR (jsonb_typeof(atributos -> 'plano') = 'string'
       AND atributos ->> 'plano' IN ('free', 'pro', 'enterprise'))
 )
 LIMIT 100;

-- 3. Correcao em lotes pequenos: cada UPDATE em jsonb grava a linha inteira de novo,
--    entao lote grande e lock longo e inchaco para o autovacuum limpar depois.
UPDATE clientes
   SET atributos = atributos - 'plano'
 WHERE id IN (
   SELECT id FROM clientes
    WHERE jsonb_typeof(atributos -> 'plano') = 'null'
    LIMIT 5000
 );

UPDATE clientes
   SET atributos = jsonb_set(atributos, '{plano}', to_jsonb(lower(atributos ->> 'plano')))
 WHERE id IN (
   SELECT id FROM clientes
    WHERE atributos ->> 'plano' IN ('Pro', 'PRO', 'Free', 'Enterprise')
    LIMIT 5000
 );

-- 4. Validar o que ja existe sem bloquear escrita: VALIDATE CONSTRAINT varre a tabela
--    com SHARE UPDATE EXCLUSIVE, que convive com INSERT, UPDATE e DELETE.
ALTER TABLE clientes VALIDATE CONSTRAINT clientes_atributos_plano_chk;

Dois detalhes do jsonb mordem nesse passo. O primeiro é que {"plano": null} não é chave ausente: jsonb_typeof devolve 'null', a CHECK recusa, e a normalização correta é remover a chave com o operador de subtração, não gravar null de novo. O segundo é que todo UPDATE em uma chave reescreve o documento inteiro, porque o Postgres grava uma nova versão da linha a cada atualização. Corrigir 1,2 milhão de linhas em uma transação é 1,2 milhão de linhas mortas de uma vez, que o autovacuum vai precisar limpar enquanto a tabela cresce; em lotes de 5 mil, com pausa entre eles, o mesmo trabalho vira ruído de fundo. A regra em código continua existindo, mas passa a ser a mesma regra, expressa uma vez no banco e espelhada no validador da aplicação, em vez de doze variações que divergem a cada importação.

A chave plano está em 99,8% das linhas, entra em todo relatório e tem três valores válidos. É uma coluna, e deveria ter nascido como uma. Promovê-la é a mesma migração em três fases usada para qualquer mudança de esquema sem janela: expandir, criando a coluna ao lado da chave; migrar, preenchendo a coluna e trocando leitores e escritores em deploys separados; e contrair, removendo a chave e o que a sustentava. A tentação é usar uma coluna gerada, GENERATED ALWAYS AS (atributos->>'plano') STORED, que resolve tudo em um comando. Em uma tabela grande esse comando reescreve todas as linhas sob lock exclusivo, e é exatamente a janela que não existe. A coluna precisa nascer anulável e vazia, e um gatilho precisa manter os dois lados iguais enquanto escritores antigos e novos convivem.

-- expandir.sql
-- Fase 1: a coluna nasce anulavel e sem default, o que nao reescreve a tabela.
-- (ADD COLUMN ... GENERATED ALWAYS AS (...) STORED reescreveria as 40 milhoes de linhas
--  sob lock exclusivo; por isso a coluna e comum e o preenchimento e feito em lotes.)
ALTER TABLE clientes ADD COLUMN plano text;

-- O gatilho mantem os dois lados iguais enquanto convivem escritores antigos (que so
-- conhecem o JSON) e escritores novos (que so conhecem a coluna).
CREATE OR REPLACE FUNCTION clientes_sincronizar_plano() RETURNS trigger AS $
BEGIN
  IF (TG_OP = 'INSERT' AND NEW.plano IS NOT NULL)
     OR (TG_OP = 'UPDATE' AND NEW.plano IS DISTINCT FROM OLD.plano) THEN
    -- escrita nova: a coluna manda e a chave JSON acompanha, para leitores antigos
    NEW.atributos := coalesce(NEW.atributos, '{}'::jsonb)
                     || jsonb_build_object('plano', NEW.plano);
  ELSE
    -- escrita antiga: a chave JSON manda e a coluna acompanha
    NEW.plano := NEW.atributos ->> 'plano';
  END IF;
  RETURN NEW;
END
$ LANGUAGE plpgsql;

CREATE TRIGGER clientes_sincronizar_plano_trg
  BEFORE INSERT OR UPDATE OF plano, atributos ON clientes
  FOR EACH ROW EXECUTE FUNCTION clientes_sincronizar_plano();

-- Fase 2 (depois do backfill): o indice na coluna, sem lock de escrita.
CREATE INDEX CONCURRENTLY clientes_plano_col_idx ON clientes (plano);

-- Fase 5 (depois que nenhum leitor usa a chave): obrigatoriedade sem varredura sob lock.
ALTER TABLE clientes ADD CONSTRAINT clientes_plano_nn CHECK (plano IS NOT NULL) NOT VALID;
ALTER TABLE clientes VALIDATE CONSTRAINT clientes_plano_nn;
ALTER TABLE clientes ALTER COLUMN plano SET NOT NULL;   -- usa a CHECK validada, nao varre
ALTER TABLE clientes DROP CONSTRAINT clientes_plano_nn;
DROP TRIGGER clientes_sincronizar_plano_trg ON clientes;
DROP INDEX CONCURRENTLY clientes_plano_idx;             -- o indice de expressao antigo

Promover a chave "plano" do jsonb a coluna, com escrita ligada o tempo todo.

Fase   Leitura                  Escrita                       Banco
----   -----------------------  ----------------------------  ----------------------------------------
 0     atributos->>'plano'      so JSON                       seq scan ou indice de expressao
 1     atributos->>'plano'      so JSON                       ADD COLUMN plano (anulavel, sem rewrite)
                                                              + gatilho JSON <-> coluna
 2     atributos->>'plano'      so JSON                       backfill em lotes de 5.000 (horas)
                                                              + CREATE INDEX CONCURRENTLY (plano)
 3     coluna plano             so JSON (gatilho preenche)    teste: zero linhas divergentes
 4     coluna plano             coluna (gatilho reescreve     leitores antigos ainda leem a chave
                                a chave no JSON)
 5     coluna plano             so coluna                     SET NOT NULL via CHECK validada,
                                                              DROP TRIGGER, chave removida em lotes

Cada fase e um deploy separado e reversivel: voltar uma fase nunca perde dado,
porque ate a fase 5 os dois lados existem e o gatilho mantem os dois iguais.

O gatilho é o que torna cada fase reversível. Enquanto ele existe, um escritor antigo que só conhece o JSON preenche a coluna sem saber, e um escritor novo que só conhece a coluna reescreve a chave no JSON para os leitores que ainda não migraram. A decisão de qual lado manda é feita por linha, pelo que mudou naquela escrita, e não por uma bandeira global. O preenchimento do que já existe é um job à parte, em lotes por faixa de id, com timeout por lote e cláusula de idempotência, para poder ser interrompido e retomado quantas vezes for preciso.

// backfill-plano.js
// Copia atributos->>'plano' para a coluna plano em lotes por faixa de id.
// Cada lote e uma transacao curta com statement_timeout e lock_timeout; a pausa
// entre lotes deixa o autovacuum e as escritas da aplicacao respirarem.
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 2 });
const TAMANHO_LOTE = 5_000;
const PAUSA_MS = 200;
const dormir = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

export async function backfill({ aPartirDe = 0 } = {}) {
  const { rows } = await pool.query('SELECT max(id) AS max FROM clientes');
  const ultimoId = Number(rows[0].max ?? 0);
  let inicio = aPartirDe;
  let atualizadas = 0;

  while (inicio < ultimoId) {
    const fim = inicio + TAMANHO_LOTE;
    const conexao = await pool.connect();
    try {
      await conexao.query('BEGIN');
      await conexao.query("SET LOCAL statement_timeout = '5s'");
      await conexao.query("SET LOCAL lock_timeout = '1s'");
      // IS DISTINCT FROM torna o lote idempotente: rodar duas vezes nao grava nada
      // na segunda, e linhas que a aplicacao ja atualizou pelo gatilho sao puladas.
      const { rowCount } = await conexao.query(
        `UPDATE clientes
            SET plano = atributos ->> 'plano'
          WHERE id > $1 AND id <= $2
            AND plano IS DISTINCT FROM (atributos ->> 'plano')`,
        [inicio, fim],
      );
      await conexao.query('COMMIT');
      atualizadas += rowCount;
    } catch (erro) {
      await conexao.query('ROLLBACK').catch(() => {});
      conexao.release();
      // 55P03 lock_not_available, 57014 query_canceled: alguem segurava a linha ou
      // o lote demorou demais. Espera mais e repete o mesmo lote, sem avancar.
      if (erro.code === '55P03' || erro.code === '57014') {
        await dormir(PAUSA_MS * 10);
        continue;
      }
      throw erro;
    }
    conexao.release();
    console.log(JSON.stringify({ ate: fim, atualizadas }));
    inicio = fim;
    await dormir(PAUSA_MS);
  }
  return atualizadas;
}

A ordem das fases não é negociável. O índice na coluna só é criado depois do backfill, porque criar antes significa manter o índice a cada uma das 40 milhões de atualizações. A leitura só troca para a coluna depois que um teste mostra zero linhas divergentes. A escrita só troca depois da leitura, porque enquanto houver um leitor na chave JSON o gatilho precisa continuar reescrevendo-a. E o NOT NULL só entra no fim, por uma CHECK NOT VALID validada antes, porque ALTER COLUMN SET NOT NULL direto varre a tabela sob lock exclusivo, enquanto com uma CHECK equivalente já validada o Postgres aceita sem varrer.

Nem toda chave do inventário merece virar coluna, e a discussão "JSON versus relacional" não ajuda a decidir chave por chave. O critério que funciona olha para como a chave é usada, não para o que ela representa. Uma chave que entra em WHERE, ORDER BY, GROUP BY ou JOIN é uma coluna, porque o banco só otimiza bem o que conhece como coluna, com estatísticas, índice simples e tipo declarado. Uma chave que uma regra de negócio exige é uma coluna com NOT NULL, porque a obrigatoriedade precisa ser verificável pelo banco. Uma chave que só é lida junto com a linha inteira e varia por cliente fica no JSON, porque é para isso que o campo existe. E uma chave que é um objeto com ciclo de vida próprio, como o endereço, que muda em datas diferentes do cliente e pode ter histórico, é uma tabela.

O que sobra no JSON depois dessa triagem precisa de duas coisas para não virar o mesmo problema daqui a dois anos. A primeira é uma chave de versão dentro do próprio documento, como "v": 2, gravada por todo escritor e usada pelo leitor para saber qual forma esperar, em vez de testar a presença de chaves. A segunda é o mesmo inventário da primeira seção rodando como consulta agendada, com alerta quando uma chave passa de 20% de presença ou aparece com um segundo tipo. O campo continua flexível; o que muda é que a flexibilidade passa a ser observada em vez de descoberta no incidente.

A migração tem quatro afirmações que precisam ser verdadeiras antes de cada troca de fase, e todas cabem em um teste contra um Postgres descartável com o esquema de expandir.sql aplicado. Depois do backfill, nenhuma linha pode divergir entre a coluna e a chave. Rodar o backfill de novo não pode atualizar nada. Uma escrita antiga, que só conhece o JSON, precisa aparecer na coluna, e uma escrita nova, que só conhece a coluna, precisa aparecer no JSON. E a consulta que motivou tudo precisa usar o índice da coluna, o que só se prova com EXPLAIN sobre uma tabela grande o bastante para o planejador preferir o índice, por isso o teste semeia 200 mil linhas e roda ANALYZE antes de perguntar o plano.

// migracao-plano.test.js
// Roda contra um Postgres descartavel (docker run --rm -e POSTGRES_PASSWORD=x -p 5432:5432 postgres:16)
// com expandir.sql ja aplicado. Semeia linhas suficientes para o planejador preferir o indice.
import test, { before, after } from 'node:test';
import assert from 'node:assert/strict';
import pg from 'pg';
import { backfill } from './backfill-plano.js';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

before(async () => {
  await pool.query('ALTER TABLE clientes DISABLE TRIGGER clientes_sincronizar_plano_trg');
  // 200 mil linhas so com a chave JSON, como o legado: a coluna fica vazia de proposito.
  await pool.query(`
    INSERT INTO clientes (atributos)
    SELECT jsonb_build_object('plano', (array['free', 'pro', 'enterprise'])[1 + (g % 3)],
                              'origem', 'importacao')
      FROM generate_series(1, 200000) AS g`);
  await pool.query('ALTER TABLE clientes ENABLE TRIGGER clientes_sincronizar_plano_trg');
});
after(() => pool.end());

test('depois do backfill nenhuma linha diverge entre a coluna e a chave JSON', async () => {
  await backfill();
  await pool.query('ANALYZE clientes');
  const { rows } = await pool.query(`
    SELECT count(*)::int AS divergentes
      FROM clientes
     WHERE plano IS DISTINCT FROM (atributos ->> 'plano')`);
  assert.equal(rows[0].divergentes, 0);
});

test('o segundo backfill nao atualiza nenhuma linha', async () => {
  assert.equal(await backfill(), 0);
});

test('uma escrita antiga, so no JSON, aparece na coluna', async () => {
  const { rows } = await pool.query(
    `INSERT INTO clientes (atributos) VALUES ('{"plano": "pro"}') RETURNING id, plano`,
  );
  assert.equal(rows[0].plano, 'pro');
});

test('uma escrita nova, so na coluna, aparece no JSON para leitores antigos', async () => {
  const { rows } = await pool.query(
    `UPDATE clientes SET plano = 'enterprise'
      WHERE id = (SELECT min(id) FROM clientes)
      RETURNING atributos ->> 'plano' AS plano_json`,
  );
  assert.equal(rows[0].plano_json, 'enterprise');
});

test('o filtro por plano usa o indice da coluna, nao varredura sequencial', async () => {
  const { rows } = await pool.query(
    `EXPLAIN (FORMAT JSON) SELECT id FROM clientes WHERE plano = 'enterprise'`,
  );
  const plano = JSON.stringify(rows[0]['QUERY PLAN'][0].Plan);
  assert.ok(!plano.includes('"Seq Scan"'), plano);
  assert.ok(plano.includes('clientes_plano_col_idx'), plano);
});

A consulta de divergência do primeiro teste é a mesma que deve rodar em produção, como passo de verificação, antes de trocar a leitura para a coluna e de novo antes de remover a chave. Ela é barata se tiver o índice da coluna e cara sem ele, e é esperado que devolva zero; qualquer número diferente de zero é uma escrita que passou por fora do gatilho, geralmente um job com o gatilho desabilitado ou um COPY direto, e precisa ser entendida antes de avançar. O teste do plano merece ficar na pipeline mesmo depois da migração, porque é o que detecta, antes da produção, o dia em que alguém reescreve a consulta com uma função sobre a coluna e o índice volta a ser ignorado.