1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

お題は不問！Qiita Tech Festa 2026で記事投稿！
Qiita Tech Festa20262026年7月13日まで開催中！
@asahideTeam Coffee

4327万件のデータで検証：AlloyDBの列指向エンジンは同じSQLのまま本当に11倍速くなるのか？

1
Posted at

1. はじめに

手元に、世界中の携帯基地局の位置データ（cell_towers・約 4,327万件）があります。ちょうど Google Cloud で検証していたので、AlloyDB の 列指向エンジン（Columnar Engine） も試してみると面白いかな、と思ったのがきっかけです。

列指向エンジンは、公式ドキュメントでは「選んだ列を列指向フォーマットで別に持ち、プランナが自動でそれを使って分析クエリを速くする。スキーマもアプリも ETL も変えなくていい」と説明されています1。公式ブログには分析クエリで最大 100 倍という例も挙がっています2。これが実データでどれくらい速くなるのか、同じ SQL のまま、列指向エンジンを OFF / ON で切り替えて確かめてみました。

検証には、手元に無料（VM料金はかかります！）で立てられる AlloyDB Omni を使いました（列指向エンジンの実装はマネージドの AlloyDB と同じです。選んだ理由は 2 章で書きます）。

先に結論です。

  • 同じ cell_towers 4,327万件・同じ SQL を、列指向エンジン OFF / ON で比べた。radio（通信方式）を数える集計は約 11.6 倍速、緯度経度を丸めるグリッド集計でも約 1.38 倍速だった
  • どちらも同じインスタンス上で ON / OFF だけを切り替えた。データも SQL もセッション設定も同じなので、差は列指向エンジンの効果だけ
  • 差を生むのは読むデータ量。列指向は必要な列だけ読む（radio なら約 43 MB）。行指向は radio 1 列を数えるだけでも全 14 列・約 5.5 GB を読む
  • 列指向が使われた証拠も取れた。EXPLAIN に列指向スキャンのノードが出て、読むブロックが 687,244 から 35 へ大きく減った
  • 差の大きさはクエリ次第。必要な列が少なく I/O が支配的なほど大きく、行ごとの計算が重いと縮む

2. 検証環境

項目 内容
実行基盤 GCE VM e2-standard-8（8 vCPU / 32 GB）、asia-northeast1、pd-ssd 50 GB
AlloyDB AlloyDB Omni（google/alloydbomni:latest、PostgreSQL 17.7）
列指向エンジン google_columnar_engine.enabled=on、memory_size_in_mb=4096
データ cell_towers 43,276,150 行（OpenCelliD 由来の公開スナップショット）
クライアント VM 上の psql

マネージドの AlloyDB ではなく Omni を選んだ理由は、コストとセットアップの軽さです。列指向エンジンの効果を見るだけなら、VM 1 台に Docker で立てられる Omni がいちばん手数が少なく済みます。マネージドだと VPC の Private Services Access の設定などが要りますが、Omni はそれが不要で、片付けも VM を消すだけです。デメリットは、Omni がマネージドサービスそのものではない点です（自動バックアップ・HA・スケールといったマネージド機能は別）。ただ、列指向エンジンの実装は共通なので、今回の目的（同じ SQL がどれだけ速くなるか）には影響しません。

データの cell_towers は、OpenCelliD が公開している、世界中の基地局の位置（緯度・経度）と通信方式（radio 列）などが入った公開データです3。今回使ったのは約 4,327万件のスナップショットで、created / updated は 2017 年ごろの日時が入っています。14 列で、行ストアでの実サイズは約 5.5 GB でした。

3. やり方

3.1. データを入れて整合性を確認する

cell_towers を AlloyDB Omni に投入します。テーブルは 14 列で作りました。元データの created / updated は 2017-09-13 04:51:56 のような日時の文字列だったので、timestamp 型で受けています（今回の集計では時刻は使いません）。投入は約 1 分 55 秒でした。

