PostgreSQL 消息平台实践
背景
一个多渠道消息平台的数据库设计。
业务规则
1、消息发送给最终用户,一则消息可以发送给多个社交软件平台(因为一个用户可能使用多个软件平台(比如旺旺,WEB版旺旺,淘宝。。。))。
- 使用数组存储 社交软件平台
2、一条消息在某社交软件平台已读,则该消息在其他社交软件平台也需要为已读。因为同一条消息,对一个人来说,当然是任意平台已读都认为是已读。
- 使用一个状态字段,标识是否已读
3、消息按照类型展示,透视未读数(统计什么类型的消息用户读的多,什么类型的消息用户读的少),或者按人查询未读数(当用户登陆时,查询未读消息有多少条)
- 聚合查询,或者按UID的简单查询
4、消息存在有效期(30天,大概6亿条消息),过期不管是否已读,均删除(当然也可以设计为未读则不删除,看需求)
-
数据老化(定期DELETE),使用任务调度。不要大批量删,会容易膨胀。
-
[《在PostgreSQL中实现update delete limit - CTID扫描实践 (高效阅后即焚)》](../201608/20160827_01.md) -
或者使用时间分区表。每天一个分区。建议用户自己维护分区,否则也可以用以下方法自动加分区(但是不推荐,自动的东西,而且在数据库里面自动的东西,不好维护)。
- 《PostgreSQL 自动创建分区实践 - 写入触发器》
业务特点&技术要求
1、写入操作:消息新增(比如消息每日增量2KW)、消息状态更新(有一定的已读比例)。
2、查询请求:(查询峰值QPS 5K左右)
- 很容易满足
3、查询条件维度:(按社交软件平台、按消息类型、按状态获取消息列表、最近一条消息、统计未读数。。。。)。
4、新增社交软件平台:社交软件平台增加时需要易于扩展。
- 使用数组存储社交软件平台,扩展性好,无需变更结构
设计
表结构
建表
create table tbl_msg (
mesgid int8 primary key, -- 消息ID
uid int8, -- 用户ID
msgtype int2, -- 消息类型
plat int2[], -- 发给了哪些 社交软件平台,数组类型
status boolean not null default false, -- 阅读状态
content text, -- 内容
crttime timestamp(0) not null, -- 消息创建时间
modtime timestamp(0) -- 消息状态修改时间
);
优化,可以按消息类型哈希分区。减少扫描量
《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》
索引
按需创建索引。
create extension btree_gin;
1、按社交软件平台查询、
create index idx_tbl_msg_1 on tbl_msg using gin (plat) where status=false;
-- 查询社交平台,某个消息类型下,未读消息
如果所有状态都想查询,则不需要 where status=false; 并把status放到索引字段里面
create index idx_tbl_msg_1 on tbl_msg using gin (plat,status);
2、按消息类型、
create index idx_tbl_msg_2 on tbl_msg (uid, msgtype) where status=false;
3、按状态获取消息列表、
create index idx_tbl_msg_3 on tbl_msg (status,uid,crttime);
4、最近一条消息、
create index idx_tbl_msg_4 on tbl_msg (uid,crttime);
查询SQL
1、按社交软件平台查询、
select count(*) from tbl_msg where plat @> array[?,?,...] and status=false;
清理过期消息
单表的情况下,如何清理消息?
[《在PostgreSQL中实现update | delete limit - CTID扫描实践 (高效阅后即焚)》](../201608/20160827_01.md) |
压测
1、每秒的写入量、更新量。10万行/s左右。
2、读取,简单SQL加分析SQL。 QPS 2万以上。
小结
用到的PostgreSQL数据库特性
1、数组类型,存储社交软件平台。
2、update,delete limit,删除过期数据
3、GIN索引,支持数组类型的高效过滤
4、分页(优化)
《论count与offset使用不当的罪名 和 分页的优化》
5、多核并行计算。数据库会根据SQL的成本、NODE自动规划是否使用并行计算,实时分析型的SQL请求非常有效。
《HTAP数据库 PostgreSQL 场景与性能测试之 23 - (OLAP) 并行计算》
6、丰富的索引接口
《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap…)的方法》