クリック分析にClickHouseを選ぶ理由

Postgresは優れています。私たちはそれを多用しており、信頼しています。すべてのリンク、すべてのワークスペース、すべての請求レコード、すべてのユーザーをPostgresに保存しています。しかし、成長しているURL短縮サービスで約90日後、rawクリックイベントも同様にそこに保存していると、壁にぶつかります。壁は劇的なものではありません--Postgresが落ちるのではなく、大きな追記専用テーブルへの集計クエリが遅くなるという、特定の、苛立たしい方法で遅くなっていくだけです。インデックススキャンが効かなくなります。Autovacuumが追いつけなくなります。800msで読み込まれていたダッシュボードが4秒かかるようになります。インデックスを追加します。さらに悪くなります。

この記事では、クリックイベントをClickHouseに移行した理由、スキーマがどのようなものか、インジェストパスがどのように機能するか、そしてどこに落とし穴があるかを説明します。リダイレクトレイテンシの記事の補足であり、リダイレクトの下流に位置するデータ層--rawな「クリックして忘れる」イベントをアナリティクスソリューションページのダッシュボードに変えるパート--をカバーします。

TL;DR

クリックイベントは追記専用で、集計クエリ型です。カラム型ストレージは両方の点で有利です。
Postgresの行ごとのイベントオーバーヘッド、インデックスのブロート、バキューム競合は共有テーブル上の約3,000万イベントを超えると複合的に悪化します。
ORDER BY (workspace_id, link_id, timestamp) を持つClickHouseは、追加のインデックスなしで90日間のワークスペースごとのロールアップを100ms以内で返します。
リンクメタデータはPostgresに残ります（更新可能、トランザクション型）。クリックイベントはClickHouseに残ります（追記専用、分析型）。書き込み時に二つが重なる必要がないため、分割はクリーンです。

Postgresがこのワークロードで苦労する理由

ワークロードの形が問題です。クリックイベントは次のような特性を持ちます：

一度書き込まれ、二度と更新されない。
高く、バースト的な速度で書き込まれる--書き込み対読み取りの比率は大まかに50:1で、キャンペーンのローンチ日にはさらに急増する。
集計でクエリされる：リンクごとの総クリック数、ワークスペースの地域内訳、キャンペーンの上位100リンクのデバイス分割。単一のクリック行を直接クエリする人はほとんどいない。

Postgresは異なる前提のセットのために構築されています。すべての行にはタプルヘッダー、トランザクションIDフィールド（xmin、xmax）、可視性メタデータが含まれています。行をインプレースで更新し、同時読み書きを処理するためにMVCCが必要なトランザクションワークロードでは、そのオーバーヘッドは正確性のために払う代価です。二度と触れない追記専用の分析データには、それは純粋な無駄です。

インデックスの問題はさらに深刻です。PostgresのB-treeインデックスは選択的なポイントルックアップ--link_id = 'xyz' の行を見つける--に対して機能します。ワークスペースの1,000万行にわたって GROUP BY link_id, country を実行するときにはあまり役立ちません。インデックスはスキャンを絞り込みますが、クエリは依然としてヒープページから圧縮解除された行データを取得し、デシリアライズし、集計する必要があります。3,000万行ではクエリは遅いです。1億行では辛いです。

Autovacuumは実際にはこれを悪化させます。高スループットの挿入はデッドタプル（アクティブなトランザクションには見えなくなったがまだクリーンアップされていないMVCC行バージョン）を生成します。AutovacuumはそれらをI/Oのためにライブクエリと競合しながら回収します。Postgresダッシュボードのイベントテーブルでautovacuumが実行されているときに対応する定期的なレイテンシスパイクが見られます。TimescaleDBの圧縮とチャンクアーキテクチャのドキュメントは、Postgresエクステンションがこれを回避しようとする方法の詳細について読む価値があります--問題は、Postgres上に専用の時系列レイヤーがそれを解決するために存在するほど深刻なものです。

私たちはプロジェクトの最初の90日間、Postgresでクリックを実行しました。約400万クリックイベントを持つワークスペースでは、7日間の国別ロールアップをレンダリングするのに3.2秒かかるダッシュボードがありました。(workspace_id, created_at) に部分インデックスを追加すると1.4秒になりました。当時は十分でしたが、クリック量が桁違いに増えていく場合にスケールする経路でないことは明らかでした。

