2024-4-26 群讨论：PostgreSQL MySQL 适用场景（仅考虑 OLTP）

以下来自本人拉的一个关于 Java 技术的讨论群。关注公众号：hashcon，私信进群拉你

PostgresSQL 和 MySQL 各自适用的场景（仅考虑 OLTP）

假设都是默认的事务引擎，默认的编码压缩方式：

MySQL 与 PG 在 OLTP 的场景下，主要区别在于：两点：

1. 对于二级索引处理的差异：
   • MySQL 二级索引叶子节点是保存的主键的值（感谢 LiZN：公众号monstaxl 指正），PG 的二级索引叶子节点与主键索引一样直接是记录位置，行记录发生更新的时候，由于 MVCC 与可变长度字段与 null 字段，很可能导致行位置变化，对于 PG 需要更新所有二级索引，但是 MySQL 不需要
     • PG 如果是非索引字段更新，缓冲池够的情况下 Heap-Only Tuples (HOT) 生效，不一定用更新索引
     • 但是 Heap-Only Tuples (HOT) 其实在实际 OLTP 场景中，命中率不是特别理想。
   • 所以 MySQL 对于有二级索引的表高并发更新，以及涉及数据位置改变的更新（比如更新 varchar 字段为更长的），以及插入，会比 PG 表现好。
   • 但是，这种设计下，MySQL 的二级索引读取性能肯定也不如 PG。因此，需要好好考虑场景。
2. 对于 MVCC 处理的差异：
   • PostgreSQL 的 MVCC 基于 xmin, xmax 机制实现：当一行数据需要被更新或删除时，PostgreSQL 并不是直接更改原有的行记录。相反，它会：
     • 更新：插入一个新的行版本，其中 xmin 设置为当前事务的 ID，同时将旧版本行的 xmax 设置为当前事务的 ID。
     • 删除：简单地将行的 xmax 设置为当前事务的 ID。
   • MySQL 的 MVCC 是基于行锁和 undo log实现的。每行记录都有两个隐藏的列,分别记录事务ID(trx_id)和回滚指针(roll_pointer)。读取数据时,InnoDB 会根据事务 ID 和回滚指针找到行数据的可见版本
   • PG 的优势体现在读取，老版本也可以直接读取，同时读取这行不阻塞这行的更新。但是劣势也很明显，频繁更新，表膨胀过快，vacuum 有时候完全跟不上高速写操作，另外 vacuum 本身也有很多问题，autovacuum 本身在部分场景下会导致 dead tuple 不断积攒以至于一段时间后查询的 IO 开销将极为高昂，必须要 DBA 手动的进行释放（此处感谢：B站 滑稽_1 https://space.bilibili.com/38107834 ）。插入性能也会受这个多版本影响。
   • MySQL 优势在写入，只为当前读写的行加锁，其他写入不受影响，并发写入更高。
   • PostgreSQL 和 MySQL 在大表更新频率很高达到一定阈值的时候，不是那种订单表，交易表，而是类似于用户余额表那种，带来的查询与插入的性能严重下降。在这种场景下，PostgreSQL 本身由于 xmin 与 xmax 的回滚 MVCC 设计导致表膨胀过快，与 MySQL 类似 Oracle 的 Redolog 设计上，MySQL 需要分库分表的阈值相对于 PostgreSQL 高一些。PostgreSQL 之前推出过 zheap 想改用 Redolog，但是后来在 20 年之后就没有下文了，不知道为啥。参考：https://wiki.postgresql.org/wiki/Zheap

综合来看，其实 MySQL 更适合 OLTP 的场景。现在云服务商提供的数据库基本都实现了主从延迟很低，读取性能可以加从库解决。例如 Aurora，一个写入实例最多可以加 12 个读取实例，延迟在我们业务最高峰的时候，也只有 300 ms，平常在 10ms 左右。

PostgreSQL 目前的生态更丰富，并且 OLAP 的很多数据库，其实在协议层用的是 PostgreSQL（比如Redshift，GreenPlum 等等）。PostgreSQL 目前的发展方向，也主要在 OLAP 的生态场景不断完善。

另外，Uber 在 2015 年的时候，从分库分表的 PostgreSQL，转移到了分库分表的 MySQL 以应对他们的 OLTP 场景，原文：https://www.uber.com/en-HK/blog/postgres-to-mysql-migration/

个人简介：个人业余研究了 AI LLM 微调与 RAG，目前成果是微调了三个模型：

1. 一个模型是基于 whisper 模型的微调，使用我原来做的精翻的视频按照语句段落切分的片段，并尝试按照方言类别，以及技术类别分别尝试微调的成果。用于视频字幕识别。
2. 一个模型是基于 Mistral Large 的模型的微调，识别提取视频课件的片段，辅以实际的课件文字进行识别微调。用于识别课件的片段。
3. 最后一个模型是基于 Claude 3 的模型微调，使用我之前制作的翻译字幕，与 AWS、Go 社区、CNCF 生态里面的官方英文文档以及中文文档作为语料，按照内容段交叉拆分，进行微调，用于字幕翻译。

目前，准确率已经非常高了。大家如果有想要我制作的视频，欢迎关注留言。

本人也是开源代码爱好者，贡献过很多项目的源码（Mycat 和 Java JFRUnit 的核心贡献者，贡献过 OpenJDK，Spring，Spring Cloud，Apache Bookkeeper，Apache RocketMQ，Ribbon，Lettuce、 SocketIO、Langchain4j 等项目 ），同时也是深度技术迷，编写过很多硬核的原理分析系列（JVM）。本人也有一个 Java 技术交流群，感兴趣的欢迎关注。

另外，一如即往的是，全网的所有收益，都会捐赠给希望工程，坚持靠爱与兴趣发电。