Por que ClickHouse para analytics de cliques (e não Postgres)

Postgres é excelente. Utilizamo-lo intensamente e confiamos nele. Armazena cada link, cada workspace, cada registro de faturação, cada usuário. Mas por volta dos 90 dias num encurtador de URL em crescimento, depara-se com um muro se também estiver guardando 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 compartilhada.
• ClickHouse com ORDER BY (workspace_id, link_id, timestamp) 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 à medida que o volume de cliques cresce em ordens de grandeza.

Por que o ClickHouse se encaixa

O ClickHouse é um motor de banco 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_code, workspace_id e timestamp. Nunca toca em user_agent ou referer. 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 device - comprimem para quase nada com os codecs LZ4 e ZSTD do ClickHouse. Colunas de baixa cardinalidade como estas são onde os codecs colunares mais ganham sobre o armazenamento por linha do Postgres. 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, timestamp). Uma query para os cliques do workspace 123 nos últimos 7 dias lê apenas as partes de dados onde workspace_id = 123 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 ou mais nós ClickHouse com coordenação equivalente a ZooKeeper (ClickHouse Keeper), replicando partes à medida que são escritas. Ainda não executamos isso: a produção é um único host, com um servidor ClickHouse em MergeTree simples. A replicação é o próximo passo planejado quando um servidor deixar de ser suficiente.

O schema que utilizamos

CREATE TABLE IF NOT EXISTS click_events
(
    timestamp       DateTime64(3, 'UTC'),
    link_id         UInt64,
    workspace_id    UInt64,
    domain_id       UInt64,
    slug            LowCardinality(String),
    host            LowCardinality(String),
    user_agent      String,
    referer         String,
    ip              String,
    country_code    LowCardinality(FixedString(2)) DEFAULT '',
    device          LowCardinality(String) DEFAULT '',
    browser         LowCardinality(String) DEFAULT '',
    destination     String DEFAULT '',
    retention_days  UInt16 DEFAULT 730,
    INDEX idx_workspace workspace_id TYPE bloom_filter GRANULARITY 4,
    INDEX idx_link      link_id      TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (workspace_id, link_id, timestamp)
TTL toDateTime(timestamp) + toIntervalDay(retention_days);

Algumas decisões de schema que merecem destaque.

LowCardinality(String) em slug, host, country_code, device, browser - estas colunas têm valores limitados ou muito repetitivos (algumas centenas de hostnames personalizados, uns 250 códigos de país, um punhado de tipos de dispositivo). Os IDs são UInt64 simples, que já comprimem bem por si. 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(timestamp) cria uma partição de dados separada por mês de calendário. As partições antigas inteiras podem ser eliminadas (ou movidas para armazenamento mais lento) com um único comando ALTER TABLE click_events DROP PARTITION '202501', que é instantâneo - sem scan completo da tabela. Uma partição contém os cliques de todos os workspaces para esse mês, porém, por isso a retenção por plano não pode ser um drop de partição. Em vez disso, cada linha é carimbada na ingestão com a janela do seu workspace em retention_days (90 dias no Free, 365 no Pro, 730 no Business), e a cláusula TTL remove as linhas expiradas à medida que o ClickHouse funde as partes.

ip armazena o endereço truncado: o edge zera o último octeto de um endereço IPv4 (203.0.113.0 e não 203.0.113.42) e mantém os primeiros 48 bits de um IPv6, antes de o evento de clique sair do processo de redirecionamento. O lookup geográfico acontece no edge antes do truncamento, por isso país, região, cidade e coordenadas aproximadas são armazenados com o clique, junto com o user agent completo. Um IP truncado mais cidade e user agent ainda deve ser tratado como dado pessoal. A página de soluções de análise indicada acima cobre a arquitetura de privacidade completa.

O caminho de ingestão

O handler de redirecionamento no serviço de edge é disparar-e-esquecer. Acrescenta um evento de clique a um tópico Redpanda (clicks em produção) 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 usuário.

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. A produção executa hoje um ingester; o consumer group é o que permitirá que uma segunda instância assuma partições, e faça failover dentro do timeout de heartbeat do grupo, quando adicionarmos uma.

O ingester acumula eventos em memória e baixa em lotes: 1.000 linhas ou 5 segundos, 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 / 5 segundos e não temos visto problemas de contagem de partes em operação normal.

A contrapressão propaga-se através do Redpanda. O ingester só confirma o offset de um registro depois de o seu lote ser escrito no ClickHouse, por isso, se o ClickHouse estiver lento ou a reiniciar, os lotes falhados são repetidos e o lag do consumer cresce, que é o indicador precoce de problemas de atualidade dos dados. Se o próprio ingester reiniciar, os registros não confirmados são lidos novamente do Redpanda, dentro da janela de retenção do tópico.

A arquitetura é descrita com mais detalhe em /docs/guides/clickhouse-export.

Postgres vs. ClickHouse: a separação

Duas bancos 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 registros 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 click_events. 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 bancos de dados no momento da escrita - o ingester insere eventos de clique com workspace_id e link_id como campos numéricos 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 bancos 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(timestamp) nas últimas 24 horas para o workspace.

Distribuição por país para uma campanha. Uma equipe de marketing a verificar o desempenho de campanha quer GROUP BY country_code, COUNT(*) filtrado pelos links da campanha num intervalo de datas.

Divisão por dispositivo, top-100 links. O separador de análise mostra uma distribuição por dispositivo/SO. Trata-se de GROUP BY device, browser, 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, timestamp). O filtro de workspace reduz o scan ao fragmento correto de dados imediatamente; as colunas link_id e timestamp 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 click_events
WHERE
    workspace_id = 123
    AND timestamp >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

