PostgreSQL vs MySQL：为你的 OLTP 工作负载找到合适的选择#
在 OLTP 场景中选择 PostgreSQL 和 MySQL 之间（假设默认事务引擎和编码/压缩设置），决策通常归结为了解它们的根本架构差异。让我带你了解在实践中真正重要的关键区别。
两种索引架构的故事#
最显著的差异在于这些数据库如何处理二级索引，相信我，这比你预期的更能影响性能。
MySQL 的巧妙方法：二级索引叶节点存储主键值（感谢 monstaxl 微信公众号的 LiZN 的澄清）。这个设计选择对于写密集型工作负载实际上非常聪明。
PostgreSQL 的直接方法：二级索引叶节点直接指向记录位置，就像主索引一样。虽然这看起来很简单，但它创造了一个有趣的挑战。
这就是它变得有趣的地方：当 PostgreSQL 更新一行时，特别是使用 MVCC 和可变长度字段时，行位置经常改变。这意味着所有二级索引都需要更新。MySQL 通过其主键引用方法完全避免了这个问题。
现在，PostgreSQL 确实有一个巧妙的优化，称为堆仅元组（HOT），当只有非索引字段改变时可以避免索引更新（假设有足够的缓冲池）。然而，在真实的 OLTP 场景中，HOT 命中率并不像我们希望的那样令人印象深刻。
底线：在高并发更新下，对于具有二级索引的表，MySQL 通常优于 PostgreSQL，特别是在处理可变长度字段更改（如扩展 varchar 字段）和重度插入工作负载时。
权衡：MySQL 的二级索引读取需要额外的主键查找，使它们比 PostgreSQL 的直接访问方法慢。这都是关于选择你的战斗！
MVCC：两种哲学，不同结果#
第二个主要差异在于它们的多版本并发控制实现。
PostgreSQL 的 xmin/xmax 舞蹈：PostgreSQL 使用优雅的 xmin/xmax 机制，其中：
- 更新创建新的行版本，xmin 设置为当前事务 ID，同时标记旧版本的 xmax
- 删除只需将行的 xmax 设置为当前事务 ID
MySQL 的撤销日志策略：MySQL 依赖于行锁和撤销日志，每条记录包含事务 ID（trx_id）和回滚指针（roll_pointer）的隐藏列。InnoDB 使用这些来定位每个事务的正确行版本。
PostgreSQL 的读取优势：旧版本保持直接可访问，读取永远不会阻止同一行的更新。对于读密集型场景来说这很美妙。
黑暗面：频繁更新导致表快速膨胀。Vacuum 操作有时无法跟上高速写入，在某些场景中 autovacuum 可能有问题，导致死元组积累，最终使查询极其 I/O 昂贵。需要手动 DBA 干预。插入性能也受到这种多版本开销的影响。
MySQL 的写入优势：只有主动读取/写入的行被锁定，允许更高的并发写入而不受干扰。
当橡胶遇到路面#
两个数据库在大型表上的高频更新都面临挑战，但突破点不同。我们这里不是在谈论典型的订单或事务表，而是像用户余额表这样不断更新的场景。
PostgreSQL 的 xmin/xmax MVCC 设计导致比 MySQL 的类似 Oracle 的重做日志方法更快的表膨胀。这意味着MySQL 通常在需要分片之前处理更大的数据集。
有趣的是，PostgreSQL 在 2020 年左右探索了 zheap 项目以采用重做日志机制，但开发似乎已经停滞。你仍然可以在以下位置找到痕迹：https://wiki.postgresql.org/wiki/Zheap
实际结论#
对于纯 OLTP 工作负载，MySQL 通常证明更合适。现代云提供商已经通过极低延迟的只读副本在很大程度上解决了读取性能差距。以 Aurora 为例 - 你可以将多达 12 个只读实例附加到单个写入实例，延迟通常在 10 毫秒左右（在高峰流量期间偶尔会飙升到 300 毫秒）。
PostgreSQL 在其丰富的生态系统和 OLAP 能力方面表现出色。许多分析数据库实际上使用 PostgreSQL 的线路协议，PostgreSQL 的开发轨迹越来越关注 OLAP 生态系统增强。
真实世界验证#
Uber 在 2015 年从分片 PostgreSQL 迁移到分片 MySQL 用于其 OLTP 工作负载，提供了令人信服的真实世界证据。他们的详细分析值得一读：https://www.uber.com/en-HK/blog/postgres-to-mysql-migration/
选择最终取决于你的特定工作负载特征，但了解这些根本差异有助于做出明智的决策，而不是跟随趋势或假设。
