Por que usamos ClickHouse para análise de cliques (e não Postgres)

Postgres é excelente. Utilizamo-lo intensamente e confiamos nele. Armazena cada link, cada workspace, cada registo de faturação, cada utilizador. Mas por volta dos 90 dias num encurtador de URL em crescimento, depara-se com um muro se também estiver a guardar eventos de clique brutos lá. O muro não é dramático - o Postgres não cai, apenas fica mais lento da forma específica e frustrante em que as queries de agregação em tabelas grandes de apenas acrescento ficam mais lentas. Os index scans deixam de ajudar. O autovacuum não consegue acompanhar. Um dashboard que carregava em 800ms agora demora 4 segundos. Adiciona mais índices. Fica pior.

Este post explica por que migrámos os eventos de clique para ClickHouse, qual é o aspeto do schema, como funciona o caminho de ingestão e onde estão os pontos de atenção. É um complemento ao post sobre latência de redirecionamento e cobre o tier de dados que se encontra a jusante do redirecionamento - a parte que transforma eventos brutos de clique em disparar-e-esquecer nos dashboards da página de soluções de análise.

TL;DR#

• Os eventos de clique são apenas de acrescento e têm a forma de queries analíticas. O armazenamento colunar vence em ambos os aspetos.
• A sobrecarga por linha do Postgres, o inchaço de índices e a contenção de vacuum acumulam-se a partir de ~30M eventos numa tabela partilhada.
• ClickHouse com ORDER BY (workspace_id, link_id, created_at) devolve rollups por workspace de 90 dias em menos de 100ms sem índices extra.
• Os metadados dos links ficam no Postgres (mutáveis, transacionais); os eventos de clique ficam no ClickHouse (apenas de acrescento, analíticos). A separação é limpa porque os dois não precisam de se sobrepor no momento da escrita.

Por que o Postgres tem dificuldades com esta carga de trabalho#

O problema é a forma da carga de trabalho. Um evento de clique é:

• Escrito uma vez e nunca atualizado.
• Escrito a taxas elevadas e irregulares - uma relação de 50:1 entre escrita e leitura é uma estimativa aproximada, e num dia de lançamento de campanha a relação aumenta ainda mais.
• Consultado em agregado: total de cliques por link, distribuição geográfica para um workspace, divisão por dispositivo nos 100 links de maior tráfego de uma campanha. Quase ninguém consulta diretamente uma única linha de clique.

O Postgres é construído para um conjunto diferente de premissas. Cada linha carrega um cabeçalho de tupla, campos de ID de transação (xmin, xmax) e metadados de visibilidade. Para cargas de trabalho transacionais em que se atualizam linhas no lugar e é necessário MVCC para lidar com leituras e escritas concorrentes, essa sobrecarga é o preço que se paga pela correção. Para dados analíticos de apenas acrescento que nunca se voltam a tocar, é desperdício puro.

O problema dos índices é pior. Os índices B-tree do Postgres funcionam bem para lookups pontuais seletivos - encontrar a linha com link_id = 'xyz'. Ajudam menos quando se executa GROUP BY link_id, country em 10 milhões de linhas para um workspace. O índice restringe o scan, mas a query ainda tem de obter dados de linha descomprimidos de páginas heap, desserializá-los e agregá-los. Com 30M linhas a query é lenta. Com 100M linhas é dolorosa.

O autovacuum torna isto pior na prática. Inserções de alto débito geram tuplas mortas (versões de linha MVCC que já não são visíveis para transações ativas mas ainda não foram limpas). O autovacuum recupera-as, mas compete com queries ativas por I/O. Verá picos periódicos de latência nos seus dashboards do Postgres que correspondem à execução do autovacuum na tabela de eventos. A documentação de compressão e arquitetura de chunks do TimescaleDB vale a pena ser lida para os detalhes de como uma extensão do Postgres tenta contornar isto - o problema é suficientemente real para que exista uma camada de séries temporais dedicada sobre o Postgres para o resolver.

