PostgreSQL分析ワークロードをClickHouseにオフロードする理由

Why you should offload your PostgreSQL analytical workloads to ClickHouseの翻訳です。

2023年1月25日

なぜPostgreSQLの分析ワークロードをClickHouseにオフロードする必要があるのか？

この投稿では、ClickHouseとPostgreSQLがYouTube動画の分析で対決しています。その結果をご覧ください。

多くの企業が、あらゆるプロジェクトに汎用データベースを使用しています。通常、すぐに稼働させるには、それが最も簡単な方法です。そのデータベースですでに多くの専門知識を持っているなら、なぜ他のものを使うのでしょうか？

答えはパフォーマンスだ。

ジェネラリスト型RDBMは、何にでも対応できるように作られている。しかし今日では、特定のワークロードやユースケースのために作られたデータベース技術がますます増えている。これらのデータベースは、ある特定のこと*を極めて得意とするようにゼロから設計されている。そして、クエリーパフォーマンスに関しては、一般的なRDBMに勝ち目はない。

このことを、私たちの新しいAiven for ClickHouseデータベースで実証しようと思います。標準的なベンチマークツールだけでなく、企業が興味を持ちそうな実際の質問に答えられるような、実世界のデータを使ってテストドライブしてみます。

データセット

私は面白い（しかも無料の）データセットを探し回った。

*これはPostgresやClickHouseの最適化について深く掘り下げたものではありません。パーティショニング、セカンダリーインデックス、あるいはあらゆる種類のテーブルやデータベースの設定を使用してパフォーマンスを向上させることは可能です。これはベースラインを提供することを目的としています。

私が見つけたのは、アーカイブ・チーム（archive.org）がYouTubeからかき集めたデータの宝庫で、2021年12月時点の45億本の動画のメタデータが掲載されている。このデータセットが存在する理由は、YouTubeが動画の「嫌い」の数を表示しないと発表したからだ。つまり、このデータセットが、YouTubeが共有しなくなったこの情報を見ることができる最後のポイントなのだ。

このデータセットは、インターネット・アーカイブからダウンロードできる。

私自身の正気を保つため、そして時間とスペースを節約するために、データを読み込む際にビデオの説明とメタデータ・フィールドを削除した。どちらも特殊文字や改行が多く、データウェアハウスに保存するには正直効率が悪い。OpenSearchのようなものがより良いホームになるだろう。

質問

次のステップは、私が探究したい質問を思いつくことだった。私が選んだのはこれだ：

1.アップロードされた動画とは対照的に、人々はライブ・コンテンツにどう反応するのか？ライブ・コンテンツは必然的に洗練されていないため、人々は録画済みビデオに比べてそれを好む（あるいは嫌う）割合が高いのか？
2.視聴数と登録者数の比率が高いのはどのチャンネルか？これらは一般的に、非常に人気があるが、視聴者を購読者に変換していないいくつかの動画によって駆動されていますか？
3.アップロード者の通常の視聴回数に対して、最もバイラルになった動画は何か？
4.字幕の使用は時間の経過とともにどのように変化したか？自動化によって、より多くの動画で字幕が利用できるようになったのか？
5.コメントをオフにすることは、「いいね！」と「嫌い」の比率にどのような影響を与えますか？
6.最も視聴された/好感を持たれた非掲載ビデオは何ですか？

これらの質問をもとに、私は調査を開始した。

データを探る

同じ料金の2つのプランを選んだ：

• Aiven for ClickHouse - Business-16
• Aiven for Postgres - Business-32

両方に余分なディスク容量を与えました。データをロードして最初に気づいたことは、ClickHouseの方がディスク圧縮に優れているということです。ClickHouseのテーブルはPostgresのテーブルの約3分の1のサイズでした。

クリックハウス｜ポストグレス
| --------------------- | ------------- | ------------- |
| プラン｜ビジネス-16｜ビジネス-32
| CPU｜2｜8｜ラム｜16｜32
| ラム｜16｜32
| ノード数｜2｜2｜2
| ディスク使用量（GB）｜1150｜1100｜（GB
| ディスク使用量（GB）｜362｜956｜クリップボードにコピー