CREATE TABLE cell_towers (
  radio          text,
  mcc            integer,
  net            integer,
  area           integer,
  cell           bigint,
  unit           integer,
  lon            double precision,
  lat            double precision,
  range_m        bigint,
  samples        bigint,
  changeable     smallint,
  created        timestamp,
  updated        timestamp,
  average_signal smallint
);

投入後、radio の内訳を数えて件数が想定どおりか確認しました。合計は 43,276,150 行です。

radio 件数
UMTS（3G） 20,686,487
LTE（4G） 12,101,148
GSM（2G） 9,931,304
CDMA 556,344
NR（5G） 867

3.2. 比べる集計は2種類

性格の違う集計を 2 つ用意しました。どちらも 4,327万件をフルスキャンします。

  1. radio だけを数える集計（I/O が支配的）。14 列のうち radio 1 列しか触らない、いちばん列指向で差が出そうなクエリ
  2. 緯度経度を丸めるグリッド集計（CPU 寄り）。round() で緯度・経度を整数に丸め、radio と合わせて件数を数える。行ごとに浮動小数の丸め計算が走る
-- (1) radio だけを数える（I/O 支配）
SELECT radio, count(*) FROM cell_towers GROUP BY radio ORDER BY 2 DESC;

-- (2) 緯度経度を丸めるグリッド集計（CPU 寄り）
SELECT count(*) FROM (
  SELECT round(lat) AS glat, round(lon) AS glon, radio, count(*) AS c
  FROM cell_towers GROUP BY glat, glon, radio
) t;

計測はセッション設定を max_parallel_workers_per_gather = 4・work_mem = 256MB に揃え、各クエリを warm（1 回流してキャッシュに載せてから）3 回実行した平均を取りました。OFF / ON で変えるのは列指向エンジンの状態だけです。

3.3. 列指向 OFF / ON の切り替え方

列指向エンジンを使うには、対象の列を列ストアに載せる必要があります。次の関数で、radio / lat / lon の 3 列を載せました（戻り値は追加されたチャンク数です）。

-- 列ストアに列を載せる
SELECT google_columnar_engine_add(relation => 'cell_towers', columns => 'radio,lat,lon');
-- 載ったかは g_columnar_relations / g_columnar_columns で確認（status が Usable）

-- 列ストアから外す（行ストア側を測るとき）
SELECT google_columnar_engine_drop(relation => 'cell_towers', columns => 'radio,lat,lon');

ここで一点、切り替え方に注意があります。google_columnar_engine.enabled 自体はセッション単位では変えられず、フラグを設定するとインスタンスが再起動します4。そのため OFF / ON を素早く比べるには、enabled をいじるのではなく「列を列ストアに載せる（add）／外す（drop）」で切り替えるのが手軽です。本記事もその方法で比べました。

同じインスタンス上で、psql から見ると参照する SQL は同じで、列指向を使うかどうかだけが変わります。

4. 結果

4.1. radio だけを数える集計（I/O 支配）

radio 1 列を数えるだけの集計です。列指向は radio 列しか読みませんが、行指向は「radio 1 列が欲しいだけ」でも行をまるごと（＝全列ぶん）読みます。SELECT * をしていなくてもそうです。行指向では 1 行ぶんの全列がディスク上でひとかたまりに並んでいるため、radio の値だけを取り出そうとしても、その行の他の列のバイトも一緒に読み込むことになるからです（どちらも 43,276,150 行すべてを数える点は同じで、違うのは「1 行あたり何バイト読むか」です）。

エンジン 実行時間（3回平均） 読んだ量
列指向 OFF（行ストア） 1844.6 ms 約 5.5 GB（687,244 ブロック）
列指向 ON（列ストア） 158.8 ms 約 280 KB（35 ブロック）

約 11.6 倍速でした。読むブロックは 687,244 から 35 へ、ほぼ 2 万分の 1 になっています。この数値はウォーム（メモリ上）での比較なので、ディスクから読むコールドならさらに開きます。

4.2. グリッド集計（CPU 寄り）

緯度経度を round() で丸めるグリッド集計です。行ごとに浮動小数の丸め計算が入るので、I/O だけでなく CPU の重さも乗ってきます。

