【重新发现PG之美】 - 系列视频

2022-04-22 13:40:54

背景

1、重新发现PG之美 - 1 预测类应用

使用PG的统计学聚合函数, 结合统计学的线形回归知识, 可以用来实现股价、用户行为、天气等数据的预测.

PG不仅支持简单的一元回归, 还能通过madlib, plR等实现多元、多重回归模型.

视频回放:  https://www.bilibili.com/video/BV1mA411g7XJ/

2、重新发现PG之美 - 2 exclude 排他!

exclude排他约束是PG的独特约束, 属于动态约束, 通过GiST索引实现, 采用对等操作符进行检查.

例如会议室预定系统:

避免会议室时间相交的异常问题、

避免行政区块存储多边形相交的问题、

排除品类系统中一个物品被误入多个品类的问题.

视频回放:  https://www.bilibili.com/video/BV1ho4y11731/

3、重新发现PG之美 - 3 range 200x!

解决全球化部署、多中心业务根据来源IP智能路由到就近机房的智能DNS性能问题, 200倍提升不是梦.

PG 支持了Range类型, 一个字段可以存储传统数据库2个字段才能存储的数据, 同时比2个字段的between and用法性能提升200倍.

原因是PG采用了range gist索引, 比btree的大范围链表扫描采用了更有效的访问路径, 二维收敛, 访问block急剧减少, 性能爆炸式提升.

视频回放:  https://www.bilibili.com/video/BV13q4y1j7Ta/

4、重新发现PG之美 - 4 随机漫步踏浪而来

在一些论坛、短视频业务中, 编辑精选和地域或大范围精选的内容会采用随机推荐的方式推送给客户.

随机查询就有了高并发、低延迟的需求, 然而通用的order by random()随机方法性能太烂, 无法满足需求.

PG 提供了tablesample method(para)方法, 能够以几千倍的性能满足高并发需求.

视频回放:  https://www.bilibili.com/video/BV1cy4y137WU/

5、重新发现PG之美 - 5 在不确定的世界寻找答案

经典物理的世界, 似乎一切都是可预测的, 而量子理论告诉我们, 世界充满不确定性:

股票哪个会涨?

房价会不会跌?

美元还会继续贬值吗?

哪些是我的意向客户?

你有多大的概率会下单?

然而现在的经典数据库只能给我们确定的答案, =, >, <, >=, <=, @>, @@, ||, &, !, ......  , 怎么办?

PG 向量检索+近似库datasketch, 在不确定世界寻找答案!

视频回放:  https://www.bilibili.com/video/BV12Q4y1R771/

6、重新发现PostgreSQL之美 - 6 index链表跳跳糖

CTE 递归语法是PG 8.4引入的功能, 至今已经10多年, 非常文档.

CTE 递归可以解决很多问题: 时序场景取所有传感器最新的value, 图式数据的搜索(一度人脉,N度人脉,最近的路径关系), 树状数据的累加分析, 知识图谱, 去稀疏数据的唯一值等.

使用CTE递归比通用的方法通常有数百倍的性能提升.

视频回放: https://www.bilibili.com/video/BV18K4y1R7fb/

7、重新发现PostgreSQL之美 - 7 垂帘听政 异步消息

场景:

  • 重要数据在写入、更新、删除时 实时告警或转存
  • 流式数据 (公务车电子围栏、刑侦数据探针、股票数据规则探针、服务器运行情况) 实时预警或事件触发
  • 危险操作 (DDL) 异步监控

规则 + 异步消息的优势: 1、通过规则过滤掉不需要写入的正常数据, 由于业务正常数据通常占比在99%以上, 从而大幅减轻写入量. 2、传统的利用定时器查询所有数据去发现问题, 还需要在时间、VAL、SID等层面去建立索引, 消耗大量存储, 同时索引增加写入RT, 性能下降. 规则+异步完全规避这个问题. 3、可以实时发现并预警或触发其他动作

视频回放:  https://www.bilibili.com/video/BV1Nq4y1j79T/

8、重新发现PostgreSQL之美 - 8 轨迹业务IO杀手克星index include(覆盖索引)

场景痛点:

  • 轨迹类业务, 一个轨迹由多个点组成, 每个点的ROW写入散落到不同的PAGE, 查询一条轨迹可能要回表访问上百千个PAGE, 号称IO杀手.

业务:

  • 共享单车、巡逻车、公务用车、网约车、金融行业股票数据、物联网行业传感器数据等.

PG index include (覆盖索引)功能:

  • 重组存储结构, 按指定维度聚集.
  • 叶子结点存储include column value, 无需回表(轨迹数据都是append only的, VM bit全部都是clean page, 因此无需回表).

视频回放: https://www.bilibili.com/video/BV1tV41177rY/

9、重新发现PostgreSQL之美 - 9 面向多值列的倒排索引 GIN|RUM

场景:

通用业务, 分词查询诉求.

挑战:

传统数据库没有分词、实时全文检索索引功能, 需要将数据同步到搜索引擎, 这种解决方案的弊端:

  • 研发成本增加、
  • 软硬件成本增加、
  • 系统问题增多(同步延迟问题、同步异常问题、同步一致性问题)、
  • 开发灵活性下降(无法同时过滤分词条件与表的其他条件, 需要业务层交换数据)
  • 同时过滤分词条件与表的其他条件后, 无法有效的按RANK排序分词相似性

PG 解决方案:

  • 1、倒排索引GIN:
    • 支持多值类型的按元素检索: tsvector, array, json, xml, hstore, 任意字段组合搜索
    • 一对多的数据模型
  • 2、增强倒排索引RUM, RANK 加速方案:
    • RUM索引在posting list里面, 每个行号后面附加addon内容(文本向量的对应位置信息), 同时支持自定义addon信息.
    • addon的内容优势: 不需要回表搜索tuple内容. 降低IO, 提高性能.

视频回放: https://www.bilibili.com/video/BV1CA411g7oK/

10、重新发现PostgreSQL之美 - 10 内卷 & 大禹治水

场景:

内卷现象, 供不应求(高峰期打车、电商秒杀), 热点数据更新

社会现象: 资源有限而需求无限的情况(春运时期的火车票、学生报补习班、企业里面的资源地盘争夺等)

挑战:

当系统中出现热点row时, 意味着大量的并发请求更新同一行数据, 因为数据库最小粒度的锁为行锁, 所以这些并发请求只能串行执行,

