# Revisão completa de índices e particionamento (PIX, Core, SPB)

Data: 2026-07-14. Mandato do dono: "eu quero uma revisão completa de todos os indices". Motivação: telas de produção lentas, em especial o detalhe de transação PIX demorando muito para carregar o histórico.

Método: levantamento empírico em três trilhas paralelas (queries quentes por grep de `Repo.query`/`from`/`ilike`/`like` em controllers, contexts e workers; inventário de índices lido das migrations e dos baselines SQL; cruzamento query a query). Zero inferência: toda afirmação abaixo tem arquivo e linha.

## 1. Diagnóstico da tela lenta (causa raiz confirmada)

O detalhe de transação PIX (timeline e o novo GET /transactions/:id/xml) correlaciona as mensagens do acervo assim (`pix/backend/apps/spi_service/lib/spi_service_web/controllers/payment_controller.ex`):

1. `bacen_outbound` e `bacen_inbound` filtradas por `resource_id IN (...)` com `OR xml_content ILIKE '%<e2e>%'` (linhas 810-836). Nenhuma das duas tabelas tinha índice em `resource_id` nem em `xml_content`: cada carregamento do detalhe fazia DOIS full scans do acervo BACEN inteiro. Em `bacen_inbound` a PK liderava por `ispb` (constante 46026562), que o planner do PG 16/17 não aproveita para busca por `resource_id` sozinho.
2. `messages` filtrada por `end_to_end_id == e2e OR original_end_to_end_id == e2e` (linha 646). O lado `end_to_end_id` tem índice; `original_end_to_end_id` não tinha NENHUM. Num OR, um braço sem índice impede o BitmapOr e devolve a query inteira ao seq scan da tabela particionada.
3. A REDA (`reda_controller.ex:180,226,232`) usa o mesmo `ILIKE '%x%'` sobre `xml_content` das duas tabelas.

O Monitor (settlement_service `admin/monitor_controller.ex`) NÃO é o gargalo principal: a query base é limitada por `created_at >= from` servido por `idx_audit_xml_created (created_at DESC)` (baseline linha 9748); os LIKE rodam dentro da janela temporal.

## 2. O que foi criado (migrations novas)

### PIX (`pix/backend/apps/shared/priv/repo/migrations/`)

`20260714210000_add_archive_correlation_indexes.exs` (CONCURRENTLY, `@disable_ddl_transaction` + `@disable_migration_lock`, padrão do repo em 20260308500001):

| Query quente (arquivo:linha) | Tabela | Índice criado |
|---|---|---|
| timeline `/xml` `ILIKE '%e2e%'` (payment_controller.ex:833), REDA (reda_controller.ex:180) | monetarie_spi.bacen_outbound | `idx_spi_bacen_out_xml_trgm` GIN `(xml_content gin_trgm_ops)` |
| timeline `/xml` `ILIKE '%e2e%'` (payment_controller.ex:833), REDA (reda_controller.ex:226,232) | monetarie_spi.bacen_inbound | `idx_spi_bacen_in_xml_trgm` GIN `(xml_content gin_trgm_ops)` |
| `resource_id IN (...)` (payment_controller.ex:812), REDA lookup (inbound_processor.ex:1639), `get_xml_content` (message_controller.ex:1060) | monetarie_spi.bacen_outbound | `idx_spi_bacen_out_resource` btree `(resource_id)` |
| `resource_id IN (...)` (payment_controller.ex:821), `mark_inbound_failed` (inbound_processor.ex:3296), `get_xml_content` (message_controller.ex:1053), camt.054 por rid (operation_query.ex:231) | monetarie_spi.bacen_inbound | `idx_spi_bacen_in_resource` btree `(resource_id)` |
| reconciliações camt.054/053 (pix_in_orphan_reconciliation.ex:190, daily_reconciliation.ex:310, reconciliation.ex:394) | monetarie_spi.bacen_inbound | `idx_spi_bacen_in_msgtype_time` btree `(message_type, receive_time DESC)` |
| correlação de fluxo do Monitor `response_msg_id = ANY($1)` (monitor_controller.ex:88) | monetarie_spi.camt060_requests | `idx_spi_camt060_response_msg_id` btree `(response_msg_id)` |

