Pular para o conteúdo principal
Esta é a Parte 3 de um guia sobre a migração do PostgreSQL para o ClickHouse. Com um exemplo prático, ele demonstra como modelar dados no ClickHouse ao migrar do PostgreSQL.
Recomendamos que os usuários que estão migrando do Postgres leiam o guia de modelagem de dados no ClickHouse. Este guia usa o mesmo conjunto de dados do Stack Overflow e explora várias abordagens usando funcionalidades do ClickHouse.

​
Chaves primárias (de ordenação) no ClickHouse

Usuários que vêm de bancos de dados OLTP frequentemente procuram o conceito equivalente no ClickHouse. Ao notar que o ClickHouse oferece suporte à sintaxe PRIMARY KEY, eles podem ficar tentados a definir o esquema da tabela usando as mesmas chaves do banco de dados OLTP de origem. Isso não é adequado.

​
Em que as chaves primárias do ClickHouse são diferentes?

Para entender por que usar sua chave primária de OLTP no ClickHouse não é apropriado, é importante entender os fundamentos da indexação no ClickHouse. Usamos o Postgres como comparação, mas esses conceitos gerais também se aplicam a outros bancos de dados OLTP.
  • As chaves primárias do Postgres são, por definição, únicas por linha. O uso de estruturas B-tree permite localizar com eficiência linhas individuais por essa chave. Embora o ClickHouse possa ser otimizado para localizar o valor de uma única linha, workloads analíticas normalmente exigem a leitura de poucas colunas, mas de muitas linhas. Na maioria das vezes, os filtros precisam identificar um subconjunto de linhas sobre o qual uma agregação será executada.
  • A eficiência de memória e disco é fundamental na escala em que o ClickHouse costuma ser usado. Os dados são gravados nas tabelas do ClickHouse em fragmentos conhecidos como partes, com regras aplicadas para mesclar essas partes em segundo plano. No ClickHouse, cada parte tem seu próprio índice primário. Quando as partes são mescladas, os índices primários da parte resultante também são mesclados. Ao contrário do Postgres, esses índices não são construídos para cada linha. Em vez disso, o índice primário de uma parte tem uma entrada de índice por grupo de linhas — essa técnica é chamada de indexação esparsa.
  • A indexação esparsa é possível porque o ClickHouse armazena em disco as linhas de uma parte ordenadas por uma chave especificada. Em vez de localizar diretamente linhas individuais (como um índice baseado em B-Tree), o índice primário esparso permite identificar rapidamente (por meio de uma busca binária nas entradas do índice) grupos de linhas que podem corresponder à consulta. Os grupos localizados de linhas potencialmente correspondentes são então processados em paralelo pelo engine do ClickHouse para encontrar as correspondências. Esse projeto de índice permite que o índice primário seja pequeno (cabendo inteiramente na memória principal) e, ainda assim, acelere significativamente o tempo de execução das consultas, especialmente em consultas de intervalo típicas de casos de uso analíticos.
Para mais detalhes, recomendamos este guia detalhado. A chave selecionada no ClickHouse determinará não apenas o índice, mas também a ordem em que os dados são gravados em disco. Por isso, ela pode afetar drasticamente os níveis de compressão, o que, por sua vez, pode impactar o desempenho da consulta. Uma chave de ordenação que faça com que os valores da maioria das colunas sejam gravados em ordem contígua permitirá que o algoritmo de compressão selecionado (e os codecs) comprima os dados com mais eficácia.
Todas as colunas de uma tabela serão ordenadas com base no valor da chave de ordenação especificada, independentemente de estarem incluídas na própria chave. Por exemplo, se CreationDate for usada como chave, a ordem dos valores em todas as outras colunas corresponderá à ordem dos valores na coluna CreationDate. É possível especificar várias chaves de ordenação — isso ordenará os dados com a mesma semântica de uma cláusula ORDER BY em uma consulta SELECT.

​
Escolhendo uma chave de ordenação