Trabalhámos com cliques no Postgres durante os primeiros 90 dias do projeto. Um workspace com ~4M eventos de clique tinha um dashboard que demorava 3,2 segundos a apresentar um rollup de 7 dias por país. Adicionar um índice parcial em (workspace_id, created_at) reduziu para 1,4 segundos. Suficientemente bom na altura; claramente não é um caminho que escala para mais de 100M eventos por mês.

Por que o ClickHouse se encaixa#

O ClickHouse é um motor de base de dados colunar construído explicitamente para cargas de trabalho analíticas - consultado em 2026-05-12. Colunar significa que os dados de cada coluna são armazenados contiguamente em disco em vez de intercalados linha a linha. Para uma query como "somar a contagem de cliques por país para o workspace X nos últimos 30 dias", o motor só lê as colunas country, workspace_id e created_at. Nunca toca em user_agent_hash ou referrer_host. A redução de I/O em tabelas largas com muitas colunas é substancial.

A taxa de compressão é a segunda propriedade. Os dados colunares comprimem significativamente melhor do que os dados por linha porque os valores repetidos na mesma coluna - o mesmo workspace_id aparecendo milhares de vezes, o mesmo conjunto de uns 60 e tal códigos de país, um punhado de valores os - comprimem para quase nada com os codecs LZ4 e ZSTD do ClickHouse. Os nossos eventos de clique comprimem para cerca de 12-15% do seu tamanho bruto em disco. Os mesmos dados no Postgres ficam em 35-45% do tamanho bruto com a compressão toast padrão. Isso não é apenas uma vitória de armazenamento - dados menores em disco significam que mais deles cabem na cache de páginas do SO, o que significa menos leituras de disco por query.

A terceira propriedade é a família de motores MergeTree - consultado em 2026-05-12. As tabelas MergeTree ordenam e agrupam os dados pela chave ORDER BY da tabela. As queries que filtram ou agrupam nas colunas iniciais dessa chave só leem as partes de dados que se sobrepõem ao intervalo do filtro. Este é o equivalente do ClickHouse a um índice agrupado, exceto que é obrigatório (define-se no momento da criação da tabela) e aplica-se a todas as queries, não apenas a colunas indexadas.

Usamos ORDER BY (workspace_id, link_id, created_at). Uma query para os cliques do workspace abc123 nos últimos 7 dias lê apenas as partes de dados onde workspace_id = 'abc123' aparece e, dentro dessas partes, lê apenas o fragmento do intervalo temporal. Em 90 dias de dados de cliques, isto toca tipicamente 3-8% do total de partes de dados. O índice primário esparso que o ClickHouse mantém sobre as colunas ORDER BY torna este targeting eficiente sem necessitar de uma B-tree densa para cada padrão de query. O ReplicatedMergeTree adiciona HA - dois nós ClickHouse com coordenação equivalente a ZooKeeper (ClickHouse Keeper), replicando partes à medida que são escritas.

O schema que utilizamos#

