Перейти к основному содержанию
Это Часть 3 руководства по миграции с PostgreSQL на ClickHouse. На практическом примере здесь показано, как моделировать данные в ClickHouse при миграции с PostgreSQL.
Мы рекомендуем пользователям, переходящим с Postgres, прочитать руководство по моделированию данных в ClickHouse. В этом руководстве используется тот же набор данных Stack Overflow и рассматриваются несколько подходов с использованием возможностей ClickHouse.

​
Первичные (сортировочные) ключи в ClickHouse

Пользователи, переходящие с OLTP-баз данных, часто ищут в ClickHouse аналогичное понятие. Увидев, что ClickHouse поддерживает синтаксис PRIMARY KEY, они могут захотеть определять схему таблицы, используя те же ключи, что и в исходной OLTP-базе данных. Однако в данном случае это не подходит.

​
Чем отличаются первичные ключи в ClickHouse?

Чтобы понять, почему OLTP-первичный ключ не подходит для ClickHouse, нужно разобраться в основах индексирования ClickHouse. Для сравнения возьмем Postgres, но эти общие принципы применимы и к другим OLTP-базам данных.
  • Первичные ключи в Postgres по определению уникальны для каждой строки. Использование структур B-Tree позволяет эффективно находить отдельные строки по этому ключу. Хотя ClickHouse можно оптимизировать для поиска по значению в одной строке, аналитические рабочие нагрузки обычно требуют чтения нескольких столбцов сразу для большого числа строк. Чаще всего фильтрам нужно определить подмножество строк, по которому затем будет выполняться агрегация.
  • Эффективное использование оперативной памяти и диска критически важно для тех масштабов, на которых обычно применяют ClickHouse. Данные записываются в таблицы ClickHouse фрагментами, называемыми частями, а затем к этим частям применяются правила фонового слияния. В ClickHouse у каждой части есть собственный первичный индекс. При слиянии частей первичные индексы слитой части тоже объединяются. В отличие от Postgres, эти индексы строятся не для каждой строки. Вместо этого первичный индекс части содержит одну запись индекса на группу строк — этот прием называется разреженным индексированием.
  • Разреженное индексирование возможно потому, что ClickHouse хранит строки каждой части на диске в порядке, заданном указанным ключом. Вместо прямого поиска отдельных строк (как в индексе на основе B-Tree) разреженный первичный индекс позволяет быстро — с помощью двоичного поиска по записям индекса — определять группы строк, которые потенциально могут соответствовать запросу. Найденные группы потенциально подходящих строк затем параллельно передаются в движок ClickHouse для поиска совпадений. Такой подход позволяет сделать первичный индекс компактным (он целиком помещается в оперативную память) и при этом существенно ускорить выполнение запросов, особенно диапазонных — типичных для аналитики данных.
За дополнительными подробностями рекомендуем обратиться к этому подробному руководству. Выбранный ключ в ClickHouse определяет не только индекс, но и порядок, в котором данные записываются на диск. Поэтому он может существенно влиять на степень сжатия, а это, в свою очередь, сказывается на производительности запросов. Ключ сортировки, при котором значения большинства столбцов записываются подряд, позволяет выбранному алгоритму сжатия (и кодекам) эффективнее сжимать данные.
Все столбцы в таблице будут отсортированы по значению указанного ключа сортировки, независимо от того, входят ли они в сам ключ. Например, если в качестве ключа используется CreationDate, порядок значений во всех остальных столбцах будет соответствовать порядку значений в столбце CreationDate. Можно указать несколько ключей сортировки — в этом случае сортировка будет выполняться по тем же правилам, что и clause ORDER BY в запросе SELECT.

​
Выбор ключа сортировки

О том, что следует учитывать и какие шаги выполнить при выборе ключа сортировки, см. на примере таблицы Posts здесь. При использовании репликации в реальном времени с CDC нужно учитывать дополнительные ограничения; способы настройки ключей сортировки для CDC описаны в этой документации.

​
Партиции

