# 实时精准营销（人群圈选）
#### 背景
在电商、金融、在线教育、游戏等面向消费者（ToC）的行业中，精准营销是提升转化率、降低营销成本的核心能力。精准营销的本质是"在合适的时机，将合适的内容推送给合适的人"，而"合适的人"即为目标人群的圈选（User Selection）。
人群圈选的业务诉求可以抽象为：基于用户身上携带的标签（Tag/Label），通过标签的交、并、差运算，从亿级用户中实时筛选出满足条件的目标人群。典型场景包括：
- 组合标签圈选：例如筛选"近30天有购买行为"且"年龄在25-35岁"且"未购买过某新品"的用户，作为新品推荐的目标人群。
- 实时更新：用户标签随行为实时变化（如用户刚完成一笔订单即被打上"高价值客户"标签），圈选结果需要秒级反映最新标签状态。
- 亿级用户、千万级标签：头部互联网平台的用户量可达数亿，标签维度可达数千至数万，传统数据库方案难以兼顾写入吞吐与查询性能。
华为云RDS for PostgreSQL提供多种方案实现实时精准营销的人群圈选。本实践以电商精准营销为典型场景，演示如何基于RDS for PostgreSQL实现亿级用户的实时标签圈选。通过对同一批用户标签数据分别采用三种方案建模与查询，对比各方案在写入、查询、存储上的差异，给出选型建议。
#### 前提条件
- 已创建RDS for PostgreSQL实例，实例状态正常。
- RDS for PostgreSQL实例版本要求：方案一和方案二要求版本为10及以上、方案三要求版本为11及以上。
- 已创建数据库账号并授予对应Schema的读写权限。
- 方案三需在RDS控制台安装位图计算插件roaringbitmap（PostgreSQL 11及以上版本支持）。
 
#### 方案概述
针对人群圈选，RDS for PostgreSQL提供以下三种方案：
- 方案一（数组+GIN索引）：将用户标签以整型数组（int\[\]）存储在用户表中，通过GIN（Generalized Inverted Index）索引加速数组的包含、相交查询。实现最简单，适用于标签数量适中（单用户标签数不超过数百）、用户量百万级的场景。
- 方案二（倒排索引）：将"用户-标签"关系拆分为倒排表（每行一个tag-uid对），通过B-tree索引与GROUP BY实现交、并、差运算。适用于标签维度多、用户量千万级的场景。
- 方案三（roaringbitmap位图）：使用位图数据结构存储每个标签对应的用户集合，通过位图的位运算（AND/OR/ANDNOT）实现毫秒级圈选。适用于亿级用户、千万级标签的超大规模场景。
 
