既然MySQL 8和PostgreSQL 10已经淘汰，现在是重新审视两个主要的开源关系数据库如何相互竞争的好时机。
在这些版本之前，人们普遍认为，尽管Postgres在功能集及其谱系方面更胜一筹，但MySQL在大规模并发读/写操作方面进行了更多的大规模测试。
但是随着最新版本的发布，两者之间的差距已大大缩小。
功能比较
让我们看一下我们都喜欢谈论的“时髦”功能。
过去常常说MySQL最适合在线交易，而PostgreSQL最适合分析过程。 但现在不再。
通用表表达式（CTE）和窗口函数一直是选择PostgreSQL的主要原因。 但是现在，在同一个表中引用boss_id递归遍历employees表，或在排序结果中找到中间值（或50％百分位数）在MySQL上不再是问题。
PostgreSQL上的复制缺乏配置灵活性，这是Uber转向MySQL的原因。 但是现在有了逻辑复制，通过使用更新版本的Postgres创建副本并切换到该副本，可以实现零停机升级。 截断大型时序事件表中的陈旧分区也容易得多。
在功能方面，两个数据库现在彼此相同。
区别在哪里？
现在，我们有一个问题–那么，为什么一个选择另一个原因呢？
生态系统就是这些因素之一。 MySQL拥有一个强大的生态系统，其中包含MariaDB，Percona，Galera等变体，以及InnoDB以外的其他存储引擎，但它们也可能令人不知所措且令人困惑。 Postgres的高端选项有限，但是随着最新版本引入的新功能，这种情况将会改变。
治理是另一个因素。 每个人都在担心甲骨文（或最初为SUN）收购MySQL时，他们会毁了该产品，但过去十年来情况并非如此。 实际上，收购后发展加速。 Postgres在工作治理和协作社区方面拥有悠久的历史。
体系结构的基本知识不会经常更改，值得回顾一下，因为这些天没有对此进行详细讨论。
这是一个复习：
进程与线程
Postgres分叉一个子进程来建立连接时， 每个连接最多可能占用10 MB的空间 。 与MySQL的“每次连接线程”模型相比，内存压力更大，后者在64位平台上， 线程的默认堆栈大小为 256KB。 （当然，线程局部排序缓冲区等可以使此开销的重要性降低，即使可以忽略不计，但仍然如此。）
即使写时复制与父进程一起保存了一些共享的，不可变的内存状态，当您有1,000多个并发连接时，作为基于进程的体系结构的基本开销仍然很重，这可能是最重要的开销之一能力计划的因素。
也就是说，如果您在30台服务器上运行Rails应用程序，其中每台服务器具有16个CPU内核和32个Unicorn worker，则您有960个连接。 在所有应用程序中，可能只有不到0.1％会达到这个规模，但这是需要牢记的。
聚簇索引与堆表
聚集索引是一种表结构，其中行直接嵌入其主键的B树结构内。 （非聚集）堆是一个常规表结构，其中填充了与索引分开的数据行。
对于聚集索引，当您通过主键查找记录时，单个I / O将检索整行，而非聚集索引始终需要通过遵循引用来至少需要两个I / O。 由于外键引用和联接将触发主键查找，因此影响可能很大，这将占查询的绝大多数。
聚簇索引的理论缺点是，在使用次级索引进行查询时，遍历树形节点的次数是您首先遍历次级索引，然后遍历聚簇索引（也是一棵树）的两倍。
但是，考虑到现代惯例 ，将自动递增整数作为主键¹（称为代理键 ）， 几乎总是希望拥有聚簇索引 。 如果您要执行很多ORDER BY id来检索最新（或最旧）的N条记录，则更是如此，我认为这适用于大多数记录。
[1]顺便说一下，UUID作为主键是一个可怕的想法-密码随机性是完全设计用来杀死参考位置的，因此会降低性能。
Postgres不支持聚集索引，而MySQL（InnoDB）不支持堆。 但是，无论哪种方式，如果您有大量内存，则差异应该很小。
页面结构和压缩
Postgres和MySQL都具有基于页面的物理存储。 （8KB和16KB）
在PostgreSQL上，页面结构看起来像左边的图像。
它包含一些标题，我们将在这里不进行介绍，但是它们包含有关页面的元数据。 标头后的项目是一个数组标识符，由指向元组或数据行的(offset, length)对组成。 请记住，在Postgres中，可以通过这种方式将同一记录的多个版本存储在同一页面中。
MySQL的表空间结构与Oracle的表空间结构相似，因为它具有段，范围，页和行的多个层次结构层。
它还为UNDO提供了一个单独的部分，称为“回退部分”。 与Postgres不同，MySQL将在同一区域保留同一记录的多个版本。
在两个数据库中，一行必须适合单个页面，这意味着一行必须小于8KB。 （至少2行必须容纳在MySQL的一个页面上，巧合的是16KB / 2 = 8KB）
那么，当列中有一个大的JSON对象时，会发生什么？
Postgres使用TOAST （专用的影子表存储）。 当且仅当选择了行和列时，才会拉出大对象。 换句话说，大量的黑盒子不会污染您宝贵的缓存。 它还支持对TOASTed对象的压缩。
由于高端SSD存储供应商Fusion-io的贡献，MySQL具有称为“ 透明页面压缩”的更高级功能。 它专门设计用于与SSD更好地配合使用，因为SSD的写入量与设备的寿命直接相关。
MySQL上的压缩不仅适用于页面外的大对象，而且适用于所有页面。 它是通过在稀疏文件中使用打孔来实现的，现代文件系统（例如ext4或btrfs）都支持稀疏文件 。
有关更多详细信息，请参阅： 通过FusionIO上的新MariaDB页面压缩显着提高性能
更新的开销
UPDATE是经常遗漏但对性能有重大影响且可能是最有争议的话题的另一个功能。
这是Uber放弃Postgres的另一个原因，这激起了许多Postgres拥护者的反驳。
为此，Postgres将旧数据保留在堆中直到VACUUMed，而MySQL将旧数据移动到称为回滚段的单独区域。
在Postgres上，当您尝试更新时，必须复制整行以及指向该行的索引条目。 部分原因是Postgres不支持聚簇索引，因此从索引引用的行的物理位置不会被逻辑键抽象出来。
为了解决此问题，Postgres使用“ 仅堆元组（HOT）”在可能的情况下不更新索引。 但是，如果更新足够频繁（或者如果一个元组很大），则元组的历史记录很容易从8KB的页面大小中流出，跨越多个页面并限制了功能的有效性。 修剪和/或碎片整理的时间取决于试探法。 此外，将fillfactor设置为小于100会降低空间效率—这是在表创建时就不必担心的艰难折衷。
这个限制更深了。 由于索引元组没有有关事务的任何信息，因此直到9.2以前一直不可能支持仅索引扫描 。 它是所有主要数据库（包括MySQL，Oracle，IBM DB2和Microsoft SQL Server）支持的最古老，最重要的优化方法之一。 但是即使使用最新版本，当有大量的UPDATE设置Visibility Map中的脏位时，Postgres也不能完全支持仅索引扫描，而在我们不希望的时候经常选择Seq Scan。
在MySQL上，更新发生在原地，旧行数据存放在称为回滚段的单独区域中。 结果是您不需要VACUUM，提交非常快而回滚相对较慢，这对于大多数用例来说是一个较好的折衷方案。
它也足够聪明，可以尽快清除历史记录。 如果事务的隔离级别设置为READ-COMMITTED或更低，则在语句完成时清除历史记录。
交易历史记录的大小不会影响主页。 碎片是没有问题的。 因此，MySQL的整体性能更好，更可预测。
垃圾收集
Postgres上的VACUUM非常昂贵，因为它可以在主堆区域中工作，从而造成直接的资源争用。 感觉就像编程语言中的垃圾回收一样，它会妨碍您并让您随意暂停。
为具有数十亿条记录的表配置自动清空仍然是一个挑战。
对MySQL的清除也可能很繁琐，但是由于它在单独的回滚段内使用专用线程运行，因此不会以任何方式对读取并发产生不利影响。 即使使用默认设置 ，膨胀的回滚段也不太可能使您减速。
拥有数十亿条记录的繁忙表不会导致MySQL的历史记录膨胀，并且诸如存储文件大小和查询性能之类的事情几乎是可以预测和稳定的。
日志和复制
Postgres有一个唯一的交易历史真相来源，称为Write Ahead Log（WAL） 。 它也用于复制，名为逻辑复制的新功能可以将二进制内容实时解码为更易消化的逻辑语句，从而可以对数据进行细粒度的控制。
MySQL维护两个单独的日志：1.用于崩溃恢复的特定于InnoDB的重做日志 ，以及2.用于复制和增量备份的二进制日志 。
与Oracle一样，InnoDB上的重做日志是免维护的循环缓冲区 ，不会随着时间的推移而增长，只能在启动时以固定大小创建。 这种设计可确保在物理设备上保留连续的连续区域，从而提高性能。 重做日志越大，性能越好，但要从崩溃中恢复时间。
在Postgres中添加了新的复制功能后，我称之为平局。
TL; DR
令人惊讶的是，事实证明，普遍的看法仍然成立。 MySQL最适合在线交易，而PostgreSQL最适合仅追加分析过程，例如数据仓库²。
[2]当我说Postgres非常适合分析时，我是说真的。 如果您不了解TimescaleDB ，它是PostgreSQL之上的包装，可让您每秒插入100万条记录，每服务器100+十亿行。 疯狂的事情。 难怪亚马逊为什么选择PostgreSQL作为Redshift的基础 。
正如我们在本文中看到的，Postgres的绝大多数复杂性源于其仅追加，过度冗余的堆体系结构。
Postgres的未来版本可能需要对其存储引擎进行重大改进。 您不必完全相信我的话- 官方Wiki上已经讨论了它，这表明是时候从InnoDB那里获取一些好主意了。
一次又一次地说MySQL正在追赶Postgres，但是这一次，潮流已经改变了。
如果您想阅读更多类似内容，请访问 Dumper博客 并订阅邮件列表。 您也可以 在Twitter上 关注我 。 除非您100％确定备份脚本可以正常工作，否则请签出 Dumper 异地备份即服务。
From: https://hackernoon.com/showdown-mysql-8-vs-postgresql-10-3fe23be5c19e
342
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