一个会话在更新的时候其他所有会话都处于等待状态, 可能导致连接打爆, 其他会话连不进来引起雪崩.

如果被秒杀的商品库存只有10个, 那么实际上只有10个请求能达成交易, 其他等待中的会话都属于无用功. 浪费大量的连接和等待时间.

PG 解决方案:

大禹治水(疏导、消灭无用等待):

  • SKIP LOCKED,
  • advisory lock

视频回放: https://www.bilibili.com/video/BV1cg411G7rx/

11、重新发现PostgreSQL之美 - 11 时空轨迹系统 新冠&刑侦&预测

1、时空轨迹的业务场景:

疫情防控:

  • 根据病毒携带者的轨迹快速找到密切接触者. 轨迹距离计算
    • 1度、2度、N度接触者

*刑侦:

  • 同行人分析: 轨迹相似度
  • 密切接触分析: 轨迹距离

预测:

  • 根据轨迹数据建模, 预测*、时空数据热点分布等, 用于智慧城市管理、智慧交通等.
  • 与商业结合, 时空轨迹+用户画像.

2、业务挑战:

数据量大, 数据与业务割裂, 计算纯靠coding实现, 开发效率低下, 运行效率良莠不齐.

3、对时序数据库系统的诉求:

写入吞吐要求高, 延迟低, 查询要求响应快, 压缩比要求高(节省存储成本), 算法的扩展能力要求强.

4、PG解决方案:

  • 轨迹类型:
    • geometry
  • 轨迹分析函数:
    • 最近距离计算
    • 相似度计算
    • 相遇时间计算
    • 相遇可能性判断
    • 轨迹有效性判断
  • 压缩能力
  • sharding 能力(citus, timescaledb, ymatrix, POLARDB 等)

ST_IsValidTrajectory — Returns true if the geometry is a valid trajectory.  

ST_ClosestPointOfApproach — Returns the measure at which points interpolated along two trajectories are closest.  

ST_DistanceCPA — Returns the distance between the closest point of approach of two trajectories.  

ST_CPAWithin — Returns true if the closest point of approach of two trajectories is within the specified distance.  

节点内并行 (PG parallel scan, since PG 9.6)

多节点并行 (fdw async append, since PG 14)

5、阿里云 PG Ganos 支持更加丰富的轨迹计算:

  • 轨迹压缩
  • 空间关系判断
  • 空间处理
  • 空间统计
  • 时空关系判断
  • 时空处理
  • 时空统计
  • 距离测量
  • 相似度分析

视频回放: https://www.bilibili.com/video/BV1eV411x7KW/

12、重新发现PostgreSQL之美 - 12 SaaS & 多租户 捣蛋鬼,你揍开

场景:

  • 游戏、SaaS行业. serverless 场景: 在一个企业内部, 业务线非常多,
  • 在同一个实例中有多个业务共同使用时,
  • 分析师、DBA或者运营人员有偶尔使用数据库的需求.

挑战:

  • 为每个业务分配一个数据库实例存在的问题: 资源浪费(每个实例在内存、空间方面的浪费)、弹性较差(创建资源慢、扩容缩容慢).
  • 多个业务共享一个实例存在的问题: 资源争抢、干扰、抖动、安全风险增加,
  • DBA或分析师人为的大查询可能将资源耗光, 影响在线业务

PG 方案:

  • 按来源IP、USER、DBNAME、application_name等信息来区分用户和业务
  • 结合cgroup隔离业务的cpu、io、网络等资源使用率, 防止干扰

视频回放: https://www.bilibili.com/video/BV1SQ4y1X7oC/

13、重新发现PostgreSQL之美 - 13 brin 时序索引

场景:

  • 物联网、游戏、金融、证券、车联网等场景, appendonly的高并发数据写入, 需要高效的按时间区间进行数据统计分析.

挑战:

  • 写入量大, 传统btree索引对写入的RT影响大, 导致性能下降严重.
  • 普通btree索引存储空间占用较大.
  • 普通btree索引采用链表存储, 逻辑上有序, 而物理离散, 加上IO有prefetch, 使得按btree的索引范围查询将占用大量IO通道. 范围查询效率差.

PG解决方案:

  • PG采用堆表存储, 使用时序索引, 每连续的N个数据块存储min,max val. 时序索引只有btree的几百分之一大小. 并且支持快速的范围查询.
  • 其他数据库产品采用聚集存储无法实现时序索引, 因为聚集存储本身必须按PK组织, 在数据块层面已经没有时序顺序了.

视频回放:  https://www.bilibili.com/video/BV1B64y1k72g/

14、重新发现PostgreSQL之美 - 14 bloom 布隆过滤器索引

场景:

分析业务, 任意字段、任意维度组合, 组合等值查询.

where a=? and b=? or c=? . 其他组合 ab ac ad bc bd bcd bdef def ... 每个字母代表一个字段.

在电商、金融等拖拽式实时分析场景中尤为常见.

挑战:

由于查询维度非常多, 完全不可控, 如果每个维度都预计算, 会导致结果数据量指数级增加.

如果为每个查询维度都创建一个索引, 那么会有N!+1个索引. 例如5个字段的任意组合有121种, 需要建121个索引, 完全不现实.

PG 解决方案:

使用bloom布隆过滤索引. 每个value被hash计算后映射到若干个bit位, 这些bit位被设置为1表示包含这个value.

一个索引即可满足任意维度的组合等值搜索.

视频回放:  https://www.bilibili.com/video/BV1c64y1C7HK/

15、重新发现PostgreSQL之美 - 15 沙盘推演, 权力的游戏

有数据说数据, 没数据说案例, 没案例说逻辑, 没逻辑谈理想. 已经成为数字时代的职场生存法则.

  • 要陈述一个观点或结论, 没有数据支撑说话没分量, 说明你没经过仔细分析, 没过脑子.
  • 实在拿不到数据, 你总得拿点案例出来, 否则就是在空谈, 没有支点.
  • 没案例, 总得有底层逻辑吧, 逻辑能自洽, 也是说得通的.
  • 如果连逻辑都没有想过, 那你只能用故事、理想或者理念来感染人, 如果你能忽悠(感染)成功, 并且最后确实也成功了, 也许能表达为因为相信所以看见.

