Mesmo índice, mesma consulta, desempenho diferente: o que o layout do heap revela numa migração de PostgreSQL para a nuvem

Um caso relatado pela equipe de suporte do Azure↳Azure76 conteúdosDeploy de Azure Stream Analytics job com CI/CD usando Azure PipelinesDevSecOps · mai 2019Azure – Como criar uma base de dados SQL no Microsoft Azure pronta para ser utilizadaData · abr 2019Azure Static Web Apps com Vue.js e Visual Studio CodeDevSecOps · out 2023Ver tudo em DevSecOps → Database for PostgreSQL↳PostgreSQL11 conteúdosPostgreSQL via SSL com GolangData · abr 20195 itens legais sobre data types do PostgreSQLData · mar 20195 serviços gratuitos na cloud para bancos de dados PostgresData · fev 2025Ver tudo em Data → mostra que o plano de execução (Index Scan) pode ser idêntico em dois ambientes e, ainda assim, esconder uma diferença brutal: quantas páginas de heap a consulta precisa visitar.

O sintoma: mesmo plano, desempenho muito diferente

Um engenheiro que presta suporte a clientes do Azure Database for PostgreSQL relatou no Planet PostgreSQL (também publicado no blog da Microsoft para PostgreSQL) um caso de migração on-premises para a nuvem. A consulta levava algumas dezenas de milissegundos no ambiente de origem. No ambiente de comparação em Azure, a mesma consulta, usando o mesmo índice, chegava a 1-2 segundos em cache frio. Rodar de novo ficava rápido, porque as páginas entravam em cache. Assim que o cache era esvaziado, o tempo alto voltava.

A primeira suspeita recaiu sobre o storage: a diferença de consumo de IOPS entre os dois servidores (de 10% a 20% a mais no ambiente que seria produção) parecia apontar para isso. A segunda suspeita foi o índice: bloat, fragmentação, densidade de folha. O cliente rodou VACUUM FULL e não viu melhora nenhuma. É exatamente o tipo de diagnóstico raso que qualquer DBA já viu antes: olhar para o sintoma errado porque o plano de execução não muda, mas o trabalho por trás dele muda bastante.

Descartando as suspeitas óbvias

A equipe usou pg_stattuple e pg_statindex para inspecionar densidade de folha e fragmentação do índice. Não havia nada fora do padrão. A telemetria de storage também não indicava problema de infraestrutura. Com as duas hipóteses mais fáceis descartadas, a equipe voltou ao princípio básico de como um Index Scan funciona: o índice localiza as tuplas correspondentes, mas depois é preciso visitar as páginas de heap da tabela para buscar os dados, porque o heap não é ordenado como o índice.

Em resumo: duas consultas podem exibir Index Scan no EXPLAIN e, mesmo assim, fazer quantidades de trabalho completamente diferentes. Se 200 linhas retornadas estão concentradas em cinco páginas de heap, o custo de I/O é baixo. Se as mesmas 200 linhas estão espalhadas por 100 páginas, o Postgres precisa trazer um conjunto de trabalho muito maior para a memória, mesmo usando o índice certo e com o plano de execução aparentemente idêntico.

Correlation: a pista que não fecha a conta sozinha

A equipe comparou EXPLAIN (ANALYZE, BUFFERS) nos dois ambientes, olhando além do tipo de scan para hits e reads de buffer. Vale o alerta técnico feito no relato: um shared-buffer read significa apenas que a página não estava nos shared buffers do PostgreSQL, não necessariamente que veio do disco físico, já que o sistema operacional pode mantê-la em cache. Habilitar track_io_timing ajuda a distinguir leituras baratas de leituras caras nesse cenário.

Uma pista relevante apareceu em pg_stats:

SELECT correlation
FROM pg_stats
WHERE schemaname = 'public'
AND tablename = 'order_items'
AND attname = 'order_id';

correlation mede o quanto a ordem lógica de uma coluna acompanha a ordem física da tabela. Valores próximos de 1 ou -1 indicam ordenação forte; perto de 0, pouca relação. O planejador usa essa estatística para estimar o custo de um index scan. No ambiente lento, a correlação ficava em torno de 0,037; no ambiente de comparação, em torno de 0,571. A diferença era grande demais para ser ruído.

Mas correlation é uma estatística amostrada e global para a coluna inteira. Ela não diz quantas páginas de heap contêm as linhas de UMA busca específica. É perfeitamente possível ter grupos de linhas relacionadas compactados fisicamente sem que esse padrão apareça numa ordenação global. Era preciso medir exatamente o que a consulta precisava, não uma média da tabela.

O diagnóstico definitivo: contar páginas de heap pelo ctid

O PostgreSQL expõe a coluna de sistema ctid, que identifica a localização física de uma tupla como (block_number, tuple_number). Extraindo o número do bloco, dá para contar quantas páginas de heap distintas contêm as linhas de uma busca específica:

SELECT
 count(*) AS rows_found,
 count(DISTINCT split_part(
 trim(both '()' FROM ctid::text), ',', 1
 )) AS distinct_heap_pages
FROM public.order_items
WHERE order_id = 12345;

Para tabela particionada, o ajuste é contar pares distintos de (tableoid, block_number), já que partições diferentes podem reaproveitar os mesmos números de bloco. Vale lembrar que ctid é uma ferramenta de diagnóstico pontual: updates e reescritas de tabela mudam o valor, então não serve como identificador permanente de linha.

Os números para dois valores de busca representativos deixaram o problema evidente:

Para a busca B, o ambiente lento precisava visitar cerca de 20 vezes mais páginas de heap para recuperar o mesmo conjunto de linhas. Esses números não contam a história inteira do I/O (páginas de índice e outros acessos também pesam), mas expõem com precisão o footprint físico daquela consulta. O índice não estava falhando em encontrar as linhas certas. As linhas estavam, simplesmente, espalhadas por muito mais páginas.

Como os dados ficaram espalhados

O cliente havia carregado os dados no ambiente Azure em paralelo, uma prática comum para ganhar throughput de carga. O problema é que o carregamento concorrente intercalou linhas de diferentes valores de busca no heap de destino. Linhas que estavam fisicamente agrupadas na origem deixaram de ficar juntas.

Isso não é uma regra automática: carga paralela não garante tabela espalhada por si só, o resultado depende de como o trabalho é dividido e de como os inserts chegam ao heap. Neste caso específico, porém, o método de carga explicou integralmente a diferença de layout observada entre os dois ambientes.

Um detalhe que todo DBA deveria internalizar: recriar o índice não resolveu nada. Um B-tree ordena suas próprias entradas; construir ou reconstruir o índice não reordena a tabela subjacente. O índice estava correto, o problema era físico, no heap.

Isso também explica o comportamento de cache observado no início: execuções repetidas se beneficiavam das páginas já em cache, mascarando o custo real do footprint maior. Quando essas páginas eram despejadas, a consulta precisava buscá-las de novo, e um layout mais espalhado significa mais páginas disputando espaço de cache.

VACUUM FULL não resolve, CLUSTER e pg_repack exigem cautela

VACUUM FULL reescreve a tabela para recuperar espaço de tuplas mortas e compactar o heap, mas não ordena as linhas sobreviventes pela chave de busca. Pode até reduzir contagem de páginas se houver bloat, mas linhas relacionadas continuam intercaladas com linhas não relacionadas depois da operação. O problema aqui não era fragmentação ou bloat: era sobre quais tuplas ficam arranjadas juntas no disco.

O passo seguinte é reorganizar o heap em torno da chave de busca. Duas ferramentas resolvem isso, com trade-offs distintos:

• CLUSTER: reescreve a tabela na ordem do índice, mas exige um ACCESS EXCLUSIVE lock durante a operação, o que obriga a agendar uma janela de manutenção criteriosa em produção.
• pg_repack com --order-by: foi a opção usada pelo cliente, reorganizando os dados numa instância de teste restaurada, com relato de melhora de performance e sem o lock exclusivo do CLUSTER.

Nenhuma das duas é operação livre de manutenção: antes de escolher, verifique suporte da extensão, espaço livre disponível e requisitos de locking do ambiente específico. Depois de rodar qualquer uma delas, atualize as estatísticas com ANALYZE, repita a consulta de páginas distintas via ctid e compare planos e tempos sob condições de cache comparáveis. Um alerta prático do relato vale ser repetido: a própria consulta diagnóstica aquece páginas, então não trate a execução imediatamente seguinte como teste de cache frio.

Também é preciso entender que nem CLUSTER nem pg_repack mantêm a ordenação para sempre. Escritas futuras corroem a localidade física aos poucos, então tanto a estratégia de carga de uma migração quanto o padrão de escrita contínuo da aplicação merecem atenção permanente, não um ajuste único.

O que isso muda para quem está migrando para a nuvem

O ponto central do caso não é "Azure precisa de disco maior" nem "não carregue dados em paralelo". É que migrar as mesmas linhas e os mesmos índices não garante migrar o mesmo layout físico. Um EXPLAIN idêntico, com o mesmo tipo de scan e o mesmo índice, pode esconder quantidades de I/O completamente diferentes dependendo de como as linhas ficaram dispostas no heap de destino.

Para quem planeja migração de PostgreSQL on-premises para qualquer provedor de nuvem, o caso sugere um checklist mínimo antes de culpar storage ou índice: olhar correlation em pg_stats como primeiro sinal, confirmar com contagem de páginas distintas via ctid para as chaves de busca reais da aplicação, e só então decidir entre CLUSTER e pg_repack para corrigir o layout. Ignorar esse passo e trocar de instância ou subir IOPS é tratar o sintoma errado com a ferramenta errada.

Fonte: Planet PostgreSQL