- [方案一：使用数组和GIN索引实现]
  该方案将每个用户的全部标签以整型数组（int\[\]）存储在用户表中，借助PostgreSQL的GIN索引加速数组的包含查询，实现标签的交、并、差圈选。该方案无需额外插件，原生SQL即可完成，适合快速验证与中小规模场景。
  表结构设计如下：
  - 创建用户标签表
    ```
    CREATE TABLE t_user (
        uid   int PRIMARY KEY,       -- 用户ID
        uname text,                   -- 用户名（演示用）
        tag   int[] NOT NULL          -- 标签数组，每个元素为一个标签ID
    );
    ```
    
  
  - 写入用户数据（示例）
    ```
    INSERT INTO t_user VALUES
    (1, 'user_001', ARRAY[1,2,3,10,20]),
    (2, 'user_002', ARRAY[2,3,5,10]),
    (3, 'user_003', ARRAY[1,3,10,30]),
    (4, 'user_004', ARRAY[2,4,10,20]),
    (5, 'user_005', ARRAY[1,5,30]);
    ```
    
  
  - 创建GIN索引以加速数组查询
    ```
    CREATE INDEX idx_user_tag ON t_user USING GIN (tag);
    ```
    
  
  - 圈选查询示例：
    - 交集圈选（同时包含标签1、3、10的用户，即AND关系）
      ```
      SELECT uid, uname FROM t_user WHERE tag @> ARRAY[1,3,10];
      ```
      
    
    - 并集圈选（包含标签1、2、3中任意一个的用户，即OR关系）
      ```
      SELECT uid, uname FROM t_user WHERE tag && ARRAY[1,2,3];
      ```
      
    
    - 差集圈选（包含标签1和3，但不包含标签5的用户）
      ```
      SELECT uid, uname FROM t_user
      WHERE tag @> ARRAY[1,3]
        AND NOT tag @> ARRAY[5];
      ```
      
    
    - 标签实时更新（追加与删除标签） 为用户1追加标签99。
      ```
      UPDATE t_user SET tag = tag || 99 WHERE uid = 1;
      ```
      删除用户1的标签20。
      ```
      UPDATE t_user SET tag = array_remove(tag, 20) WHERE uid = 1;
      ```
      
    
    
    @\>运算符表示"包含"，即左侧数组包含右侧数组的所有元素。\&\&运算符表示"相交"，即两侧数组有共同元素。GIN索引会自动加速这两个运算符。方案一在单用户标签数不超过数百、用户量百万级时性能良好；当用户量达到千万级以上，且需要多标签组合圈选时，数组扫描与GIN索引的位图扫描成本会显著上升。
    
   
- [方案二：使用倒排索引实现]
  该方案借鉴搜索引擎的倒排索引思想，将"用户-标签"关系拆分为独立的倒排表：每行存储一个(tag, uid)对。这样每个标签对应的所有用户可以通过主键索引范围扫描快速获取，再通过GROUP BY与HAVING实现多标签的交、并、差运算。
  表结构设计如下：
  - 倒排表：每个(标签, 用户)一行
    ```
    CREATE TABLE t_user_tag (
        tag int NOT NULL,
        uid int NOT NULL,
        PRIMARY KEY (tag, uid)
    );
    ```
    
  
  - 写入倒排数据（示例）
    ```
    INSERT INTO t_user_tag VALUES
    (1,1),(2,1),(3,1),(10,1),(20,1),
    (2,2),(3,2),(5,2),(10,2),
    (1,3),(3,3),(10,3),(30,3),
    (2,4),(4,4),(10,4),(20,4),
    (1,5),(5,5),(30,5);
    ```
    主键(tag, uid)自带B-tree复合索引，无需额外创建索引。
    
  
  - 圈选查询示例：
    - 交集圈选（同时拥有标签1、3、10的用户）
      ```
      SELECT uid
      FROM t_user_tag
      WHERE tag IN (1,3,10)
      GROUP BY uid
      HAVING count(*) = 3;   -- 标签个数
      ```
      
    
    - 并集圈选（拥有标签1、3、10中任意一个的用户）
      ```
      SELECT uid
      FROM t_user_tag
      WHERE tag IN (1,3,10)
      GROUP BY uid;
      ```
      
    
    - 差集圈选（拥有标签1和3，但不拥有标签5的用户）
      ```
      SELECT uid
      FROM t_user_tag
      WHERE tag IN (1,3)
      GROUP BY uid
      HAVING count(*) = 2
      EXCEPT
      SELECT uid FROM t_user_tag WHERE tag = 5;
      ```
      
    
    - 标签实时更新 为用户6添加标签10。
      ```
      INSERT INTO t_user_tag VALUES (10,6) ON CONFLICT DO NOTHING;
      ```
      删除用户1的标签20。
      ```
      DELETE FROM t_user_tag WHERE uid=1 AND tag=20;
      ```
      
    
    
    方案二将标签关系拆分为行级存储，写入与删除均为轻量的单行操作，更新成本极低，适合标签频繁变动的场景。但在亿级用户、千万级标签时，单个热门标签可能对应数千万行，多标签组合的**GROUP BY**聚合仍需扫描大量数据，查询延迟会随数据量线性增长。
    
   
