既然MySQL 8和PostgreSQL 10已經發布了，現在是時候回顧一下這兩大開源關系型數據庫是如何彼此競爭的。

在這些版本之前，人們普遍認為，Postgres在功能集表現更出色，也因其“學院派”風格而備受稱贊，MySQL則更善長大規模并發讀/寫。

但是隨著它們最新版本的發布，兩者之間的差距明顯變小了。

特性比較

首先來看看我們都喜歡談論的“時髦”功能。

過去經常會說MySQL最適合在線事務，PostgreSQL最適合分析流程，但現在不是了。

公共表表達式（CTEs）和窗口函數是選擇PostgreSQL的主要原因。但是現在，通過引用同一個表中的boss_id來遞歸地遍歷一張雇員表，或者在一個排序的結果中找到一個中值（或50%），這在MySQL上不再是問題。

在PostgreSQL中進行復制缺乏配置靈活性，這就是Uber轉向MySQL的原因。但是現在，有了邏輯復制特性，就可以通過創建一個新版本的Postgres并切換到它來實現零停機升級。在一個巨大的時間序列事件表中截斷一個陳舊的分區也要容易得多。

就特性而言，這兩個數據庫現在都是一致的。

不同之處

現在，我們只剩下一個問題——選擇這一個而不選另一個的原因是什么呢？

生態系統是其中一個因素。MySQL有一個充滿活力的生態系統，包括MariaDB、Percona、Galera等等，以及除了InnoDB以外的存儲引擎，但這也可能會令人困惑。Postgres的高端選擇有限，但隨著最新版本引入的新功能，這個情況會有所改變。

治理是另一個因素。當Oracle（或最初的SUN）收購MySQL時，每個人都擔心會毀掉這個產品，但在過去的十年里，這并不是事實。事實上，在收購之后，MySQL的發展反而加速了。而Postgres在工作管理和協作社區方面有著豐富的經驗。

基礎架構不會經常改變，雖然最近沒有對這方面的詳細討論，但這也是值得再次考慮的。

來復習一下：

進程 vs 線程

當Postgres派生出一個子進程來建立連接時，每個連接最多可以占用10MB。與MySQL的線程連接模型相比，它的內存壓力更大，在64位平臺上，線程的默認堆棧大小為256KB（當然，線程本地排序緩沖區等使這種開銷變得不那么重要，即使在不可以忽略的情況下，仍然如此）。

盡管“寫時復制”保存了一些與父進程共享的、不可變的內存狀態，但是當你有1000多個并發連接時，基于流程的架構的基本開銷是很繁重的，而且它可能是容量規劃的最重要因素之一。

也就是說，如果你在30臺服務器上運行一個Rails應用，每個服務器都有16個CPU核心32線程，那么你有960個連接。可能只有不到0.1%的應用會超出這個范圍，但這是需要記住的。

聚簇索引 vs 堆表

聚簇索引是一種表結構，其中的行直接嵌入其主鍵的b樹結構中。一個（非聚集）堆是一個常規的表結構，它與索引分別填充數據行。

有了聚簇索引，當你通過主鍵查找記錄時，單次I/O就可以檢索到整行，而非集群則總是需要查找引用，至少需要兩次I/O。由于外鍵引用和JOIN將觸發主鍵查找，所以影響可能非常大，這將導致大量查詢。

聚簇索引的一個理論上的缺點是，當你使用二級索引進行查詢時，它需要遍歷兩倍的樹節點，第一次掃描二級索引，然后遍歷聚集索引，這也是一棵樹。

但是，如果按照現代表設計的約定，將一個自動增量整數作為主鍵[1]——它被稱為代理鍵——那么擁有一個聚集索引幾乎總是可取的。更重要的是，如果你做了大量的ORDER BY id來檢索最近的（或最老的）N個記錄的操作，我認為這是很適用的。

Postgres不支持聚集索引，而MySQL(InnoDB)不支持堆。但不管怎樣，如果你有大量的內存，差別應該是很小的。

頁結構與壓縮

Postgres和MySQL都有基于頁面的物理存儲。(8KB vs 16KB)

PostgreSQL物理存儲的介紹

頁結構看起來就像上圖所示。它包含一些我們不打算在這里討論的條目，但是它們包含關于頁的元數據。條目后面的項是一個數組標識符，由指向元組或數據行的（偏移、長度）對組成。在Postgres中，相同記錄的多個版本可以以這種方式存儲在同一頁面中。

MySQL的表空間結構與Oracle相似，它有多個層次，包括層、區段、頁面和行層。

此外，它還有一個用于撤銷的單獨段，稱為“回滾段”。與Postgres不同的是，MySQL將在一個單獨的區域中保存同一記錄的多個版本。

如果存在一行必須適合兩個數據庫的單個頁面，這意味著一行必須小于8KB（至少有2行必須適合MySQL的頁面，恰巧是16KB/2 = 8KB）。

那么，當你在一個列中有一個大型JSON對象時會發生什么呢？

Postgres使用TOAST，這是一個專用的影子表（shadow table）存儲。當行和列被選中時，大型對象就會被拉出。換句話說，大量的黑盒不會污染你寶貴的緩存。它還支持對TOAST對象的壓縮。

MySQL有一個更復雜的特性，叫做透明頁壓縮，這要歸功于高端SSD存儲供應商Fusio-io的貢獻。它設計目的是為了更好地使用SSD，在SSD中，寫入量與設備的壽命直接相關。

