ApsaraDB RDS:リアルタイム精密マーケティング（オーディエンスセグメンテーション）

前提条件

開始する前に、以下の準備を行ってください。

• ApsaraDB RDS for PostgreSQL インスタンスを用意します。詳細については、「ApsaraDB RDS for PostgreSQL インスタンスの作成」をご参照ください。

• RDS インスタンスに対して IP アドレスホワイトリストを設定します。詳細については、「IP アドレスホワイトリストの設定」をご参照ください。

• データベースアカウントを用意します。詳細については、「アカウントの作成」をご参照ください。

• データベースを用意します。詳細については、「データベースの作成」をご参照ください。

ソリューション 3 を使用するには、roaringbitmap 拡張と PostgreSQL 12 以降が必要です。本トピックの例では PostgreSQL 12 を使用しています。拡張の詳細については、「roaringbitmap 拡張の使用」をご参照ください。

背景情報

精密マーケティングシステムでは、リアルタイムで任意のタグ組み合わせに基づいてユーザーを検索する必要があります。たとえば、「過去 24 時間以内にモバイル端末を閲覧したが、まだ注文していない 24 歳以上の男性ユーザー」を特定するといった処理です。課題は、ユーザー数が数千万、タグ数が数万という規模においても、これを高速に実行することにあります。

主な課題は以下のとおりです。

• タグ数が数十万に達すると、データベースのフィールド制限（通常はテーブルあたり 1,000 フィールド）に抵触します。

• タグを配列として格納する場合、汎用逆インデックス（GIN）のサポートが必要ですが、すべてのデータベースがこれを提供しているわけではなく、またストレージ消費量も大きくなります。

• クエリ条件の組み合わせは予測不可能であるため、静的なインデックスではすべてのケースを網羅できません。

• ユーザーのプロファイルデータはリアルタイムに近い状態を維持する必要があります。たとえば、前日夜に注文を行ったユーザーには、その商品に対する広告を当日中に表示してはなりません。

本トピックでは、これらの課題に対応する 3 つの PostgreSQL ベースのソリューションを紹介し、それぞれのトレードオフについて説明します。

ソリューションの選択

テスト環境：MySQL 8.0 および PostgreSQL 12 を実行中の RDS インスタンス（CPU コア数：8、メモリ：32 GB、Enhanced SSD（ESSD）：1,500 GB）。データセット：ユーザー数 2,000 万人、タグ数 10 万、ユーザーあたりタグ数 64（タグレコード総数：12.8 億）。

ユーザー数が数百万人以上でリアルタイムクエリが求められる本番システムには、ソリューション 3 をご使用ください。MySQL を必須とする場合はソリューション 1 を、ストレージよりクエリ速度を優先できない場合の中間的な選択肢としてソリューション 2 をご検討ください。

ソリューション 1：ユーザー × タグごとに 1 行

このソリューションは PostgreSQL および MySQL の両方で動作します。

スキーマ概要

キー：ユーザー ID<br>値：タグごとに 1 行（タグ 1、タグ 2、… タグ N）

• インデックス：各タグフィールドに対する B-tree インデックス

• 検索方法：WHERE tag = a AND tag = b（INTERSECT／UNION を併用）

課題点

• ストレージ使用量が非常に大きい：2,000 万人のユーザーに対してタグフィールドごとにインデックスを作成すると、インデックスストレージだけで 62,464 MB になります。

• 新しいタグを追加するには、該当グループ内の全ユーザーに対して行を挿入する必要があります。

• フィルター条件に含めるタグ数が増えると、クエリパフォーマンスが低下します。

操作手順

1. タグ辞書テーブル（タグごとに 1 行）を作成します。

   CREATE TABLE t_tag_dict (
     tag      int PRIMARY KEY,   -- タグ ID
     info     text,              -- タグの説明
     crt_time timestamp          -- 作成日時
   );

2. 10 万件のタグを挿入します。

   INSERT INTO t_tag_dict VALUES (1, '男性', now());
   INSERT INTO t_tag_dict VALUES (2, '女性', now());
   INSERT INTO t_tag_dict VALUES (3, '24 歳以上', now());
   -- … その他のタグ …
   
   INSERT INTO t_tag_dict
   SELECT generate_series(4, 100000), md5(random()::text), clock_timestamp();