Если вы работали с Postgres, вам, вероятно, знакома концепция партиционирования таблиц: для повышения производительности и упрощения управления большими базами данных таблицы делят на более мелкие и удобные части, называемые партициями. Такое партиционирование можно реализовать либо с помощью диапазона по указанному столбцу (например, по датам), либо с помощью заданных списков, либо с помощью хеша по ключу. Это позволяет администраторам организовывать данные по определенным критериям, таким как диапазоны дат или географическое расположение. Партиционирование помогает повысить производительность запросов, обеспечивая более быстрый доступ к данным за счет отсечения партиций и более эффективного индексирования. Оно также упрощает задачи обслуживания, такие как резервное копирование и удаление данных, позволяя выполнять операции над отдельными партициями, а не над всей таблицей. Кроме того, партиционирование может значительно повысить масштабируемость баз данных PostgreSQL, распределяя нагрузку между несколькими партициями. В ClickHouse партиционирование задается для таблицы при ее первоначальном определении с помощью предложения PARTITION BY. Это предложение может содержать SQL-выражение по любым столбцам, результат которого определяет, в какую партицию будет отправлена строка. Части данных на диске логически связаны с каждой партицией и могут запрашиваться изолированно. В примере ниже мы партиционируем таблицу posts по годам с помощью выражения toYear(CreationDate). По мере вставки строк в ClickHouse это выражение будет вычисляться для каждой строки, и строка будет направляться в соответствующую партицию, если она уже существует (если строка для данного года первая, партиция будет создана). 
 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)
Подробное описание партиционирования см. в “Партиции таблиц”.

​
Применение партиций

В ClickHouse партиционирование применяется примерно так же, как в Postgres, но с некоторыми нюансами. В частности:
  • Управление данными - В ClickHouse партиционирование следует в первую очередь рассматривать как средство управления данными, а не как метод оптимизации запросов. При логическом разделении данных по ключу с каждой партицией можно работать независимо, например удалять её. Это позволяет эффективно перемещать партиции, а значит и подмножества данных, между уровнями хранения по времени, а также задавать срок хранения данных/эффективно удалять их из кластера. В примере ниже мы удаляем посты за 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.
  • Оптимизация запросов - Хотя партиции могут помочь повысить производительность запросов, это сильно зависит от характера доступа к данным. Если запросы затрагивают только несколько партиций (в идеале одну), производительность может улучшиться. Обычно это имеет смысл только тогда, когда ключ партиционирования не входит в первичный ключ и фильтрация выполняется по нему. Однако запросы, которым нужно охватывать много партиций, могут работать хуже, чем без партиционирования (поскольку из-за партиционирования может образовываться больше частей). Преимущество обращения к одной партиции будет еще менее заметным или вовсе отсутствовать, если ключ партиционирования уже находится среди первых полей первичного ключа. Партиционирование также можно использовать для оптимизации запросов GROUP BY, если значения в каждой партиции уникальны. Однако в целом следует убедиться, что первичный ключ оптимизирован, и рассматривать партиционирование как метод оптимизации запросов только в исключительных случаях, когда характер доступа предполагает работу с определенным предсказуемым подмножеством данных, например партиционирование по дням, если большинство запросов обращается только к последнему дню.

​
Рекомендации по партициям

Партиционирование следует рассматривать как метод управления данными. Оно особенно полезно, когда при работе с данными временных рядов нужно удалять данные из кластера: например, самую старую партицию можно просто удалить. Важно: Убедитесь, что выражение ключа партиционирования не приводит к множеству высокой мощности, то есть следует избегать создания более 100 партиций. Например, не партиционируйте данные по столбцам с высокой мощностью, таким как идентификаторы или имена клиентов. Вместо этого сделайте идентификатор или имя клиента первым столбцом в выражении ORDER BY.
Внутри ClickHouse создаёт части для вставляемых данных. По мере вставки новых данных число частей увеличивается. Чтобы предотвратить чрезмерный рост числа частей, который ухудшает производительность запросов (из-за большего количества файлов для чтения), части объединяются в фоновом асинхронном процессе. Если число частей превышает заранее настроенный предел, ClickHouse сгенерирует исключение при вставке — ошибку “too many parts”. В нормальном режиме работы этого происходить не должно; такое случается только если ClickHouse настроен неправильно или используется некорректно, например при большом количестве мелких вставок.
Поскольку части создаются изолированно для каждой партиции, увеличение числа партиций приводит и к увеличению числа частей, то есть оно становится кратным числу партиций. Поэтому ключи партиционирования с высокой мощностью могут вызывать эту ошибку, и их следует избегать.