- [方案三：使用位图计算插件roaringbitmap实现]
  该方案使用PostgreSQL的roaringbitmap扩展，将每个标签对应的用户集合以压缩位图（Roaring Bitmap）存储。Roaring Bitmap将32位整数空间分桶，对每个桶根据数据密度自适应选择密集数组或位图压缩，兼顾存储效率与运算速度。圈选时通过位图的AND（交集）、OR（并集）、ANDNOT（差集）运算在毫秒级完成亿级用户的筛选。
  roaringbitmap插件支持的核心特性：
  - 取值范围：40亿（int4，0到2的31次方减1），覆盖绝大多数用户ID空间。
  
  - 压缩存储：相比普通位图大幅节省空间，1亿用户位图仅约数十MB。
  
  - 位图运算：支持AND、OR、XOR、ANDNOT等集合运算及聚合函数。
  
  - 转换函数：rb_build构造位图，rb_to_array输出数组，rb_cardinality计算基数。
  
  
  该方案需要先在RDS控制台安装roaringbitmap插件（要求版本为11及以上）。
  表结构设计如下：
  - 标签位图表：每个(标签, 偏移量桶)一行，存储该桶内的用户位图
    ```
    CREATE TABLE t_tag_users (
        tag        int NOT NULL,            -- 标签ID
        uid_offset int NOT NULL,            -- 用户ID分桶偏移量（每桶2的31次方）
        userbits   roaringbitmap NOT NULL,  -- 该桶内拥有此标签的用户位图
        PRIMARY KEY (tag, uid_offset)
    );
    ```
    当用户ID超过int4范围（约42亿）时，通过uid_offset分桶存储；绝大多数场景uid_offset=0即可。
    
  
  - 写入位图数据（示例）
    ```
    -- 标签1：用户1、3、5拥有
    INSERT INTO t_tag_users VALUES (1, 0, rb_build(ARRAY[1,3,5]));
    -- 标签3：用户1、2、3拥有
    INSERT INTO t_tag_users VALUES (3, 0, rb_build(ARRAY[1,2,3]));
    -- 标签10：用户1、2、3、4拥有
    INSERT INTO t_tag_users VALUES (10, 0, rb_build(ARRAY[1,2,3,4]));
    ```
    
  
  - 圈选查询示例
    - 交集圈选（同时拥有标签1、3、10的用户）
      ```
      SELECT uid_offset, rb_and_agg(userbits) AS ub
      FROM t_tag_users
      WHERE tag IN (1,3,10)
      GROUP BY uid_offset;
       
      -- 将位图转为用户ID数组查看结果
      SELECT uid_offset, rb_to_array(rb_and_agg(userbits)) AS uids
      FROM t_tag_users
      WHERE tag IN (1,3,10)
      GROUP BY uid_offset;
      ```
      
    
    - 并集圈选（拥有标签1、3、10中任意一个的用户）
      ```
      SELECT uid_offset, rb_or_agg(userbits) AS ub
      FROM t_tag_users
      WHERE tag IN (1,3,10)
      GROUP BY uid_offset;
      ```
      
    
    - 差集圈选（拥有标签1和3，但不拥有标签10的用户）
      ```
      WITH tag_1_3 AS (
          SELECT uid_offset, rb_and_agg(userbits) AS ub
          FROM t_tag_users
          WHERE tag IN (1,3)
          GROUP BY uid_offset
      ),
      tag_10 AS (
          SELECT uid_offset, rb_or_agg(userbits) AS ub
          FROM t_tag_users
          WHERE tag = 10
          GROUP BY uid_offset
      )
      SELECT a.uid_offset, rb_andnot(a.ub, b.ub) AS result
      FROM tag_1_3 a JOIN tag_10 b USING (uid_offset);
      ```
      
    
    - 统计圈选人数（不展开用户ID，仅返回数量）
      ```
      SELECT uid_offset, rb_cardinality(rb_or_agg(userbits)) AS user_cnt
      FROM t_tag_users
      WHERE tag IN (1,3,10)
      GROUP BY uid_offset;
      ```
      
    
    - 标签实时更新（追加与删除单个用户的标签，参数值userbits请填写实际取值）
      ```
      -- 为用户6添加标签10（位图追加）
      UPDATE t_tag_users
      SET userbits = rb_add(userbits, rb_build(ARRAY[6]))
      WHERE tag = 10 AND uid_offset = 0;
       
      -- 删除用户1的标签10
      UPDATE t_tag_users
      SET userbits = rb_remove(userbits, rb_build(ARRAY[1]))
      WHERE tag = 10 AND uid_offset = 0;
      ```
      
    
    
    方案三通过位图压缩与位运算，将亿级用户的集合运算压缩到毫秒级，是三种方案中性能最高、最适合超大规模场景的方案。
    
   