场景:

  • 项目上线前, 申请资源, 如何评估需要多少资源?
  • 做IT部门的全年预算?
  • 如何做到精准, 有理有据有节?

挑战:

  • 项目往往只有业务指标(应该叫目标, 和IT预算精算还差了十万八千里), 靠经验或拍脑袋来确定IT预算, 上线后发现资源不足或严重超过预期, 造成铺张浪费.
  • 难以模拟和快速产生真实的业务数据.
  • 难以模拟真实的业务交互行为.
  • 难以捕捉和分析性能问题的原因.

PG解决方案:

  • pgbench, 支持沙盘推演, 避免纸上谈兵、假大空.
    • 一个可以近乎真实的模拟业务上线后的压力的内置benchmark program.
    • 内置多种数据生成算法, 多种模拟业务和数据库交互的command,
    • 同时支持根据业务定制化压测程序, 模拟真实的业务交互行为.
  • awr, pg_stat_statements, 捕获和分析业务瓶颈
  • 快速构建海量测试数据

视频回放: https://www.bilibili.com/video/BV1Zq4y1j7xs/

16、重新发现PostgreSQL之美 - 16 like '%西出函谷关%' 模糊查询

场景: 1、电商 商品、店铺、描述等. 搜索框 模糊查询 2、企业ERP、社交APP、网站 内容搜索框, 模糊查询

挑战: 传统数据库不支持模糊查询, 需要全表扫描, 性能极差, 少量并发查询极可能导致雪崩. 通常的解决方案需要将数据同步到搜索引擎, 这种解决方案的弊端:

  • 研发、软硬件成本增加、
  • 系统问题增多(同步延迟问题、同步异常问题、同步一致性问题)、
  • 开发灵活性下降(无法同时过滤模糊查询条件与表的其他条件, 需要业务层交换数据)
  • 不支持同时过滤like与其他条件

PG 解决方案:

  • 倒排索引GIN + btree_gin: 支持模糊查询+任意字段组合过滤.

视频回放: https://www.bilibili.com/video/BV1144y1z7MM/

17、重新发现PostgreSQL之美 - 17 物以类聚 人以群分

场景:

  • 数据分类, 直面精准运营, 产生价值.

挑战:

  • 传统数据库只有1种分类方法,width_bucket,而且bucket必须均分,而数据多数遵循28法则,我们需要的是抓大放小,所以均匀的bucket不能对数据很好的诠释和透视.
  • 传统数据库只能对1个维度进行分类,无法支持多个维度的分类需求.
  • 高级分析必须将数据拉到程序端计算, 拉取数据效率极低, 增加了运行时间.

PG解决方案:

  • width_bucket支持自定义bucket窗口大小,支持抓大放小.
  • 通过kmeans分类方法, 可以支持多维度的数据分类, 满足业务的分类需求(例如从喜好、消费情况、活跃时间区间、性别、地域等等多种维度精准定位目标群体)
  • 通过madlib扩展库, 能在数据库中整合更加高级的机器学习能力, 同时支持plpython扩展机器学习算法, 支持业务可定制的算法诉求.
  • 在数据库中分类比在业务中分类效率更高, 因为不需要move data, 同时PG支持greenplum这样的MPP形态, 完全不需要担心算力和存储问题.
  • https://developer.aliyun.com/article/128017
  • http://madlib.apache.org/product.html
  • https://github.com/digoal/blog/blob/master/201508/20150817_01.md

视频回放: https://www.bilibili.com/video/BV1uq4y1L7u6/

18、重新发现PostgreSQL之美 - 18 火眼金睛

场景:

电商、社交、姻缘、算命、媒体、论文、打击创新和盗版、精准营销

快速找出 相似、不相似

挑战:

只能全表扫描, 性能极差. 只能堆机器, 而且无法满足实时的需求.

PG 解决方案:

通过数组支持特征字典, 倒排索引增强, BLOCK位图合并, 快速收敛到少量block, 亿级画像毫秒响应.

https://github.com/jirutka/smlar

https://github.com/digoal/blog/blob/master/201708/20170804_01.md

https://github.com/digoal/blog/blob/master/201701/20170116_03.md

https://github.com/digoal/blog/blob/master/201701/20170116_04.md

https://github.com/digoal/blog/blob/master/201711/20171107_18.md

视频回放: https://www.bilibili.com/video/BV1Kf4y187xC/

19、重新发现PostgreSQL之美 - 19 困扰古惑仔和海盗的数学问题

场景:

纵观增长黑客、等营销类书籍. 目前互联网行业存在一种随处可见的商业模式, 例如:

  • 游戏工会, 给工会成员提供好的交流、交易平台, 同时工会通过发展会员数来提高工会本身的竞争力, 为了刺激已有会员去拉新的会员, 工会与成员, 成员与成员之间存在网状关系, 拉来会员可以得到奖励.
  • 直播行业工会。工会为主播提供好的流量平台, 主播运营等服务. 大工会挂小工会等多层结构, 流量平台与大工会、大工会与小工会、小工会与主播, 主播与主播之间存在网状关系.
  • 共享出行, 代理商, 加盟商层层关系.
  • 零售行业, 小型超市, 餐饮行业, 加盟、代理。
  • 化妆品行业。
  • 保险行业。
  • 在线教育行业。

挑战:

明细多, 数据量大慢的要命, 一个晚上都跑不出分佣数据, 更无法支持实时分佣

PG解决方案:

1、递归

2、ltree

例子:

《森林状图式数据(树状) 的 高效生成方法》

《PostgreSQL 递归应用实践 - 非“传销”的高并发实时藤、树状佣金分配体系》

https://www.postgresql.org/docs/14/ltree.html

视频回放:  https://www.bilibili.com/video/BV1ff4y1b73U/

20、重新发现PostgreSQL之美 - 20 为什么啤酒&纸尿裤最搭

场景:

  • 电商、零售等行业, 根据用户购物车、订单等数据找到最合理的搭配组合. 用于引导营销策略.
    • 或者以用户最近N笔或N天的订单内的所有商品作为一个大的group
  • 根据用户评论涉及的关键词, 找到最佳搭配关键词. 用于引导品牌策略.

挑战:

  • 每一个组合都是一组商品、标签或关键词. 相当于需要从现有的海量组合中找到高频组合(最搭组合).
  • 传统数据库不支持多值列(数组), 展开成多条记录数据量至少上升1个量级, 而且需要Join聚合才能得到最佳组合, 效率极差.
  • 在划窗分析需求中, 需要大量历史数据的基础进行计算, 性能差