​
Materialized views и проекции

Postgres позволяет создавать несколько индексов для одной таблицы, что дает возможность оптимизировать ее для различных сценариев доступа. Такая гибкость позволяет администраторам и разработчикам настраивать производительность базы данных под конкретные запросы и эксплуатационные потребности. Концепция проекций в ClickHouse, хотя и не является полным аналогом этого подхода, позволяет задавать для таблицы несколько секций ORDER BY. В документации по моделированию данных ClickHouse мы рассматриваем, как materialized view в ClickHouse можно использовать для предварительного вычисления агрегаций, преобразования строк и оптимизации запросов для различных сценариев доступа. Во втором случае мы привели пример, где materialized view отправляет строки в целевую таблицу с другим ключом сортировки, чем у исходной таблицы, в которую выполняются вставки. Например, рассмотрим следующий запрос: 
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.
Этот запрос требует сканирования всех 90 млн строк (хотя и, надо признать, довольно быстро), поскольку UserId не является ключом сортировки. Ранее мы решали эту задачу с помощью materialized view, выступающего в качестве механизма поиска для PostId. Ту же проблему можно решить с помощью проекции. Приведённая ниже команда добавляет проекцию для ORDER BY user_id. 
ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)

ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id
Обратите внимание, что сначала нужно создать проекцию, а затем материализовать её. Последняя команда приводит к тому, что данные сохраняются на диске дважды в двух разных порядках. Проекцию также можно определить при созданении данных, как показано ниже, и она будет автоматически поддерживаться по мере вставки новых данных. 
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
Если проекция создаётся через ALTER, то после выполнения команды MATERIALIZE PROJECTION она создаётся асинхронно. Ход этой операции можно проверить с помощью следующего запроса, дождавшись значения 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 строка в наборе. Elapsed: 0.003 сек.
Если повторить приведённый выше запрос, можно увидеть, что производительность значительно выросла за счёт дополнительного места для хранения. 
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.
С помощью команды EXPLAIN мы также можем убедиться, что для этого запроса использовалась проекция: 
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.

​
Когда использовать проекции

Проекции — привлекательная возможность для новых пользователей, поскольку они автоматически поддерживаются при вставке данных. Кроме того, запросы можно отправлять к одной таблице, а проекции по возможности будут использоваться для сокращения времени отклика. В отличие от materialized views, здесь пользователю не нужно выбирать подходящую оптимизированную целевую таблицу или переписывать запрос в зависимости от фильтров. Это повышает нагрузку на пользовательские приложения и увеличивает сложность на стороне клиента. Несмотря на эти преимущества, у проекций есть присущие ограничения, о которых следует знать, поэтому применять их стоит умеренно. Мы рекомендуем использовать проекции, когда:
  • Требуется полное переупорядочивание данных. Хотя выражение в проекции теоретически может использовать GROUP BY, materialized views эффективнее подходят для поддержки агрегатов. Оптимизатор запросов также с большей вероятностью будет использовать проекции с простым переупорядочиванием, то есть SELECT * ORDER BY x. В этом выражении можно выбрать подмножество столбцов, чтобы уменьшить объем хранилища.
  • Пользователи готовы к связанному с этим увеличению объема хранилища и дополнительным накладным расходам из-за двойной записи данных. Проверьте влияние на скорость вставки и оцените накладные расходы на хранение.
Начиная с версии 25.5, ClickHouse поддерживает виртуальный столбец _part_offset в проекциях. Это позволяет хранить проекции более экономно с точки зрения дискового пространства.Подробнее см. в разделе “Проекции”

​
Денормализация

Поскольку Postgres — реляционная база данных, её модель данных обычно сильно нормализована и нередко включает сотни таблиц. В ClickHouse денормализация иногда бывает полезна для оптимизации производительности JOIN. Вы можете обратиться к этому руководству, где показаны преимущества денормализации набора данных Stack Overflow в ClickHouse. На этом наше базовое руководство для тех, кто переходит с Postgres на ClickHouse, заканчивается. Мы рекомендуем прочитать руководство по моделированию данных в ClickHouse, чтобы больше узнать о расширенных возможностях ClickHouse.
Последнее изменение 10 июня 2026 г.