以下は私が使用したテーブルの定義である。

クリックハウス

``CREATE TABLE default.videos
(
 id String、
 fetch_date String、
 upload_date String、
 title String、
 uploader_id String、
 uploader String、
 uploader_sub_count Int32、
 is_age_limit Bool、
 view_count UInt64、
 like_count UInt64、
 dislike_count UInt64、
 is_crawlable Bool、
 is_live_content Bool、
 has_subtitles Bool、
 is_ads_enabled Bool、
 is_comments Bool
) ENGINE = MergeTree()
ORDER BY (uploader_id)`Copy to clipboard

Postgres

CREATE TABLE videos
(
 id TEXT、
 fetch_date TEXT、
 upload_date TEXT、
 title TEXT、
 uploader_id TEXT、
 uploader TEXT、
 uploader_sub_count BIGINT、
 is_age_limit Boolean、
 view_count BIGINT、
 like_count BIGINT、
 dislike_count BIGINT
 is_crawlable Boolean、
 is_live_content Boolean、
 has_subtitles Boolean、
 is_ads_enabled Boolean、
 is_comments_enabled Boolean、
 PRIMARY KEY (id)
)`クリップボードにコピー

これらのテーブルはどちらも基本的なもので、セカンダリインデックスを追加したり、いくつかの設定を変更することで、クエリのパフォーマンスを向上させることができます。また、より少ないスペースでデータを圧縮するように変更することもできる。しかし、まだ調査段階です。どのようなクエリを実行するのかが分かってから、最適化に集中することができます。

ライブコンテンツとアップロードコンテンツ

定義上、ライブ・コンテンツは、アップロードされたビデオに比べ、生々しく、洗練されていない。視聴者はそれをどう感じるだろうか？インフルエンサーやコンテンツクリエイター文化の専門家の多くは、信憑性の価値に重きを置いている。ライブコンテンツはより本物らしく感じられるだろうか？他の視聴者やクリエイターとチャットすることで、視聴者が動画（やコミュニティ）に参加していると感じることができるライブパフォーマンスのインタラクティブ性についてはどうだろうか。

クリックハウスのクエリー

SELECT
 is_live_content、
 AVG(
 COALESCE(like_count,0) / COALESCE(view_count,1)
 ) as LIKE_AVG、
 STDDEV_POP(
 COALESCE(like_count,0) / COALESCE(view_count,1)
 ) as LIKE_STD、
 AVG(
 COALESCE(dislike_count,0) / COALESCE(view_count,1)
 ) を DISLIKE_AVG とする、
 STDDEV_POP(
 COALESCE(dislike_count,0) / COALESCE(view_count,1)
 ) as DISLIKE_STD
FROM 動画
WHERE view_count > 0
GROUP BY is_live_content;`Copy to clipboard

Postgresクエリ

SELECT
 is_live_content、
 AVG(
 COALESCE(like_count,0) / COALESCE(view_count,1)
 ) as LIKE_AVG、
 STDDEV_POP(
 COALESCE(like_count,0) / COALESCE(view_count,1)
 ) as LIKE_STD、
 AVG(
 COALESCE(dislike_count,0) / COALESCE(view_count,1)
 ) を DISLIKE_AVG とする、
 STDDEV_POP(
 COALESCE(dislike_count,0) / COALESCE(view_count,1)
 ) as DISLIKE_STD
FROM 動画
WHERE view_count > 0
GROUP BY is_live_content;`Copy to clipboard

ランタイム

クリックハウス｜ポストグレス｜% Diff
| ----------- | ----------- | ------ |
| 113秒｜918秒｜712%｜``クリップボードにコピー

結果

ライブか？| 平均いいね！数｜標準偏差｜平均嫌い/視聴数｜標準偏差
| -------- | -------------- | ------- | ----------------- | ------- |
| 偽｜0.0530｜0.3160｜0.0025｜0.0479
| 真｜0.0985｜0.4036｜0.0060｜0.0396｜``クリップボードへコピー

