データモデリング技術

これはPostgreSQLからClickHouseに移行するためのガイドのパート3です。このコンテンツは入門的な内容と考えられ、ユーザーがClickHouseのベストプラクティスに従った初期の実用的システムを展開するのを助けることを目的としています。複雑なトピックを避け、完全に最適化されたスキーマを結果として提供するのではなく、ユーザーがプロダクションシステムを構築し、学習の基盤を築くためのしっかりとした基盤を提供します。

Postgresから移行するユーザーには、ClickHouseにおけるデータモデリングのガイドを読むことをお勧めします。このガイドでは、同じStack Overflowのデータセットを使用し、ClickHouseの機能を利用した複数のアプローチを探ります。

パーティション

Postgresユーザーは、テーブルをパーティション（パート）と呼ばれる小さく管理しやすい部分に分割することで、大規模なデータベースのパフォーマンスと管理を向上させるためのテーブルのパーティショニングという概念に慣れているでしょう。このパーティショニングは、指定されたカラム（例：日付）に対する範囲を使用するか、定義されたリストを使用するか、またはキーに対するハッシュを使用することで達成できます。これにより、管理者は日付範囲や地理的な場所など、特定の基準に基づいてデータを整理できます。パーティショニングは、パーティションプルーニングによってデータアクセスを高速化し、より効率的なインデックス作成を可能にすることで、クエリパフォーマンスの向上を助けます。また、バックアップやデータの削除などのメンテナンスタスクにも役立ち、全テーブルではなく個々のパーティションに対して操作を行うことができます。さらに、パーティショニングはPostgreSQLデータベースのスケーラビリティを大幅に向上させ、負荷を複数のパーティションに分散させることができます。

ClickHouseでは、パーティショニングはテーブルが初めて定義される際にPARTITION BY句を使用して指定されます。この句には任意のカラムに対するSQL式を含めることができ、この結果が行が送信されるパーティションを定義します。

データのパーツは、ディスク上の各パーティションと論理的に関連付けられ、独立してクエリできます。以下の例では、postsテーブルを年ごとにパーティション分けし、式toYear(CreationDate)を使用します。行がClickHouseに挿入されると、この式が各行に対して評価され、結果のパーティションにルーティングされます（その年の最初の行であれば、パーティションが作成されます）。

パーティションの応用

ClickHouseにおけるパーティショニングはPostgresと似た応用がありますが、いくつかの微妙な違いがあります。具体的には：

• データ管理 - ClickHouseでは、ユーザーはパーティショニングを主にデータ管理機能と見なすべきであり、クエリ最適化技術と考えるべきではありません。キーに基づいてデータを論理的に分離することで、各パーティションは独立して操作でき、例えば削除できます。これにより、パーティションを移動させることができ、サブセットをストレージ階層間で効率的に移動できます。また、古いパーティションは単純に削除することができる。以下の例では、2008年の投稿を削除します。

• クエリ最適化 - パーティションはクエリパフォーマンスの改善に寄与することがありますが、これはアクセスパターンに大きく依存します。クエリが少数のパーティション（理想的には1つ）をターゲットにする場合、パフォーマンスが向上する可能性があります。ただし、パーティショニングキーが主キーに含まれず、フィルタリングされている場合に限ります。しかし、多くのパーティションを対象とする必要があるクエリは、パーティショニングを使用しない場合よりもパフォーマンスが低下する可能性があります（パーティショニングの結果、パーツの数が増える可能性があるためです）。単一のパーティションをターゲットにする利点は、パーティショニングキーがすでに主キーの初期エントリである場合にはほとんどなくなります。パーティショニングは、各パーティション内の値が一意である場合、GROUP BYクエリを最適化するために使用することができます。ただし、一般的には、ユーザーは主キーが最適化されていることを確認し、特定の予測可能なサブセットへのアクセスパターンがある例外的な場合にのみクエリ最適化技術としてパーティショニングを考慮すべきです。たとえば、日ごとのパーティショニングを行い、ほとんどのクエリが前日のものである場合などです。

パーティションの推奨事項

ユーザーはパーティショニングをデータ管理技術と見なすべきです。これは、時系列データを扱う際にクラスターからデータを期限切れにする必要がある場合に最適です。例えば、最も古いパーティションは単純に削除できる。

重要: パーティショニングキーの式が高いカーディナリティのセットを生成しないようにしてください。すなわち、100以上のパーティションを作成することは避けるべきです。例えば、クライアント識別子や名前のような高カーディナリティのカラムでデータをパーティショニングするのではなく、クライアント識別子または名前をORDER BY式の最初のカラムにします。