A query lê apenas as partições no intervalo de datas e apenas as colunas que nomeia, por isso o seu custo cresce com a fatia que pede e não com a tabela inteira. A query equivalente no Postgres tem de percorrer linhas, e foi daí que vieram os 1,4 segundos acima.

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

A produção executa um servidor ClickHouse, um broker Redpanda e um ingester num único host, por isso os modos de falha que vale a pena registrar são os que essa configuração tem hoje e os que chegam com o scale-out que planejamos.

Reinícios do ClickHouse. Com um único servidor, um reinício ou upgrade do ClickHouse pausa as inserções. O ingester mantém o lote falhado em memória, tenta de novo e só confirma os offsets do Redpanda depois de uma inserção bem-sucedida, por isso os cliques esperam em vez de desaparecer. O efeito visível é uma lacuna nos dados do dashboard em tempo real até o backlog ser drenado. Uma lacuna que conhecemos: o buffer em memória não tem limite superior enquanto o ClickHouse está em baixo, e isso está no nosso backlog. A replicação com ReplicatedMergeTree e ClickHouse Keeper é a correção planejada para esta pausa, e traz a sua própria: uma eleição de líder do Keeper também bloqueia inserções em tabelas replicadas por pouco tempo, por isso os nós Keeper precisam ficar em hosts separados.

Equilíbrio de partições do ingester. Hoje um ingester detém todas as partições do tópico clicks. Quando adicionarmos uma segunda instância, a estratégia de atribuição do consumer group decide quão uniformemente as partições são repartidas, e uma divisão desigual aparece como uma instância agrupando lentamente enquanto a outra fica ociosa. O que há a observar é o lag do consumer por instância, não só o total do grupo.

ALTER TABLE assíncrono. Adicionámos colunas a click_events várias vezes (region, city, lat e lng, visitor_hash, as flags do scanner). O ALTER TABLE ... ADD COLUMN do ClickHouse confirma imediatamente e não reescreve as partes existentes; as linhas em partes mais antigas devolvem o valor por omissão da coluna (uma string vazia ou zero) até as fusões a materializarem. Nada falha, mas uma query que trate um city vazio como "sem cidade" vai contar mal as linhas antigas. A lição: trate o ALTER TABLE em tabelas ClickHouse grandes como eventualmente consistente, e escolha valores por omissão que se distingam dos valores reais.

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 click_events 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 destination, o URL para o qual o visitante foi realmente enviado) 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 os bancos de dados operando 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 fato - por exemplo, para marcá-lo retroativamente como clique de bot - a arquitetura está errada. Decidimos essas flags antes da inserção: o edge pontua cliques suspeitos e classifica scanners de links e redes anonimizadoras antes de emitir o evento, por isso is_suspicious, is_scanner e anonymizer_type são escritos com a linha. Se uma pontuação alguma vez tivesse de ser calculada depois, o padrão seria uma tabela separada com 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/guides/clickhouse-export. 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 construindo 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 equipes avaliando 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 lendo logs de fusão do ClickHouse.