エンジン 実行時間（3回平均） 結果グループ数
列指向 OFF（行ストア） 12830.8 ms 28,811
列指向 ON（列ストア） 9284.7 ms 28,811

約 1.38 倍速に縮みました。差が縮むのは、round() の浮動小数演算が行あたり重く、さらに GROUP BY の集計テーブルが約 194 MB（198,673 kB）まで膨らんで、そちら側の処理が支配的になるためです。列指向スキャン自体は ON でも使われています（5 章）。結果グループ数は OFF / ON とも 28,811 で完全に一致しました。列指向は結果を変えずに速くする、という前提どおりです。

5. 列指向が使われた証拠

「速かった」だけでは、本当に列指向エンジンが効いたのか（行ストアのまま速くなっただけではないか）は分かりません。これを EXPLAIN (ANALYZE, BUFFERS) で確かめます。ON 側の radio 集計の実行計画を抜き出すと、次のようになっていました。

Parallel Custom Scan (columnar scan) on cell_towers   ← 列指向スキャンが使われた
  Rows Removed by Columnar Filter: 0
  Rows Aggregated by Columnar Scan: 2,012,273
  Columnar cache search mode: native
Parallel Seq Scan on cell_towers  (never executed)    ← 行ストアへのフォールバックは未実行
Buffers: shared hit=35

ポイントは 2 つです。

  • Custom Scan (columnar scan) が出ている。列指向スキャンが選ばれた証拠です
  • そのすぐ下に行ストアの Parallel Seq Scan が候補として残っていますが (never executed) です。列指向スキャンだけで全行を処理でき、列ストアに無い行を拾うための代替スキャンを動かす必要がなかったことを示します（行ストアにフォールバックしていません）

読むブロック（Buffers: shared hit）は、OFF が 687,244 だったのに対し ON は 35 でした。速さの源は、読むブロックがこれだけ減ったことにあります。列指向の統計を集める g_columnar_stat_statements にも、columnar_unit_read などの値が記録されていました5。

6. 考察

6.1. 差が大きいのは「必要な列が少なく I/O が支配的」なとき

2 つの集計で、差の大きさがはっきり分かれました。

  • radio 集計（約 11.6 倍）: 14 列のうち 1 列しか要らない。列指向は radio の約 43 MB だけ読み、行指向は全 5.5 GB を読む。読むデータ量の差がそのまま速度差になる
  • グリッド集計（約 1.38 倍）: 3 列を読み、さらに行ごとに round() の計算が走り、集計テーブルも大きい。読む量の差は残るが、CPU 側の重さが乗ってきて差が縮む

つまり、列指向が大きく差をつけるのは、必要な列が少なく、I/O が支配的なクエリです。逆に、全列を触る・行ごとの計算が重いクエリほど差は縮みます。「列指向だから常に何倍も速い」ではなく「読む量を減らせるクエリで差が出る」と読むのが正確だと考えられます。

なお、「必要な列だけ読むから速い」こと自体は列指向の一般的な利点で、専用の分析エンジンにも当てはまります。AlloyDB ならではなのは、これが Postgres にそのまま組み込まれていて、同じ SQL・アプリ変更なしで使えて、プランナが自動で列指向を選ぶ1点です。

6.2. 列ストアのサイズと圧縮

列ストアに載せた 3 列のサイズはこうなっていました。

列 列ストアのサイズ
radio 約 43 MB
lon 約 415 MB
lat 約 398 MB
合計 約 856 MB

radio は値が 5 種類しかないのでよく縮みますが、lon / lat は浮動小数の座標なのでほとんど縮みません。3 列ぶんを行ストア換算（約 1.18 GB）すると、列ストアは 856 MB で、圧縮としては約 1.38 倍程度です。

ここから言えるのは、列指向の速さは圧縮そのものより「必要な列だけ読む」効果が主だということです。浮動小数の多い表では圧縮はあまりかかりませんが、それでも radio 集計は 11.6 倍速くなりました。読む列を減らせるかどうかが速さの差を決めます。

6.3. ハマりどころ（運用で知っておきたい2点）