内部的にClickHouseは、挿入されたデータのためにパーツを作成します。データが挿入されるにつれて、パーツの数が増加します。クエリパフォーマンスを低下させる過度に多くのパーツを防ぐために、パーツはバックグラウンドの非同期プロセスで一緒にマージされます。パーツの数が事前に設定された限界を超えると、ClickHouseは挿入時に例外をスローします - "too many parts"エラーとしてです。これは、通常の運用では発生せず、ClickHouseが不適切に設定されているか、誤って使用されている場合（たとえば、多くの小規模な挿入の場合）にのみ発生します。

パーツはパーティションごとに独立して作成されるため、パーティションの数を増やすと、パーツの数が増加します。すなわち、それはパーティションの数の倍数です。高カーディナリティのパーティショニングキーはこのエラーを引き起こす可能性があるため、避けるべきです。

マテリアライズドビューとプロジェクションの違い

Postgresは、単一のテーブルに対して複数のインデックスを作成できるため、さまざまなアクセスパターンに最適化できます。この柔軟性により、管理者や開発者は特定のクエリと運用ニーズに応じてデータベースパフォーマンスを調整できます。ClickHouseのプロジェクションの概念はこれと完全には類似していませんが、ユーザーはテーブルに対して複数のORDER BY句を指定できます。

ClickHouseのデータモデリングドキュメントでは、マテリアライズドビューを使って集計を事前に計算し、行を変換し、異なるアクセスパターン向けにクエリを最適化する方法を探ります。

後者の例として、マテリアライズドビューが異なる順序キーを持つターゲットテーブルに行を送る例を提供しました。

例えば、以下のクエリを考えてみましょう：

このクエリでは、UserIdが順序キーでないため、すべての90m行をスキャンする必要があります（迅速に行われるにしても）。以前は、PostIdのためのルックアップアクションとしてマテリアライズドビューを使用してこの問題を解決しました。同様の問題はプロジェクションでも解決可能です。以下のコマンドはORDER BY user_idのためのプロジェクションを追加します。

プロジェクションを最初に作成し、次にマテリアライズする必要があります。この後者のコマンドは、データが異なる順序でディスクに二重に保存される原因となります。データが作成される際にプロジェクションを定義することも可能で、以下に示すように、データが挿入されると自動的に維持されます。

プロジェクションがALTERを介して作成される場合、MATERIALIZE PROJECTIONコマンドが発行されたときに非同期で作成されます。ユーザーは以下のクエリを使ってこの操作の進行状況を確認でき、is_done=1を待ちます。

上記のクエリを繰り返すと、パフォーマンスが著しく向上していることが確認でき、追加ストレージのコストがかかります。

EXPLAINコマンドを使って、このクエリにプロジェクションが使用されたか確認します：

プロジェクションを使用する時期

プロジェクションは、新しいユーザーにとって魅力的な機能で、データが挿入されると自動的に維持されます。さらに、クエリは単一のテーブルに送信され、可能な限りプロジェクションを利用することで応答時間を短縮できます。

これは、マテリアライズドビューの対照的な位置付けで、ユーザーは適切な最適化されたターゲットテーブルを選択する必要があるか、フィルターに応じてクエリを書き直す必要があります。これはユーザーアプリケーションにより大きな重点を置き、クライアント側の複雑さを増加させます。

これらの利点にもかかわらず、プロジェクションにはユーザーが注意すべきいくつかの固有の制限があります。そのため、プロジェクションは控えめに展開すべきです。

• プロジェクションでは、ソーステーブルと（隠れた）ターゲットテーブルに異なるTTLを使用することはできず、マテリアライズドビューでは異なるTTLが許可されます。
• プロジェクションは現在サポートされていません optimize_read_in_order（隠れた）ターゲットテーブルに対して。
• プロジェクションのあるテーブルでは、軽量更新と削除はサポートされていません。
• マテリアライズドビューはチェーン化可能です：1つのマテリアライズドビューのターゲットテーブルは、別のマテリアライズドビューのソーステーブルとなることができます。このようなことはプロジェクションでは不可能です。
• プロジェクションは結合（JOIN）をサポートしていませんが、マテリアライズドビューはサポートしています。
• プロジェクションはフィルタ（WHERE句）をサポートしていませんが、マテリアライズドビューはサポートしています。

次の状況でプロジェクションを使用することをお勧めします：

• データの完全な再配置が必要な場合。プロジェクション内の式は理論的にはGROUP BYを使用できますが、マテリアライズドビューは集計を維持するのにより効果的です。クエリオプティマイザは、単純な再配置を使用するプロジェクションを利用しやすいです。すなわち、SELECT * ORDER BY x。ユーザーはこの式の中でカラムのサブセットを選択し、ストレージフットプリントを削減できます。
• ユーザーがストレージフットプリントとデータを二重に書き込むことによるオーバーヘッドの関連増加に快適である場合。挿入速度への影響をテストし、ストレージオーバーヘッドを評価。

こちらをクリックしてパート4へ進む.