Техники моделирования данных

Это Часть 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-дерева позволяет эффективно находить отдельные строки по этому ключу. Хотя ClickHouse может быть оптимизирован для поиска одного значения строки, аналитические рабочие нагрузки обычно требуют чтения нескольких колонок, но для многих строк. Фильтры чаще будут необходимы для идентификации подмножества строк, на которых будет выполняться агрегация.
Эффективность использования памяти и диска имеют первостепенное значение для масштаба, на котором ClickHouse часто используется. Данные записываются в таблицы ClickHouse частями, известными как части, с правилами объединения частей в фоновом режиме. В ClickHouse каждая часть имеет свой собственный основной индекс. Когда части объединяются, основные индексы объединенных частей также сливаются. В отличие от Postgres, эти индексы не строятся для каждой строки. Вместо этого основной индекс для части имеет одну запись индекса на группу строк - эта техника называется разреженной индексацией.
Разреженная индексация возможна, потому что ClickHouse хранит строки для части на диске в порядке, определенном заданным ключом. Вместо прямого нахождения отдельных строк (как это делает индекс на основе B-дерева), разреженный основной индекс позволяет быстро (через бинарный поиск по записям индекса) идентифицировать группы строк, которые могут соответствовать запросу. Найденные группы потенциально подходящих строк затем параллельно передаются в движок ClickHouse для поиска совпадений. Этот дизайн индекса позволяет основному индексу быть маленьким (он полностью помещается в основной памяти), при этом значительно ускоряет время выполнения запросов, особенно для диапазонных запросов, которые типичны для случаев анализа данных.

Для получения дополнительных сведений мы рекомендуем это углубленное руководство.

Выбранный ключ в ClickHouse будет определять не только индекс, но и порядок, в котором данные записываются на диск. Из-за этого это может значительно повлиять на уровень сжатия, что, в свою очередь, может повлиять на производительность запросов. Упорядочивающий ключ, который приводит к тому, что значения большинства колонок записываются в смежном порядке, позволит выбранному алгоритму сжатия (и кодекам) более эффективно сжимать данные.

Все колонки в таблице будут отсортированы на основе значения заданного упорядочивающего ключа, независимо от того, включены ли они в сам ключ. Например, если в качестве ключа используется CreationDate, порядок значений во всех остальных колонках будет соответствовать порядку значений в колонке CreationDate. Можно указать несколько упорядочивающих ключей - это будет обеспечивать такой же смысл, как и оператор ORDER BY в запросе SELECT.

Выбор упорядочивающего ключа

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

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

Материализованные представления против проекций

Postgres позволяет создавать несколько индексов на одной таблице, что позволяет оптимизировать различные паттерны доступа. Эта гибкость позволяет администраторам и разработчикам настраивать производительность базы данных под конкретные запросы и операционные нужды. Концепция проекций в ClickHouse, хотя и не полностью аналогична этому, позволяет пользователям указывать несколько операторов ORDER BY для таблицы.

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

Для последнего из них мы предоставили пример, где материализованное представление отправляет строки в целевую таблицу с другим упорядочивающим ключом, чем оригинальная таблица, принимающая вставки.

Например, рассмотрим следующий запрос:

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 не является упорядочивающим ключом. Ранее мы решили эту задачу с помощью материализованного представления, действующего как справочник для 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 row in set. Elapsed: 0.003 sec.

Если мы повторим вышеуказанный запрос, то увидим, что производительность значительно улучшилась за счет дополнительного использования памяти.

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.

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

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

Это контрастирует с материализованными представлениями, где пользователю необходимо выбрать соответствующую оптимизированную целевую таблицу или переписать свой запрос, в зависимости от фильтров. Это создает большую нагрузку на пользовательские приложения и увеличивает сложность на стороне клиента.

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

Мы рекомендуем использовать проекции, когда:

Полная перекомпоновка данных необходима. Хотя выражение в проекции может, теоретически, использовать GROUP BY, материализованные представления более эффективно для поддержания агрегатов. Оптимизатор запросов также более вероятно использует проекции, которые используют простую перекомпоновку, т.е. SELECT * ORDER BY x. Пользователи могут выбрать подмножество колонок в этом выражении, чтобы уменьшить занимаемое место.
Пользователи готовы к связанному увеличению пространства для хранения и накладным расходам на запись данных дважды. Протестируйте влияние на скорость вставки и оцените накладные расходы на хранение.

примечание

Начиная с версии 25.5, ClickHouse поддерживает виртуальную колонку _part_offset в проекциях. Это открывает более экономичный способ хранения проекций.

Для получения дополнительных сведений смотрите "Проекции"

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

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

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

Это завершает наше базовое руководство для пользователей, мигрирующих с Postgres на ClickHouse. Мы рекомендуем пользователям, мигрирующим с Postgres, ознакомиться с руководством по моделированию данных в ClickHouse, чтобы узнать больше о продвинутых функциях ClickHouse.

Основные (упорядочивающие) ключи в ClickHouse​

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

Выбор упорядочивающего ключа​

Партиции​

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

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

Материализованные представления против проекций​

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

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