CREATE TABLE clicks
(
    workspace_id    LowCardinality(String),
    link_id         String,
    click_id        UUID,
    created_at      DateTime64(3, 'UTC'),
    country         LowCardinality(FixedString(2)),
    device          LowCardinality(String),
    os              LowCardinality(String),
    browser         LowCardinality(String),
    referrer_host   String,
    utm_source      LowCardinality(String),
    utm_medium      LowCardinality(String),
    utm_campaign    String,
    utm_term        String,
    utm_content     String,
    ip_truncated    String,
    user_agent_hash FixedString(64)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/clicks', '{replica}')
PARTITION BY toYYYYMM(created_at)
ORDER BY (workspace_id, link_id, created_at)
SETTINGS index_granularity = 8192;

Algumas decisões de schema que merecem destaque.

LowCardinality(String) em workspace_id, country, device, os, browser, utm_source, utm_medium - estas colunas têm cardinalidade limitada (centenas de IDs de workspace distintos, uns 250 códigos de país, um punhado de tipos de dispositivo). LowCardinality ativa a codificação por dicionário, que armazena os valores como referências inteiras num dicionário de strings únicas. A velocidade das queries nestas colunas melhora materialmente; a compressão melhora ainda mais. Aprendemos isto com a documentação do ClickHouse sobre o tipo de dados low-cardinality - a regra geral é que qualquer coluna de string com menos de 10.000 valores distintos é uma candidata.

PARTITION BY toYYYYMM(created_at) cria uma partição de dados separada por mês de calendário. As partições antigas podem ser eliminadas (ou movidas para armazenamento mais lento) com um único comando ALTER TABLE clicks DROP PARTITION '202501', que é instantâneo - sem scan completo da tabela. Para a aplicação de retenção de dados do RGPD, isto é praticamente importante: eliminar os cliques históricos de um workspace é uma operação única por partição mensal em vez de um DELETE WHERE workspace_id = ? distribuído.

ip_truncated armazena o endereço IP com o último octeto a zeros (203.0.113.0 e não 203.0.113.42). O IP completo nunca é armazenado. A forma truncada é suficiente para lookup geográfico - que fazemos no momento da ingestão, antes do armazenamento - e não constitui dados pessoais ao abrigo da definição do Artigo 4.º do RGPD quando combinada com as nossas práticas de minimização de dados. Consulte solutions/analytics para a arquitetura de privacidade completa.

O caminho de ingestão#

O handler de redirecionamento em cada POP de edge é disparar-e-esquecer. Acrescenta um evento de clique a um tópico Redpanda (clicks.v1) e emite o 302. Não aguarda confirmação do ClickHouse. Se o Redpanda não estiver disponível nesse momento, o clique é descartado - o redirecionamento não. A hierarquia é deliberada: um clique perdido é recuperável (podemos reconstruir contagens aproximadas a partir dos logs de acesso); um redirecionamento falhado é visível para o utilizador.

Os consumer groups do Redpanda - consultado em 2026-05-12 - fornecem a garantia de entrega entre o edge e o ingester. O serviço click-ingester funciona como consumer group no tópico clicks.v1. As partições são atribuídas automaticamente; se uma instância do ingester falhar, as suas partições reequilibram para instâncias sobreviventes dentro do timeout de heartbeat do grupo.

O ingester acumula eventos em memória e descarrega em lotes: 1.000 linhas ou 250ms, o que ocorrer primeiro. Isto é importante. O motor MergeTree do ClickHouse está otimizado para inserções em massa - escreve cada inserção como uma nova parte de dados, e muitas inserções pequenas geram muitas partes pequenas, o que despoleta uma fusão de fundo agressiva e abranda as queries durante a carga de fusão. A documentação do ClickHouse recomenda inserções de pelo menos 1.000 linhas para manter o número de partes gerível. Fazemos o flush a 1.000 linhas / 250ms e não temos visto problemas de contagem de partes em operação normal.

A contrapressão propaga-se através do Redpanda. Se o ClickHouse estiver lento ou a reiniciar, o lag do consumer do ingester aumenta, que monitorizamos como indicador precoce de problemas de atualidade dos dados. A configuração de retenção do Redpanda (14 dias) dá-nos uma janela de replay significativa se o ClickHouse precisar de ser reconstruído do zero.

A arquitetura é descrita com mais detalhe em /docs/architecture/click-ingester.

Postgres vs. ClickHouse: a separação#

Duas bases de dados para um serviço é um custo que precisa de justificação. A justificação aqui é que as duas cargas de trabalho são genuinamente incompatíveis a escala, e os dados não se sobrepõem de forma que requeira coordenação síncrona.

O Postgres é responsável pelos metadados dos links: as tabelas links, workspaces, users, billing, rules. Estas são mutáveis (os links ficam arquivados, os URLs de destino mudam, os registos de faturação são atualizados), transacionais (criar um link e cunhar o seu slug têm de ser atómicos) e orientadas para leitura em relação às escritas. Índices B-tree e MVCC são exatamente o que se pretende.

O ClickHouse é responsável pelos eventos de clique: a tabela clicks. Estes são apenas de acrescento (um clique, uma vez escrito, nunca é atualizado), intensivos em escrita e consultados em padrões de agregação que o armazenamento colunar trata melhor. Não existem joins entre bases de dados no momento da escrita - o ingester insere eventos de clique com workspace_id e link_id como campos de string desnormalizados. No momento da query, a API de análise pode procurar metadados de links no Postgres e contagens de cliques no ClickHouse separadamente, e depois fazer o join no código da aplicação. Isso é uma viagem de rede extra; é um trade muito melhor do que colocar a carga de queries analíticas no Postgres.

A documentação do Citus distributed Postgres descreve a alternativa: extensões de Postgres distribuído projetadas para lidar com cargas de trabalho analíticas de séries temporais. Avaliámos este caminho e concluímos que o desempenho de queries e a taxa de compressão do ClickHouse estão suficientemente à frente para cargas de trabalho puramente analíticas para que a sobrecarga operacional de duas bases de dados valha a pena. Se a sua carga de trabalho for mista - alguma analítica, muitas mutações ao nível da linha - a abordagem Citus faz mais sentido.

Padrões de query que executamos a cada hora#

Os dashboards atualizam para cada workspace num ciclo de aproximadamente 60 segundos. Existem três queries pesadas:

Rollup de cliques por link, últimas 24 horas. O topo de cada lista de links mostra um sparkline. Trata-se de um GROUP BY link_id, toHour(created_at) nas últimas 24 horas para o workspace.

Distribuição por país para uma campanha. Uma equipa de marketing a verificar o desempenho de campanha quer GROUP BY country, COUNT(*) filtrado por utm_campaign num intervalo de datas.

Divisão por dispositivo nos top-100 links de um workspace. O separador de análise mostra uma distribuição por dispositivo/SO. Trata-se de GROUP BY device, os, COUNT(*) para os links de maior tráfego do workspace nos últimos 30 dias.

Os três padrões são bem servidos pelo agrupamento ORDER BY (workspace_id, link_id, created_at). O filtro de workspace reduz o scan ao fragmento correto de dados imediatamente; as colunas link_id e created_at estreitam-no ainda mais para os primeiros dois padrões. A query de divisão por dispositivo scanneia mais colunas mas ainda beneficia do agrupamento ao nível do workspace.

Uma query representativa para os top-100 links por contagem de cliques nas últimas 24 horas:

SELECT
    link_id,
    count() AS clicks_24h
FROM clicks
WHERE
    workspace_id = 'ws_abc123'
    AND created_at >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

Num workspace com 12 milhões de eventos de clique ao longo de 90 dias, esta query retorna em aproximadamente 45-80ms dependendo do número de partes de dados na partição relevante. A mesma query contra uma tabela Postgres de tamanho equivalente demora 2-4 segundos com a melhor configuração de índices que experimentámos.

Não utilizamos materialized views do ClickHouse para estes padrões. O agrupamento ORDER BY e o partition pruning são suficientes, e as materialized views acrescentam complexidade na migração de schema. Se a latência das queries alguma vez se tornar um problema com volumes de eventos mais elevados, as materialized views são a próxima alavanca - não indexação adicional.

Modos de falha que encontrámos#

Três incidentes de produção merecem ser documentados.

Falha no ClickHouse Keeper. O ClickHouse Keeper é a camada de coordenação equivalente ao ZooKeeper para ReplicatedMergeTree. Numa ocasião, o reinício de um nó Keeper durante uma fusão de fundo fez com que a tabela replicada pausasse a aceitação de inserções durante aproximadamente 90 segundos enquanto o novo líder era eleito. O ingester tentou novamente com backoff exponencial (2s, 4s, 8s, máximo 60s) - trata-se de lógica de retry Go padrão com tratamento de context.DeadlineExceeded. Durante a janela de interrupção, cerca de 1.800 eventos de clique acumularam-se no buffer em memória do ingester e foram descarregados com sucesso assim que o Keeper recuperou. O único efeito visível para o utilizador foi uma lacuna de ~2 minutos nos dados do dashboard em tempo real. A correção foi garantir que os nós Keeper estão distribuídos por hosts físicos separados com circuitos elétricos separados na rede Hetzner.

Desequilíbrio de partições no Redpanda. O tópico clicks.v1 usa 12 partições. Após adicionar uma segunda instância do ingester, a atribuição de partições pelo balanceador padrão do Redpanda atribuiu 9 partições a uma instância e 3 à outra - usou a estratégia de total de partições por consumer e as contagens de instâncias produziram um desequilíbrio de 3:1. Não era um risco de perda de dados, mas a instância sobrecarregada estava a agrupar lentamente sob a carga, aumentando a latência de processamento de cliques para cerca de 800ms. A correção foi um reequilíbrio manual de partições usando a API de administração do Redpanda e a mudança para o balanceador rack-aware para usar atribuição round-robin.

ALTER TABLE numa tabela grande é assíncrono. Adicionámos uma coluna browser_version em janeiro. O ALTER TABLE ... ADD COLUMN do ClickHouse confirma imediatamente e aplica a alteração de schema de forma assíncrona ao longo das fusões de partes em fundo. Durante cerca de 20 minutos após o alter, as queries que referenciavam a nova coluna retornavam NULL para linhas em partes não mutadas. Nada falhou - estávamos a adicionar uma coluna, não a alterar uma existente - mas uma query que assumisse browser_version IS NOT NULL teria retornado resultados incorretos. A lição: trate o ALTER TABLE em tabelas ClickHouse grandes como eventualmente consistente, não síncrono.

O que não fazemos no ClickHouse#

Duas coisas que surgem em conversas e que evitámos deliberadamente.

Joins por evento para tabelas Postgres. Algumas queries de análise naturalmente querem fazer join entre eventos de clique e metadados de links - "mostra-me todos os cliques para links com a tag 'campaign-q2', com os seus URLs de destino". Fazer isto como uma query federada (tabela clicks do ClickHouse em join com links do Postgres via uma função de tabela JDBC do ClickHouse) é possível mas lento. Em vez disso, ou desnormalizamos campos relevantes no evento de clique no momento da ingestão (como utm_campaign, que é derivado do template UTM do link) ou resolvemos o join no código da aplicação: duas queries separadas, fundidas na camada da API de análise. Isto mantém ambas as bases de dados a operar dentro dos seus envelopes de desempenho.

Atualizações por linha. O ClickHouse suporta ALTER TABLE UPDATE para mutações ao nível da linha, mas é uma operação assíncrona e intensiva em recursos que vai contra as premissas do motor. Se se vir a necessitar de atualizar um evento de clique após o facto - por exemplo, para marcá-lo retroativamente como clique de bot - a arquitetura está errada. O padrão correto é armazenar uma tabela click_quality separada no ClickHouse que mapeia click_id para uma pontuação de qualidade, calculada pelo serviço url-scanner de forma assíncrona, e fazer JOIN no momento da query. As leituras são baratas; as atualizações retroativas são caras e devem ser raras.

Para a imagem mais alargada da infraestrutura - como o redirecionamento de edge interage com o Redpanda, como funciona a deduplicação de cliques e como o serviço click-ingester se encaixa na topologia de serviços - consulte /docs/architecture/click-ingester. A história do lado do edge (publicação fire-and-forget no Redpanda, orçamento de latência) está no post de p95 do redirecionamento. Para o que os dados permitem a jusante - distribuições geográficas, divisões por dispositivo, dashboards de atribuição UTM - a página solutions/analytics cobre a superfície do produto. Se estiver a construir campanhas de links contra a API e se preocupa com quais os padrões de query que o SDK expõe, solutions/developers é o ponto de entrada relevante.

Para equipas a avaliar o Elido como infraestrutura de links, a escolha arquitetural aqui - ClickHouse para cliques, Postgres para tudo o resto - é também o que nos permite oferecer análise sem amostragem. O post sobre smart links explicados cobre como as decisões de roteamento que geram estes eventos de clique funcionam no edge.

Marius Voß é DevRel e edge infra no Elido. Ajudou a projetar o pipeline do click-ingester e passou mais tempo do que gostaria a depurar o timing de eleição do ClickHouse Keeper.