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

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

Части данных логически ассоциируются с каждой партицией на диске и могут запрашиваться в изоляции. Для приведенного ниже примера мы партиционируем таблицу posts по годам, используя выражение toYear(CreationDate). Когда строки вставляются в ClickHouse, это выражение будет оцениваться для каждой строки и направляться в соответствующую партицию, если она существует (если строка является первой за год, партиция будет создана).

Для полного описания партиционирования смотрите "Партиции таблиц".

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

Партиционирование в ClickHouse имеет аналогичные применения, как и в Postgres, но с некоторыми тонкими отличиями. Более конкретно:

• Управление данными - В ClickHouse пользователи должны прежде всего рассматривать партиционирование как функцию управления данными, а не как технику оптимизации запросов. Разделяя данные логически по ключу, каждая партиция может обрабатываться независимо, например, удаляться. Это позволяет пользователям перемещать партиции, а следовательно и подмножества, между уровнями хранения эффективно по времени или истекать данные/эффективно удалять из кластера. Например, ниже мы удаляем посты 2008 года.

• Оптимизация запросов - Хотя партиции могут помогать с производительностью запросов, это сильно зависит от паттернов доступа. Если запросы нацелены только на несколько партиций (в идеале на одну), это может потенциально улучшить производительность. Это обычно полезно только в том случае, если ключ партиционирования не входит в первичный ключ и вы фильтруете по нему. Однако запросы, которые должны охватывать многие партиции, могут работать хуже, чем если бы партиционирование вообще не использовалось (так как, возможно, будет больше частей в результате партиционирования). Польза от нацеливания на одну партицию будет еще менее выраженной или отсутствовать, если ключ партиционирования уже является ранним элементом в первичном ключе. Партиционирование также может использоваться для оптимизации запросов GROUP BY, если значения в каждой партиции уникальны. Однако в общем пользователи должны удостовериться, что первичный ключ оптимизирован, и рассматривать партиционирование как технику оптимизации запросов только в исключительных случаях, когда паттерны доступа обращаются к конкретному предсказуемому подмножеству дня, например, партиционирование по дням, с большинством запросов за последний день.

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

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

Важно: Убедитесь, что ваше выражение ключа партиционирования не приводит к созданию множества с высокой кардинальностью, т.е. создание более 100 партиций следует избегать. Например, не партиционируйте ваши данные по колонкам с высокой кардинальностью, таким как идентификаторы клиентов или имена. Вместо этого сделайте идентификатор клиента или имя первой колонкой в выражении ORDER BY.

Внутри ClickHouse создаются части для вставленных данных. По мере вставки большего объема данных количество частей увеличивается. Чтобы предотвратить чрезмерно высокое количество частей, что ухудшает производительность запросов (больше файлов для чтения), части объединяются в фоновом асинхронном процессе. Если количество частей превышает предварительно настроенный лимит, ClickHouse выбросит исключение при вставке — как ошибка "слишком много частей". Это не должно происходить в обычном режиме работы и случается только если ClickHouse неправильно настроен или используется неправильно, например, много мелких вставок.

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

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

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

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

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

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

Этот запрос требует сканирования всех 90 миллионов строк (хотя и быстро), поскольку UserId не является упорядочивающим ключом. Ранее мы решили эту задачу с помощью материализованного представления, действующего как справочник для PostId. Ту же проблему можно решить с помощью проекции. Команда ниже добавляет проекцию для ORDER BY user_id.

Обратите внимание, что нам сначала нужно создать проекцию, а затем материализовать её. Эта последняя команда заставляет данные храниться дважды на диске в двух разных порядках. Проекция также может быть определена при создании данных, как показано ниже, и будет автоматически поддерживаться по мере вставки данных.

Если проекция создается через ALTER, создание происходит асинхронно, когда команда MATERIALIZE PROJECTION выполняется. Пользователи могут подтвердить ход этой операции с помощью следующего запроса, дождавшись is_done=1.

Если мы повторим приведённый выше запрос, мы можем увидеть, что производительность значительно улучшилась за счет дополнительных затрат на хранение.

С помощью команды EXPLAIN мы также подтверждаем, что проекция была использована для выполнения этого запроса:

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

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

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

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

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

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

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

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

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

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