PostgreSQL 消息平台实践

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 标签PostgreSQL , 消息平台 , 数组 , in any array背景一个多渠道消息平台的数据库设计。业务规则1、消息发送给最终用户,一则消息可以发送给多个社交软件平台(因为一个用户可能使用多个软件平台(比如旺旺,WEB版旺旺,淘宝。

标签

PostgreSQL , 消息平台 , 数组 , in any array


背景

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

业务规则

1、消息发送给最终用户,一则消息可以发送给多个社交软件平台(因为一个用户可能使用多个软件平台(比如旺旺,WEB版旺旺,淘宝。。。))。

  • 使用数组存储 社交软件平台

2、一条消息在某社交软件平台已读,则该消息在其他社交软件平台也需要为已读。因为同一条消息,对一个人来说,当然是任意平台已读都认为是已读。

  • 使用一个状态字段,标识是否已读

3、消息按照类型展示,透视未读数(统计什么类型的消息用户读的多,什么类型的消息用户读的少),或者按人查询未读数(当用户登陆时,查询未读消息有多少条)

  • 聚合查询,或者按UID的简单查询

4、消息存在有效期(30天,大概6亿条消息),过期不管是否已读,均删除(当然也可以设计为未读则不删除,看需求)

业务特点&技术要求

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

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

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
存储 SQL Cloud Native
深入了解云原生数据库CockroachDB的概念与实践
作为一种全球领先的分布式SQL数据库,CockroachDB以其高可用性、强一致性和灵活性等特点备受关注。本文将深入探讨CockroachDB的概念、设计思想以及实践应用,并结合实例演示其在云原生环境下的优越表现。
|
1月前
|
Cloud Native 关系型数据库 大数据
CockroachDB:云原生数据库的新概念与实践
本文将介绍CockroachDB,一种先进的云原生数据库,它具备分布式、强一致性和高可用性等特点。我们将探讨CockroachDB的基本原理、架构设计以及在实际应用中的种种优势和挑战。
|
1月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版并行查询技术探索与实践
PolarDB MySQL版并行查询技术探索与实践 PolarDB MySQL版在企业级查询加速特性上进行了深度技术探索,其中并行查询作为其重要组成部分,已经在线稳定运行多年,持续演进。本文将详细介绍并行查询的背景、挑战、方案、特性以及实践。
234 2
|
1月前
|
监控 关系型数据库 分布式数据库
【PolarDB 开源】PolarDB HTAP 实践:混合事务与分析处理的性能优化策略
【5月更文挑战第21天】PolarDB开源后在HTAP领域表现出色,允许在同一系统处理事务和分析工作负载,提高数据实时性。通过资源分配、数据分区、索引优化等策略提升性能。示例代码展示了创建和查询事务及分析表的基本操作。PolarDB还提供监控工具,帮助企业优化系统并应对业务变化。其HTAP能力为开发者和企业提供了强大支持,推动技术进步,加速数字化时代的业务发展。
395 1
|
30天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
202 1
|
26天前
|
安全 关系型数据库 分布式数据库
【PolarDB 开源】PolarDB 在金融行业中的实践:高可用与安全合规解决方案
【5月更文挑战第28天】PolarDB,一款适用于金融行业的强大数据库,以其高可用性和安全合规性脱颖而出。通过多副本机制和自动故障转移确保业务连续性,结合严格的访问控制和数据加密技术保护信息安全。在实际应用中,如银行核心系统,PolarDB 负责处理海量交易数据,同时支持主从架构以备故障切换。此外,设置强密码策略和加密存储确保合规性,并通过监控预警及时解决问题。随着金融科技发展,PolarDB 将在云原生架构和人工智能等领域发挥更大作用,助力金融行业创新与进步。
102 0
|
28天前
|
负载均衡 关系型数据库 分布式数据库
【PolarDB开源】PolarDB读写分离实践:优化读取性能与负载均衡策略
【5月更文挑战第26天】PolarDB是云原生关系型数据库,通过读写分离优化性能和扩展性。它设置主节点处理写操作,从节点处理读操作,异步复制保证数据一致性。优化读取性能的策略包括增加从节点数量、使用只读实例和智能分配读请求。负载均衡策略涉及基于权重、连接数和地理位置的分配。实践示例中,电商网站通过主从架构、只读实例和负载均衡策略提升商品查询效率。PolarDB的读写分离与负载均衡为企业应对大数据和高并发提供了有效解决方案。
137 0
|
10月前
|
SQL 关系型数据库 MySQL
PolarDB-X 针对跑批场景的思考和实践
金融行业和运营商系统,业务除了在线联机查询外,同时有离线跑批处理,跑批场景比较注重吞吐量,同时基于数据库场景有一定的使用惯性,比如直连MySQL分库分表的存储节点做本地化跑批、以及基于Oracle/DB2等数据库做ETL的数据清洗跑批等。
PolarDB-X 针对跑批场景的思考和实践
|
1月前
|
关系型数据库 MySQL 分布式数据库
PolarDB auto_inc场景下的性能优化实践
PolarDB auto_inc场景下的性能优化实践 在数据库的使用场景中,并发插入数据或并发导入数据场景是最常见的。针对这一场景,PolarDB MySQL版进行了深度性能优化,以提高插入性能。本文将详细介绍PolarDB在auto_inc场景下的性能优化相关内容。
70 2

相关产品

  • 云原生数据库 PolarDB