PG解决方案:

  • 1、内置数组, 数据量节省至少1个量级.
  • 2、内置数组倒排索引, 快速定位想要得到的搭配组合.
  • 3、内置数组的元素级别统计信息, 可以利用统计信息快速定位到最佳组合.
  • 4、datasketches 近似解, 支持海量数据毫秒级别实时输出搭配组合.
  • 5、使用topn的方法, 可以每天为每个商品存储1条记录, 这样就能实行实时滑窗分析, 也是传统数据库无法高效实现的.

视频回放: https://www.bilibili.com/video/BV13B4y1M7K4/

21、重新发现PostgreSQL之美 - 21 探访宇航员的食物

场景:

物联网, 金融, 社交, 电商, 电力, 游戏等行业. 大量传感器、业务日志、FEED等数据.

因为数据分析、或行业合规, 要求日志类流水类数据需要永久保存

最近的数据需要高效点查, 高效更新, 高速写入

历史的数据需要大范围快速分析计算, 高压缩比

挑战:

1、历史数据量大, 存储耗费巨大

2、无法同时解决高并发写入、更新、点查同时快速分析和高压缩比的问题

PG解决方案:

  • WO写优化、RO读优化
  • RO(读优化)存储高压缩比
  • 向量化计算性能提升一个数量级
  • 分区支持行、列混合存储
  • 通过parser analyze hook,扩展向量类型,扩展向量算子,基于代价的自动化JIT选择,在同一个实例中支持oltp, olap两种模型.

视频回放: https://www.bilibili.com/video/BV1Ly4y1u7AM/

22、重新发现PostgreSQL之美 - 22 黄帝内经

场景:

电商、社交、SAAS软件行业.

用户表、租户表、用户行为表.

挑战:

按企业、appid等hash分区可能出现倾斜, 例如某些APPID的数据量可能非常大, 热数据可能在同一个分区, saas的某些大企业可能被分到同一分区, 使得数据倾斜.

PG 解决方案:

非对称分区方法.

视频回放: https://www.bilibili.com/video/BV13B4y1M7bz/

23、重新发现PostgreSQL之美 - 23 彭祖的长寿秘诀

场景:

  • 论坛、短视频、社交、电商等推荐业务.
  • 在推荐的过程中要过滤已读列表.

挑战:

  • 已读列表巨大, 普通数据库没有多值列类型, 每个已读ID需要存储1条.
  • 使用not in过滤, 性能极差.

PG 解决方案:

  • 1、多值列, 一个用户已读只需要存储1条
  • 2、datasketch 近似类型, 几KB空间可以存储上亿唯一值.
  • 3、压缩存储多值列 roaringbitmap类型, 效率和空间的平衡.
  • 4、使用partial index, 拆散过滤列表, 降低每次请求的无效数据.

视频回放: https://www.bilibili.com/video/BV13v411p7cz/

24、重新发现PostgreSQL之美 - 24 滑动窗口分析 2000x

场景:

游戏、社交、电商场景.

流失用户、新增用户、UV计算.

滑动计算, 任意窗口.

挑战:

数据量大、计算量大

传统方案需要记录明细, 才能支持滑动计算

PG解决方案:

采用HLL类型不需要存储明细, 支持滑动, 交、并、差计算.

视频回放: https://www.bilibili.com/video/BV1t44y167mj/

25、重新发现PostgreSQL之美 - 25 最强大脑题目 泰森多边形(空间战略布局问题)

场景:

  • 电商、手机、运营商、小米、化妆品 等加盟店, 每个门店应该覆盖哪些商圈最合理. KPI如何规划, 空间战略如何布局比较合理?
  • 运营商基站, 每个基站调多大功率最合理.

挑战:

点多, 算法复杂.

PG解决方案:

冯诺洛伊图, 泰森多边形为以上需求提供了理论基础.

内置GIS系统, 支持根据离散点生成泰森多边形. 每个基站代表多边形的离散点.

基站功率覆盖泰森多边形的空间范围即可.

给加盟店下达KPI的时候可以结合多边形内包含的POI(商圈、写字楼、住宅等人群)来实现.

视频回放: https://www.bilibili.com/video/BV1p44y167nA/

26、重新发现PostgreSQL之美 - 26 这个推荐算法价值1亿

场景:

短视频、电商、社交等基于标签的动态推荐.

挑战:

标签多, 按标签权重比例每个标签取N条, 与数据库的交互次数多.

已读列表巨大, 过滤已读采用传统not in资源消耗大.

效率低下.

PG解决方案:

效率提升1000倍, 可能价值1个亿.

技术点: 数组、自定义type、partial index降低无效过滤、hash subplan优化not in、array agg, jsonb

视频回放:  https://www.bilibili.com/video/BV1do4y1y7cq/

27、重新发现PostgreSQL之美 - 27 无中生有

场景:

  • 路径规划: 大型商业综合体, 自动驾驶, 虚拟现实.
  • 共享出行(拼车), 配送调度(餐饮、包裹).
  • 刑侦: 轨迹相遇分析

挑战:

  • 传统数据库不支持路网数据, 需要move data到应用端进行计算.
  • 现实中轨迹存在缺失点, 当需要分析轨迹相遇事件时, 准确度下降.
  • 在共享出行拼单, 包裹和餐饮配送中存在一到多, 多到多的路径规划, 非常复杂传统数据库不支持.

PG 解决方案:

  • 支持路网数据存储: 点, 线. 支持道路正、反权重来表示路段通畅度.
  • 内置多重路径规划算法, 支持one to one,one to many,many to one,many to many等算法.
  • 支持道路施工、限行、单行线、按车牌或车辆规格限行、限高、限宽等实现

视频回放: https://www.bilibili.com/video/BV17K4y197J8/

28、重新发现PostgreSQL之美 - 28 旋转门

视频回放: https://www.bilibili.com/video/BV1Eg411g7Sx/

场景:

物联网、金融、游戏、社交、电商等时序场景.

挑战:

数据量超大, 即使采用列存、数据压缩方式还是很大.

PG 解决方案:

有损压缩, 自定义旋转门算法.

《SQL流式案例 - 旋转门压缩(前后计算相关滑窗处理例子)》