#### 方案对比
三种方案在实现复杂度、数据更新成本、查询性能与适用规模上各有侧重，对比如下。
表1方案对比 
| **对比维度**   | **方案一（数组+GIN** **）** | **方案二（倒排索引）**       | **方案三（roaringbitmap** **）** |
|:---|:---|:---|:---|
| 实现复杂度      | 低，原生int\[\]与GIN索引    | 中，需维护倒排表            | 中，需安装插件并设计分桶                |
| 数据更新       | 数组追加/删除，单行更新         | 单行INSERT/DELETE，极轻量 | 位图rb_add/rb_remove，单行更新     |
| 查询性能（百万用户） | 毫秒到百毫秒级              | 毫秒到百毫秒级             | 毫秒级                         |
| 查询性能（亿级用户） | 秒级以上，性能衰减明显          | 秒级以上，聚合成本高          | 毫秒级，性能稳定                    |
| 存储成本       | 中（数组+GIN索引）          | 高（每tag-uid一行）       | 低（位图压缩）                     |
| 适用用户量级     | 百万级                  | 千万级                 | 亿级                          |
| 适用标签量级     | 数百到数千                | 数千到数万               | 数万到数十万                      |
| 插件依赖       | 无                    | 无                   | 需roaringbitmap（PG 11+）      |
| 适用场景       | 中小规模、快速验证            | 标签频繁变动、中等规模         | 超大规模、实时性要求高                 |
   
选型建议：
- 用户量在百万级以内、标签维度数千以内：优先方案一，实现最简单，无需额外插件，开发与运维成本最低。
- 用户量千万级、标签频繁变动、需要灵活的多标签组合：选择方案二，倒排表更新轻量、查询灵活。
- 用户量亿级、对圈选延迟要求毫秒级：选择方案三，roaringbitmap在超大规模下性能优势显著。
 
#### 最佳实践建议
- 插件版本：方案三的roaringbitmap插件要求RDS for PostgreSQL实例版本为11及以上；请在RDS控制台确认实例大版本，低版本实例需先升级后再安装插件。
- 用户ID规划：方案三要求用户ID为int4（0到2的31次方减1，约42亿）；若用户ID超过此范围，必须通过uid_offset分桶存储，否则位图无法表示。建议在用户ID生成阶段即规划为连续整数，避免稀疏ID导致位图空间浪费。
- 索引维护：方案一的GIN索引在标签频繁更新时会产生索引膨胀，建议定期通过REINDEX检查索引健康度，并在低峰期执行索引重建；RDS for PostgreSQL支持在线重建索引（CREATE INDEX CONCURRENTLY）以避免锁表。
- 批量导入：初始灌数据时，方案一应在数据导入完成后再创建GIN索引，避免边写边索引导致写入性能下降；方案三应按标签批量构造位图后一次性INSERT，避免逐行更新位图的高成本。
- 连接池与参数：亿级圈选查询涉及位图聚合，内存消耗较大，建议根据实例规格适当调大work_mem（如256MB到1GB），并通过PgBouncer控制并发连接数，防止高并发下内存溢出。
- 监控：通过RDS控制台的监控指标观察CPU、内存与IOPS使用率；通过pg_stat_statements观察慢圈选SQL，对耗时较高的组合标签查询进行分析与优化。
- 标签治理：建议建立标签元数据表（标签字典），记录每个标签的含义、数据来源、更新频率与有效期，定期清理失效标签，避免无效标签堆积导致圈选性能下降与业务理解偏差。
 