ClickHouseが適合する理由

ClickHouseは分析ワークロードのために明示的に構築されたカラム型データベースエンジンです--2026年5月12日アクセス。カラム型とは、各カラムのデータが行ごとにインターリーブされるのではなく、ディスク上で連続して保存されることを意味します。「過去30日間のワークスペースXの国別クリック数を合計する」というようなクエリでは、エンジンは country_code、workspace_id、timestamp カラムのみを読み込みます。user_agent や referer には一切触れません。多くのカラムを持つ幅広いテーブルでのI/O削減は実質的です。

圧縮率が二番目の特性です。カラム型データは行データよりも大幅に圧縮されます。同じカラム内の繰り返し値--何千回も現れる同じ workspace_id、60種類ほどの国コードのセット、いくつかの device 値--がClickHouseのLZ4とZSTDコーデックでほぼ何もないほどに圧縮されます。このような低カーディナリティのカラムは、カラム型コーデックがPostgresの行ストレージに対して最も優位に立つところです。それはストレージの勝利だけでなく--ディスク上のデータが小さいほど、OSページキャッシュに多く収まり、クエリごとのディスク読み取りが減ります。

三番目の特性はMergeTreeエンジンファミリーです--2026年5月12日アクセス。MergeTreeテーブルはテーブルの ORDER BY キーによってデータをソートしてクラスタリングします。そのキーの先頭カラムでフィルタリングまたはグループ化するクエリは、フィルター範囲と重なるデータパーツのみを読み込みます。これはClickHouseのクラスタードインデックスに相当するもので、テーブル作成時に定義が必要であり、インデックス付きのカラムだけでなくすべてのクエリに適用されます。

私たちは ORDER BY (workspace_id, link_id, timestamp) を使用しています。ワークスペース 123 の過去7日間のクリックのクエリは、workspace_id = 123 が現れるデータパーツのみを読み、そのパーツ内で時間範囲のスライスのみを読み込みます。90日間のクリックデータでは、これは通常総データパーツの3〜8%に触れます。ClickHouseが ORDER BY カラムで維持するスパースプライマリインデックスにより、すべてのクエリパターンに対して密なB-treeを必要とせず、このターゲティングを効率的に行えます。ReplicatedMergeTreeはHAを追加します--ZooKeeper相当の調整（ClickHouse Keeper）を備えた2つ以上のClickHouseノードが、書き込まれたパーツをレプリケートします。私たちはまだこれを運用していません。本番は単一のホストで、プレーンな MergeTree を使う1台のClickHouseサーバーです。1台では足りなくなったら、レプリケーションが次の計画です。

私たちが採用しているスキーマ