《PostgreSQL 三角函数的用法举例 - 已知3点求任意夹角（旋转门续）》

《旋转门数据压缩算法在PostgreSQL中的实现 - 流式压缩在物联网、监控、传感器等场景的应用》

29、重新发现PostgreSQL之美 - 29 防止老年痴呆

用PG生成数独游戏, 来锻炼锻炼大脑吧

视频回放: https://www.bilibili.com/video/bv1wq4y1L7eN

30、重新发现PostgreSQL之美 - 30 打蛇打七寸

场景:

电商、网站、社交.如头条,微博等.

评价、文章、短语内容文本分析, 找出热词、关键词.

挑战:

传统数据库不支持文本分析, 需要拉去到程序内计算, 非常慢.

程序效率差(大多数不是C语言写的,性能弱上加弱).

PG 解决方案:

支持文本分析, 包括tf-idf等,支持自定义分词能力.

分词性能强大: macmini 2018 i5每秒分析速度360万词.

视频回放: https://www.bilibili.com/video/BV1Fy4y1g73D/

31、重新发现PostgreSQL之美 - 31 激光点云 LiDAR - 一尺之锤日取一半万世不竭

场景:

  • 3D打印、自动驾驶、测绘、3D城市、VR.
  • 激光雷达传感器.

挑战:

  • 数据量大, 要求高效写入和查询
  • 每个点存储的信息量大: 维度多(pcid表达格式,如三原色、材质、时间、位置等等)、且维度需要动态弹性, 存储和查询不方便
  • 存储空间消耗、计算挑战
  • 传统采用文件存储,
    • 数据效率低查询(为了得到一部分数据，您需要读取整个文件)
    • 无法并发性(一次只能有一个用户修改点/读取点)
    • 文件往往会有很多重复(每个工人都有自己的私有版本)，没有数据的安全性(文件可能会被处理破坏，很难管理谁访问什么)
    • 很难同时使用几个不同的点云
    • 难以与其他空间数据(矢量、栅格、图像)一起使用点云

PG 解决方案:

  • 支持点云插件
  • 包含点云类型、patch类型、支持空间属性、附加属性
  • 支持自定义点云格式
  • 包含相应的索引和操作符接口
  • 自动压缩存储(二进制)
  • 支持高效写入、查询

pgPointcloud提供了一种在postgres数据库中有效存储点的方法。

实际上，pgpPointCloud存储点组(pcPoints)，称为pcPatch，存储点组允许有效地压缩数据。

这个补丁有一个边界框，PostGIS空间特性可以使用它来大大加快查询速度。

pgPointcloud允许高效地查询非常大的点云。可以使用空间标准(哪些点在这个区域中)和点属性标准(between and)。

点云位于服务器中，它们可以用于处理或可视化，或流。

视频回放: https://www.bilibili.com/video/bv1Tw411f7mh

32、重新发现PostgreSQL之美 - 32 天不怕地不怕, 就怕老板问为什么?

场景:

  • 黑天鹅, 突发事件.
  • 负载突然升高, 响应突然变慢.

挑战:

  • 已经发生的突发事件, 如何分析其原因? 哪里有 数据、案例、逻辑 ?

PG 解决方案:

  • 性能洞察, 采样点(平均活跃会话数、等待事件、SQL细粒度)
  • stats 统计快照
  • auto_explain, 设置阈值, 超出记录执行和消耗详情.
  • 锁等待日志记录.

参考:

《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 过去任何时间段性能分析 [推荐、收藏]》

《PostgreSQL 函数调试、诊断、优化 & auto_explain & plprofiler》

《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》

https://www.postgresql.org/docs/14/auto-explain.html

https://www.postgresql.org/docs/devel/runtime-config-locks.html

视频回放:  https://www.bilibili.com/video/BV1sb4y1C72E/

33、重新发现PostgreSQL之美 - 33 刘姥姥进大观园

场景:

很多使用JSON类型的场景

  • 敏捷开发
  • 游戏、多媒体、教育、SAAS等行业的半结构数据
  • 离线业务的终端数据上传, 格式或标准没有定义

挑战:

  • JSON内容查询慢
  • JSON内容查询、部分内容的更新、删除麻烦,
  • 更新的隔离性和原子性要求高, 怎么实现?
  • JSON+其他字段组合搜索的问题怎么解决?

PG解决方案:

  • jsquery语法支持
  • gin index
  • partial index
  • btree_gin

参考:

https://www.postgresql.org/docs/devel/datatype-json.html

https://www.postgresql.org/docs/devel/functions-json.html

https://www.postgresql.org/docs/devel/btree-gin.html

https://www.postgresql.org/docs/devel/indexes-partial.html

视频回放: https://www.bilibili.com/video/BV15v411H7Sn/

34、重新发现PostgreSQL之美 - 34 月老 - 撮合系统

场景:

  • 交易撮合系统

挑战:

  • 有时间优先约束, 单只股票只能串行操作, 容易导致交易拥堵的瓶颈.
  • 一笔交易涉及多份, 例如200股, 存在部分交易, 部分撤单的需求.
  • 买卖双方的数量可能不匹配, 一笔撮合交易可能涉及多方.
  • 隐蔽问题: 价格挂太高或太低, 无法撮合时, 会导致资源浪费. 类似vacuum, 有长事务, 导致垃圾不能回收, 但是依旧要触发扫描.
  • 业务层实现撮合的挑战: 与数据库需要进行很多轮交互, 并且需要在事务中完成, 事务RT和死锁问题增加. 性能弱.

PG解决方案:

  • 业务逻辑放在函数内完成, 大幅降低应用与数据库交互, 降低RT.
  • advisory lock, skip locked等技术手段避免死锁.

视频回放: https://www.bilibili.com/video/BV1DV411s74x/

35、重新发现PostgreSQL之美 - 35 茅山道士 - rdkit 化学分析

场景:

  • 化学计算
  • 医药行业

挑战:

  • 化学键、分子式等非结构化数据在数据库中不好表达. 如何解决高效存储.
  • 不好表达也不好查询, 例如在化学指纹、化合物相似搜索、化合物分裂和溶解性包含等常见查询.

PG 解决方案:

  • rdkit 插件
  • 新增指纹类型、mol类型
  • 新增了分子式、指纹等类型对应运算符、索引、排序能力等

