AlloyDBを業務システムのバックエンドとして利用するためのアイデア

はじめに

fujiuです。この記事ではGoogle CloudのフルマネージドPostgreSQL互換データベースであるAlloyDBについて、業務システムのバックエンドとして利用する方法を紹介します。

AlloyDBはPostgreSQL互換なインターフェースを備えているためOLTPのユースケースにおいて高速にクエリすることができます。
さらに、AlloyDBはカラムナエンジンという仕組みを備えており、これをうまく扱えるようなSQLでクエリすることで、OLAPのユースケースでも使えるようなパフォーマンスを発揮します。

STORES において顧客データを集約し活用することは、事業者さまのビジネスを継続的に成長させる観点で非常に重要なテーマとなっています。アプリケーションとしてユースケースに応じた顧客の分析機能をいかに素早く提供し続けていくかは大きな課題です。
STORES ではこうした課題に対応するために AlloyDB を採用し、カラムナエンジン等の特性を活かした柔軟な分析機能を提供することを目指しています。

この記事では、STORESでの活用例をもとに、業務システムの一覧画面や検索画面といったOLTPのユースケースでAlloyDBをうまく活用するためのアイデアを紹介します。

アイデア

AlloyDBは、先ほど触れたカラムナエンジンという仕組みによってテーブルの単一列をまるっとキャッシュし、集計などの分析クエリのパフォーマンスを大幅に向上させることができます。
カラムナエンジンは、従来のSQLによるクエリ時にカラムナキャッシュを有効に活用できる実行計画を見出して自動的に利用するため、カラムナエンジンの力をフルに活用するためにはカラムナエンジンの特性と実行計画を理解する必要があります。
そしてカラムナキャッシュを有効活用する上でのルールを定めたものが下記になります。

大ルール

サブクエリで条件を絞り込む
サブクエリではJOINを避ける
サブクエリ結果をトップレベルでJOINする

大ルールの背景

各サブクエリではJOINを行わずシンプルに保ちカラムナエンジンによる最適化を最大限に活用する
サブクエリでは必ずテナントIDによるフィルタリングを行う。必要であれば非正規化も検討する
各サブクエリの結果を最後にまとめてINNER JOINする。ここではLEFT JOINやWHEREによる絞り込みは極力行わない。

さらに詳しく解説していきます。

まず、AlloyDBのカラムナエンジンは列ごとにデータを効率的にキャッシュするため、業務システムに組み込むにあたって単一テーブルで完結するシンプルな絞り込み条件であれば効果を発揮しました。
しかし、テーブルJOINを多用するとカラムナエンジンの絞り込みの効率性に比してテーブルJOINのコストが嵩み、結果として全体のクエリ性能に悪影響を及ぼし、期待するほどパフォーマンスが出ないことがわかりました。

-- テーブルJOINが非常に重いクエリのイメージ
SELECT *
FROM users
LEFT OUTER JOIN customer_residential_address
ON users.id = customer_residential_address.user_id
INNER JOIN orders
ON users.id = orders.user_id
-- 以下に絞り込み条件のためのjoin句が続く...


WHERE prefecture LIKE '東京都'
AND tenant_id = 777
-- 以下に絞り込み条件がwhere句で続く...

つまり

絞り込みではカラムナエンジンの最適化を制御しやすいようにシンプルにクエリする
レコード量に対してテーブルJOINをできるだけ避ける形に調整する

の二つがカラムナキャッシュを有効活用するためのクエリ戦略の要点となっていきます。

次のセクションでは、この方針を実際にどのようにSQLクエリに反映させるかを具体的に説明していきます。

実装例

例として、「商品を一度でも購入したことのある東京都在住のユーザー」を絞り込むクエリを考えてみます。

-- 東京都在住の顧客を絞り込む
WITH filtered_customer_residential_address AS (
    SELECT user_id
    FROM customer_residential_address
    WHERE prefecture LIKE '東京都'
      AND tenant_id_cache = 777
),
-- 商品を購入した顧客を絞り込む
filtered_orders AS (
    SELECT user_id
    FROM orders
    WHERE tenant_id_cache = 777
    GROUP BY user_id
)
-- 最終的な結果を取得する
SELECT *
FROM users
INNER JOIN filtered_customer_residential_address
ON id = filtered_customer_residential_address.user_id
INNER JOIN filtered_orders
ON id = filtered_orders.user_id
WHERE tenant_id = 777;

クエリ解説

サブクエリ:filtered_customer_residential_address
- サブクエリとしてcustomer_residential_addressから、住所が東京都であるユーザーを絞り込みます。
- この絞り込みはカラムナキャッシュの活用によって高速に処理されることを事前に担保しておきます。
サブクエリ: filtered_orders
- サブクエリとしてordersから一度でも商品を購入したユーザーを取得します。
- この絞り込みも同じくカラムナキャッシュによって最適化されることを担保しておきます。
最終クエリ
- 絞り込まれたリストをusersとJOINして、最終的な条件を満たすユーザーを取得します。
- WHERE句は指定せず(テナントIDによる絞り込みを除く)、サブクエリのINNER JOINによってのみ絞り込みが行われます

各サブクエリ内でカラムナエンジンが有効に機能し高速に絞り込まれ、且つ十分に行数が絞られることが担保されます。
そのため、仮にusersの行数が膨大になったとしても十分に絞り込みが行われた現実的なレコード量に対してテーブルJOINが実行されることを担保できるため、結果としてクエリ全体で高速なパフォーマンスを実現できるだろう、という見立てがつきます。

まとめ

業務システムにおいては、複雑な絞り込み条件を拡張しつつ、時には動的で柔軟な要件に対応しながらもクエリ性能は維持する、という性質が強く求められます。
そのためにもアプリケーション自体の拡張性や柔軟性を保ち続けるという意識は非常に重要です。

今回ご紹介したサブクエリによって分割する方針は、

絞り込み条件がサブクエリによって分割されるため全体のクエリが複雑化しない
作業時にはサブクエリ単位でのパフォーマンスを担保すれば良い
検索に特化したキャッシュテーブルを用意しなくても良く、ある程度ドメイン要求によってデータモデリングされたテーブル設計を扱えば良い
各サブクエリでパフォーマンスが担保できていれば全体のクエリ性能が大きく劣化する可能性は低いので絞り込み条件を拡張しやすい

というメリットがあると思います。
クエリ戦略において、このような分割統治的なアプローチは、複雑な絞り込み条件のクエリ性能を維持しつつ、システム全体の拡張性を保つため非常に有効だと思います。

まだまだAlloyDBやカラムナエンジンの活用についてノウハウや理解が浅い部分もありますし、アプリケーション開発者は機能開発においては意識せずともカラムナエンジンのハイパフォーマンスの恩恵にあずかることができる状態が理想ではあります。これからも試行錯誤を続けながらもっとうまく活用する方法を模索していきたいと思っています。

STORES Product Blog

こだわりを持ったお商売を支える「STORES」のテクノロジー部門のメンバーによるブログです。