Ajuste de Índice MergeTree no ClickHouse em Produção

Quando o I/O de disco saturou em 98% durante o pico de ingestão, o ajuste de índice MergeTree no ClickHouse tornou-se urgente pois a latência p99 saltou para 4,2 segundos. Os painéis de monitoramento em tempo real estavam violando os SLOs porque os processos de merge em segundo plano competiam com a escrita de fluxos contínuos. A causa raiz não era o hardware dos servidores, mas uma chave primária super-indexada combinada com uma granularidade de índice agressiva que forçava o mecanismo a escanear centenas de marcas de dados desnecessárias para consultas simples por intervalo.

Engenheiros enfrentando pressões de ingestão em tempo real frequentemente tratam o ClickHouse como um banco de dados relacional tradicional, assumindo que chaves primárias garantem unicidade ou criam caminhos de busca B-Tree densos. Na realidade, o ClickHouse depende de índices esparsos que indexam blocos de dados em vez de linhas individuais. Quando os projetistas de esquema incluem colunas de alta cardinalidade na frente da chave ORDER BY ou configuram a granularidade muito baixa, a amplificação de leitura e escrita sai do controle.

Para estabilizar métricas operacionais, plataformas de dados que servem fluxos de eventos—como nossa Streaming Radar API—exigem alinhamento rigoroso de índices. Neste guia, analisamos a mecânica do índice esparso, calculamos custos de memória, otimizamos as configurações de granularidade e demonstramos uma estratégia de migração em tempo real para grandes tabelas sem tempo de inatividade.

Compreendendo Índices Esparsos do ClickHouse vs B-Trees Relacionais

Mecanismos relacionais tradicionais, como o PostgreSQL, constroem B-Trees densas onde cada linha inserida recebe uma entrada no índice. Isso garante buscas em tempo constante $O(\log N)$ para registros individuais, mas introduz penalidades massivas de IOPS de escrita à medida que a tabela cresce para bilhões de linhas. O ClickHouse inverteu essa troca utilizando uma arquitetura de índice esparso dentro da família de motores MergeTree.

No ClickHouse, os dados são armazenados no disco fisicamente ordenados pelas colunas definidas na cláusula ORDER BY. O arquivo de índice primário (primary.idx) não contém um ponteiro para cada linha. Em vez disso, ele extrai uma entrada de índice para cada granulo de linhas (por padrão, 8.192 linhas). Se uma tabela contém 81.920.000 linhas, seu arquivo primary.idx conterá exatamente 10.000 marcas de índice.

Quando uma consulta é executada com uma condição WHERE que corresponde à chave primária, o ClickHouse executa uma busca binária sobre o primary.idx para identificar os grânulos candidatos. Ele então lê apenas os blocos de dados físicos correspondentes do disco para a memória. Se as colunas da chave primária estiverem dispostas incorretamente, o ClickHouse não conseguirá podar grânulos de forma eficaz, resultando em amplificação de leitura onde gigabytes de dados compactados desnecessários são descompactados da memória secundária.

Como a Escolha Incorreta da Chave Primária Gera Amplificação de Leitura e Escrita

A seleção da ordem da chave primária no ClickHouse exige a compreensão de como a filtragem funciona em múltiplas colunas. Considere uma tabela de telemetria contendo tenant_id, event_type, timestamp e device_id. Se um engenheiro definir ORDER BY (timestamp, device_id, tenant_id), consultas filtrando principalmente por tenant_id executarão varreduras completas na tabela em todas as partições.

Como os dados estão fisicamente ordenados por timestamp em primeiro lugar, os eventos de um tenant_id específico ficam espalhados por quase todos os grânulos no disco. O índice esparso não consegue podar nenhuma marca de dados. Por outro lado, colocar device_id (um UUID de alta cardinalidade) como o primeiro elemento da chave primária cria uma ordenação quase única. Embora buscas pontuais para um dispositivo específico fiquem rápidas, as mesclagens em segundo plano se tornam extremamente dispendiosas, e consultas por intervalo de tempo sofrem degradação catastrófica.

Quando os processos de merge em segundo plano (background_pool_size) combinam partes de dados, prefixos de alta cardinalidade quebram a localidade espacial. A thread de merge precisa realizar intercalações complexas entre valores fragmentados, levando a utilização de IOPS de disco ao limite e gerando fatores de amplificação de escrita superiores a 15x. Como destacado em análises de arquitetura como a Daily Trend Briefing, equilibrar custos de infraestrutura e velocidade analítica é vital para sistemas modernos em tempo real.

Configurando Granularidade de Índice para Cargas de Trabalho em Produção

O ClickHouse fornece parâmetros de configuração para controlar como os grânulos são construídos e delimitados durante a criação da tabela. As duas configurações principais são index_granularity e index_granularity_bytes.

