A ilusão de culpar o índice quando o storage em rede bateu no teto de IOPS

Toda vez que uma consulta em PostgreSQL começa a demorar centenas de milissegundos em produção, a reação instintiva da engenharia se divide em dois campos opostos e igualmente barulhentos. De um lado, estão os que cravam que o desenvolvedor não sabe escrever SQL, que o schema foi desenhado sem pensar no motor relacional e que a solução é entupir a tabela de índices compostos ou enfiar Redis na frente. Do outro, os que acusam o banco de ser uma tecnologia arcaica que deveria ser substituída por soluções customizadas ou hardware exótico.

Essas duas posturas erram o alvo com frequência porque ignoram a física do ambiente onde o banco roda. Há uma diferença brutal entre um plano de execução ineficiente que varre páginas demais na memória e o colapso silencioso do storage gerenciado em nuvem, onde o teto artificial de IOPS e a latência de rede transformam cada leitura em disco num gargalo intransponível.

O abismo entre buffer cache e disco em rede

O PostgreSQL foi desenhado sob a premissa de que páginas de 8 KB residem no buffer pool do sistema operacional e no shared_buffers do processo. Quando uma leitura precisa sair dessa camada de memória e buscar dados persistidos, o comportamento varia de forma drástica dependendo da topologia de hardware.

Em servidores dedicados ou VPS com NVMe local conectado diretamente ao barramento PCIe, uma leitura aleatória leva cerca de 20 a 50 microssegundos. Já em volumes de bloco anexados por rede, como os discos gerenciados padrão dos grandes provedores de nuvem, essa mesma operação física raramente custa menos de 1 a 3 milissegundos. Estamos falando de uma penalidade de duas ordens de magnitude por bloco lido.

Se uma consulta precisa ler 500 páginas fora do cache:

• No NVMe local: 500 páginas vezes 40 microssegundos representam 20 milissegundos de I/O puro.
• No storage anexado por rede: 500 páginas vezes 2 milissegundos significam 1 segundo inteiro de espera síncrona.

Criar um índice novo para transformar um Seq Scan em um Index Scan pode reduzir o volume bruto de dados avaliados, mas se o índice for grande o bastante para não caber no cache, cada salto na árvore B-Tree exige travessias adicionais que batem diretamente na rede do storage. O resultado prático é que o índice novo pode inclusive piorar a latência da consulta sob carga concorrente.

A armadilha do throttling de IOPS e créditos de rajada

O detalhe perverso dos discos de nuvem gerenciados é o modelo de cobrança baseado em IOPS provisionados e créditos de rajada (burst buckets). Enquanto a aplicação tem pouco tráfego, o volume consome créditos acumulados e entrega latências aceitáveis. Quando a carga sobe e a cota se esgota, o provedor impõe throttling imediato.

A partir desse momento, as requisições de I/O entram em filas no driver do kernel da máquina virtual. O sintoma visível no PostgreSQL não é erro de sintaxe ou crash; é uma explosão súbita em métricas como iowait no sistema operacional e eventos de espera do tipo IO:DataFileRead ou IO:WALWrite no próprio banco.

Se a equipe analisa apenas o tempo total da query sem observar os eventos de espera, conclui erroneamente que o plano de consulta degradou. Começam as tentativas desesperadas de adicionar índices, reescrever joins ou forçar hints, enquanto o problema real é que o storage simplesmente se recusa a entregar mais do que a cota contratada permite.

Como diagnosticar se o gargalo é de plano ou de storage

Para separar um problema algorítmico de saturação física de infraestrutura, a saída padrão do EXPLAIN ANALYZE precisa ser enriquecida com o parâmetro de buffers:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, updated_at
FROM pedidos
WHERE cliente_id = 48291
ORDER BY criado_em DESC
LIMIT 50;

A inspeção desses blocos revela a verdade:

Limit  (cost=0.42..15.20 rows=50 width=24) (actual time=1.820..420.150 rows=50 loops=1)
  Buffers: shared hit=12 read=380
  ->  Index Scan using idx_pedidos_cliente on pedidos  (cost=0.42..295.10 rows=1000 width=24) (actual time=1.815..420.130 rows=50 loops=1)
        Index Cond: (cliente_id = 48291)
        Buffers: shared hit=12 read=380
