PostgresからClickHouseへ: データモデリングのヒント

先月、当社はPeerDBを買収しました。PeerDBはPostgresのCDCを専門とする企業で、PeerDBを使うと、PostgresからClickHouseへのデータレプリケーションが高速かつ簡単に行えます。PeerDBのユーザーからよくある質問として、「データをどのようにClickHouseにモデル化するとClickHouseの利点を最大限活かせるか」というものがあります。

この質問が出る理由は、ClickHouseとPostgresのデータモデルが異なるからです。それぞれ最適化された目的別データベースであり、Postgresはトランザクション(OLTP)向け、ClickHouseは分析(OLAP)向けのカラムナデータベースだからです。本ガイドでは、Postgresの世界から来たユーザー向けに、ClickHouseのデータモデリングの基本を解説します。なお、これはブログシリーズの第1弾で、今後も続編を予定しています。

PeerDBは、PostgreSQLのテーブルをReplacingMergeTreeエンジンでClickHouseにマッピングします。ClickHouseは追記型のワークロードで最も高いパフォーマンスを発揮し、頻繁なUPDATEは推奨されません。ここで特に強力なのがReplacingMergeTreeです。

ReplacingMergeTreeは、データの取り込みと変更の両方が行われるワークロードをサポートします。テーブルは追記専用で、ユーザーによるUPDATEはバージョン付きのINSERTとして取り込まれます。ReplacingMergeTreeエンジンはバックグラウンドで行をマージしながら重複排除を行うため、ClickHouseはリアルタイムの取り込みで非常に高いパフォーマンスを発揮します。

PeerDBでは、PostgresからのINSERTとUPDATEがClickHouse側では異なるバージョン（_peerdb_version）を持つ新しい行として取り込まれます。ReplacingMergeTreeテーブルエンジンは、Ordering Key（ORDER BY カラム）を使ってバックグラウンドで重複を処理し、最新の_peerdb_versionを持つ行だけを最終的に残します。PostgreSQLからのDELETEは、_peerdb_is_deletedカラムを使って削除フラグ付きの新規行として反映されます。以下のスニペットは、ClickHouse上のpublic_goalsテーブル定義例です。

clickhouse-cloud :) SHOW CREATE TABLE public_goals;
CREATE TABLE peerdb.public_goals
(
    `id` Int64,
    `owned_user_id` String,
    `goal_title` String,
    `goal_data` String,
    `enabled` Bool,
    `ts` DateTime64(6),
    `_peerdb_synced_at` DateTime64(9) DEFAULT now(),
    `_peerdb_is_deleted` Int8,
    `_peerdb_version` Int64
)
ENGINE = SharedReplacingMergeTree
('/clickhouse/tables/{uuid}/{shard}', '{replica}', _peerdb_version)
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192

ReplacingMergeTreeはバックグラウンドで非同期に重複排除を行うため、重複を完全になくすことは保証されません。そのため、クエリ結果に同じ行や同じ主キーを持つ行が異なるバージョンで表示されることがあります。これは想定どおりの動作です。重複を取り除くには、いくつか方法があります。

ClickHouseにはFINALというユニークな修飾子があり、クエリ実行時に行のマージ（重複排除）を行います。重複排除はWHERE句の後、GROUP BYなどの集計の前に実行されます。

過去にはFINALを使うとクエリ性能が低下するという懸念がありましたが、ClickHouseの最近のリリースではFINALクエリのパフォーマンスが大幅に改善されています。そのため、まずはFINAL句を使ってみて、クエリのパフォーマンスを評価してみるのがいいでしょう。以下はFINAL句の例です。

SELECT owner_user_id, COUNT(*) FROM goals FINAL 
WHERE enabled = true GROUP BY owner_user_id;

ClickHouseのargMaxは、クエリ実行時に動的に重複排除するのに便利な関数です。バージョンやタイムスタンプ列に基づいて最新のレコードだけを取りたい場合によく使います。

たとえば、peerdb.public_goalsテーブルでidが主キー、_peerdb_versionがバージョンを示す場合、argMaxを使って各idの最大_peerdb_versionを持つ行を選択できます。これで元データを変更せずに重複を取り除き、サブクエリで集計を行えます。以下はargMaxの例です。

SELECT
    owned_user_id,
    COUNT(*) AS active_goals_count,
    MAX(ts) AS latest_goal_time
