ClickHouse vs PostgreSQL: quando usar um banco colunar para analytics

Um relatório de “média de vendas por hora nos últimos 5 anos” costuma ser lento no PostgreSQL por uma razão estrutural, não por defeito. O PostgreSQL foi feito para transações. O ClickHouse foi feito para agregações sobre muitos dados. Este texto explica a diferença e mostra um teste pequeno.

Linhas contra colunas

O PostgreSQL guarda cada linha inteira junta. É ótimo para SELECT * FROM users WHERE id = 1, e para somar uma coluna ele precisa percorrer linhas com dados que a conta não usa. O ClickHouse guarda cada coluna em arquivos separados, então uma agregação lê só as colunas citadas na consulta. Isso, somado a compressão e a execução vetorizada (SIMD), explica o ganho em análise.

Esse ganho depende da consulta e dos dados. Uma versão anterior deste post citava “100x a 1000x” em benchmarks públicos sem apontar nenhum. Não consegui confirmar o número, então saiu. Meça nos seus dados.

De onde vem o ClickHouse

É um banco OLAP open source, com licença Apache 2.0. Em 2017, a Cloudflare descreveu como usa o ClickHouse no analytics de DNS, num fluxo de cerca de 1 milhão de consultas por segundo: logs do edge passam por Kafka e chegam ao ClickHouse por um adaptador em Go. O texto também cita um índice esparso que precisa de apenas 122.070 marcas para indexar uma tabela de 1 trilhão de linhas.

Teste rápido com Docker

docker run -d --name clickhouse-server --ulimit nofile=262144:262144 -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server
docker exec -it clickhouse-server clickhouse-client

No ClickHouse você escolhe o motor da tabela. O mais comum é o MergeTree:

CREATE TABLE acessos_site (
    id UInt64,
    url String,
    usuario_id UInt32,
    data_acesso DateTime
) ENGINE = MergeTree()
ORDER BY (data_acesso, url);

O ORDER BY não serve só para ordenar: ele define o índice primário esparso. Insira em lote, não linha a linha:

INSERT INTO acessos_site
SELECT
    number,
    concat('https://exemplo.com/post/', toString(rand() % 100)),
    rand() % 1000,
    now() - rand() % 10000
FROM numbers(1000000);

E faça uma agregação:

SELECT url, count() AS total
FROM acessos_site
GROUP BY url
ORDER BY total DESC
LIMIT 5;

Num milhão de linhas, a resposta deve vir em milissegundos, mas o tempo exato depende do hardware. O ponto é comparar com o equivalente no seu Postgres.

Usando a partir de Go

go get github.com/ClickHouse/clickhouse-go/v2

conn, err := clickhouse.Open(&clickhouse.Options{
    Addr: []string{"127.0.0.1:9000"},
    Auth: clickhouse.Auth{Database: "default", Username: "default"},
})
if err != nil {
    log.Fatal(err)
}
var total uint64
if err := conn.QueryRow(context.Background(), "SELECT count() FROM acessos_site").Scan(&total); err != nil {
    log.Fatal(err)
}
fmt.Println("linhas:", total)

Quando não usar

• Estado transacional da aplicação: cadastro de usuários, pedidos e pagamentos continuam melhores no PostgreSQL, que oferece transações ACID completas.
• Busca por chave única: WHERE id = 123 costuma ir melhor em banco orientado a linhas.
• Updates: a regra antiga era que alterar linhas é caro, porque exige mutations que reescrevem colunas. Isso mudou: o ClickHouse introduziu UPDATE leve (lightweight updates) na versão 25.7, com “patch parts” em vez de reescrita completa. A documentação de updates indica esse recurso para alterações pequenas, de cerca de 0,1% a 10% das linhas, e mutations para mudanças maiores. Ainda assim, não é um banco para ser alterado linha a linha o dia todo.

Arquitetura comum

O padrão que a maioria adota usa os dois: PostgreSQL para o que muda (usuários, pedidos) e ClickHouse para o histórico de eventos (logs, cliques, métricas).