PostgreSQL 索引和复制技术

Question

背景:到目前为止，我一直使用 Django 及其 ORM 来构建小型网站，因此哪个数据库(MySQL 与 PostgreSQL)在幕后完成所有工作并不是真正的问题。

最近我决定进一步了解这两者之间的差异。我刚刚读完这篇文章(长篇)article它探讨了索引在 PostgresSQL 中的工作方式，我对以下事实感到非常震惊:

"For instance, if we have a table with a dozen indexes defined on it, an update to a field that is only covered by a single index must be propagated into all 12 indexes to reflect the ctid for the new row."

我根本不是专家，但在更新不涉及索引的字段时设计应该发生这样的过载对我来说听起来很疯狂。

此外，文章继续解释了 PostgreSQL 复制策略如何不在逻辑级别工作，而是在磁盘级别工作，即主服务器向从服务器发送一个列表(逐字节)，其中包含要应用到磁盘上的所有更改而不是更抽象的指令，例如 UPDATE <fields> ON <table> WHERE ... .

虽然网络上很多比较MySQL和PostgreSQL的短文一般倾向于声称PostgreSQL在技术上更先进(ACID、JSON支持等)，但这两个问题对我来说似乎是严重的缺点。您能否确认这些陈述并可能指出有关这些问题的更多资源？

谢谢。

Answer 1

关于索引和性能

当一行被更新时，PostgreSQL 确实必须在索引上做更多的工作。这是因为 UPDATE actually creates a new row version在表中，索引必须指向新的行版本。

但是，有一种方法可以减轻影响:如果您设置 fillfactor小于 100，以便数据页中有可用空间，并且没有更新索引列，PostgreSQL 可以创建一个 “heap only tuple” ，这样的热更新不需要触及任何索引。

MySQL 的 InnoDB 及其 secondary indexes (that reference the primary key index)必须做更少的工作来更新索引。每次索引扫描都会为此付出代价:首先，您必须扫描二级索引以找到主键，然后您必须扫描主键索引以找到表行。

所以这是一个权衡，但我认为无条件地说一种解决方案更好是片面的。

关于复制

MySQL 的复制解决方案比 PostgreSQL 早得多。它使用 二进制日志 进行复制，这是一个有点欺骗性的名称，因为它实际上包含 SQL 语句。

PostgreSQL 9.0 版引入了流复制，它将事务日志发送到备用数据库。此信息位于物理级别，因此主数据库和备用数据库在物理上保持相同。这通常比传送 SQL 语句(索引更新!)更浪费，但它是一个非常稳定的解决方案，不会为复制冲突留下余地。

PostgreSQL v10 引入了逻辑复制，可以生成变化的抽象描述，类似于 SQL 语句。这允许更灵活的复制方案。

因此，您引用的文章在这方面已经有些过时了。