`20260714211000_add_messages_original_e2e_index.exs` (SEM CONCURRENTLY de propósito: `messages` é particionada e o PG rejeita CONCURRENTLY no pai, erro 0A000, provado ao vivo em 20260704150000; o índice é PARCIAL e minúsculo, só devoluções preenchem a coluna):

| Query quente | Tabela | Índice criado |
|---|---|---|
| timeline OR `original_end_to_end_id == e2e` (payment_controller.ex:646) | monetarie_spi.messages | `idx_spi_messages_orig_e2e` btree parcial `(original_end_to_end_id) WHERE original_end_to_end_id IS NOT NULL` |

Já existiam e NÃO precisaram de nada: `message_history` tem `idx_spi_msghist_msgid_time_desc (message_id, history_time DESC)` (baseline 11813), que serve o `/history` e a timeline; `messages` já tem btree e GIN trigram em `end_to_end_id` (`idx_spi_messages_e2e`, `idx_spi_messages_e2e_trigram`), servindo os `ILIKE` de busca da lista (payment_controller.ex:1089,1093). `pg_trgm` já era criada no baseline do PIX.

### Core (`core/backend/priv/repo/migrations/`)

`20260714220000_add_index_review_hot_indexes.exs`. Dois defeitos reais:

1. REGRESSÃO DE PARTICIONAMENTO: `account_entries` foi reconstruída particionada em 20260308400004 com `LIKE ... EXCLUDING INDEXES` e ninguém recriou os índices secundários. Sobrou só a PK `(id, entry_date)`. O extrato por conta e período (`admin/account_entries_controller.ex:140-160,246`, `admin/bank_accounts_controller.ex:400,534`) filtra `account_id = ? AND entry_date BETWEEN ?` com `ORDER BY entry_date, inserted_at` e fazia seq scan por `account_id` dentro de cada partição mensal (231.412 lançamentos em HML na carga de 2026-07-01).
2. A busca da tela de Transações (`admin/transactions_controller.ex:225-232` e `coreproviders_parity_controller.ex:2091-2094`) faz OR de 3 ILIKEs `'%x%'` (`transaction_id`, `end_to_end_id`, `recipient_key`); `recipient_key` não tinha índice nenhum e a extensão `pg_trgm` NEM EXISTIA no mon_core (só citext, pgcrypto, uuid-ossp).

| Query quente | Tabela | Índice criado |
|---|---|---|
| extrato conta+período (account_entries_controller.ex:140-160) | account_entries (particionada) | `account_entries_account_date_idx` btree `(account_id, entry_date, inserted_at)` |
| busca de Transações OR de 3 ILIKEs (transactions_controller.ex:225-232) | transactions (particionada) | `tx_search_trgm_idx` GIN multicoluna `(transaction_id, end_to_end_id, recipient_key) gin_trgm_ops` |

Como as duas tabelas são particionadas, a migration usa a receita segura do precedente 20260623100000: `CREATE INDEX ... ON ONLY pai` (sem lock de dados) + `CREATE INDEX CONCURRENTLY` por partição + `ALTER INDEX ... ATTACH PARTITION`, com guarda de índice inválido e de attach repetido. Zero bloqueio de escrita no money path; partições futuras do PartitionMaintenance herdam o índice do pai. `CREATE EXTENSION IF NOT EXISTS pg_trgm` incluído.

### SPB (`spb/services/bacen_gateway/priv/repo/migrations/`)

`20260714213000_add_index_review_hot_indexes.exs` (tudo CONCURRENTLY). mon_spb não tinha pg_trgm nem nenhum índice GIN.

