Проектирование схем

Это Часть 2 руководства по миграции из PostgreSQL в ClickHouse. Этот контент можно считать вводным, цель которого - помочь пользователям развернуть начальную функциональную систему, которая соответствует передовым практикам ClickHouse. Он избегает сложных тем и не приведет к полностью оптимизированной схеме; скорее, он предоставляет прочную базу для пользователей, чтобы построить производственную систему и основать свое обучение.

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

Установка начальной схемы

Следуя этому принципу, мы сосредоточимся на основной таблице posts. Схема Postgres для этой таблицы показана ниже:

Чтобы установить эквивалентные типы для каждого из указанных столбцов, мы можем использовать команду DESCRIBE с табличной функцией Postgres. Измените следующую команду для вашей установки Postgres:

Это дает нам начальную не оптимизированную схему.

Без ограничения NOT NULL столбцы Postgres могут содержать нулевые значения. Не проверяя значения строк, ClickHouse сопоставляет их с эквивалентнымиNullable типами. Обратите внимание, что первичный ключ не может быть Null, что является требованием в Postgres.

Мы можем создать таблицу ClickHouse, используя эти типы с помощью команды CREATE AS EMPTY SELECT.

Этот же подход можно использовать для загрузки данных из s3 в других форматах. Здесь приведен эквивалентный пример загрузки этих данных из формата Parquet.

Начальная загрузка

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

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

Каждый SELECT из ClickHouse в Postgres использует одно соединение. Это соединение берется из пула соединений на стороне сервера, размер которого определяется настройкой postgresql_connection_pool_size (по умолчанию 16).

Если использовать полный набор данных, пример должен загрузить 59 миллионов постов. Подтвердите с помощью простого подсчета в ClickHouse:

Оптимизация типов

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

Мы можем заполнить эту таблицу простой командой INSERT INTO SELECT, считывая данные из нашей предыдущей таблицы и вставляя их в эту:

В нашей новой схеме мы не сохраняем никаких Null-значений. Указанный выше запрос преобразует их неявно в значения по умолчанию для соответствующих типов - 0 для целых чисел и пустое значение для строк. 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. Можно указать несколько упорядочивающих ключей - это будет упорядочивать с теми же семантиками, что и clause ORDER BY в запросе SELECT.

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

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

Сжатие

Столбцовая структура хранения ClickHouse означает, что сжатие часто будет значительно эффективнее по сравнению с Postgres. Ниже приведено сравнение требований к памяти для всех таблиц Stack Overflow в обеих базах данных:

Дополнительные сведения об оптимизации и измерении сжатия можно найти здесь.

Нажмите здесь для Часть 3.