> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Приёмы моделирования данных
> Часть 3 руководства по миграции с PostgreSQL на ClickHouse
export const Image = ({img, alt, size}) => {
return
;
};
> Это **Часть 3** руководства по миграции с PostgreSQL на ClickHouse. На практическом примере здесь показано, как моделировать данные в ClickHouse при миграции с PostgreSQL.
Мы рекомендуем пользователям, переходящим с Postgres, прочитать [руководство по моделированию данных в ClickHouse](/ru/guides/clickhouse/data-modelling/schema-design). В этом руководстве используется тот же набор данных Stack Overflow и рассматриваются несколько подходов с использованием возможностей ClickHouse.
## Первичные (сортировочные) ключи в ClickHouse
Пользователи, переходящие с OLTP-баз данных, часто ищут в ClickHouse аналогичное понятие. Увидев, что ClickHouse поддерживает синтаксис `PRIMARY KEY`, они могут захотеть определять схему таблицы, используя те же ключи, что и в исходной OLTP-базе данных. Однако в данном случае это не подходит.
### Чем отличаются первичные ключи в ClickHouse?
Чтобы понять, почему OLTP-первичный ключ не подходит для ClickHouse, нужно разобраться в основах индексирования ClickHouse. Для сравнения возьмем Postgres, но эти общие принципы применимы и к другим OLTP-базам данных.
* Первичные ключи в Postgres по определению уникальны для каждой строки. Использование [структур B-Tree](/ru/guides/clickhouse/data-modelling/sparse-primary-indexes#an-index-design-for-massive-data-scales) позволяет эффективно находить отдельные строки по этому ключу. Хотя ClickHouse можно оптимизировать для поиска по значению в одной строке, аналитические рабочие нагрузки обычно требуют чтения нескольких столбцов сразу для большого числа строк. Чаще всего фильтрам нужно определить **подмножество строк**, по которому затем будет выполняться агрегация.
* Эффективное использование оперативной памяти и диска критически важно для тех масштабов, на которых обычно применяют ClickHouse. Данные записываются в таблицы ClickHouse фрагментами, называемыми частями, а затем к этим частям применяются правила фонового слияния. В ClickHouse у каждой части есть собственный первичный индекс. При слиянии частей первичные индексы слитой части тоже объединяются. В отличие от Postgres, эти индексы строятся не для каждой строки. Вместо этого первичный индекс части содержит одну запись индекса на группу строк — этот прием называется **разреженным индексированием**.
* **Разреженное индексирование** возможно потому, что ClickHouse хранит строки каждой части на диске в порядке, заданном указанным ключом. Вместо прямого поиска отдельных строк (как в индексе на основе B-Tree) разреженный первичный индекс позволяет быстро — с помощью двоичного поиска по записям индекса — определять группы строк, которые потенциально могут соответствовать запросу. Найденные группы потенциально подходящих строк затем параллельно передаются в движок ClickHouse для поиска совпадений. Такой подход позволяет сделать первичный индекс компактным (он целиком помещается в оперативную память) и при этом существенно ускорить выполнение запросов, особенно диапазонных — типичных для аналитики данных.
За дополнительными подробностями рекомендуем обратиться к этому [подробному руководству](/ru/guides/clickhouse/data-modelling/sparse-primary-indexes).
Выбранный ключ в ClickHouse определяет не только индекс, но и порядок, в котором данные записываются на диск. Поэтому он может существенно влиять на степень сжатия, а это, в свою очередь, сказывается на производительности запросов. Ключ сортировки, при котором значения большинства столбцов записываются подряд, позволяет выбранному алгоритму сжатия (и кодекам) эффективнее сжимать данные.
> Все столбцы в таблице будут отсортированы по значению указанного ключа сортировки, независимо от того, входят ли они в сам ключ. Например, если в качестве ключа используется `CreationDate`, порядок значений во всех остальных столбцах будет соответствовать порядку значений в столбце `CreationDate`. Можно указать несколько ключей сортировки — в этом случае сортировка будет выполняться по тем же правилам, что и clause `ORDER BY` в запросе `SELECT`.
### Выбор ключа сортировки
О том, что следует учитывать и какие шаги выполнить при выборе ключа сортировки, см. на примере таблицы Posts [здесь](/ru/guides/clickhouse/data-modelling/schema-design#choosing-an-ordering-key).
При использовании репликации в реальном времени с CDC нужно учитывать дополнительные ограничения; способы настройки ключей сортировки для CDC описаны в этой [документации](/ru/integrations/clickpipes/postgres/ordering-keys).
## Партиции
Если вы работали с Postgres, вам, вероятно, знакома концепция партиционирования таблиц: для повышения производительности и упрощения управления большими базами данных таблицы делят на более мелкие и удобные части, называемые партициями. Такое партиционирование можно реализовать либо с помощью диапазона по указанному столбцу (например, по датам), либо с помощью заданных списков, либо с помощью хеша по ключу. Это позволяет администраторам организовывать данные по определенным критериям, таким как диапазоны дат или географическое расположение. Партиционирование помогает повысить производительность запросов, обеспечивая более быстрый доступ к данным за счет отсечения партиций и более эффективного индексирования. Оно также упрощает задачи обслуживания, такие как резервное копирование и удаление данных, позволяя выполнять операции над отдельными партициями, а не над всей таблицей. Кроме того, партиционирование может значительно повысить масштабируемость баз данных PostgreSQL, распределяя нагрузку между несколькими партициями.
В ClickHouse партиционирование задается для таблицы при ее первоначальном определении с помощью предложения `PARTITION BY`. Это предложение может содержать SQL-выражение по любым столбцам, результат которого определяет, в какую партицию будет отправлена строка.
Части данных на диске логически связаны с каждой партицией и могут запрашиваться изолированно. В примере ниже мы партиционируем таблицу `posts` по годам с помощью выражения `toYear(CreationDate)`. По мере вставки строк в ClickHouse это выражение будет вычисляться для каждой строки, и строка будет направляться в соответствующую партицию, если она уже существует (если строка для данного года первая, партиция будет создана).
```sql theme={null}
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)
```
Подробное описание партиционирования см. в ["Партиции таблиц"](/ru/concepts/core-concepts/partitions).
### Применение партиций
В ClickHouse партиционирование применяется примерно так же, как в Postgres, но с некоторыми нюансами. В частности:
* **Управление данными** - В ClickHouse партиционирование следует в первую очередь рассматривать как средство управления данными, а не как метод оптимизации запросов. При логическом разделении данных по ключу с каждой партицией можно работать независимо, например удалять её. Это позволяет эффективно перемещать партиции, а значит и подмножества данных, между [уровнями хранения](/ru/integrations/connectors/data-ingestion/AWS/integrating-s3-with-clickhouse#storage-tiers) по времени, а также [задавать срок хранения данных/эффективно удалять их из кластера](/ru/reference/statements/alter/partition). В примере ниже мы удаляем посты за 2008 год.
```sql theme={null}
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'
```
```response theme={null}
┌─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.
```
```sql theme={null}
ALTER TABLE posts
(DROP PARTITION '2008')
```
```response theme={null}
Ok.
0 rows in set. Elapsed: 0.103 sec.
```
* **Оптимизация запросов** - Хотя партиции могут помочь повысить производительность запросов, это сильно зависит от характера доступа к данным. Если запросы затрагивают только несколько партиций (в идеале одну), производительность может улучшиться. Обычно это имеет смысл только тогда, когда ключ партиционирования не входит в первичный ключ и фильтрация выполняется по нему. Однако запросы, которым нужно охватывать много партиций, могут работать хуже, чем без партиционирования (поскольку из-за партиционирования может образовываться больше частей). Преимущество обращения к одной партиции будет еще менее заметным или вовсе отсутствовать, если ключ партиционирования уже находится среди первых полей первичного ключа. Партиционирование также можно использовать для [оптимизации запросов GROUP BY](/ru/reference/engines/table-engines/mergetree-family/custom-partitioning-key#group-by-optimisation-using-partition-key), если значения в каждой партиции уникальны. Однако в целом следует убедиться, что первичный ключ оптимизирован, и рассматривать партиционирование как метод оптимизации запросов только в исключительных случаях, когда характер доступа предполагает работу с определенным предсказуемым подмножеством данных, например партиционирование по дням, если большинство запросов обращается только к последнему дню.
### Рекомендации по партициям
Партиционирование следует рассматривать как метод управления данными. Оно особенно полезно, когда при работе с данными временных рядов нужно удалять данные из кластера: например, самую старую партицию можно [просто удалить](/ru/reference/statements/alter/partition#drop-partitionpart).
**Важно:** Убедитесь, что выражение ключа партиционирования не приводит к множеству высокой мощности, то есть следует избегать создания более 100 партиций. Например, не партиционируйте данные по столбцам с высокой мощностью, таким как идентификаторы или имена клиентов. Вместо этого сделайте идентификатор или имя клиента первым столбцом в выражении ORDER BY.
> Внутри ClickHouse [создаёт части](/ru/guides/clickhouse/data-modelling/sparse-primary-indexes#clickhouse-index-design) для вставляемых данных. По мере вставки новых данных число частей увеличивается. Чтобы предотвратить чрезмерный рост числа частей, который ухудшает производительность запросов (из-за большего количества файлов для чтения), части объединяются в фоновом асинхронном процессе. Если число частей превышает заранее настроенный предел, ClickHouse сгенерирует исключение при вставке — ошибку "too many parts". В нормальном режиме работы этого происходить не должно; такое случается только если ClickHouse настроен неправильно или используется некорректно, например при большом количестве мелких вставок.
> Поскольку части создаются изолированно для каждой партиции, увеличение числа партиций приводит и к увеличению числа частей, то есть оно становится кратным числу партиций. Поэтому ключи партиционирования с высокой мощностью могут вызывать эту ошибку, и их следует избегать.
## Materialized views и проекции
Postgres позволяет создавать несколько индексов для одной таблицы, что дает возможность оптимизировать ее для различных сценариев доступа. Такая гибкость позволяет администраторам и разработчикам настраивать производительность базы данных под конкретные запросы и эксплуатационные потребности. Концепция проекций в ClickHouse, хотя и не является полным аналогом этого подхода, позволяет задавать для таблицы несколько секций `ORDER BY`.
В [документации по моделированию данных](/ru/guides/clickhouse/data-modelling/schema-design) ClickHouse мы рассматриваем, как materialized view в ClickHouse можно использовать для предварительного вычисления агрегаций, преобразования строк и оптимизации запросов для различных сценариев доступа.
Во втором случае мы привели [пример](/ru/concepts/features/materialized-views/incremental-materialized-view#lookup-table), где materialized view отправляет строки в целевую таблицу с другим ключом сортировки, чем у исходной таблицы, в которую выполняются вставки.
Например, рассмотрим следующий запрос:
```sql theme={null}
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
```
```response theme={null}
┌──────────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`. Ту же проблему можно решить
с помощью [проекции](/ru/concepts/features/projections/projections). Приведённая ниже команда добавляет
проекцию для `ORDER BY user_id`.
```sql theme={null}
ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)
ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id
```
Обратите внимание, что сначала нужно создать проекцию, а затем материализовать её. Последняя команда приводит к тому, что данные сохраняются на диске дважды в двух разных порядках. Проекцию также можно определить при созданении данных, как показано ниже, и она будет автоматически поддерживаться по мере вставки новых данных.
```sql theme={null}
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`.
```sql theme={null}
SELECT
parts_to_do,
is_done,
latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')
```
```response theme={null}
┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │ 1 │ 0 │ │
└─────────────┴─────────┴────────────────────┘
1 строка в наборе. Elapsed: 0.003 сек.
```
Если повторить приведённый выше запрос, можно увидеть, что производительность значительно выросла за счёт дополнительного места для хранения.
```sql theme={null}
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
```
```response theme={null}
┌──────────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` мы также можем убедиться, что для этого запроса использовалась проекция:
```sql theme={null}
EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
```
```response theme={null}
┌─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, здесь пользователю не нужно выбирать
подходящую оптимизированную целевую таблицу или переписывать запрос в зависимости от фильтров.
Это повышает нагрузку на пользовательские приложения и увеличивает сложность на стороне клиента.
Несмотря на эти преимущества, у проекций есть [присущие ограничения](/ru/concepts/features/projections/projections#when-to-use-projections),
о которых следует знать, поэтому применять их стоит умеренно.
Мы рекомендуем использовать проекции, когда:
* Требуется полное переупорядочивание данных. Хотя выражение в
проекции теоретически может использовать `GROUP BY,` materialized views
эффективнее подходят для поддержки агрегатов. Оптимизатор запросов также с большей вероятностью
будет использовать проекции с простым переупорядочиванием, то есть `SELECT * ORDER BY x`.
В этом выражении можно выбрать подмножество столбцов, чтобы уменьшить объем хранилища.
* Пользователи готовы к связанному с этим увеличению объема хранилища и
дополнительным накладным расходам из-за двойной записи данных. Проверьте влияние на скорость вставки и
[оцените накладные расходы на хранение](/ru/guides/clickhouse/data-modelling/compression/compression-in-clickhouse).
Начиная с версии 25.5, ClickHouse поддерживает виртуальный столбец `_part_offset` в
проекциях. Это позволяет хранить проекции более экономно с точки зрения дискового пространства.
Подробнее см. в разделе ["Проекции"](/ru/concepts/features/projections/projections)
## Денормализация
Поскольку Postgres — реляционная база данных, её модель данных обычно сильно [нормализована](https://en.wikipedia.org/wiki/Database_normalization) и нередко включает сотни таблиц. В ClickHouse денормализация иногда бывает полезна для оптимизации производительности JOIN.
Вы можете обратиться к этому [руководству](/ru/guides/clickhouse/data-modelling/denormalization), где показаны преимущества денормализации набора данных Stack Overflow в ClickHouse.
На этом наше базовое руководство для тех, кто переходит с Postgres на ClickHouse, заканчивается. Мы рекомендуем прочитать [руководство по моделированию данных в ClickHouse](/ru/guides/clickhouse/data-modelling/schema-design), чтобы больше узнать о расширенных возможностях ClickHouse.