對MySQL的壓縮不僅適用于頁面外的大型對象，而且適用于所有頁面。它通過在稀疏文件中使用打孔來實現這一點，這是被ext4或btrfs等現代文件系統支持的。

有關更多細節，請參見：在FusionIO上使用新MariaDB頁壓縮獲得顯著的性能提升。（https://mariadb.org/significant-performance-boost-with-new-mariadb-page-compression-on-fusionio/）

更新的開銷

另一個經常被忽略的特性，但是對性能有很大的影響，并且可能是最具爭議的話題，是更新。

這也是Uber放棄Postgres的另一個原因，這激起了許多Postgres支持者的反駁。

• MySQL對Uber可能是合適的，但是未必對你合適

https://dzone.com/articles/on-ubers-choice-of-databases

• 一篇PostgreSQL對Uber的回應（PDF）

http://thebuild.com/presentations/uber-perconalive-2017.pdf

兩者都是MVCC數據庫，它們可以隔離多個版本的數據。

為了做到這一點，Postgres將舊數據保存在堆中，直到被清空，而MySQL將舊數據移動到一個名為回滾段的單獨區域。

在Postgres中，當你嘗試更新時，整個行必須被復制，以及指向它的索引條目也被復制。這在一定程度上是因為Postgres不支持聚集索引，所以從索引中引用的一行的物理位置不是由邏輯鍵抽象出來的。

為了解決這個問題，Postgres使用了堆上元組（HOT），在可能的情況下不更新索引。但是，如果更新足夠頻繁（或者如果一個元組比較大），元組的歷史可以很容易地超過8KB的頁面大小，跨越多個頁面并限制該特性的有效性。修剪和/或碎片整理的時間取決于啟發式解決方案。另外，設置不超過100的填充參數會降低空間效率——這是一種很難在創建表時考慮的折衷方案。

這種限制更深入，因為索引元組沒有關于事務的任何信息，所以直到9.2之前一直不能支持僅索引掃描。 它是所有主要數據庫（包括MySQL、Oracle、DB2和SQL Server）支持的最古老，最重要的優化方法之一。 但即使使用最新版本，當有許多UPDATE在可見性映射中設置臟位時，Postgres也不能完全支持僅索引掃描，并且在我們不需要時經常選擇Seq掃描。

在MySQL上，更新發生在原地，舊的行數據被封存在一個稱為回滾段的獨立區域中。 結果是你不需要VACUUM，并且提交非常快，而回滾相對較慢，這對于大多數用例來說是一個可取的折衷。

它也足夠聰明，盡快清除歷史。 如果事務的隔離級別設置為READ-COMMITTED或更低，則在語句完成時清除歷史記錄。

事務記錄的大小不會影響主頁面。碎片化是一個偽命題。因此，在MySQL上能更好、更可預測整體性能。

Garbage Collection垃圾回收

在Postgres中VACUUM上開銷很高，因為它主要工作在堆區，造成了直接的資源競爭。它感覺就像是編程語言中的垃圾回收——它會擋在路上，并隨時讓你停下來。

為具有數十億記錄的表配置autovacuum仍然是一項挑戰。

在MySQL上清除（Purge）也可能相當繁重，但由于它是在單獨的回滾段中使用專用線程運行的，因此它不會以任何方式影響讀取的并發性。即使使用默認配置，變膨脹的回滾段使你執行速度減慢的可能性也是很低的。

擁有數十億記錄的繁忙表不會導致MySQL上的歷史數據膨脹，諸如存儲上的文件大小和查詢性能等事情上幾乎是可以預測的并且很穩定。

日志與副本

Postgres擁有被稱作預寫日志（WAL）的單信源事務歷史。它一直被用于副本，并且稱為邏輯復制的新功能可將二進制內容快速解碼為更易消化的邏輯語句，從而可對數據進行細粒度控制。

MySQL維護兩個單獨的日志：1、用于崩潰恢復的InnoDB特定的重做日志；2、用于復制和增量備份的二進制日志。

InnoDB上的重做日志與Oracle一致，它是一個免維護的循環緩沖區，不會隨著時間的推移而增長，只在啟動時以固定大小創建。 這種設計保證在物理設備上保留一個連續的連續區域，從而提高性能。 更大的重做日志產生更高的性能，但要以崩潰恢復時間為代價。

隨著新的復制功能添加到Postgres，我覺得他們不分伯仲。

總結

令人驚訝的是，它證明了普遍的觀點依然存在。MySQL最適合在線交易，而PostgreSQL最適合僅用于append only模式，像數據倉庫一樣分析過程。[2]

正如我們在這篇文章中看到的，Postgres的絕大多數難題都來自于append only模式，過于冗余的堆結構。

Postgres的未來版本可能需要對其存儲引擎進行重大改進。你不必接受我所說的——實際上在官方wiki上已經有對它的討論，這表明現在是時候從InnoDB身上學回來一些好的想法了。

人們一次又一次地說MySQL正在追趕Postgres，但是這一次，潮流已經改變。

注解：

[1] UUID作為主鍵是一個可怕的想法，順便說一句——密碼隨機性完全是為了殺死引用的局部性而設計，因此性能會損失。

[2] 當我說Postgres特別適合分析時，我是認真的：萬一你不知道TimescaleDB，它是PostgreSQL上邊的一個封裝，允許你每秒插入100萬條數據，每臺服務器有1000億行。多么瘋狂的事情。難怪Amazon會選擇PostgreSQL作為Redshift的基礎。