Planning Time: 0.120 ms
Execution Time: 420.210 ms

No exemplo acima, a consulta executou um Index Scan, comprovando que o planejador encontrou e usou o índice correto. No entanto, foram necessárias 380 leituras reais (shared read=380) contra apenas 12 acertos em memória (shared hit=12).

Se a execução levou 420 milissegundos para ler apenas 380 blocos, isso significa uma média superior a 1 milissegundo por bloco de 8 KB. O gargalo não é o índice nem o SQL: é a latência de entrega do disco.

Para confirmar se o storage está sofrendo contenção no nível do sistema operacional, ferramentas simples como iostat mostram a fila de espera e o tempo de serviço:

iostat -xz 1

Foque em duas colunas cruciais:

• await ou r_await: tempo médio em milissegundos que as requisições de leitura levaram desde o envio até o atendimento. Números acima de 2 a 5 ms em volumes SSD indicam contenção de rede ou estrangulamento do provedor.
• %util: percentual de tempo em que o dispositivo esteve ocupado. Em discos virtuais de nuvem, esse valor pode bater 100% mesmo com baixa vazão em MB/s se o limite de operações por segundo tiver sido atingido.

Dentro da engine, a visão pg_stat_activity expõe o estado das conexões travadas:

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY 1, 2
ORDER BY 3 DESC;

Se a maioria das sessões ativas estiver acumulada em wait_event_type = 'IO', qualquer esforço de refatoração de código na aplicação terá impacto marginal até que o problema de I/O seja resolvido.

O mito da flexibilidade operacional

Muitos engenheiros defendem o armazenamento de bloco gerenciado alegando facilidade de snapshots, migração a quente e redimensionamento sem desligar a máquina. O trade-off raramente admitido é que essa comodidade custa caro e cobra um pedágio permanente em cada operação transacional síncrona, principalmente durante a escrita do Write-Ahead Log (WAL).

Cada COMMIT exige a descarga forçada (fsync) do log no disco. Em volumes remotos, cada commit espera pelo ciclo completo de ida e volta da rede até o storage do provedor confirmar a persistência. Quando a taxa de escrita aumenta, o gargalo se agrava exponencialmente.

Em arquiteturas que rodam sobre bare metal ou instâncias com discos NVMe locais, a latência de fsync cai para frações de milissegundo. A flexibilidade operacional que a nuvem vende com seus volumes de rede pode ser compensada de forma mais eficiente com réplicas de leitura em streaming contínuo e ferramentas maduras de backup físico como pgBackRest ou WAL-G, que enviam dados para armazenamento de objetos de forma assíncrona sem comprometer o caminho crítico da transação.

O que fazer antes de queimar orçamento

Antes de pagar o triplo por classes mais caras de IOPS provisionados ou reescrever camadas inteiras da aplicação:

1. Avalie o tamanho do conjunto de trabalho ativo em relação à memória RAM. Se os dados mais acessados não cabem no shared_buffers e no cache do sistema, você sempre estará à mercê do disco. Aumentar a memória costuma ser mais barato do que tentar compensar I/O ruim com IOPS provisionados.
2. Identifique os índices que não são usados e descarte-os. Cada índice existente consome espaço em cache e exige escritas adicionais durante operações de INSERT, UPDATE e DELETE, consumindo a cota de I/O que as leituras precisam.
3. Considere partições de tabelas para manter os dados históricos arquivados e os dados recentes concentrados em espaços menores, aumentando a taxa de acerto do cache.
4. Se o limite de desempenho foi atingido e o volume continua estrangulado mesmo com consultas limpas, aceite o fato de que a abstração de storage em rede chegou ao limite da física. Mover a base de dados para instâncias com armazenamento NVMe local e replicação contínua é frequentemente o único caminho sustentável para bancos com exigência real de baixa latência.

Fontes

• https://clickhouse.com/blog/posette-talk-recap-postgres-isnt-slow-your-storage-is