3. ユーザー × タグごとに 1 行のユーザー・プロファイルテーブルを作成します。

   CREATE TABLE t_user_tag (
     uid      int8,       -- ユーザー ID
     tag      int,        -- タグ ID
     mod_time timestamp,  -- 最終更新日時
     PRIMARY KEY (tag, uid)
   );

4. 2,000 万人のユーザーに、それぞれランダムなタグを 64 個割り当てます（合計 12.8 億行）。

   CREATE OR REPLACE FUNCTION gen_rand_tag(int, int) RETURNS SETOF int AS
   $$
     SELECT CASE WHEN random() > 0.5 THEN 1::int ELSE 2::int END AS tag
     UNION ALL
     SELECT ceil(random() * $1)::int AS tag FROM generate_series(1, $2);
   $$ LANGUAGE sql STRICT VOLATILE;
   
   INSERT INTO t_user_tag
   SELECT uid, gen_rand_tag(100000, 63) AS tag, clock_timestamp()
   FROM generate_series(1, 20000000) AS uid
   ON CONFLICT (uid, tag) DO NOTHING;

5. タグ 1 およびタグ 3 の両方を持つユーザーを検索します。

   -- 該当ユーザー数のカウント
   SELECT count(*) FROM (
     SELECT uid FROM t_user_tag WHERE tag = 1
     INTERSECT
     SELECT uid FROM t_user_tag WHERE tag = 3
   ) t;
   -- 実行時間：1,494 ms
   
   -- ユーザー ID の取得
   SELECT uid FROM t_user_tag WHERE tag = 1
   INTERSECT
   SELECT uid FROM t_user_tag WHERE tag = 3;
   -- 実行時間：3,246 ms

6. タグ 1、3、10、200 のいずれかを持つユーザーを検索します。

   -- 該当ユーザー数のカウント
   SELECT count(*) FROM (
     SELECT uid FROM t_user_tag WHERE tag = 1
     UNION SELECT uid FROM t_user_tag WHERE tag = 3
     UNION SELECT uid FROM t_user_tag WHERE tag = 10
     UNION SELECT uid FROM t_user_tag WHERE tag = 200
   ) t;
   -- 実行時間：3,578 ms
   
   -- ユーザー ID の取得
   SELECT uid FROM t_user_tag WHERE tag = 1
   UNION SELECT uid FROM t_user_tag WHERE tag = 3
   UNION SELECT uid FROM t_user_tag WHERE tag = 10
   UNION SELECT uid FROM t_user_tag WHERE tag = 200;
   -- 実行時間：5,682 ms

ソリューション 2：タグ配列と GIN インデックス

このソリューションは PostgreSQL のみで動作します。MySQL は配列および GIN インデックスをサポートしていません。

スキーマ概要

キー：ユーザー ID<br>値：タグ ID の配列

• インデックス：タグ配列フィールドに対する GIN インデックス

• 検索演算子：@>（AND／含まれる）、&&（OR／重複）、NOT @>（NOT）

課題点

• 大規模データセットでは、GIN インデックスのストレージ消費量が B-tree インデックスよりも大幅に増加します。

• ユーザーにタグを追加するには、そのユーザーの行を更新する必要があり、大規模環境ではコストが高くなります。

• GIN の書き込みは遅延型の fastupdate メカニズムを使用します。更新は保留リストに蓄積され、バッチ単位でフラッシュされます。保留リストが gin_pending_list_limit（デフォルト：4 MB）に達すると、書き込み操作時にフラッシュが発生し、周期的なレイテンシのピークを引き起こします。リアルタイムなユーザー・プロファイル更新ワークロードでは、この動作をモニターし、書き込みレイテンシの一貫性が特に重要な場合は、gin_pending_list_limit のチューニングや fastupdate の無効化を検討してください。

操作手順