Para ver as considerações e etapas para escolher uma chave de ordenação, usando a tabela posts como exemplo, veja aqui. Ao usar replicação em tempo real com CDC, há restrições adicionais a considerar. Consulte esta documentação para conhecer técnicas de personalização de chaves de ordenação com CDC.

​
Partições

Se você vem do Postgres, já deve estar familiarizado com o conceito de particionamento de tabelas para melhorar o desempenho e facilitar o gerenciamento de grandes bancos de dados, dividindo as tabelas em partes menores e mais fáceis de administrar, chamadas partições. Esse particionamento pode ser feito com base em um intervalo de valores em uma coluna específica (por exemplo, datas), em listas predefinidas ou por meio de hash em uma chave. Isso permite que os administradores organizem os dados com base em critérios específicos, como intervalos de datas ou localizações geográficas. O particionamento ajuda a melhorar o desempenho das consultas ao permitir acesso mais rápido aos dados por meio de partition pruning e de uma indexação mais eficiente. Ele também auxilia em tarefas de manutenção, como backups e expurgos de dados, ao permitir operações em partições individuais em vez de na tabela inteira. Além disso, o particionamento pode melhorar significativamente a escalabilidade de bancos de dados PostgreSQL ao distribuir a carga entre várias partições. No ClickHouse, o particionamento é especificado na tabela quando ela é criada, por meio da cláusula PARTITION BY. Essa cláusula pode conter uma expressão SQL sobre qualquer coluna, e o resultado dessa expressão define para qual partição uma linha será enviada. As partes de dados são associadas logicamente a cada partição no disco e podem ser consultadas de forma isolada. No exemplo abaixo, particionamos a tabela posts por ano usando a expressão toYear(CreationDate). À medida que as linhas são inseridas no ClickHouse, essa expressão é avaliada para cada linha e ela é encaminhada para a partição correspondente; se for a primeira linha daquele ano, a partição será criada. 
 CREATE TABLE posts
(
        `Id` Int32 CODEC(Delta(4), ZSTD(1)),
        `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
        `AcceptedAnswerId` UInt32,
        `CreationDate` DateTime64(3, 'UTC'),
...
        `ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)
Para uma descrição completa do particionamento, consulte “Partições de tabela”.

​
Aplicações das partições

O particionamento no ClickHouse tem aplicações semelhantes às do Postgres, mas com algumas diferenças sutis. Mais especificamente:
  • Gerenciamento de dados - No ClickHouse, você deve considerar o particionamento principalmente como um recurso de gerenciamento de dados, e não como uma técnica de otimização de consultas. Ao separar os dados logicamente com base em uma chave, cada partição pode ser manipulada de forma independente, por exemplo, excluída. Isso permite mover partições e, portanto, subconjuntos de dados, entre camadas de armazenamento com eficiência com base no tempo ou expirar dados/excluí-los do cluster com eficiência. No exemplo abaixo, removemos posts de 2008.
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'

┌─partition─┐
│ 2008      │
│ 2009      │
│ 2010      │
│ 2011      │
│ 2012      │
│ 2013      │
│ 2014      │
│ 2015      │
│ 2016      │
│ 2017      │
│ 2018      │
│ 2019      │
│ 2020      │
│ 2021      │
│ 2022      │
│ 2023      │
│ 2024      │
└───────────┘

17 rows in set. Elapsed: 0.002 sec.

ALTER TABLE posts
(DROP PARTITION '2008')

Ok.

0 rows in set. Elapsed: 0.103 sec.
  • Otimização de consultas - Embora as partições possam ajudar no desempenho das consultas, isso depende fortemente dos padrões de acesso. Se as consultas atingirem apenas algumas partições (de preferência, uma só), o desempenho pode melhorar. Em geral, isso só é útil se a chave de particionamento não estiver na chave primária e você estiver filtrando por ela. No entanto, consultas que precisam abranger muitas partições podem ter um desempenho pior do que sem particionamento (pois ele pode resultar em mais partes). O benefício de atingir uma única partição será ainda menos perceptível — ou até inexistente — se a chave de particionamento já aparecer no início da chave primária. O particionamento também pode ser usado para otimizar consultas GROUP BY se os valores em cada partição forem únicos. No entanto, em geral, você deve garantir que a chave primária esteja otimizada e só considerar o particionamento como técnica de otimização de consultas em casos excepcionais, nos quais os padrões de acesso se concentram em um subconjunto específico e previsível do dia, por exemplo, particionamento por dia, com a maioria das consultas voltada para o último dia.

