クリックアナリティクスにClickHouseを使う理由（Postgresではなく）

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

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

TL;DR#

• クリックイベントは追記専用で、集計クエリ型です。カラム型ストレージは両方の点で有利です。
• Postgresの行ごとのイベントオーバーヘッド、インデックスのブロート、バキューム競合は共有テーブル上の約3,000万イベントを超えると複合的に悪化します。
• ORDER BY (workspace_id, link_id, created_at) を持つ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秒になりました。当時は十分でしたが、月1億件以上のイベントにスケールする経路でないことは明らかでした。

ClickHouseが適合する理由#

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

圧縮率が二番目の特性です。カラム型データは行データよりも大幅に圧縮されます。同じカラム内の繰り返し値--何千回も現れる同じ workspace_id、60種類ほどの国コードのセット、いくつかの os 値--がClickHouseのLZ4とZSTDコーデックでほぼ何もないほどに圧縮されます。クリックイベントはディスク上でrawサイズの約12〜15%に圧縮されます。Postgresの同じデータはデフォルトのtoast圧縮でrawサイズの35〜45%です。それはストレージの勝利だけでなく--ディスク上のデータが小さいほど、OSページキャッシュに多く収まり、クエリごとのディスク読み取りが減ります。

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

私たちは ORDER BY (workspace_id, link_id, created_at) を使用しています。ワークスペース abc123 の過去7日間のクリックのクエリは、workspace_id = 'abc123' が現れるデータパーツのみを読み、そのパーツ内で時間範囲のスライスのみを読み込みます。90日間のクリックデータでは、これは通常総データパーツの3〜8%に触れます。ClickHouseが ORDER BY カラムで維持するスパースプライマリインデックスにより、すべてのクエリパターンに対して密なB-treeを必要とせず、このターゲティングを効率的に行えます。ReplicatedMergeTreeはHAを追加します--ZooKeeper相当の調整（ClickHouse Keeper）を持つ二つのClickHouseノードで、パーツが書き込まれるときにレプリケートされます。

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