1. タグ辞書テーブルを作成します（ソリューション 1 と同じ）。

   CREATE TABLE t_tag_dict (
     tag      int PRIMARY KEY,
     info     text,
     crt_time timestamp
   );

2. 10 万件のタグを挿入します。

   INSERT INTO t_tag_dict VALUES (1, '男性', now());
   INSERT INTO t_tag_dict VALUES (2, '女性', now());
   INSERT INTO t_tag_dict VALUES (3, '24 歳以上', now());
   
   INSERT INTO t_tag_dict
   SELECT generate_series(4, 100000), md5(random()::text), clock_timestamp();

3. ユーザーごとに 1 行、タグは配列形式のユーザー・プロファイルテーブルを作成します。

   CREATE TABLE t_user_tags (
     uid      int8 PRIMARY KEY,  -- ユーザー ID
     tags     int[],             -- タグ ID の配列
     mod_time timestamp
   );

4. ランダムなタグ配列を生成するヘルパー関数を作成します。

   CREATE OR REPLACE FUNCTION gen_rand_tags(int, int) RETURNS int[] AS $$
     SELECT array_agg(ceil(random() * $1)::int) FROM generate_series(1, $2);
   $$ LANGUAGE sql STRICT;

5. 2,000 万人のユーザーに、それぞれランダムなタグを 64 個割り当てます（うち男性ユーザー 1,000 万人、女性ユーザー 1,000 万人）。

   INSERT INTO t_user_tags
   SELECT generate_series(1, 10000000),
     array_append(gen_rand_tags(100000, 63), 1), now();
   
   INSERT INTO t_user_tags
   SELECT generate_series(10000001, 20000000),
     array_append(gen_rand_tags(100000, 63), 2), now();

6. タグ配列に対する GIN インデックスを作成します。

   CREATE INDEX idx_t_user_tags_1 ON t_user_tags USING gin (tags);
   -- インデックス作成時間：約 20 分

7. タグ 1 およびタグ 3 の両方を持つユーザーを検索します。

   -- 該当ユーザー数のカウント
   SELECT count(uid) FROM t_user_tags WHERE tags @> ARRAY[1, 3];
   
   -- ユーザー ID の取得
   SELECT uid FROM t_user_tags WHERE tags @> ARRAY[1, 3];

8. タグ 1、3、10、200 のいずれかを持つユーザーを検索します。

   -- 該当ユーザー数のカウント
   SELECT count(uid) FROM t_user_tags WHERE tags && ARRAY[1, 3, 10, 200];
   
   -- ユーザー ID の取得
   SELECT uid FROM t_user_tags WHERE tags && ARRAY[1, 3, 10, 200];

ソリューション 3：Roaring Bitmap 集約

このソリューションは、roaringbitmap 拡張を有効化した PostgreSQL が必要です。MySQL はこの拡張をサポートしていません。

仕組み

Roaring Bitmap モデルでは、スキーマが反転されます。つまり、ユーザーが持つタグを記録するのではなく、各タグに対して、そのタグを持つユーザーの ID を圧縮されたビットマップ形式で記録します。roaringbitmap を、非常に長いビット文字列と考えてください。各ビット位置はユーザー ID を表し、ビット *n* が立っている場合、ユーザー *n* がそのタグを持つことを意味します。

Roaring Bitmap アルゴリズムでは、32 ビット整数を上位 16 ビットに基づいて 2^16 個のチャンクに分割し、下位 16 ビットをコンテナに格納します。自動的に使用されるコンテナの種類は 2 種類あり、疎なデータ（4,096 個未満の整数）には配列コンテナ、密なデータにはビットマップコンテナが使われます。この適応的な構造により、ソリューション 1 のインデックスサイズ（62,464 MB）が本ソリューションではわずか 2 MB まで削減され、AND／OR クエリが 2 ミリ秒未満で完了します。

スキーマ概要

キー：タグ ID<br>値：ユーザー ID の圧縮ビットマップ

• インデックス：タグ ID に対する B-tree インデックス

• 操作：rb_and_agg／rb_and_cardinality_agg（AND）、rb_or_agg／rb_or_cardinality_agg（OR）、rb_build_agg（ビットマップの構築）

利点

