您可以通过RDS PostgreSQL 12实现实时精准营销，快速圈选目标用户。

背景信息

几乎所有行业（如互联网、游戏、教育等）都有实时精准营销的需求。通过系统生成用户画像，在营销时通过条件组合筛选用户，快速提取目标群体，例如：

• 电商行业中，商家在运营活动前，需要根据活动的目标群体的特征，圈选出一批目标用户进行广告推送或进行活动条件的判断。
• 游戏行业中，商家需要根据玩家的某些特征进行圈选，针对性地发放大礼包，提高玩家活跃度。
• 教育行业中，需要根据学生不同的特征，推送有针对性的习题，帮助学生查缺补漏。
• 搜索、门户、视频网站等业务中，根据用户的关注热点，推送不同的内容。

这些行业都有一些共同的业务痛点：

• 数据量庞大，运算量也极大。
• 用户标签多、字段多，占用存储空间也多。
• 字段多，可能会超过数据库的字段数限制，一般数据库最多支持1000多个字段。
• 使用数组替代多字段存储标签，需要数据库支持倒排索引，但并不是所有数据库都支持倒排索引。
• 使用数组替代多字段存储标签，加上倒排索引，存储空间会暴增。
• 圈选条件组合多样化，没有固定索引可以优化，如果每个字段一个索引，存储空间又会暴增。
• 性能要求高，因为实时营销要求秒级响应。
• 数据更新时效要求高，用户画像要求近实时的更新，否则可能圈选到不精准用户（例如用户昨天在浏览手机，昨晚已下单，但是数据未实时更新，那么手机卖家圈选时这个用户就会被选中，但是该用户实际上已经不是目标群体）。

常见的产品（如MySQL）无法在有限的资源下满足实时圈选目标群体的需求。

本文介绍如何基于RDS PostgreSQL实现实时精准营销，您可以根据业务需要使用三种方案之一。

准备工作

• 创建RDS PostgreSQL 12实例。
• 设置白名单。
• 创建账号。
• 创建数据库。

方案一

说明 PostgreSQL和MySQL都支持方案一。

表结构如下：

KEY: 用户ID
标签1: 
标签2: 
...
标签N:

索引如下：

每个标签字段一个索引

搜索方法如下：

and , or , not 组合
where 标签a and 标签b and ...

方案缺陷

• 用户标签多、字段多，占用存储空间也多。
• 字段多，可能会超过数据库的字段数限制，一般数据库最多支持1000多个字段。也可以设置为多对多的结构，每个标签一条记录，解决字段数限制的问题。
• 圈选条件组合多样化，没有固定索引可以优化，如果每个字段一个索引，存储空间又会暴增。
• 新增一个用户群体标签时，需要更新大量数据。
• 查询性能差。

操作示例

• 创建人群表，每条记录代表一个人群。示例如下：

create table t_tag_dict (
tag int primary key,   -- 标签(人群)id
info text,  -- 人群描述
crt_time timestamp  -- 时间
); 

• 生成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();

• 创建用户画像表，每个用户N条记录，每条记录代表这个用户贴的某个标签。示例如下：

create table t_user_tag (
uid int8,   -- 用户id
tag int,            -- 用户对应标签（人群）
mod_time timestamp,     -- 时间
primary key (tag,uid)
); 

• 给2000万个用户打标，每个用户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;
-- 或使用如下方法加速导入
create sequence seq;
vi test.sql
insert into t_user_tag
select uid, gen_rand_tag(100000,63) as tag, clock_timestamp() 
from nextval('seq'::regclass) as uid 
on conflict(tag,uid) do nothing;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 50 -j 50 -t 400000

• 查询包含标签1和3的人群。示例如下：

1、人群数量
select count(*) from 
(
select uid from t_user_tag where tag=1 
intersect
select uid from t_user_tag where tag=3
) t;
-- Time: 1494.789 ms (00:01.495)
2、提取人群ID
select uid from t_user_tag where tag=1 
intersect
select uid from t_user_tag where tag=3;
-- Time: 3246.184 ms (00:03.246)

• 查询包含标签1、3、10或200的人群。示例如下：

1、人群数量
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;
-- Time: 3577.714 ms (00:03.578)
2、提取人群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;
-- Time: 5682.458 ms (00:05.682)

方案二

说明 仅PostgreSQL支持方案二。MySQL不支持数组类型、倒排索引。

表结构如下：

KEY：用户ID
VALUES：标签数组

索引如下：

标签数组字段: GIN倒排索引

搜索方法如下：

与、或、非
where VALUES @> array[标签s] -- 与
where VALUES && array[标签s] -- 或
where not VALUES @> array[标签s] -- 非

方案缺陷