​
Recomendações para partições

Você deve considerar o particionamento como uma técnica de gerenciamento de dados. Ele é ideal quando os dados precisam expirar do cluster ao trabalhar com dados de séries temporais; por exemplo, a partição mais antiga pode simplesmente ser removida. Importante: Certifique-se de que a expressão da sua chave de particionamento não resulte em um conjunto de alta cardinalidade, ou seja, deve-se evitar criar mais de 100 partições. Por exemplo, não particione seus dados por colunas de alta cardinalidade, como identificadores ou nomes de clientes. Em vez disso, use o identificador ou nome do cliente como a primeira coluna na expressão ORDER BY.
Internamente, o ClickHouse cria partes para os dados inseridos. À medida que mais dados são inseridos, o número de partes aumenta. Para evitar um número excessivamente alto de partes, o que degradará o desempenho da consulta (mais arquivos para ler), as partes são mescladas em um processo assíncrono em segundo plano. Se o número de partes exceder um limite pré-configurado, o ClickHouse lançará uma exceção na inserção, como um erro de “partes demais”. Isso não deve acontecer em condições normais de operação e só ocorre se o ClickHouse estiver mal configurado ou sendo usado incorretamente, por exemplo, com muitas inserções pequenas.
Como as partes são criadas de forma isolada para cada partição, aumentar o número de partições faz com que o número de partes também aumente, ou seja, ele será um múltiplo do número de partições. Portanto, chaves de particionamento de alta cardinalidade podem causar esse erro e devem ser evitadas.

​
Visões materializadas vs projeções

O Postgres permite criar vários índices em uma única tabela, possibilitando a otimização para uma variedade de padrões de acesso. Essa flexibilidade permite que administradores e desenvolvedores ajustem o desempenho do banco de dados a consultas específicas e a necessidades operacionais. O conceito de projeções no ClickHouse, embora não seja totalmente análogo a isso, permite especificar várias cláusulas ORDER BY para uma tabela. Na documentação de modelagem de dados do ClickHouse, exploramos como visões materializadas podem ser usadas no ClickHouse para pré-calcular agregações, transformar linhas e otimizar consultas para diferentes padrões de acesso. Para este último caso, fornecemos um exemplo em que a visão materializada envia linhas para uma tabela de destino com uma chave de ordenação diferente da tabela original que recebe inserções. Por exemplo, considere a seguinte consulta: 
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.040 sec. Processed 90.38 million rows, 361.59 MB (2.25 billion rows/s., 9.01 GB/s.)
Peak memory usage: 201.93 MiB.
Esta consulta exige que todas as 90m linhas sejam varridas (ainda que rapidamente), já que o UserId não é a chave de ordenação. Anteriormente, resolvemos isso usando uma visão materializada que atuava como lookup para o PostId. O mesmo problema pode ser resolvido com uma projeção. O comando abaixo adiciona uma projeção para o ORDER BY user_id. 
ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)

ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id
Observe que primeiro precisamos criar a projeção e depois materializá-la. Esse último comando faz com que os dados sejam armazenados duas vezes no disco, em duas ordens diferentes. A projeção também pode ser definida quando os dados são criados, como mostrado abaixo, e será mantida automaticamente à medida que os dados forem inseridos. 
CREATE TABLE comments
(
        `Id` UInt32,
        `PostId` UInt32,
        `Score` UInt16,
        `Text` String,
        `CreationDate` DateTime64(3, 'UTC'),
        `UserId` Int32,
        `UserDisplayName` LowCardinality(String),
        PROJECTION comments_user_id
        (
        SELECT *
        ORDER BY UserId
        )
)
ENGINE = MergeTree
ORDER BY PostId
Se a projeção for criada por meio de um ALTER, a criação será assíncrona quando o comando MATERIALIZE PROJECTION for executado. Você pode verificar o andamento dessa operação com a consulta a seguir, aguardando até is_done=1. 
SELECT
        parts_to_do,
        is_done,
        latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')

   ┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │           1 │       0 │                    │
   └─────────────┴─────────┴────────────────────┘