私たちのデータベースは、一般的にライブコンテンツが視聴者一人当たりにより多くの「いいね！」と「嫌い」を獲得することに同意しており、どちらの場合もほぼ2倍の割合である。奇妙なことに、「いいね！」の標準偏差はライブ・コンテンツの方が高く、「嫌い」の標準偏差は低い。

再生回数は多いが購読者は少ない

チャンネル登録者数とは対照的に、再生回数が非常に多いのはどんなチャンネルなのか知りたかった。そのようなチャンネルはどのような動画を作っているのだろうか？バイラルになったのは1本の動画なのか？それとも、上位に食い込むには再生回数の多い動画がいくつか必要なのだろうか？

クリックハウスのクエリー

SELECT
 uploader_id、
 SUM(view_count)/MAX(uploader_sub_count)
FROM videos
WHERE uploader_sub_count > 10
GROUP BY uploader_id
ORDER BY SUM(view_count)/MAX(uploader_sub_count) desc
LIMIT 5;`クリップボードにコピー

Postgresクエリ

SELECT
 uploader_id、
 SUM(view_count)/MAX(uploader_sub_count)
FROM videos
WHERE uploader_sub_count > 10
GROUP BY uploader_id
ORDER BY SUM(view_count)/MAX(uploader_sub_count) desc
LIMIT 5;`クリップボードにコピー

ランタイム

クリックハウス｜ポストグレス｜% Diff
| ----------- | ------------ | ------- |
| 193秒｜3756秒｜1846%｜``クリップボードにコピー

結果

uploader_id｜現在のチャンネル名｜再生回数：チャンネル登録者数｜チャンネル登録者数：チャンネル登録者数
| ------------------------ | -------------------- | -----------------------|
| UCZcSkQahCZvXg742oP7hhsw｜Ziyan Lee｜10,423,195 |
| UC3OqJzvmr4dJkoWxLuXMR1Q | デイ・ニュー | 8,427,802 |
| UCzXfnq1jnSKsbCYwCgci-vA｜Vive Games｜7,704,057｜｜Vive Games
| UCCP_JmGPLM319iSVURXhPQ｜ ゲーム面白い｜7,491,109｜7,491,109
| UCGwjz9YOQXoAXJW-2kNyD8g | Naga | 6,576,452 |``クリップボードにコピーする

ランク1、2、5が低俗なモバイルゲームの広告のようなもので、3と4は未掲載の動画しかない。

最低登録者数を1000人に増やし、リストアップされた動画のみをカウントするようにフィルターをかけることで、広告を除外し、より興味深い結果を得られるかどうか試してみよう：

uploader_id｜現在のチャンネル名｜再生回数：チャンネル登録者数｜再生回数：チャンネル登録者数｜再生回数：チャンネル登録者数｜再生回数：チャンネル登録者数
| ------------------------ | -------------------- | ---------------------- |
| UCRuLp80UV9FO3EFbQzF-D5A | SHADIAO VIDEOS | 673887 |

| UC21yzf1STldxuTtFV_y_qKg | (アカウント終了) | 622086 |｜ UC21yzf1STldxuTtFV_y_qKg
| UCSSmw60pH3kKOj2xUwYdOQ | UAC Google | 461971 | (終了したアカウント)
| UC5LCpCZy0tnDhzGGI-fHlkQ｜Eshare kwai｜415367｜``Copy to clipboard

うーん、やはりランク1、2、5は似ていて、ほとんどがLikeeからのリポストで、どのアカウントも非常に人気のある動画をいくつか持っている。ランク3のアカウントは終了しているので、特に報告することはない。ランク4はまたもやモバイルアプリの広告で、今回は自撮り写真を老化させたり、老けさせたりするアプリの広告だ。

デジタル広告はコンバージョン率が低い傾向があるので、このセクションが予想したようなバイラル動画ではなく広告で占められているのは驚くことではないかもしれない。

長年にわたる字幕

音声認識の進歩により、ビデオの字幕作成はかつてないほど簡単になりました。人々はこれを活用しているのだろうか？2009年末に自動字幕が追加されましたが、その頃に急増したのでしょうか？

クリックハウスクエリー