• 使用数组替代多字段存储标签，需要数据库支持倒排索引，但并不是所有数据库都支持倒排索引。
• 使用数组替代多字段存储标签，加上倒排索引，存储空间会暴增。
• 新增一个用户群体标签时，需要更新大量数据。

操作示例

• 创建人群表，每条记录代表一个人群。示例如下：

create table t_tag_dict (
tag int primary key,   -- 标签(人群)id
info text,  -- 人群描述
crt_time timestamp  -- 时间
); 

• 生成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();

• 创建用户画像表，每个用户一条记录，用数组表示这个用户归属哪些标签。示例如下：

create table t_user_tags (
uid int8 primary key,   -- 用户id
tags int[],            -- 用户标签（人群）数组
mod_time timestamp     -- 时间
); 

• 创建随机生成打标数组的函数。示例如下：

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;

• 在10万个标签内随机提取8个标签。示例如下：

select gen_rand_tags(100000, 8);
                   gen_rand_tags                   
---------------------------------------------------
 {43494,46038,74102,25308,99129,40893,33653,29690}
(1 row)

• 给2000万个用户打标，每个用户64个随机标签，其中男、女各一半。示例如下：

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();

• 创建人群标签字段倒排索引。示例如下：

create index idx_t_user_tags_1 on t_user_tags using gin (tags);

• 查询包含标签1和3的人群。示例如下：

1、人群数量
select count(uid) from t_user_tags where tags @> array[1,3];
2、提取人群ID
select uid from t_user_tags where tags @> array[1,3];

• 查询包含标签1、3、10或200的人群。示例如下：

1、人群数量
select count(uid) from t_user_tags where tags && array[1,3,10,200];
2、提取人群ID
select uid from t_user_tags where tags && array[1,3,10,200];

方案三

说明 仅PostgreSQL支持方案三。MySQL不支持位图功能。

方案三通过roaringbitmap插件实现快速查询，相关说明请参见使用roaringbitmap插件。

表结构如下：

KEY：标签ID
VALUES: 用户bitmap

索引如下：

标签ID字段: Btree索引

搜索方法如下：

聚合bitmap: 与、或、非
and_agg(bitmaps) where KEY in (标签s) -- 与
or_agg(bitmaps) where KEY in (标签s) -- 或
except(bitmap1,bitmap2) -- 非

• 方案优势
  • 表存储占用空间小。
  • 索引存储占用空间小。仅需一个Btree索引，索引记录数少（有多少标签，就有多少条记录，通常标签数在百万以内）。
  • 新增一个用户群体标签时，不需要更新大量数据，仅需新增一条人群的bitmap记录。
  • 查询性能极好。
• 方案缺陷
  • bitmap最大长度为1GB，用户数超过长度时需要使用offset，方法如下：

offset0_bitmap, offset1gb_bitmap, ... 

• 用户ID需要是数字（建议连续数值），如果没有数值型UID，需要建立映射表。

操作示例

说明

• 当UID超过40亿（int4）时，使用offset转换。转换方法请参见UID溢出时的处理方法。
• 更多roaringbitmap的使用方法请参见pg_roaringbitmap。

• 安装插件。示例如下：

create extension roaringbitmap;

• 创建用户标签bitmap表。示例如下：

create table t_tag_users (
  tagid int primary key,   -- 用户标签（人群）id 
  uid_offset int,          -- 由于userid是int8类型，roaringbitmap内部使用int4存储，需要转换一下。   
  userbits roaringbitmap,     -- 用户id聚合的 bitmap  
  mod_time timestamp       -- 时间 
);

• 生成UID标签bitmap表。示例如下：

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; 

• 查询包含标签1和3的人群。示例如下：

1、人群数量
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;
2、提取人群ID
select uid_offset,rb_and_agg(userbits) as ub 
from t_tag_users 
where tagid in (1,3) 
group by uid_offset;

• 查询包含标签1、3、10或200的人群。示例如下：

1、人群数量
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;
2、提取人群ID
select uid_offset,rb_or_agg(userbits) as ub 
from t_tag_users 
where tagid in (1,3,10,200) 
group by uid_offset;

方案对比

说明 方案使用的MySQL 8.0和PostgreSQL 12实例规格均为8核CPU、32GB内存、1500GB ESSD云盘。

总结

RDS PostgreSQL 12支持位图功能（roaringbitmap插件），可以高效生成、压缩、解析位图数据，支持最常见的位图聚合操作（与、或、非、异或），满足您在亿级以上用户、千万级标签的大数据量下实时精准营销、快速圈选用户的需求。

相比MySQL的方案，RDS PostgreSQL方案优势非常明显，是一个低成本、高效率的解决方案。

视频教程

实时精准营销

优惠活动

新用户9.9元试用、老用户5折升级。