1 row in set. Elapsed: 0.003 sec.
Se repetirmos a consulta acima, podemos ver que o desempenho melhorou significativamente em troca de armazenamento adicional. 
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

   ┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
   └─────────────────────┘

1 row in set. Elapsed: 0.008 sec. Processed 16.36 thousand rows, 98.17 KB (2.15 million rows/s., 12.92 MB/s.)
Peak memory usage: 4.06 MiB.
Com um comando EXPLAIN, também confirmamos que a projeção foi usada para executar esta consulta: 
EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047

    ┌─explain─────────────────────────────────────────────┐
 1. │ Expression ((Projection + Before ORDER BY))         │
 2. │   Aggregating                                       │
 3. │   Filter                                            │
 4. │           ReadFromMergeTree (comments_user_id)      │
 5. │           Indexes:                                  │
 6. │           PrimaryKey                                │
 7. │           Keys:                                     │
 8. │           UserId                                    │
 9. │           Condition: (UserId in [8592047, 8592047]) │
10. │           Parts: 2/2                                │
11. │           Granules: 2/11360                         │
    └─────────────────────────────────────────────────────┘

11 rows in set. Elapsed: 0.004 sec.

​
Quando usar projeções

As projeções são um recurso atraente para novos usuários, pois são mantidas automaticamente à medida que os dados são inseridos. Além disso, as consultas podem ser enviadas para uma única tabela, em que as projeções são aproveitadas sempre que possível para acelerar o tempo de resposta. Isso contrasta com as visões materializadas, nas quais o usuário precisa selecionar a tabela de destino otimizada apropriada ou reescrever sua consulta, dependendo dos filtros. Isso transfere mais responsabilidade para as aplicações do usuário e aumenta a complexidade no lado do cliente. Apesar dessas vantagens, as projeções têm algumas limitações inerentes das quais você deve estar ciente e, por isso, devem ser usadas com moderação. Recomendamos usar projeções quando:
  • É necessária uma reordenação completa dos dados. Embora a expressão na projeção possa, em teoria, usar um GROUP BY, as visões materializadas são mais eficazes para manter agregações. O otimizador de consultas também tende mais a aproveitar projeções que usam uma reordenação simples, ou seja, SELECT * ORDER BY x. Você pode selecionar um subconjunto de colunas nessa expressão para reduzir o uso de armazenamento.
  • Os usuários estão confortáveis com o aumento associado no uso de armazenamento e com a sobrecarga de gravar dados duas vezes. Teste o impacto na velocidade de inserção e avalie a sobrecarga de armazenamento.
Desde a versão 25.5, o ClickHouse oferece suporte à coluna virtual _part_offset em projeções. Isso permite uma forma mais eficiente em termos de espaço de armazenar projeções.Para mais detalhes, consulte “Projeções”

​
Desnormalização

Como o Postgres é um banco de dados relacional, seu modelo de dados é altamente normalizado, muitas vezes envolvendo centenas de tabelas. No ClickHouse, a desnormalização pode, em alguns casos, ser vantajosa para otimizar o desempenho de JOIN. Consulte este guia, que mostra os benefícios de desnormalizar o conjunto de dados do Stack Overflow no ClickHouse. Com isso, concluímos nosso guia básico para quem está migrando do Postgres para o ClickHouse. Recomendamos a leitura do guia de modelagem de dados no ClickHouse para saber mais sobre os recursos avançados do ClickHouse.
Última modificação em 10 de junho de 2026