| Query quente (arquivo:linha) | Tabela | Índice criado |
|---|---|---|
| busca da lista de Mensagens, OR de 6 ILIKEs (messages.ex:169-181, count em 298-310) + Arquivo com `id::text`/`operation_id::text` (archive_controller.ex:81-98) | bacen_messages | `bacen_messages_search_trgm_idx` GIN multicoluna 8 entradas `(message_type, message_id, sender_ispb, receiver_ispb, control_number, nuop, (id)::text, (operation_id)::text) gin_trgm_ops` |
| busca da tela de Pagamentos, OR de 3 ILIKEs (payments_controller.ex:54) | spb_operations | `spb_operations_search_trgm_idx` GIN `(message_type, (id)::text, sender_ispb) gin_trgm_ops` |
| correlação R1/R2 do money path (inbound_processor.find_operation_by_ctrl:811-820; backfills orphan_response_link.ex:136-148, error_backfill.ex:68-80) | spb_operations | `spb_operations_msgtype_ctrl_idx` btree `(message_type, control_number)` |
| correlação de respostas (lifecycle_engine.lookup_by_correlation:2072-2135; response_correlation.ex:33-42) | bacen_messages | `bacen_messages_msgtype_ctrl_idx` btree `(message_type, control_number)` e `bacen_messages_ctrl_clearing_idx` parcial `(control_number_clearing) WHERE ... IS NOT NULL` |
| saldo por conta+período (posting_recorder.get_balance_by_account:208-219) | accounting_entries | `accounting_entries_account_posting_idx` parcial `(account_code, posting_date) WHERE reversal_of_id IS NULL` |
| sweeper de operação presa no MQ (stale_operation_sweeper.ex:100-106) | spb_operations | `spb_operations_stale_mq_idx` parcial na expressão exata `((COALESCE(sent_at, created_at))) WHERE state = 'sent_to_mq'` |
| sweeper de inbound preso (stale_inbound_sweeper.ex:62-83) | bacen_messages | `bacen_messages_stuck_inbound_idx` parcial `(updated_at) WHERE direction='inbound' AND state='processing' AND content->>'audit_status'='retrying'` |
| varredura de órfãs dos backfills (orphan_response_link.ex:77-94, error_backfill.ex:42-51, inbound_str_credit.ex:56-65) | bacen_messages | `bacen_messages_inbound_orphan_idx` parcial `(message_type, created_at) WHERE direction='inbound' AND operation_id IS NULL` |

Racional do GIN multicoluna: num OR, TODOS os braços precisam de índice para o planner montar um BitmapOr; um braço sem índice devolve a query inteira ao seq scan. GIN multicoluna atende qualquer subconjunto de colunas, então um único índice cobre a lista, a contagem e o Arquivo.

## 3. Testes criados

Padrão do repo (pg_indexes/pg_index, precedente em `spb .../lifecycle_engine_inbound_str_credit_test.exs:164`). Além da existência, os testes verificam `pg_index.indisvalid` (um build CONCURRENTLY que falha deixa índice INVALID) e, no Core, que TODA partição tem o filho anexado.

- `pix/backend/apps/shared/test/shared/index_review_indexes_test.exs` (5 testes)
- `spb/services/bacen_gateway/test/bacen_gateway/index_review_indexes_test.exs` (7 testes)
- `core/backend/test/monetarie/index_review_indexes_test.exs` (4 testes)

## 4. Alternativa ESTRUTURAL ao ILIKE em xml_content (documentada, não aplicada)

O GIN trigram resolve a dor agora sem tocar em código (payment_controller e pix_handler estão com outra frente). A solução definitiva é parar de procurar E2E DENTRO do XML:

1. `ALTER TABLE monetarie_spi.bacen_outbound ADD COLUMN end_to_end_id varchar(32)`; idem `bacen_inbound`.
2. Gravar no insert: o ponto único de escrita é `Shared.Audit.BacenLogger.log_exchange/1` (extrai por regex `<EndToEndId>...</EndToEndId>` ou `PI-EndToEndId` do header quando presente).
3. Backfill em lotes (UPDATE ... WHERE end_to_end_id IS NULL com regexp_substr do xml_content, em janelas por send_date/receive_date).
4. `CREATE INDEX CONCURRENTLY` btree na coluna nova e trocar o `maybe_or_ilike_xml` por igualdade.
5. Só então avaliar DROP dos GIN trigram (eles seguem servindo a REDA, que procura ISPB e MsgId dentro do XML, não E2E).