CREATE TABLE IF NOT EXISTS click_events
(
    timestamp       DateTime64(3, 'UTC'),
    link_id         UInt64,
    workspace_id    UInt64,
    domain_id       UInt64,
    slug            LowCardinality(String),
    host            LowCardinality(String),
    user_agent      String,
    referer         String,
    ip              String,
    country_code    LowCardinality(FixedString(2)) DEFAULT '',
    device          LowCardinality(String) DEFAULT '',
    browser         LowCardinality(String) DEFAULT '',
    destination     String DEFAULT '',
    retention_days  UInt16 DEFAULT 730,
    INDEX idx_workspace workspace_id TYPE bloom_filter GRANULARITY 4,
    INDEX idx_link      link_id      TYPE bloom_filter GRANULARITY 4
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (workspace_id, link_id, timestamp)
TTL toDateTime(timestamp) + toIntervalDay(retention_days);

これがベーステーブルです。その後のマイグレーションで、リージョン、市区町村、おおよその座標、日ごとの訪問者ハッシュ、ボット・スキャナー・不審クリックのフラグが ALTER TABLE ... ADD COLUMN で追加されています。

いくつかのスキーマの決定について説明します。

slug、host、country_code、device、browser に LowCardinality(String) を使用--これらのカラムは値が限られているか、繰り返しが非常に多いものです（数百のカスタムホスト名、250種類ほどの国コード、いくつかのデバイスタイプ）。IDはプレーンな UInt64 で、それだけで十分によく圧縮されます。LowCardinality はディクショナリエンコーディングを有効にし、値を一意の文字列のディクショナリへの整数参照として保存します。これらのカラムのクエリ速度が大幅に向上し、圧縮もさらに改善されます。これはClickHouseのlow-cardinalityデータ型のドキュメントから学びました--経験則として、異なる値が10,000未満の文字列カラムは候補になります。

PARTITION BY toYYYYMM(timestamp) はカレンダー月ごとに別のデータパーティションを作成します。古いパーティションは丸ごと、単一の ALTER TABLE click_events DROP PARTITION '202501' ステートメントでドロップ（または低速ストレージへ移動）できます。これは即時で--フルテーブルスキャンなし。ただし、1つのパーティションにはその月のすべてのワークスペースのクリックが入るため、プランごとの保持期間をパーティションのドロップで実現することはできません。代わりに、各行はインジェスト時にそのワークスペースの期間を retention_days として刻印され（Freeは90日、Proは365日、Businessは730日）、TTL 句がClickHouseのパーツマージ時に期限切れの行を削除します。

ip は切り詰めたアドレスを保存します。クリックイベントがリダイレクトプロセスを離れる前に、エッジがIPv4アドレスの最後のオクテットをゼロにし（203.0.113.42 ではなく 203.0.113.0）、IPv6アドレスは先頭48ビットだけを残します。ジオルックアップは切り詰めの前にエッジで行われるため、国、地域、市区町村、おおよその座標は、完全なユーザーエージェントとともにクリックと一緒に保存されます。切り詰めたIPに市区町村とユーザーエージェントが加わったものは、引き続き個人データとして扱うべきです。完全なプライバシーアーキテクチャについてはsolutions/analyticsを参照してください。

インジェストパス

水平フロー図：エッジのリダイレクトサービスがクリックイベントをパーティション分割されたRedpandaトピックに送信し、click-ingester Goサービスが最大1,000行のチャンクでバッチ処理し、ClickHouseにINSERTする

エッジサービスのリダイレクトハンドラーは火薬砲撃型です。クリックイベントをRedpandaトピック（本番では clicks）に追加して302を発行します。ClickHouseからの確認を待ちません。その時点でRedpandaが利用できない場合、クリックはドロップされます--リダイレクトはドロップされません。この優先順位は意図的なものです。見逃したクリックは回復可能（アクセスログから近似カウントを再構成できます）。失敗したリダイレクトはユーザーに見えます。

Redpandaコンシューマーグループ--2026年5月12日アクセス--はエッジとインジェスターの間のデリバリー保証を提供します。click-ingester サービスは clicks トピックに対してコンシューマーグループとして動作します。本番で動くインジェスターは現在1つで、コンシューマーグループは、2つ目のインスタンスを追加したときに、そのインスタンスがパーティションを引き継ぎ、グループのハートビートタイムアウト内にフェイルオーバーできるようにするものです。

インジェスターはイベントをメモリに蓄積し、バッチでフラッシュします：1,000行または5秒、どちらか早い方。これは重要です。ClickHouseのMergeTreeエンジンはバルクインサートに最適化されています--各インサートを新しいデータパーツとして書き込み、小さなインサートが多いと小さなパーツが多くなり、積極的なバックグラウンドマージがトリガーされ、マージ負荷の間クエリが遅くなります。ClickHouseのドキュメントでは、パーツ数を管理可能に保つために少なくとも1,000行のインサートを推奨しています。私たちは1,000行/5秒でフラッシュし、通常の操作でパーツ数の問題は見ていません。

バックプレッシャーはRedpandaを通じて伝播します。インジェスターはバッチがClickHouseに書き込まれた後にのみレコードのオフセットをコミットするため、ClickHouseが遅いか再起動している場合、失敗したバッチはリトライされ、コンシューマーラグが増加します。これがデータの鮮度問題の先行指標です。インジェスター自体が再起動した場合、コミットされていないレコードは、トピックの保持ウィンドウの範囲内でRedpandaから再度読み込まれます。

アーキテクチャの詳細は/docs/guides/clickhouse-exportで説明されています。

PostgresとClickHouseの分割

一つのサービスに二つのデータベースは正当化が必要なコストです。ここでの正当化は、二つのワークロードがスケールで本当に非互換であり、書き込み時に同期調整が必要な方法でデータが重ならないことです。

Postgresはリンクメタデータを所有します：links、workspaces、users、billing、rules テーブル。これらは更新可能（リンクはアーカイブされ、宛先URLが変更され、請求レコードが更新される）、トランザクション型（リンクの作成とスラッグの生成はアトミックでなければならない）、書き込みに対して読み取りが多い。B-treeインデックスとMVCCがまさに欲しいものです。

ClickHouseはクリックイベントを所有します：click_events テーブル。これらは追記専用（クリックは一度書き込まれると更新されない）、書き込みが多く、カラム型ストレージがより得意な集計パターンでクエリされます。書き込み時にクロスデータベースの結合はありません--インジェスターはクリックイベントを非正規化された数値フィールドとして workspace_id と link_id を含めて挿入します。クエリ時には、アナリティクスAPIはPostgresからリンクメタデータを、ClickHouseからクリック数を別々に検索し、アプリケーションコードで結合します。これは1回の追加ネットワークラウンドトリップです。分析クエリの負荷をPostgresに置くよりもずっと良いトレードオフです。

Citusの分散Postgresドキュメントは代替案を説明しています：時系列分析ワークロードを処理するように設計された分散Postgresエクステンション。私たちはこの経路を評価し、純粋な分析ワークロードにおけるClickHouseのクエリパフォーマンスと圧縮率が二つのデータベースの運用オーバーヘッドを正当化するほど十分に先んじていると結論付けました。ワークロードが混在している--ある程度分析的で、多くの行レベルの変更がある--場合は、Citusのアプローチがより理にかなっています。

毎時間実行するクエリパターン

ダッシュボードは各ワークスペースで約60秒サイクルで更新されます。三つの重いクエリがあります：

過去24時間のリンクごとのクリックロールアップ。 すべてのリンクリストの上部にスパークラインが表示されます。これはワークスペースの過去24時間に対する GROUP BY link_id, toHour(timestamp) です。

キャンペーンの国別内訳。 キャンペーンのパフォーマンスを確認しているマーケティングチームは、日付範囲でキャンペーンのリンクに絞り込んだ GROUP BY country_code, COUNT(*) を求めます。

ワークスペースの上位100リンクのデバイス分割。 アナリティクスタブはデバイス/OS内訳を表示します。これは過去30日間のワークスペースの最高トラフィックリンクの GROUP BY device, browser, COUNT(*) です。

三つのパターンはすべて ORDER BY (workspace_id, link_id, timestamp) のクラスタリングでうまく処理されます。ワークスペースフィルターがすぐにデータの適切なスライスにスキャンを絞り込みます。link_id と timestamp カラムが最初の二つのパターンをさらに絞り込みます。デバイス分割クエリはより多くのカラムをスキャンしますが、依然としてワークスペースレベルのクラスタリングの恩恵を受けます。

過去24時間のクリック数上位100リンクの代表的なクエリ：

SELECT
    link_id,
    count() AS clicks_24h
FROM click_events
WHERE
    workspace_id = 123
    AND timestamp >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

このクエリが読むのは、日付範囲のパーティションと、クエリが名指ししたカラムだけなので、コストはテーブル全体ではなく、求めたスライスの大きさに応じて増えます。同等のPostgresクエリは行をたどる必要があり、上の1.4秒はそこから来ています。

これらのパターンにClickHouseのマテリアライズドビューを使用していません。ORDER BYのクラスタリングとパーティションプルーニングで十分であり、マテリアライズドビューはスキーマ移行の複雑さを増やします。より高いイベントボリュームでクエリレイテンシが問題になった場合、次のレバーはマテリアライズドビューです--追加インデックスではありません。

想定している障害モード

本番ではClickHouseサーバー1台、Redpandaブローカー1台、インジェスター1つを単一のホストで動かしているため、書き留める価値のある障害モードは、この構成に今ある問題と、計画しているスケールアウトに伴って生じる問題です。

ClickHouseの再起動。 サーバーが1台なので、ClickHouseの再起動やアップグレードの間、インサートが止まります。インジェスターは失敗したバッチをメモリに保持してリトライし、挿入に成功した後にのみRedpandaのオフセットをコミットするため、クリックは消えずに待機します。目に見える影響は、バックログが解消するまでライブダッシュボードのデータに空白が生じることです。把握しているギャップが1つあります。ClickHouseが停止している間、メモリ内のバッファには上限がなく、これはバックログに入っています。ReplicatedMergeTree とClickHouse Keeperによるレプリケーションがこの停止に対する計画上の解決策ですが、それには独自の問題も伴います。Keeperのリーダー選出中は、レプリケートされたテーブルへのインサートもしばらく止まるため、Keeperノードは別々のホストに置く必要があります。

インジェスターのパーティションバランス。 現在は1つのインジェスターが clicks トピックのすべてのパーティションを所有しています。2つ目のインスタンスを追加すると、コンシューマーグループの割り当て戦略がパーティションをどれだけ均等に分けるかを決め、不均等な分割は、一方のインスタンスがバッチ処理に時間をかけ、もう一方が遊ぶ形で現れます。注視すべきは、グループ全体の合計だけでなく、インスタンスごとのコンシューマーラグです。

大きなテーブルでのALTER TABLEは非同期です。 click_events には何度かカラムを追加してきました（region、city、lat と lng、visitor_hash、スキャナーのフラグ）。ClickHouseの ALTER TABLE ... ADD COLUMN はすぐに応答し、既存のパーツを書き換えません。古いパーツの行は、マージで実体化されるまで、そのカラムのデフォルト値（空文字列またはゼロ）を返します。何も壊れませんが、空の city を「市区町村なし」として扱うクエリは、古い行を誤って数えてしまいます。教訓：大きなClickHouseテーブルへのALTER TABLEは最終的に一致するものとして扱い、実際の値と区別できるデフォルトを選んでください。

ClickHouseでやらないこと

会話に出てくるが、意図的に避けてきた二つのことがあります。

Postgresテーブルへのイベントごとのジョイン。 一部の分析クエリは自然にクリックイベントをリンクメタデータと結合したがります--「campaign-q2とタグ付けされたリンクのすべてのクリックを宛先URLと共に表示する」など。これをフェデレーションクエリとして行うことは可能ですが（ClickHouseのJDBC テーブル関数経由でClickHouseの click_events をPostgresの links に結合）、遅いです。代わりに、関連フィールドをインジェスト時にクリックイベントに非正規化するか（訪問者が実際に送られたURLである destination など）、またはアプリケーションコードでジョインを解決します：二つの別々のクエリ、アナリティクスAPIレイヤーでマージ。これにより両方のデータベースがパフォーマンスの範囲内で動作します。

行ごとの更新。 ClickHouseは行レベルの変更に対して ALTER TABLE UPDATE をサポートしていますが、これはエンジンの前提に反する非同期でリソース集約的な操作です。事後にクリックイベントを更新する必要があることに気づいた場合--例えば、ボットクリックとして遡及的にマークする--アーキテクチャが誤っています。私たちはそれらのフラグをインサートの前に決めています。エッジが、イベントを送出する前に、不審なクリックを採点し、リンクスキャナーと匿名化ネットワークを分類するため、is_suspicious、is_scanner、anonymizer_type は行と一緒に書き込まれます。もしスコアを後から計算する必要が生じた場合は、クエリ時に結合する別のテーブルを使うパターンになります。読み取りは安価で、遡及的な更新は高コストで稀であるべきです。

エッジリダイレクトがRedpandaとどのようにやり取りするか、クリックの重複排除がどのように機能するか、click-ingesterサービスがサービストポロジーにどのように収まるかを含む、より広いインフラの全体像については、/docs/guides/clickhouse-exportを参照してください。エッジ側の話（Redpandaへの「クリックして忘れる」パブリッシュ、レイテンシバジェット）はリダイレクトP95の記事にあります。データが下流で可能にすること--地域内訳、デバイス分割、UTMアトリビューションダッシュボード--については、solutions/analyticsページが製品サーフェスをカバーしています。SDKが公開するクエリパターンに関心を持つAPIに対してリンクキャンペーンを構築している場合は、solutions/developersが関連エントリポイントです。

Elidoをリンクインフラとして評価しているチームにとって、ここでのアーキテクチャの選択--クリックにはClickHouse、それ以外にはPostgres--は、サンプリングなしのアナリティクスを提供できる理由でもあります。スマートリンクの解説記事は、これらのクリックイベントを生成するルーティング決定がエッジでどのように機能するかをカバーしています。

Marius VoßはElidoのDevRelとエッジインフラ担当です。click-ingesterパイプラインの設計を支援し、ClickHouseのマージログを読むのに、思った以上の時間を費やしてきました。

Elidoを試す

URLを貼り付けて短縮リンクを取得

登録不要。リンクは30日間有効。永久に保存するには登録してください。

登録不要。無料登録すると月 50 件のリンクを作成できます。