PostgreSQL 消息平台实践

less than 1 minute read

背景

一个多渠道消息平台的数据库设计。

业务规则

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、丰富的索引接口

《PostgreSQL 9种索引的原理和应用场景》

《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap…)的方法》

Flag Counter

digoal’s 大量PostgreSQL文章入口