Приёмы моделирования данных

Это Часть 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. Можно указать несколько ключей сортировки — в этом случае сортировка будет выполняться по тем же правилам, что и предложение ORDER BY в запросе SELECT."

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

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

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

Партиции

Если вы работали с Postgres, вам, вероятно, знакома концепция партиционирования таблиц: для повышения производительности и упрощения управления большими базами данных таблицы делят на более мелкие и удобные части, называемые партициями. Такое партиционирование можно реализовать либо с помощью диапазона по указанному столбцу (например, по датам), либо с помощью заданных списков, либо с помощью хеша по ключу. Это позволяет администраторам организовывать данные по определенным критериям, таким как диапазоны дат или географическое расположение. Партиционирование помогает повысить производительность запросов, обеспечивая более быстрый доступ к данным за счет отсечения партиций и более эффективного индексирования. Оно также упрощает задачи обслуживания, такие как резервное копирование и удаление данных, позволяя выполнять операции над отдельными партициями, а не над всей таблицей. Кроме того, партиционирование может значительно повысить масштабируемость баз данных PostgreSQL, распределяя нагрузку между несколькими партициями.

В ClickHouse партиционирование задается для таблицы при ее первоначальном определении с помощью предложения PARTITION BY. Это предложение может содержать SQL-выражение по любым столбцам, результат которого определяет, в какую партицию будет отправлена строка.

Партиции PostgreSQL в партиции ClickHouse

Части данных на диске логически связаны с каждой партицией и могут запрашиваться изолированно. В примере ниже мы партиционируем таблицу 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, если значения в каждой партиции уникальны. Однако в целом следует убедиться, что первичный ключ оптимизирован, и рассматривать партиционирование как метод оптимизации запросов только в исключительных случаях, когда характер доступа предполагает работу с определенным предсказуемым подмножеством данных, например партиционирование по дням, если большинство запросов обращается только к последнему дню.

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. В этом выражении можно выбрать подмножество столбцов, чтобы уменьшить объем хранилища.
Пользователи готовы к связанному с этим увеличению объема хранилища и дополнительным накладным расходам из-за двойной записи данных. Проверьте влияние на скорость вставки и оцените накладные расходы на хранение.

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

Поскольку Postgres — реляционная база данных, её модель данных в значительной степени нормализована и часто включает сотни таблиц. В ClickHouse денормализация иногда помогает оптимизировать производительность JOIN.

Вы можете обратиться к этому руководству, в котором показаны преимущества денормализации набора данных Stack Overflow в ClickHouse.

На этом наше вводное руководство для тех, кто переходит с Postgres на ClickHouse, завершается. Чтобы больше узнать о расширенных возможностях ClickHouse, рекомендуем прочитать руководство по моделированию данных в ClickHouse.