Custo do paliativo atual: GIN trigram sobre XML integral é o índice mais caro da revisão (tamanho da ordem do próprio acervo e custo de escrita a cada insert). É o preço de não tocar no money path agora.

## 5. Particionamento: estado atual e plano (NADA foi particionado nesta revisão)

### Já particionadas (RANGE mensal)

- PIX `monetarie_spi.messages` (operation_time), `monetarie_auth.activity_log` (created_at), `monetarie_auth.login_history` (logged_in_at). Runway: `Shared.PartitionManager` (GenServer diário, lookahead 60 dias, purge de auditoria).
- Core `transactions`, `inflow_requests`, `outflow_requests` (started_at), `account_entries` e `cosif_journal_entries` (entry_date, 2024-2028), `fee_transactions` (created_at), `fee_split_transactions` (charged_at), `message_timeline` (received_at). Runway: Oban `PartitionMaintenance` + migration 20260704133000 (estendeu message_timeline/fee_* até 2028-12).
- SPB: NENHUMA tabela particionada.

### Tabelas de acervo que crescem SEM partição (plano proposto, ordem de prioridade)

| Base | Tabela | Volume conhecido/esperado | Chave proposta | Quando particionar |
|---|---|---|---|---|
| PIX | monetarie_audit.xml_audit_logs | maior crescimento do PIX: TODA troca ICOM/DICT vira linha (na carga real de 13-14/07, 210 PIX geraram centenas de linhas; retenção 10 anos ICOM) | RANGE mensal `created_at`, migrar purge de `retention_expires_at` para DROP de partição | primeiro candidato; quando passar de ~5-10M linhas ou o Monitor degradar |
| PIX | monetarie_spi.bacen_outbound / bacen_inbound | acervo BACEN cru; cresce com todo envio/recebimento; ETL legado PIX tinha 3,37M linhas de staging como referência de ordem de grandeza | RANGE mensal `send_date` / `receive_date` (as PKs já contêm colunas de tempo em outbound; em inbound exigirá PK `(ispb, resource_id, receive_?)`, mudança de PK, planejar com calma) | segundo candidato; hoje os índices novos seguram as telas |
| PIX | monetarie_spi.message_history | cresce N transições por mensagem | RANGE mensal `history_time` (PK já contém history_time) | quando messages girar >1M/mês |
| PIX | monetarie_dict.cid_events / cid_files | sync CID pagina eventos do BACEN inteiro | RANGE mensal em `inserted_at` | baixa urgência |
| SPB | bacen_messages | 40.643 no seed dev; acervo AutBank importado; 2 mensagens+ por operação | RANGE mensal `created_at` | quando passar de ~2-5M; ANTES disso resolver a dívida: NENHUMA tabela SPB é particionada e o volume TED da SCD é baixo |
| SPB | spb_operations | 16.330 seed; 7.016 ops import AutBank em produção | RANGE mensal `created_at` | acompanha bacen_messages |
| SPB | accounting_entries / balance_movements / operation_events / audit_logs | N legs por operação | posting_date / position_date / occurred_at | baixa urgência |
| Core | audit_logs | append-only alto (purge por data já existe em workers/audit/purge_worker.ex:35) | RANGE mensal `inserted_at`, purge vira DROP de partição | quando purge por DELETE começar a doer |
| Core | webhook_deliveries, processed_messages, outbound_requests | append-only médio | RANGE mensal `inserted_at` | baixa urgência |