SELECT
 Substring(upload_date,1,4) as upload_year、
 AVG(has_subtitles)
FROM videos
GROUP BY Substring(upload_date,1,4)
ORDER BY Substring(upload_date,1,4) ASC;`クリップボードにコピー

Postgresクエリ

SELECT
 Substring(upload_date,1,4) as upload_year、
 AVG(CASE WHEN has_subtitles THEN 1 ELSE 0 END)
FROM videos
GROUP BY Substring(upload_date,1,4)
ORDER BY Substring(upload_date,1,4) ASC;`クリップボードにコピー

ランタイム

クリックハウス｜ポストグレス｜% Diff
| ----------- | ------------ | ------- |
| 121秒｜1021秒｜743%｜``クリップボードにコピー

結果

年｜字幕付き動画の割合
| ---- | -------------------- |
| 2005 | 9.4 |
| 2006 | 10.9 |
| 2007 | 11.4 |
| 2008 | 12.8 |
| 2009 | 14.8 |
| 2010 | 16.3 |
| 2011 | 16.4 |
| 2012 | 22.5 |
| 2013 | 30.4 |
| 2014 | 28.1 |
| 2015 | 31.1 |
| 2016 | 32.0 |
| 2017 | 32.1 |
| 2018 | 35.6 |
| 2019 | 39.8 |
| 2020 | 56.0 |
| 2021年｜58.5｜``クリップボードにコピーする

動画に字幕を利用する人は確かに増えている。最も急上昇したのは2020年で、好奇心から調べてみた。どうやらその頃、YouTubeは他人の動画に字幕をアップロードできるコミュニティ・キャプション機能を削除していたようだ。そのため、耳の不自由な視聴者のためにクリエイターが動画にキャプションを追加するキャンペーンが大成功を収めた。

コメントと物議を醸す動画

コメントは、複雑な恵みをもたらす。インターネットを支配するアルゴリズムにとっては重要なエンゲージメントの証であり、クリエイターにとっては貴重なフィードバック源となり得る。しかし、単純な「Dislike」よりもはるかにインパクトのある嫌がらせのベクトルにもなり得る。クリエイターは、物議を醸すような動画のコメントをオフにすることがあるのだろうか？

クリックハウス

セレクト
CEILING(log10(view_count)) as Views_OOM、
AVG(
 CASE
 WHEN is_comments = 1 THEN NULL
 ELSE
 CASE
 COALESCE(like_count,0) = COALESCE(dislike_count,0)
 THEN 0.5
 WHEN like_count IS NULL or like_count = 0
 THEN 0
 WHEN dislike_count IS NULL or dislike_count = 0
 THEN 1
 ELSE like_count / dislike_count
 END
 END) as comments_disabled_like_ratio、
AVG(
 CASE
 WHEN is_comments_enabled = 1 THEN
 CASE
 COALESCE(like_count,0) = COALESCE(dislike_count,0)
 THEN 0.5
 like_count IS NULL または like_count = 0
 THEN 0
 WHEN dislike_count IS NULL or dislike_count = 0
 THEN 1
 ELSE like_count / dislike_count
 END
 ELSE NULL
 
 END) as comments_enabled
