本番環境でのClickHouse運用：20億行におけるインジェスト、マージ、コスト

tracio.aiの分析層の構築を始めたとき、私たちには特定のワークロードを処理できるデータベースが必要でした。毎秒50,000件のデバイス識別イベントをインジェストし、20億行以上を保存し、分析クエリに1秒未満で回答するというものです。PostgreSQL（この規模の集計には遅すぎる）、Elasticsearch（時系列分析には高価すぎる）、そしてClickHouseを評価しました。ClickHouseが圧倒的に勝りました。

なぜClickHouseか

ClickHouseは、リアルタイム分析のために設計されたカラム指向のOLAPデータベースです。私たちのワークロードにおける主要な利点は、各クエリに必要なカラムだけを読み取ることです。不正分析担当者が「過去7日間の国別の不正率を見せて」と尋ねると、ClickHouseはcountry、timestamp、risk_scoreのカラムだけを読み取り——イベントテーブルの他の40以上のカラムを無視します。20億行のテーブルでは、これによりI/Oが95%削減されます。

ClickHouseはまた、データを非常によく圧縮します。20億行のeventsテーブルはディスク上で340 GBを占めます——圧縮後は1行あたり約170バイト、非圧縮の1行あたり1.2 KBに対してです。7対1の圧縮率は、より多くのデータがメモリに収まることを意味し、それが直接、より高速なクエリにつながります。

スキーマ設計

主テーブルは、識別イベント1件あたり1行を保存します。

このテーブルはMergeTreeエンジンを使い、(workspace_id, toDate(timestamp), visitor_hash)で並べられています。この並び順は極めて重要です——ワークスペースと日付範囲でフィルタされたクエリが、最小限のデータを読み取ることを意味します。visitor_hashカラムは、セカンダリインデックスなしで訪問者IDによる高速な検索を可能にします。

country、device_type、browser_family、os_familyにはLowCardinality(String)を選びました。これらのカラムは10,000未満の異なる値しか持たないためです。ClickHouseはLowCardinalityカラムを辞書エンコードされた整数として保存し、プレーンな文字列と比べて保存容量を80%削減し、GROUP BY操作を高速化します。

シャーディング戦略

eventsテーブルは、workspace_idのハッシュを使って6ノードにわたってシャーディングします。これにより、特定の顧客のすべてのイベントが同じシャードに配置され、ほとんどのクエリ（workspace_idでフィルタされたもの）が単一のシャードに当たることになります。クロスシャードのクエリは、内部分析のためだけに必要です。

各シャードは、高可用性のために2つのレプリカを持ちます。レプリケーションには、ZooKeeperによる協調を伴うClickHouse組み込みのReplicatedMergeTreeエンジンを使います。フェイルオーバーは自動です——シャードがダウンすると、クエリはクライアント側の変更なしにレプリカへルーティングされます。

インジェストパイプライン

イベントは、私たちのKafkaトピックから、挿入をバッチ化するカスタムGoサービスを通じてClickHouseに流れ込みます。500msごとに10,000行のバッチで挿入します——これは、インジェストのレイテンシ（サブ秒）と挿入の効率（ClickHouseは大きなバッチで最も高い性能を発揮する）のバランスを取るものです。

このインジェストサービスは、バックプレッシャーを丁寧に扱います。ClickHouseが挿入の受け入れに時間がかかる場合（マージ中や重いクエリ負荷時）、サービスは最大100万件のイベントをメモリにバッファし、Kafkaコンシューマーにバックプレッシャーをかけます。18か月の本番運用で、イベントを一度も失っていません。

クエリ最適化

マテリアライズドビュー

一般的なダッシュボードクエリには、データを事前集計するマテリアライズドビューを使います。たとえば不正率のダッシュボードは、fraud_detectedのカウントをワークスペース、国、時間ごとに集計するマテリアライズドビューから読み取ります。このビューは、このクエリでスキャンされるデータを20億行から500万行に削減します。

プロジェクションの並び順

ClickHouseのプロジェクションを使うと、データを複製せずにテーブルの代替ソート順を定義できます。訪問者タイムラインのクエリのために、(workspace_id, visitor_hash, timestamp)で並べたプロジェクションを追加しました。プロジェクションがなければ、これらのクエリは日付範囲全体をスキャンしていました。あることで、対象の訪問者を含むブロックだけを読み取ります。

近似関数

正確なカウントが重要でないダッシュボードクエリには、ClickHouseの近似関数を使います。異なる値のカウントにはuniqCombined（誤差2%、uniqExactより10倍高速）、パーセンタイル計算にはquantileTDigestです。不正分析ダッシュボードは近似関数のみを使っており、これによりすべてのダッシュボードクエリを200ms未満に保っています。

パフォーマンスの数値

私たちの20億行の本番クラスタにおける代表的なクエリのベンチマークは以下のとおりです。

国別の不正率（過去7日間）：120ms。訪問者タイムライン（50イベント）：8ms。1日あたりのユニーク訪問者数（過去30日間）：340ms。リスクスコアの分布（過去24時間）：95ms。イベント数による上位100デバイス（過去30日間）：210ms。

これらの数値には、アプリケーションサーバーからClickHouseクラスタへのネットワーク往復が含まれます。純粋なクエリ実行時間は、通常30〜50%低くなります。

運用から得た教訓

教訓1：マージ遅延を監視する

ClickHouseのMergeTreeエンジンは、小さなデータパーツを大きなものへと継続的にマージします。マージが遅れると（高い挿入レートやディスクI/Oの競合のため）、クエリはより多くのパーツをスキャンしなければならず、クエリ性能が低下します。私たちはパーティションごとのパーツ数を監視し、300を超えたときにアラートを出します。

教訓2：大規模なALTER TABLE操作を避ける

ClickHouseで20億行のテーブルにカラムを追加するのは瞬時です（メタデータのみのため）。しかしカラムの型を変更するには、すべてのデータパーツの書き換えが必要で——私たちのクラスタでは6時間を要したプロセスでした。今ではスキーマを追記のみとして扱っています。新しいカラムは自由に追加しますが、型変更はマイグレーションテーブルを経由させます。

教訓3：TTLは慎重に

ClickHouseはTTLによる自動データ有効期限をサポートします。私たちはeventsテーブルに90日のTTLを設定しました。落とし穴は、TTL削除がマージ中に発生することです。つまり、削除対象のデータはTTLの有効期限が切れた後、数時間から数日残ることがあります。コンプライアンス上重要な削除には、スケジュールに従って明示的なALTER TABLE DELETEクエリを実行します。

コスト

私たちの6ノードのClickHouseクラスタ（各ノード：32 vCPU、128 GB RAM、2 TB NVMe）は、ベアメタルホスティングで月額約8,400ドルです。これは90日間の保持で20億行を保存し、毎秒5万件の挿入に加えて200件の同時ダッシュボードクエリを処理します。保存イベント1件あたりのコストは0.0000042ドルで——マネージド型クラウドデータベースでの同等の分析より桁違いに安価です。