#### 常见问题
- **问题1：roaringbitmap插件如何安装？**
  在RDS for PostgreSQL控制台的插件管理页面搜索roaringbitmap并安装。要求实例大版本为11及以上。安装后可通过SELECT \* FROM pg_extension WHERE extname='roaringbitmap';确认插件状态。
  
- **问题2：用户ID不是连续整数（如使用UUID）能否使用方案三？**
  roaringbitmap要求用户ID为int4整数。若业务使用UUID或字符串作为用户标识，需建立一张用户ID映射表（user_mapping），将业务ID映射为连续整数uid，圈选时使用uid参与位图运算，结果再JOIN映射表还原为业务ID。映射表的uid可通过自增序列（SERIAL）或Snowflake等方案生成。
  
- **问题3：方案一的GIN索引为何在亿级数据下变慢？**
  GIN索引本质是倒排索引，每个标签对应一个posting list（命中该标签的TID列表）。当用户量达亿级、单标签命中数千万时，GIN的位图扫描需读取大量posting list与TID，且数组本身的TOAST存储会带来额外IO；多标签组合时位图AND/OR运算的成本也线性增长，因此方案一在亿级场景下性能衰减明显。
  
- **问题4：方案三的位图更新是否适合高频实时写入？**
  roaringbitmap的rb_add与rb_remove是单行UPDATE操作，适合中频标签更新（如秒级到分钟级）。若标签更新频率极高（如每秒数万次），建议引入消息队列加批量合并写入，避免高频单行UPDATE导致行锁竞争与WAL压力。也可结合方案二（倒排表）承接实时写入，再周期性合并为位图供圈选查询。
  
- **问题5：三种方案能否混合使用？**
  可以。典型混合架构：方案二（倒排表）承接实时标签写入，保证更新轻量；后台周期性（如每分钟）将倒排表合并为方案三的位图，供在线圈选查询使用。这样兼顾实时写入与毫秒级查询，是亿级实时营销系统的推荐架构。
  
- **问题6：圈选结果如何对接营销投放系统？**
  圈选结果可通过以下方式对接下游营销系统：一是将结果写入结果表，由营销系统轮询消费；二是通过RDS for PostgreSQL的逻辑订阅（Publication/Subscription）将结果变更实时推送到下游；三是通过数据复制服务（DRS）将圈选结果同步到数仓做进一步分析与画像沉淀。
  
- **问题7：方案三中rb_and_agg与rb_or_agg的区别是什么？**
  rb_and_agg是聚合函数，对输入的多行位图做"按位与"（交集），返回同时出现在所有位图中的用户集合，用于实现AND圈选；rb_or_agg对多行位图做"按位或"（并集），返回出现在任意一个位图中的用户集合，用于实现OR圈选。两者均需配合GROUP BY按uid_offset分组使用。
  
- **问题8：如何评估标签圈选的查询性能是否达标？**
  可通过EXPLAIN ANALYZE查看圈选SQL的执行计划与实际耗时；重点关注位图聚合是否走了索引扫描、是否出现全表扫描、work_mem是否足够避免磁盘排序。建议在真实数据量下压测，目标为单次圈选在100毫秒以内（亿级用户场景）。RDS控制台的性能监控与pg_stat_statements可辅助定位慢SQL。