FROM 動画
GROUP BY Views_OOM
ORDER BY Views_OOM;`クリップボードにコピー

Postgres クエリ

SELECT
CEILING(log10(view_count + 1)) as Views_OOM、
AVG(
 CASE
 WHEN is_comments コメント有効 THEN NULL
 ELSE
 CASE
 WHEN COALESCE(like_count,0) = COALESCE(dislike_count,0)
 THEN 0.5
 WHEN like_count IS NULL or like_count = 0
 THEN 0
 WHEN dislike_count IS NULL or dislike_count = 0
 THEN 1
 ELSE like_count / dislike_count
 END
 END) as comments_disabled_like_ratio、
AVG(
 CASE
 WHEN is_comments_enabled THEN
 CASE
 COALESCE(like_count,0) = COALESCE(dislike_count,0)
 THEN 0.5
 like_count IS NULL または like_count = 0
 THEN 0
 WHEN dislike_count IS NULL or dislike_count = 0
 THEN 1
 ELSE like_count / dislike_count
 END
 ELSE NULL
 END) as comments_enabled
FROM 動画
GROUP BY 再生回数
ORDER BY Views_OOM；
-- PGはLog(0)を好まず、ブール値を1/0ではなくT/Fとして保存するため、少し変更する``Copy to clipboard

ランタイム

クリックハウス｜ポストグレス｜% Diff
| ----------- | ------------ | ------- |
| 216秒｜1106秒｜412%｜``クリップボードにコピー

結果

再生回数｜好き嫌い｜コメント無効｜コメント有効
| | コメント無効 | コメント有効
| --------------------- | ----------------- | ---------------- |

| 10未満｜0.63｜0.66


| 千｜14.01｜22.37｜｜千
| 数万｜22.88｜40.92｜千
| 数十万｜19.01｜37.63｜百万｜12.05｜30.92
| 百万｜12.05｜30.58
| 数千万｜6.66｜23.24｜百万｜3.05｜30.58
| 億｜3.08｜20.65
| 億｜1.77｜19.55｜``クリップボードにコピー

コメントが無効になっている動画は、平均して「いいね！」率がかなり悪く、再生回数が1万回を超える動画ではさらに顕著です。このことから、不人気な動画、特に再生回数が多い動画では、コメントを無効にする人が多いことがわかる。

このデータでもう一つ興味深いのは、コメントありの動画でもコメントなしの動画でも、10万～100万ビューの範囲で「いいね！」と「嫌い」の比率が急上昇していることだ。これは、この範囲では、YouTubeのアルゴリズムが、誰がその動画に「いいね！」を押すかをうまく予測していることを示唆している。しかし、再生回数が増えれば増えるほど、動画の内容を嫌う人がその動画に触れる確率は高まる。

そこで、私は次のような質問をした。コメントが無効になっている場合、人々は動画について自分の感情を表現するために、「好き」と「嫌い」のどちらを選ぶ可能性が高いのだろうか？

クリックハウスのクエリー

セレクト
CEILING(log10(view_count)) as Views_OOM、
AVG(
 CASE
 WHEN is_comments = 1 THEN NULL
 ELSE like_count + dislike_count
 END) as comments_disabled_interactions、
AVG(
 CASE
 WHEN is_comments_enabled =1
 THEN like_count + dislike_count
 ELSE NULL
 END) as comments_enabled
FROM 動画
GROUP BY 再生回数
ORDER BY Views_OOM;`クリップボードにコピー

Postgres クエリ

SELECT
CEILING(log10(view_count + 1)) as Views_OOM、
AVG(
 CASE
 WHEN is_comments コメント有効 THEN NULL
 ELSE like_count + dislike_count
 END) as comments_disabled_interactions、
AVG(
 CASE
 WHEN is_comments_enabled
 THEN like_count + dislike_count
 ELSE NULL
 END) as comments_enabled
FROM 動画
GROUP BY 再生回数
ORDER BY Views_OOM；
-- PGはLog(0)を好まず、ブール値を1/0ではなくT/Fとして保存するため、少し変更を加える``Copy to clipboard

ランタイム

クリックハウス｜ポストグレス｜% Diff
| ----------- | ------------ | ------- |
| 165秒｜992秒｜501%｜``クリップボードにコピー

結果

再生回数｜平均「いいね！」＋「嫌い
| | コメント無効 | コメント有効
| --------------------- | ----------------- | ---------------- |
| 0 | 0.10 | 0.10 |
| 10未満｜0.45｜0.56

| 百｜10.56｜13.74
| 千｜55.86｜80.88｜｜｜｜｜｜｜｜｜千
| 数万｜414.67｜699.82｜千
| 数十万｜3,193.55｜5,903.21｜百万｜22,368.82｜百万円
| 百万円｜22,368.78｜42,363.47｜百万円
| 数千万｜138,028.31｜256,495.88｜百万単位
| 百万｜967,767.47｜1,520,044.48｜億｜8,072.78｜42,363.47｜千万
| 億｜8,072,880.48｜9,185,313.54｜``クリップボードにコピー