CREATE TABLE clicks
(
    workspace_id    LowCardinality(String),
    link_id         String,
    click_id        UUID,
    created_at      DateTime64(3, 'UTC'),
    country         LowCardinality(FixedString(2)),
    device          LowCardinality(String),
    os              LowCardinality(String),
    browser         LowCardinality(String),
    referrer_host   String,
    utm_source      LowCardinality(String),
    utm_medium      LowCardinality(String),
    utm_campaign    String,
    utm_term        String,
    utm_content     String,
    ip_truncated    String,
    user_agent_hash FixedString(64)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/clicks', '{replica}')
PARTITION BY toYYYYMM(created_at)
ORDER BY (workspace_id, link_id, created_at)
SETTINGS index_granularity = 8192;

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

workspace_id、country、device、os、browser、utm_source、utm_medium に LowCardinality(String) を使用--これらのカラムはカーディナリティが限られています（数百の異なるワークスペースID、250種類の国コード、いくつかのデバイスタイプ）。LowCardinality はディクショナリエンコーディングを有効にし、値を一意の文字列のディクショナリへの整数参照として保存します。これらのカラムのクエリ速度が大幅に向上し、圧縮もさらに改善されます。これはClickHouseのlow-cardinalityデータ型のドキュメントから学びました--経験則として、1万未満の異なる値を持つ文字列カラムは候補です。

PARTITION BY toYYYYMM(created_at) はカレンダー月ごとに別のデータパーティションを作成します。古いパーティションは単一の ALTER TABLE clicks DROP PARTITION '202501' ステートメントでドロップ（または低速ストレージへ移動）できます。これは即時で--フルテーブルスキャンなし。GDPRのデータ保持強制については、これは実用的に重要です。ワークスペースの過去のクリックを削除することは、分散した DELETE WHERE workspace_id = ? ではなく、月次パーティションごとの単一オペレーションです。

ip_truncated は最終オクテットをゼロにしたIPアドレス（203.0.113.42 ではなく 203.0.113.0）を保存します。完全なIPは保存されません。切り捨てられた形式はジオルックアップには十分です--これは保存前のインジェスト時に行います--そして当社のデータ最小化の実践と組み合わせると、GDPR第4条の定義の下で個人データを構成しません。完全なプライバシーアーキテクチャについてはsolutions/analyticsを参照してください。

インジェストパス#

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

Redpandaコンシューマーグループ--2026年5月12日アクセス--はエッジとインジェスターの間のデリバリー保証を提供します。click-ingester サービスは clicks.v1 トピックに対してコンシューマーグループとして動作します。パーティションは自動的に割り当てられます。インジェスターインスタンスが失敗すると、そのパーティションはグループのハートビートタイムアウト内に生き残っているインスタンスに再バランスされます。

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

バックプレッシャーはRedpandaを通じて伝播します。ClickHouseが遅いか再起動している場合、インジェスターのコンシューマーラグが増加し、私たちはこれをデータの鮮度問題の先行指標として監視しています。Redpandaの保持設定（14日間）により、ClickHouseをゼロから再構築する必要がある場合に意味のあるリプレイウィンドウが得られます。

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

PostgresとClickHouseの分割#

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

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

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

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

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

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

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

キャンペーンの国別内訳。 キャンペーンのパフォーマンスを確認しているマーケティングチームは、日付範囲で utm_campaign でフィルタリングした GROUP BY country, COUNT(*) を求めます。

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

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

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

SELECT
    link_id,
    count() AS clicks_24h
FROM clicks
WHERE
    workspace_id = 'ws_abc123'
    AND created_at >= now() - INTERVAL 1 DAY
GROUP BY link_id
ORDER BY clicks_24h DESC
LIMIT 100

90日間にわたる1,200万クリックイベントを持つワークスペースでは、このクエリは関連するパーティション内のデータパーツの数によって約45〜80msで返されます。同じクエリを試した最良のインデックス設定でも、同等サイズのPostgresテーブルでは2〜4秒かかります。

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

経験した障害モード#

本番インシデントが三件あり、記録する価値があります。

ClickHouse Keeperのブリップ。 ClickHouse KeeperはReplicatedMergeTreeのZooKeeper相当の調整レイヤーです。ある機会に、バックグラウンドマージ中のKeeperノードの再起動により、新しいリーダーが選出される間、約90秒間レプリケートされたテーブルがインサートの受け入れを停止しました。インジェスターは指数バックオフ（2s、4s、8s、上限60s）でリトライしました--これは context.DeadlineExceeded 処理を含む標準的なGoのリトライロジックです。停止ウィンドウ中に約1,800のクリックイベントがインジェスターのインメモリバッファにたまり、Keeperが回復すると正常にフラッシュされました。ユーザーに見える唯一の影響は、ライブダッシュボードデータに約2分間のギャップがあったことでした。修正はHetznerネットワーク上で別々の物理ホストと別々の電源回路にKeeperノードを分散することでした。

Redpandaのパーティション不均衡。 clicks.v1 トピックは12のパーティションを使用しています。2番目のインジェスターインスタンスを追加した後、Redpandaのデフォルトバランサーによるパーティション割り当てが、1つのインスタンスに9つ、もう1つに3つを割り当てました--コンシューマーあたりの総パーティション戦略を使用し、インスタンス数がたまたま3:1の不均衡を生み出しました。これはデータ損失のリスクではありませんでしたが、過剰に割り当てられたインスタンスが負荷の下で遅いバッチ処理を行い、クリック処理レイテンシを約800msに増加させていました。修正はRedpanda管理APIを使った手動パーティション再バランスと、ラウンドロビン割り当てを使用する rack-aware バランサーへの切り替えでした。

大きなテーブルでのALTER TABLEは非同期です。 1月に browser_version カラムを追加しました。ClickHouseの ALTER TABLE ... ADD COLUMN はすぐに応答し、バックグラウンドパーツマージを通じてスキーマ変更を非同期で適用します。alterの約20分後、新しいカラムを参照するクエリは未変換のパーツの行に対して NULL を返しました。何も壊れませんでした--カラムを追加しており、既存のものを変更しているわけではありませんでした--しかし browser_version IS NOT NULL を仮定するクエリは誤った結果を返していたでしょう。教訓：大きなClickHouseテーブルへのALTER TABLEを同期ではなく最終的に一致するものとして扱ってください。

ClickHouseでやらないこと#

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

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

行ごとの更新。 ClickHouseは行レベルの変更に対して ALTER TABLE UPDATE をサポートしていますが、これはエンジンの前提に反する非同期でリソース集約的な操作です。事後にクリックイベントを更新する必要があることに気づいた場合--例えば、ボットクリックとして遡及的にマークする--アーキテクチャが誤っています。正しいパターンは url-scanner サービスによって非同期に計算された click_id をクオリティスコアにマッピングする別の click_quality テーブルをClickHouseに保存し、クエリ時にJOINすることです。読み取りは安価で、遡及的な更新は高コストで稀であるべきです。

---

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

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

---

Marius VoßはElidoのDevRelとエッジインフラ担当です。click-ingesterパイプラインの設計を支援し、ClickHouse Keeperの選出タイミングのデバッグに多くの時間を費やしました（もっと少なくて済めばよかったと思っています）。