《PostgreSQL 化学分析 - RDKit Cartridge 1 - 环境部署》

《PostgreSQL 化学插件 - pgchem_tigress molecules rdkit》

视频回放: https://www.bilibili.com/video/BV1og411g7fN/

36、重新发现PostgreSQL之美 - 36 方世玉 安全第一

场景:

数据安全法颁布.

行业安全合规, 敏感信息加密: 手机号码、密码、邮箱、账号、资金等个人隐私加密

挑战:

防谁? 网络层窃取、内部工作人员窃取(DBA、SA)、机房或数据库服务方窃取

数据加密后: 不能被检索、不能排序. 使用不方便

PG 解决方案:

  • SSL
  • pgcrypto插件:
    • 加密效率、加密强度*, 不可逆(静态、动态)、可逆(对称、非对称)
  • RLS
  • RDS PG sgx 全加密数据库, 防一切问题: 网络、内部人员、机房或数据库服务方窃取

参考:

《PostgreSQL 数据库安全指南 - 以及安全合规》

《PG 目前的两种数据库用户密码存储方法, 建议选择scram-sha-256.》

《PostgreSQL pgcrypto 对称加密、非对称加密用法介绍》

https://help.aliyun.com/document_detail/144156.html

https://www.postgresql.org/docs/14/pgcrypto.html#PGCRYPTO-ICFC-TABLE

http://www.npc.gov.cn/npc/c30834/202106/7c9af12f51334a73b56d7938f99a788a.shtml

《为什么校园贷、注销校园贷、刷单、杀猪盘等电信诈骗这么猖獗? - 数据库安全解决方案(SSL)》

《[珍藏级] PostgreSQL ssl 证书配置 - 防止中间攻击者 - 以及如何使用证书无密码登录配置cert》

《EnterpriseDB & PostgreSQL RLS & Oracle VPD》

视频回放: https://www.bilibili.com/video/BV1gV411x7QY/

37、重新发现PostgreSQL之美 - 37 三焦者,决渎之官,水道出焉. FDW

场景:

多数据源的数据引用和互通. 数据流通产生价值.

挑战:

数据源多, 需要写大量ETL, 费研发资源, 开发成本高、数据重复存放, 存在ETL延迟等问题.

PG 解决方案:

FDW, 在PG内可以直接读写外部数据源. FDW为开放式接口, 可以对接一切数据源: 数据库、文件、NOSQL、LDAP、WWW、bigdata等.

统一SQL访问, 使用成本大幅降低, 会写SQL就可以访问一切数据源, 而且支持pushdown, 任意数据源包括本地表的相互JOIN等运算.

参考:

《从人类河流文明 洞察 数据流动的重要性》

https://wiki.postgresql.org/wiki/Foreign_data_wrappers

视频回放: https://www.bilibili.com/video/BV1xg41137Wf/

38、重新发现PostgreSQL之美 - 38 肝者,将军之官,谋虑出焉. 优化器

场景:

  • HTAP业务, 企业级OLTP业务.
  • ORM(自动生成SQL, 无法优化, 数十表的SQL JOIN)

挑战:

  • 优化器弱无法选择最佳执行路径,
  • 统计信息不及时, 无法得出最佳执行计划,
  • 环境: ssd, disk 多种不同硬件组成的表空间, 怎么才能算出最佳代价
  • 执行器弱支持的数据扫描、计算方法少的可怜.

PG 解决方案:

  • 优化器强大
  • 执行器强大
  • 支持扩展执行器
  • 支持并行计算、JIT

参考:

《重新发现PostgreSQL之美 - 38 肝者,将军之官,谋虑出焉. 优化器》

视频回放: https://www.bilibili.com/video/BV1Uw411o7t9/

39、重新发现PostgreSQL之美 - 39 谁动了我的奶酪

场景:

  • 关键数据被误操作或篡改怎么办?

挑战:

  • 怎么发现和定位?
  • 怎么快速通知?
  • 能不能记下前后变化差异?
  • 能不能回退?
  • 采取什么行动?
  • 能不能限制?
  • 能不能藏起来, 基于行或者列?

PG 解决方案:

  • 发现并记下来
  • 可回退
  • 告状
  • 拒绝执行
  • 藏起来
  • 细粒度权限控制

参考:

《重新发现PostgreSQL之美 - 39 谁动了我的奶酪》

视频回放: https://www.bilibili.com/video/BV17M4y1M7Zk/

40、重新发现PostgreSQL之美 - 40 雪崩, 压死骆驼的最后一根稻草

场景:

  • 高峰期出现慢SQL, 资源打满(cpu、内存、IO、连接数等), 引起雪崩
  • 热表的DDL大锁进入队列中, 可能因为其他长事务的锁和这个大锁冲突, 引起等待, 导致连接数打满, 引起雪崩

挑战:

  • 雪崩时业务完全受损, 无一幸免

PG解决方案: 1、各种超时参数.

  • deadlock_timeout, 解决死锁造成的死等
  • idle_in_transaction_session_timeout, 解决堵塞DDL的小锁长事务引起的雪崩问题
  • lock_timeout, 解决DDL死等引起雪崩
  • statement_timeout, 解决慢SQL堆积导致的雪崩

2、可编程: 钩子, SQL 限流 Executor Hooks

  • ExecutorStart_hook — called at the beginning of any execution of any query plan.
  • ExecutorRun_hook — called at any plan execution, after ExecutorStart.
  • ExecutorFinish_hook — called after the last ExecutorRun call
  • ExecutorEnd_hook — called at the end of execution of any query plan.
  • ProcessUtility_hook — hook for the ProcessUtility.

参考: 《PostgreSQL hook & callback》

视频回放: https://www.bilibili.com/video/BV1z44y1q7sb/

41、重新发现PostgreSQL之美 - 41 小结巴,语言处理

场景:

  • 短文本(评论、客服交互、弹幕等)字符串清洗

挑战:

  • 语言字符处理逻辑复杂, 例如常见的词组重复、字符重复要求去除, 数字可能重复但是又不能去除

PG解决方案

  • 正则表达式, 快速清洗数据

《PostgreSQL 正则表达式 短文本清洗, 去除重复字符》

视频回放: https://www.bilibili.com/video/BV11q4y1p7m1/

42、重新发现PostgreSQL之美 - 42 精准营销之减负

场景:

  • 精准营销场景, 判断某个标签里面是否包含当前的用户ID