CREATE TABLE telemetry.sensor_events_v2
(
    tenant_id UUID,
    event_type LowCardinality(String),
    timestamp DateTime64(3, 'UTC'),
    device_id UUID,
    metric_value Float64,
    payload String CODEC(ZSTD(3))
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
PRIMARY KEY (tenant_id, event_type, timestamp)
ORDER BY (tenant_id, event_type, timestamp, device_id)
SETTINGS
    index_granularity = 8192,
    index_granularity_bytes = 1048576,
    min_bytes_for_wide_part = 104857600;

Neste esquema otimizado, separamos PRIMARY KEY de ORDER BY. O ClickHouse permite que a PRIMARY KEY seja apenas um prefixo da cláusula ORDER BY. Isso reduz o tamanho do arquivo primary.idx mantido continuamente na memória RAM, mantendo a ordenação física em disco para o critério secundário device_id.

Por padrão, index_granularity = 8192 cria uma nova marca de índice a cada 8.192 linhas. No entanto, se as linhas contiverem payloads JSON volumosos ou textos não compactados, um único grânulo de 8.192 linhas pode ocupar 500 MB em disco. Quando uma consulta toca esse grânulo, o ClickHouse precisa ler e descompactar 500 MB apenas para inspecionar alguns registros.

A configuração index_granularity_bytes = 1048576 (1 MB) impõe um limite adaptativo. O ClickHouse cria uma nova marca sempre que 8.192 linhas OU 1 MB de dados descompactados forem acumulados—o que ocorrer primeiro. Esse mecanismo adaptativo evita que payloads grandes causem amplificação descontrolada de leitura durante execuções de agregadores analíticos.

Benchmark de Variações de Chave Primária Sob Alta Concorrência

Para demonstrar o impacto de desempenho, executamos um benchmark em um conjunto de dados de 500 milhões de linhas hospedado em um nó ClickHouse com 16 cores, 64 GB de RAM e armazenamento NVMe. Avaliamos três designs distintos de chave primária contra um padrão de consulta filtrando por cliente e janela de 1 hora:

1. Esquema Não Otimizado: ORDER BY (timestamp, tenant_id, device_id)
2. Esquema de Alta Cardinalidade: ORDER BY (device_id, tenant_id, timestamp)
3. Esquema Otimizado Multi-Tenant: PRIMARY KEY (tenant_id, event_type, timestamp) ORDER BY (tenant_id, event_type, timestamp, device_id)

Comparação de Métricas de Benchmark:
----------------------------------------------------------------------------------------
Configuração do Esquema     | Volume Lido | Marcas Lidas | Latência p95 | Uso Pico de IOPS
----------------------------------------------------------------------------------------
1. Não Otimizado (Time)     | 14.2 GB     | 1.733.400    | 3.840 ms     | 96%
2. Alta Cardinalidade (UUID)| 1.1 GB      | 134.200      | 1.120 ms     | 88%
3. Otimizado (Tenant First) | 42 MB       | 5.120        | 48 ms        | 12%
----------------------------------------------------------------------------------------

O layout otimizado alcançou uma redução de 80x na latência e reduziu o volume de leitura em disco de 14,2 GB para apenas 42 MB por consulta. Ao posicionar tenant_id e event_type antes de timestamp, o ClickHouse podou 99,7% das partes de dados na etapa de avaliação do índice antes de realizar lecturas físicas.

Estratégia de Migração Sem Tempo de Inatividade Passo a Passo

Alterar a cláusula ORDER BY ou PRIMARY KEY de uma tabela existente no ClickHouse não pode ser feito no mesmo local com uma simples instrução ALTER TABLE, pois exige a reescrita dos arquivos físicos em disco. Para executar essa alteração em um cluster de produção sem interromper a ingestão contínua, utilize o padrão de tabela espelho (shadow table):

Passo 1: Crie a tabela de destino (sensor_events_v2) com os valores otimizados de PRIMARY KEY, ORDER BY e SETTINGS.

Passo 2: Configure a dupla escrita no seu serviço de ingestão ou ajuste os grupos de consumidores Kafka para popular simultaneamente sensor_events_v1 e sensor_events_v2.

Passo 3: Realize a carga dos dados históricos bloco por bloco utilizando consultas INSERT INTO ... SELECT delimitadas por intervalos de partição para evitar sobrecarregar a memória do sistema:

INSERT INTO telemetry.sensor_events_v2
SELECT * FROM telemetry.sensor_events_v1
WHERE timestamp >= '2026-03-01 00:00:00' 
  AND timestamp < '2026-03-08 00:00:00';

Passo 4: Verifique a paridade de dados entre as tabelas v1 e v2 executando consultas de verificação de checksum por partição no ClickHouse.

Passo 5: Troca atômica de tabelas utilizando o recurso de troca de metadados do ClickHouse:

EXCHANGE TABLES telemetry.sensor_events_v1 AND telemetry.sensor_events_v2;

Este comando de troca é concluído em milissegundos, alternando instantaneamente as referências de tabela nos roteadores de consulta sem derrubar conexões ativas ou falhar chamadas de clientes.

Consultas de Diagnóstico para Monitorar Eficiência do Índice Esparso

Administradores de sistema devem acompanhar continuamente a eficiência do cache de marcas e as métricas de amplificação de leitura dentro das tabelas do sistema. A consulta a seguir identifica tabelas que sofrem com poda ineficiente calculando a média de marcas varridas por consulta executada:

SELECT
    table,
    count() AS query_count,
    round(avg(query_duration_ms), 2) AS avg_duration_ms,
    round(avg(read_rows), 0) AS avg_read_rows,
    round(avg(read_bytes) / 1024 / 1024, 2) AS avg_read_mb,
    round(avg(result_rows), 0) AS avg_result_rows,
    round(avg(read_rows) / nullif(avg(result_rows), 0), 2) AS read_amplification_ratio
FROM system.query_log
WHERE type = 'QueryFinish'
  AND query_kind = 'Select'
  AND event_date >= currentDate() - 1
GROUP BY table
HAVING query_count > 50
ORDER BY read_amplification_ratio DESC
LIMIT 10;

Se a read_amplification_ratio ultrapassar 1.000 para consultas operacionais padrão, a ordem da chave primária está desalinhada com os padrões de acesso. Combinar o monitoramento de tabelas do sistema com políticas rigorosas de design de esquemas—como as detalhadas em nosso Data Governance and Quality Framework—garante que bancos de dados analíticos mantenham perfis de latência determinísticos à medida que o volume total de armazenamento escala.