作者
digoal
日期
2018-08-03
标签
PostgreSQL , 消息平台 , 数组 , in any array
背景
一个多渠道消息平台的数据库设计。
业务规则
1、消息发送给最终用户，一则消息可以发送给多个社交软件平台（因为一个用户可能使用多个软件平台（比如旺旺，WEB版旺旺，淘宝。。。））。
- 使用数组存储 社交软件平台
2、一条消息在某社交软件平台已读，则该消息在其他社交软件平台也需要为已读。因为同一条消息，对一个人来说，当然是任意平台已读都认为是已读。
- 使用一个状态字段，标识是否已读
3、消息按照类型展示，透视未读数(统计什么类型的消息用户读的多，什么类型的消息用户读的少)，或者按人查询未读数（当用户登陆时，查询未读消息有多少条）
- 聚合查询，或者按UID的简单查询
4、消息存在有效期（30天，大概6亿条消息），过期不管是否已读，均删除（当然也可以设计为未读则不删除，看需求）
-
数据老化（定期DELETE），使用任务调度。不要大批量删，会容易膨胀。
-
或者使用时间分区表。每天一个分区。建议用户自己维护分区，否则也可以用以下方法自动加分区（但是不推荐，自动的东西，而且在数据库里面自动的东西，不好维护）。
业务特点&技术要求
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扫描实践 (高效阅后即焚)》
压测
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...)的方法》
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