検証中に見つけた、運用で知っておくとよい点を 2 つ挙げます。

  • enabled はセッションで変えられない。google_columnar_engine.enabled を設定するとインスタンスが再起動します4。OFF / ON を素早く比べたいときは、列を載せる／外す（add / drop）で切り替える方が手軽です
  • add で載せた列は再起動で消える。google_columnar_engine_add で追加した列は、インスタンスの再起動・フェイルオーバーで列ストアから外れます。再起動後も自動で載せ直すには、google_columnar_engine.relations フラグに対象（データベース・スキーマ・表・列）を指定しておく必要があります6。今回は短時間の検証なので add で足りましたが、本番で常用するならフラグでの永続化が必要です

6.4. この比較のフェアさ

最後に、数値の前提です。

  • 同一インスタンス・同一データ・同一 SQL・同一セッション設定で、列指向 ON / OFF だけを差し替えました。CPU も並列度も同じなので、差は列指向エンジンの効果と読めます
  • 数値はウォーム（メモリにキャッシュ済み）です。コールドでディスクから 5.5 GB を読むなら、OFF はさらに遅くなり、差は開く方向です
  • 結果は OFF / ON で一致しました（グリッド集計のグループ数も 28,811 で同じ）。列指向は結果を変えずに速くする、という前提どおりに動いています

7. まとめ

観点 結論
radio 集計（I/O 支配） 列指向が約 11.6 倍速（読む量 約43 MB 対 約5.5 GB）
グリッド集計（CPU 寄り） 列指向が約 1.38 倍速
差を生む要因 列指向は必要な列だけ読む。行指向は1列だけ欲しくても行まるごと（全列ぶん）読む
使われた証拠 EXPLAIN に Custom Scan (columnar scan)
読むブロックが 687,244→35
前提 同一インスタンスで ON / OFF のみ切替
差は列指向エンジンの効果

AlloyDB の列指向エンジンを、実データ 4,327万件で OFF / ON 比較してみました。列指向は「必要な列だけ読む」ので、I/O が支配的なクエリほど差が開くこと、同じ SQL・アプリ変更なしで速くなること、そして EXPLAIN で「本当に列指向が使われた」ことまで確かめられました。スキーマもアプリも変えずに分析クエリを速くしたい、という場面で素直に使える機能だと感じました。

8. 参考

  1. AlloyDB 公式ドキュメント「About the AlloyDB columnar engine」。列指向フォーマットで保持し、プランナが自動で列指向スキャン・集計を高速化する旨。https://docs.cloud.google.com/alloydb/docs/columnar-engine/about ↩ ↩2

  2. Google Cloud Blog「AlloyDB for PostgreSQL Columnar Engine」。分析クエリで標準 PostgreSQL 比 最大 100 倍・スキーマ/アプリ/ETL 変更不要というベンダーの主張。https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-columnar-engine ↩

  3. OpenCelliD。世界中の基地局位置（緯度・経度）と通信方式などを公開するオープンデータ。本記事の cell_towers の一次提供元。https://www.opencellid.org/ ↩

  4. AlloyDB 公式ドキュメント「Configure the columnar engine」。google_columnar_engine.enabled を設定するとインスタンスが自動で再起動する（＝セッション単位では変更できない）旨と、memory_size_in_mb 等の設定。https://docs.cloud.google.com/alloydb/docs/columnar-engine/configure ↩ ↩2

  5. AlloyDB 公式ドキュメント「Monitor the columnar engine」。EXPLAIN の Custom Scan (columnar scan) で列指向の使用を確認する方法、g_columnar_stat_statements の見方。https://docs.cloud.google.com/alloydb/docs/columnar-engine/monitor-tune ↩

  6. AlloyDB 公式ドキュメント「Manage column store content manually」。google_columnar_engine_add で追加した列は再起動で永続しないこと、google_columnar_engine.relations フラグで再起動・フェイルオーバー時に対象列を再投入する方法。https://docs.cloud.google.com/alloydb/docs/columnar-engine/manage-content-manually ↩

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?