挑战:

  • 一个标签里面有很多UID, 例如年龄段标签, 可能包含上亿的UID, 造成大量的memory copy, 同时匹配计算时的运算量增加
  • 展开来存储KV的话, 查询某个用户有哪些标签是快了, 但是会带来指数级的记录数增加, 存储空间撑爆

PG解决方案:

  • 使用roaringbitmap存储UIDs, 同时把每个标签组按UID进行哈希取模分片, 降低每次用户ID包含计算的运算量和memory copy量.
    • 性能得到量级提升

《实时营销, 人群圈选推荐业务 性能优化 - memory copy+rb contains计算瓶颈 - rb hash分片》

视频回放: https://www.bilibili.com/video/BV1xh41167uN/

43、重新发现PostgreSQL之美 - 43 快速破镜重圆

场景:

  • standby 临时开启读写后继续成为standby.
  • standby 激活时老主库没有完全同步, 希望老的primary可以变成新主库的standby.

挑战:

  • 传统方式, 需要重新拷贝整个数据库, 重建standby. 速度慢, 对当前主库的IO、网络冲击很大.
  • 采用rsync的方式, 需要比对所有的数据文件, 找到变化的文件, 即使只是少量的字节变化也需要同步整个文件. 速度慢, 对当前主库的IO冲击很大.

PG解决方案:

  • pg_rewind, 支持在线修复分裂, 只需解析并同步自分裂点以来老库的变化blocks. 速度快、对当前主库的IO、网络影响小.

参考: 《重新发现PostgreSQL之美 - 43 快速破镜重圆》

视频回放: https://www.bilibili.com/video/BV1zL411H7Z9/

44、重新发现PostgreSQL之美 - 44 摩斯电码

场景:

某些字段的值经过计算后再过滤的场景, 例如:

json里面的内容包含经纬度, 我们需要对经纬度进行地理信息空间查询过滤.

a,b,c,d分别代表语、数、英、科的分数, 查询总分等于或范围时, 需要计算后再搜索.

挑战:

大多数数据库无法使用表达式索引, 只能全表扫描, 逐条计算. 效率低下.

PG解决方案:

支持表达式索引(也可以叫函数索引), 性能指数级提升.

支持表达式统计信息柱状图, 用于优化器计算

参考: 《重新发现PostgreSQL之美 - 44 摩斯电码》

视频回放: https://www.bilibili.com/video/BV1kB4y1N7LT/

45、重新发现PostgreSQL之美 - 45 个性化

场景:

  • 业务场景越来越丰富, 但是传统数据库只提供单方面能力, 无法定制化, 仅有一些场景市场空间足够大的时候, 有一些垂直领域的数据库冒出来(例如搜索、图、时序、向量等), 用户需要采用多个种类的DB(关系、图、搜索、推荐、分析等)。

挑战:

  • 数据需要多份冗余、同步延迟高、数据一致性难以保证、开发、维护成本高 诸多问题.
  • 跨产品的功能很难对齐, 如租户隔离功能, 很难要求所有种类的数据库都有租户隔离能力.
  • 跨数据库产品的类型、功能无法对齐.

PG解决方案:

  • RDS PG采用模块化设计, 可以针对不同业务场景进行深度优化, 通过对业务需求的深度挖掘, 开发出精准匹配业务的“数据类型、存储和索引结构、操作符与函数”。
  • 相比传统方案性能提升数十倍甚至上万倍. (实时精准营销、分词、全模糊查询、向量相似检索)
  • 同时由于使用同一份数据, 大幅度降低开发、运维、产品成本, 避免同步、一致性等问题.

《为什么PG可以模块化堆叠新功能》

《PG模块化功能案例》

视频回放: https://www.bilibili.com/video/BV1QU4y1n7ve/

46、重新发现PostgreSQL之美 - 46 既要又要还要

场景:

  • 实时分析行业SaaS, 低代码场景满足客户个性化分析的诉求.
  • 单个用户的数据总量T级别.
  • 业务数据需要实时写入.
  • 用户分析师拖拽式试错, 产生合理的分析模板. 结果则需要实时高并发查询(例如为不通属性用户定制的动态页面, 需要实时识别用户的属性(即分析结果)), 结果还有二次分析诉求.

挑战:

  • 既要又要还要:
    • 用户拖拽式试错, 需要实时分析计算能力.
    • 分析框架固定后, 需要实时查询, 结果有高并发诉求.
    • 业务数据实时写入, 用业务+大数据库成本高, 同步延迟高、一致性等问题突出.
    • 单个用户的数据总量T级别, 不大不小. 用大数据成本高.
    • 如果拖拽后的固定结果使用普通视图, 那么它只是SQL语句, 不存储结果数据, 也无法支持索引, 查询视图时耗费计算, 效率低, 无法支持高并发.
    • 如果存储结果, 那么对于采用逻辑复制的数据库, 需要等事务结束客户端才能apply事务, 只读实例延迟高. 物化视图刷新是大事务, 因此这种场景无法通过只读实例扩展性能.

PG解决方案:

  • 并行计算+JIT满足TB级别拖拽式实时分析需求.
  • 物化视图, 已经算好, 查询效率高.
  • 支持在物化视图上创建索引, 效率高.
  • 定时任务增量刷新物化视图, 可以反映基表变更实时信息.
  • 流复制只读实例, 流式复制, 不需要等事务结束, 解决只读实例延迟高问题.
  • 支持物化视图与基表采用不一致的存储引擎, 例如基表要高并发dml使用行存储, 物化视图如果要大量二次分析可以使用列存储. 使得可以适合最好的效率.

视频回放: https://www.bilibili.com/video/BV1oM4y1P7QT/

47、重新发现PostgreSQL之美 - 47 为什么脑容量更大的尼安德特人会被现代智人消灭?

场景:

在不同的场合需要的功能也是不一样的, 例如全文检索要的是分词和倒排索引, 空间搜索要的是空间索引, 图式关系查询需要递归. 高并发的点查要的是行存储, 而大范围的分析要的是列存储和并行计算.

挑战:

大多数的数据库都只做单项冠军, 综合能力差.

PG解决方案:

table access method, 可扩展的数据存储结构.

  • 列存储, 解决大范围的分析导致的IO和计算瓶颈.
  • lsmtree, 解决高速写入时索引更新增加RT导致的写入吞吐瓶颈.
  • heap, 解决高速写入, 高并发查询OLTP业务的性能问题.
  • zedstore 行列混合存储, 解决OLTP OLAP混合场景性能问题.
  • zheap undo多版本控制, 解决高频率更新导致的膨胀问题.
  • 索引组织表, 解决PKV搜索IO多跳瓶颈.

index access method, 可扩展的索引存储结构.

  • btree
  • hash, 解决大字段点查,btree的空间占用瓶颈或大字段超出btree page1/3的错误问题
  • bitmap, 基于标签的少量条件大量记录聚合查询的精准营销场景性能问题
  • GIN, 提高数组元素搜索、JSON元素搜索、全文检索、模糊查询性能.
  • SP_GIST、GIST, 提高空间搜索、range搜索、JSON查询性能.
  • BRIN, 时序数据索引, 解决引入的索引空间占用大, RT增加的性能问题
  • BLOOM, 解决分析场景, 任意字段搜索时的索引空间占用大, RT增加的性能问题

如果以上表或索引的存储结构还不能满足你的需求, PG还能自定义的表和索引接口

使用PG可以因时因地置宜的选择最好的存储结构、索引结构.

视频回放: https://www.bilibili.com/video/BV1Hq4y1p7Ai/

48、重新发现PostgreSQL之美 - 48 聚合、窗口过滤

场景:

  • 聚合查询、窗口查询时, 对聚合的内容或窗口的内容本身有过滤条件诉求.
    • 如: 每个分组排除噪点后的方差
    • 某些分组排除噪点后的方差

挑战:

  • 传统的方法需要使用 case when 来进行过滤, 然而对于有上下文相关的记录使用case when无法支持, 例如求标准方差、平均值等需要收敛到子 集空间进行计算时, case when结果不一致.
  • 传统方法需要扫描多遍table

PG 解决方案: 语法简单, 同时只需要扫描一次table, 而且结果不存在语意问题.

  • agg filter
  • window filter

视频回放: https://www.bilibili.com/video/BV1K3411B7p9/

49、重新发现PostgreSQL之美 - 49 热启动

视频回放: https://www.bilibili.com/video/BV11Q4y1h7Qw/

场景:

  • 业务正常使用期间发生HA, 或者数据库崩溃重启, 或者业务计划性重启.

挑战:

  • 数据库重启后原来在shared buffer中的热数据将需要重磁盘读取, 在此前开放给业务访问, 可能导致原有的SQL响应变慢, 高峰期可能导致雪崩效应影响业务.

PG 解决方案:

《重新发现PostgreSQL之美 - 49 热启动》

50、重新发现PostgreSQL之美 - 50 一粒老鼠屎

视频回放: https://www.bilibili.com/video/BV1aq4y1U7Rm/

场景:

  • 在正常业务使用期间, DBA、开发者、分析师在数据库中跑大查询, 某些大表采用了全表扫描.

挑战:

  • 大表的全表扫描会占用buffer pool, 从而将shared buffer中的热数据挤出去, 导致其他业务的SQL变慢, 严重的导致雪崩.

PG 解决方案:

除了全表扫描, PG的bulk - write, vacuum都有类似机制:

bulk - write 16MB ring buffer

COPY FROM command.

CREATE TABLE AS command.

CREATE MATERIALIZED VIEW or REFRESH MATERIALIZED VIEW command.

ALTER TABLE command.

vacuum 256KB ring buffer.

《重新发现PostgreSQL之美 - 50 一粒老鼠屎》

51、重新发现PostgreSQL之美 - 51 删表跑路

视频回放: https://www.bilibili.com/video/BV11M4y137oL/

场景:

  • drop table, drop database等危险操作无力回天.

挑战:

  • 误删除或被黑客攻击后发生了drop table, drop database操作, 在没有备份的情况下无力回天. 在有备份的情况下, 恢复慢, 影响业务.

PG 解决方案:

  • event trigger 增加回收站, 支持drop table快速恢复
  • drop database 通过权限控制, 或者使用HOOK增加database 回收站功能. 例如 ProcessUtility_hook
  • truncate table 使用trigger可以实现回收站功能.

《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》

《PostgreSQL 回收站功能 - 基于HOOK的recycle bin pgtrashcan》

《重新发现PostgreSQL之美 - 51 删表跑路》

52、重新发现PostgreSQL之美 - 52 跨越鸿沟

视频回放: https://www.bilibili.com/video/BV1TL4y1Y7EE/

场景:

  • PG 每年发布一个大版本, 每个大版本社区支持FIX BUG 5-6年, 用户使用的版本可能已经不再支持, 那么需要升级大版本
  • 每个大版本在性能、安全、功能等方面都有大幅度提升, 用户希望升级到大版本

挑战:

  • 升级大版本通常需要迁移数据, 时间漫长. 使用逻辑倒出导入耗时长, 影响业务长.
  • 使用逻辑增量复制有前置依赖: 必须9.4以上版本, 必须有主键或UK. 而且不支持DDL迁移, 序列迁移等. 配置相对来说也比较复杂, 还需要数据一致性验证等. 一般用户搞不定.

PG 解决方案:

  • pg_upgrade 仅仅需要迁移元数据(例如表结构, 视图、序列、索引、函数的定义), 通常可以分钟左右完成. 而且配置简单.

《重新发现PostgreSQL之美 - 52 跨越鸿沟》

53、重新发现PostgreSQL之美 - 53 共同富裕

视频回放: https://www.bilibili.com/video/BV1ZU4y1w7Xi/

场景:

  • 国产化、二次开发、发型版、云服务、DBaaS、私有化.

挑战:

  • *
  • 门槛
  • ROI
  • 法律

PG解决方案:

  • 开源许可: 类BSD, 可商用, 可售卖, 可开源, 可闭源, 非常*.
  • 开放架构:

基于PG的成功产品:

  • 云厂商的RDS, 以及基于PG的高度自研产品例如阿里云存储计算分离产品PolarDB(已开源)
  • aws aurora for pg
  • tbase
  • gauss
  • 瀚高
  • greenplum
  • antdb
  • pg-xc
  • 等等





上一篇：【DB吐槽大会】第70期 - PG 不支持update | delete skip locked, nowait语法


下一篇：【DB吐槽大会】第66期 - PG 缺乏更简单的数据热插拔能力