• 最小限のストレージ：タグごとに 1 つのエントリを持つ B-tree インデックス（最大でも数十万エントリ）のみで済みます。

• ユーザーグループにタグを追加するには、ビットマップの1行を挿入または更新するだけで済み、大量の行の更新は不要です。

• 2,000 万人のユーザーに対して AND／OR クエリを 2 ミリ秒未満で実行できます。

制限事項

• ユーザー ID は整数である必要があります。非整数のユーザー ID を使用している場合は、マッピングテーブルを作成してください。

• 標準の roaringbitmap 型は 32 ビット整数（最大約 40 億のユーザー ID）をサポートします。ユーザー ID が 40 億を超える場合は、以下で説明する uid_offset 手法をご利用ください。

操作手順

roaringbitmap 拡張の詳細については、「pg_roaringbitmap」をご参照ください。UID オーバーフローへの対応については、「UID オーバーフローのトラブルシューティング」をご参照ください。

1. roaringbitmap 拡張をインストールします。

   CREATE EXTENSION roaringbitmap;

2. 各タグを、そのタグを持つユーザーの集合にマップするビットマップテーブルを作成します。

   CREATE TABLE t_tag_users (
     tagid      int PRIMARY KEY,   -- タグ ID
     uid_offset int,               -- オフセットバケット（INT8 ユーザー ID を INT4 範囲に変換）
     userbits   roaringbitmap,     -- ユーザー ID の圧縮ビットマップ
     mod_time   timestamp
   );

3. ソリューション 2 で作成した t_user_tags テーブルからビットマップテーブルを初期化します。

   INSERT INTO t_tag_users
   SELECT tagid, uid_offset, rb_build_agg(uid::int) AS userbits
   FROM (
     SELECT
       unnest(tags) AS tagid,
       (uid / (2^31)::int8) AS uid_offset,   -- 上位ビットをオフセットバケットとして使用
       mod(uid, (2^31)::int8) AS uid          -- 下位ビットをビットマップ位置として使用
     FROM t_user_tags
   ) t
   GROUP BY tagid, uid_offset;

4. タグ 1 およびタグ 3 の両方を持つユーザーを検索します。

   -- 該当ユーザー数のカウント
   SELECT sum(ub) FROM (
     SELECT uid_offset, rb_and_cardinality_agg(userbits) AS ub
     FROM t_tag_users
     WHERE tagid IN (1, 3)
     GROUP BY uid_offset
   ) t;
   -- 実行時間：1.5 ms
   
   -- ビットマップ結果の取得（オフセットとビットマップから再構築されたユーザー ID）
   SELECT uid_offset, rb_and_agg(userbits) AS ub
   FROM t_tag_users
   WHERE tagid IN (1, 3)
   GROUP BY uid_offset;

5. タグ 1、3、10、200 のいずれかを持つユーザーを検索します。

   -- 該当ユーザー数のカウント
   SELECT sum(ub) FROM (
     SELECT uid_offset, rb_or_cardinality_agg(userbits) AS ub
     FROM t_tag_users
     WHERE tagid IN (1, 3, 10, 200)
     GROUP BY uid_offset
   ) t;
   -- 実行時間：1.7 ms
   
   -- ビットマップ結果の取得
   SELECT uid_offset, rb_or_agg(userbits) AS ub
   FROM t_tag_users
   WHERE tagid IN (1, 3, 10, 200)
   GROUP BY uid_offset;

まとめ

ApsaraDB RDS for PostgreSQL 12 以降では、roaringbitmap 拡張がサポートされており、AND、OR、NOT、XOR 演算を伴う効率的なビットマップの生成、圧縮、および集約が可能になります。ユーザー数が数億、タグ数が数千万に及ぶ精密マーケティングシステムにおいて、ソリューション 3 はクエリ時間を 2 ミリ秒未満、インデックスストレージを 2 MB 未満に抑え、シンプルなスキーマモデルでは満たせないスピードとスケールの両方の要件を満たします。

次のステップ

• roaringbitmap 拡張の使用

• ApsaraDB RDS for PostgreSQL インスタンスの作成