FROM
(
    SELECT
        id,
        argMax(owned_user_id, _peerdb_version) AS owned_user_id,
        argMax(goal_title, _peerdb_version) AS goal_title,
        argMax(goal_data, _peerdb_version) AS goal_data,
        argMax(enabled, _peerdb_version) AS enabled,
        argMax(ts, _peerdb_version) AS ts,
        argMax(_peerdb_synced_at, _peerdb_version) AS _peerdb_synced_at,
        argMax(_peerdb_is_deleted, _peerdb_version) AS _peerdb_is_deleted,
        max(_peerdb_version) AS _peerdb_version
    FROM peerdb.public_goals
    WHERE enabled = true
    GROUP BY id
) AS deduplicated_goals
GROUP BY owned_user_id;

ClickHouseのウィンドウ関数を使って、idごとに_peerdb_versionが最大の行だけを選択し、重複を排除することもできます。以下は例です。

SELECT
    owned_user_id,
    COUNT(*) AS active_goals_count,
    MAX(ts) AS latest_goal_time
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn
    FROM peerdb.public_goals
    WHERE enabled = true
) AS ranked_goals
WHERE rn = 1
GROUP BY owned_user_id;

VIEWを使って重複排除のロジックをカプセル化し、BIツールなどから常に最新データだけを簡単に参照できるようにする方法もあります。たとえば、ウィンドウ関数で最新バージョンだけを残すVIEWを作成できます。

CREATE VIEW goals AS
SELECT * FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn
    FROM peerdb.public_goals
    WHERE enabled = true
) WHERE rn = 1;

SELECT
    owned_user_id,
    COUNT(*) AS active_goals_count,
    MAX(ts) AS latest_goal_time
FROM goals
GROUP BY owned_user_id;

Postgresから移行して驚くことの一つに、ClickHouseでは、Nullableで明示的に指定しない限りNULL値を格納しないという仕様があります。たとえば日付のカラムであれば、NULLを格納する代わりに1970-01-01のようなデフォルト値を使うので、想定外に感じるかもしれません。これはカラムナデータベースとしての特性で、NULLを格納するとクエリパフォーマンスに影響を与えるためです。そのため、ClickHouseではユーザーがNullableを明示する必要があります。

PeerDBでは、PEERDB_NULLABLEという設定を導入しており、trueを指定すると、PostgresでNULLがあり得るカラムを自動的にNullable扱いでClickHouseにマッピングしてくれます。そのため、手動でNullableを定義する必要はありません。詳しくはこちらのPRを参照してください。

ClickHouseは数値、テキスト、タイムスタンプ、日付、配列から、最近追加されたJSON型まで、多彩なデータ型をサポートしています。Postgresの多くのデータ型は、ほとんど修正なしでClickHouseに格納できます。

参考までに、PeerDBがPostgresからClickHouseへデータをレプリケートするときに使っているデータ型マトリックスを共有します。

Ordering Keyを正しく選ぶことはClickHouseにおけるクエリ性能の要です。テーブル作成時に指定するORDER BY句で定義され、Postgresにおけるインデックスのような役割を果たしますが、分析用途に最適化されています。PostgresのB-treeインデックスが行ごとにポインタを管理するのとは異なり、ClickHouseはSparse Indexingを用います。

1. データはOrdering Keyに基づいてソート: ORDER BYで指定されたカラムに基づき、ディスク上のデータがソートされます。値が近いもの同士がまとまるため、圧縮が効きやすくなります。
2. Ordering Keyはスパースインデックスも作成: Ordering Keyによりカラムの範囲のみを保存するスパースインデックスも作られます。エントリが各行を指すのではなく、ソートされた行のまとまりを指すため、インデックス自体が小さく、バイナリサーチで素早くデータの範囲を絞り込めます。詳しくはこちらを参照してください。

Ordering Keyは、PostgresのBRINインデックスに似た考え方ですが、ClickHouseではデータがOrdering Keyに基づいて自動的にソート（パーツの非同期マージ）されるため、取り込み時にユーザーがソートを意識する必要はありません。

Ordering Keyを選ぶ際は、クエリのWHERE句でよく使うカラムを優先的に指定します。カーディナリティ（重複の少なさ）が低いカラムから順番に並べると圧縮効率も高まり、クエリ性能も上がります。より詳しい内容はこちらを参照してください。

public_goalsテーブル定義をみるとPRIMARY KEYが指定されていますが、ORDER BY句もあります。両者の違いは何でしょうか？