Regras para a execução futura (lições já pagas neste repo): PG rejeita `CREATE INDEX CONCURRENTLY` em pai particionado (0A000), todo UNIQUE precisa conter a chave de partição (20260704150000), toda tabela nova particionada entra no runway (PartitionManager no PIX, PartitionMaintenance no Core) ou estoura INSERT sem partição destino (20260704133000).

## 6. Gaps registrados e NÃO atacados agora (com motivo)

- PIX `xml_audit_logs`: busca do Monitor com LIKE em 5 colunas (monitor_controller.ex:537-539) e filtros de categoria por `http_path LIKE '%...%'` (450-485). Servidos hoje pela janela `created_at`; trigram nas 6 colunas encareceria a ESCRITA do log de auditoria (caminho de toda mensagem ICOM). Criar apenas se o operador usar janelas largas com busca livre.
- PIX `monetarie_dict.keys` (reports.ex:287-288), `refund_requests` (reports.ex:110-111 + `(status, inserted_at DESC)` do dict_proxy_controller.ex:381-393), `bacen_pix_participants` (901 linhas), `institutions`: tabelas pequenas ou telas frias na SCD hoje. Reavaliar com volume.
- Core: buscas ILIKE de `users`/`accounts`/`cooperative_members`/`med_cautelar_blocks`/`business_onboardings` e ~25 contexts (lista exaustiva no levantamento): tabelas de 1,5-2k linhas, seq scan é barato. O fragment `COALESCE(bacen_reference, end_to_end_id, metadata->>'num_ctrl_str') ILIKE` do coreproviders_parity_controller.ex:1409 exigiria índice de expressão exato, ferramenta interna de paridade, não prioritário.
- SPB `bacen_error_codes` (5.314 linhas, referência): seq scan barato.
- SPB `lookup_by_correlation` (lifecycle_engine.ex:2090-2104): os ~15 braços `content->>'NumCtrlIF' = $2` em OR com tudo tornam a query não indexável por definição. Dívida de DESIGN: quebrar em tentativas sequenciais por chave (colunas primeiro, JSONB por último) ou materializar as chaves em colunas. Registrado, não é índice.

## 7. Defeitos LATENTES achados pela revisão (código, fora do mandato de índices)

1. SPB `circular_3290_generator.ex:375` filtra `bm.xml_payload LIKE ...`, mas a coluna `xml_payload` NÃO existe em nenhuma migration nem no dump (as colunas reais são `xml_content` e `response_xml`). A query quebraria em runtime; por isso NÃO foi criado índice.
2. SPB `str/inbound_processor.ex:830-837` consulta `spb_operations.num_ctrl_str`, coluna inexistente em migrations/dump (o erro é engolido pelo `case Repo.query`, fallback nil). Mesmo tratamento.
3. PIX `settlement_service message_controller.ex:679-757` seleciona `m.id`, `m.processing_status`, `m.xsd_valid`, `m.channel`, `m.message_type` de `bacen_outbound`, que não tem NENHUMA dessas colunas (baseline linha 4965). Endpoint de listagem quebrado em silêncio ou morto.
4. A tela do Arquivo do SPB busca `id::text ILIKE '%x%'` sobre UUID (archive_controller.ex:87): funciona e agora está indexado, mas procurar fragmento de UUID é sintoma de tela sem chave de busca melhor.

## 8. Validação

- Migrations aplicadas nos bancos de teste dos 3 backends pelas próprias suítes (alias `test` roda `ecto.migrate`).
- Testes novos: PIX 5/5, SPB 7/7, Core 4/4.
- Suítes completas dos 3 backends: resultado registrado no fechamento da sessão (obrigatório: zero regressão sobre PIX 3835/0, SPB 17664/0, Core 7315/0).
- Produção: as migrations são idempotentes (IF NOT EXISTS, drop de índice INVALID antes de recriar no Core) e não bloqueiam escrita (CONCURRENTLY; no Core, dance ON ONLY + attach por partição). Deploy = rodar migration normal de cada serviço. Rollback = `down` de cada migration (DROP INDEX, sem tocar em dado).