いや、確かに違う。コメントが有効な動画は、同じような視聴回数でコメントが無効な動画よりも、平均してより多くのインタラクション（「いいね！」と「嫌い」）を持っています。

非掲載動画

YouTubeは公式には8億以上の動画があると言っているが、我々のデータセットでは40億以上のレコードがある。これらはすべて未掲載なのでしょうか？私たちの分析は、主にリストアップされた動画に焦点を当てています。では、未掲載で最も視聴されている動画は何でしょうか？

クリックハウスのクエリー

セレクト
 タイトルを選択します、
 アップローダー
 ビュー数
FROM videos
WHERE is_crawlable = 0
ORDER BY view_count desc
LIMIT 2;`クリップボードにコピー

Postgresクエリ

セレクト
 タイトルを選択します、
 アップローダー
 ビュー数
FROM 動画
WHERE is_crawlable
ORDER BY view_count desc
LIMIT 2;`クリップボードにコピー

ランタイム

クリックハウス｜ポストグレス｜% Diff
| ----------- | ------------ | ------- |
| 301秒｜1006秒｜234%｜``クリップボードにコピー

結果

| 009 211 Baton Recut 1920x1080 15s | Instagram | 2,048,634,523 |
| 009 214 HeartShapedWorld Acq EN UAC 1920x1080 15s V1 FB | Instagram | 1,554,823,463 |``Copy to clipboard

つまらない！再生回数20億回のインスタグラム広告だ。再生回数とサブの比率が高い動画を分析した結果、これは予想通りだったのかもしれない。バイラル動画と広告を分ける方法があるはずだ！掘り下げていくしかない。

再生回数トップ1

ClickHouseには、いくつかの統計機能も組み込まれています。私は再びビューに興味を持っているように、ビューがビデオ間でどのように分布しているかの感覚を得ることができます.見る前に、上位1％の動画に入るために必要な再生回数を推測してみてください。

クリックハウスクエリー

選択
 quantiles(0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9, 0.99)(view_count)
FROM videos;`クリップボードにコピー

そう、postgresは同様の機能を実行できるが、この特定の例では複数のステップが必要で、桁違いのリソースが必要になる。ClickHouseの分位関数では、サンプリングを使って近似値を計算します。

ランタイム

42秒（クリックハウス

結果

パーセンタイル
| ---------- | ---------- |
| 10位
| 20位
| 30位｜36本｜40位｜64本
| 第40位｜64｜回

| 第60位｜220人｜第70位｜433人
| 第70位｜433｜1,093
| 第80位｜1,093人｜第90位｜4,256人
| 第90位｜4,256人｜第99位｜179,308人
| 第99位｜179,308｜クリップボードにコピーする

このように数字を並べると、かなりクールだ。プラットフォーム上の動画の数を考えると、上位1％がこれほど高いとは思わなかった。

結論

平均して、Aiven for ClickHouseデータベースはAiven for PostgreSQLよりも5～10倍パフォーマンスが優れていました。そして、ある特定のケース、質問#2では、パフォーマンスは20倍優れていました！Postgresは素晴らしい汎用データベースですが、このような大規模なデータセット、特にカラム集計ではClickHouseに追いつくのに苦労します。ClickHouseはまさにその目的のために設計されているのです。更新と削除の多いパフォーマンステストを行う場合、ClickHouseは命がけで戦うことになるでしょう。

どんなデータベースにも使用目的があります。開発の初期段階から、開発者はある種のトレードオフを行っており、それに逆らうのではなく、その流れに乗りたいものです。

私たちの小さな実験から1つだけ教訓を得るとすれば、それは次のようなことだ：**ユースケースに適したツールを選択することで、開発にも実行にも時間を節約することができる。

Aivenと私たちのサービスに関する最新ニュースや、オープンソースに関する様々な情報を入手するには、月刊ニュースレターを購読してください！Aivenに関する日々のニュースは、LinkedInとTwitterのフィードでご覧いただけます。

サービスのアップデート情報を知りたい方は、変更履歴をご覧ください。