1. PRIMARY KEYを指定した場合、そのカラムがスパースインデックスとして使われ、ORDER BY句で指定されたカラム順でディスク上のデータがソートされます。そしてReplacingMergeTreeでのデータの重複排除にも使われます。
2. PRIMARY KEYを指定しなかった場合、Ordering Keyが自動的にPRIMARY KEYにもなり、スパースインデックスとして機能します。

NOTE: PRIMARY KEYのカラムは、常にOrdering Keyの先頭に含める必要があります。インデックスと物理的なデータの順序が一致することで、不要なデータスキャンを最小化し、クエリ性能を最大化できます。

PRIMARY KEYとORDER BYが異なる例

たとえば、クエリでcustomer_idを使うことが多く、idではあまりフィルタしないケースを考えます。この場合、PRIMARY KEYをcustomer_idにして、ORDER BYをcustomer_id, idにすると、スパースインデックスが小さくなって効率的になり、データの重複排除もid単位で行えます。

NOTE: PostgresのPRIMARY KEYは一意性を保証しますが、ClickHouseではそうではなく、スパースインデックスの定義に使われるという点が異なります。

Ordering Key（こちらに解説あり）は、クエリ性能に直結するので非常に重要です。PeerDBではデフォルトでPostgreSQLのPRIMARY KEYをOrdering Keyとして使いますが、変更する方法はいくつかあります。

マテリアライズドビューを使うと、新しいOrdering Keyを持つテーブルを作成できます。重複排除のために、ReplacingMergeTreeを使う場合は主キーとなるカラムをOrdering Keyの末尾に含めるのがおすすめです。以下は例です。

CREATE MATERIALIZED VIEW goals_mv
ENGINE = ReplacingMergeTree(_peerdb_version)
ORDER BY (enabled, ts, id) POPULATE AS
SELECT * FROM peerdb.public_goals;

NOTE: マテリアライズドビュー作成後は、前のセクションで説明した重複対応策を適用して、クエリ時の重複排除をきちんと行ってください。

Ordering Keyを変えたい場合は、あらかじめ目的のOrdering Keyで新しいテーブルを作り、既存のテーブルと入れ替える方法もあります。手順は以下です。

1. Dummy Mirrorを作成する: PeerDBでダミーのミラーを作り、必要なメタデータカラムやデータ型を定義した既定テーブルを生成します。

2. 新しいOrdering Keyでテーブルを作成: PeerDBが作成したテーブルを参考に、新しいOrdering Keyを使ったテーブルを作ります。重複排除の観点から、主キーのカラムをOrdering Keyの末尾に入れるのがおすすめです。以下は例です。

CREATE TABLE public_events_new AS public_events
ENGINE = ReplacingMergeTree(_peerdb_version)
ORDER BY (user_id,id);

3. 古いテーブルを削除:

DROP TABLE public_events;

4. 新しいテーブルの名前を変更:

RENAME TABLE public_events_new TO public_events;

5. MIRRORを新テーブルに向けて開始: MIRROR設定を新しいテーブルに向けます。PeerDBは内部的にCREATE TABLE IF NOT EXISTSを使っているので、そのまま新テーブルにデータが取り込まれます。

前述のとおり、PostgreSQLのDELETEは_peerdb_is_deletedカラムに削除フラグを立てた行として取り込まれます。この削除フラグが立った行をクエリから除外したい場合は、ClickHouseの行レベルポリシーを使うことができます。例は以下のとおりです。

CREATE ROW POLICY policy_name ON table_name
FOR SELECT USING _peerdb_is_deleted = 0;

このポリシーを設定すると、_peerdb_is_deletedが0の行だけがSELECTクエリで参照されるようになります。

ここまで読んでいただきありがとうございます。PostgreSQLからClickHouseへ移行するときにありがちなデータモデリング上の注意点を中心に解説しました。次回のブログでは、さらに高度なトピックとしてJOINや効率的なSQLの書き方などを深掘りする予定です。もしPostgresからClickHouseへのデータレプリケーションを試してみたい方は、以下のリンクからPeerDBやClickHouseを触っていただくか、直接お問い合わせください！

1. ClickHouse Cloudを無料で試す
2. PeerDB Cloudを無料で試す
3. PostgresからClickHouseへのレプリケーションドキュメント
4. PeerDBチームに直接連絡する