乍一看，這篇文章看起來(lái)像是標(biāo)題黨。可是咱們不能否定一個(gè)事實(shí)，公司從MySQL到PostgreSQL遷移現(xiàn)已開端，由于MySQL越來(lái)越雜亂了。

回想公司剛成立那會(huì)兒，我們團(tuán)隊(duì)只有三個(gè)人，沒有太多的資源和精力投入到技術(shù)的基礎(chǔ)架構(gòu)上；如果你正處在一個(gè)小團(tuán)隊(duì)，那么遷移到PostgreSQL就是個(gè)不錯(cuò)的選擇。

不可否認(rèn)，MySQL有許多優(yōu)點(diǎn)，沒有它，我們公司就不會(huì)發(fā)展到今天這一步。但是隨著時(shí)間的推移，它的缺點(diǎn)慢慢的掩蓋了其優(yōu)點(diǎn)。由于缺乏更深入的專業(yè)知識(shí)，我們始終依賴于Percona MySQL和Ubuntu LTS 14.04的機(jī)帶版本，從未使用過外部工具，因?yàn)橐坏┦褂眠@些，就必須有人學(xué)習(xí)，了解以及維護(hù)這些工作流程，這將是一個(gè)巨大的人力成本的投入。

我們對(duì)MySQL 5.6不滿意的最主要原因

• 無(wú)法在線添加新的字段
  大表無(wú)法在線添加新字段的問題常常迫使我們每隔一個(gè)月就要暫停一次數(shù)據(jù)庫(kù)服務(wù)器。而對(duì)于像pt-online-schema-change這樣的在線DDL的第三方工具，我們又不是很了解，這讓我們感到很崩潰；
• 不能用一種可靠的方式在線添加索引
  事實(shí)上，MySQL 5.6開始支持在線添加索引了，但是在實(shí)際操作中，我們一直很緊張，因?yàn)槟悴恢肋@樣的添加方式是否可靠，錯(cuò)誤的操作會(huì)引起線上項(xiàng)目的巨大反應(yīng)；
• 5.6版本不支持json原生類型的字段（5.7.8版本開始引入）
• 缺少CTE（公用表表達(dá)式）和窗口函數(shù)等數(shù)據(jù)庫(kù)高級(jí)功能
• 在數(shù)據(jù)庫(kù)超出最大連接數(shù)的地方，我們已經(jīng)鎖住了；但是我們不能在我們的系統(tǒng)中找到問題的根源，最后發(fā)現(xiàn)就是幾百個(gè)連接執(zhí)行的select語(yǔ)句造成的，最終我們手動(dòng)殺死了這些查詢進(jìn)程；

  還有另外幾個(gè)看似不是問題的小問題，但是它們確實(shí)讓我們感到不滿：

• 沒有原生的布爾類型
  取而代之人們以Tinyint(0)來(lái)實(shí)現(xiàn)布爾類型，但是這樣就需要所有適配器語(yǔ)言執(zhí)行本地強(qiáng)制類型轉(zhuǎn)換，或者至少配置其ORM/DataModels；
• 使用mysqldump創(chuàng)建一個(gè)完整的數(shù)據(jù)庫(kù)備份要花費(fèi)了幾個(gè)小時(shí)（3-4小時(shí)）
  另外，并行備份數(shù)據(jù)庫(kù)也不是很容易
• 導(dǎo)入該數(shù)據(jù)庫(kù)備份比創(chuàng)建備份所花時(shí)間更長(zhǎng)（通常8-9小時(shí)）
• DDL語(yǔ)句不是事務(wù)的一部分
  2014年前后，我們的前合作伙伴25th floor建議我們使用Postgres，實(shí)踐證明，當(dāng)我們的應(yīng)用遷移到Postgres，并沒有增加我們團(tuán)隊(duì)的資源消耗。

開啟輕量級(jí)的Postgres？我們做到了！

我們遵循了建議，開始更深入的考察Postgres。為了簡(jiǎn)單起見，我們打算通過更改驅(qū)動(dòng)程序來(lái)達(dá)到遷移的目的。但是，同志們，我們錯(cuò)了，2014年11月，我們提出了第一個(gè)完全遷移到Postgres的愿景的方案，標(biāo)題為“[數(shù)據(jù)庫(kù)]勇敢的新世界”。然而，這個(gè)方案有太多的問題，最終在半年后被拋棄了。

但是我們一直沒有放棄。經(jīng)過一番討論和規(guī)劃，我們決定將在新的swat.io分析引擎項(xiàng)目中引入Postgres。事實(shí)證明，這是一個(gè)絕妙的機(jī)會(huì)：一切從零開始，與現(xiàn)在的系統(tǒng)無(wú)關(guān)。唯一的缺點(diǎn)就是我們主要的數(shù)據(jù)仍然在MySQL中。要遷移到Postgres，就需要同步數(shù)據(jù)。雖然不是很完美，但是從商業(yè)角度來(lái)講可以接受，從而為我們遷移的道路邁出了第一步。

這個(gè)項(xiàng)目需要花費(fèi)了幾個(gè)月時(shí)間完成，除了開發(fā)新的后端和UI代碼之外，還給了我們足夠的時(shí)間來(lái)學(xué)習(xí)新的數(shù)據(jù)庫(kù)系統(tǒng)：
- 如何將其集成到我們的開發(fā)人員虛擬機(jī)堆棧中？
- 數(shù)據(jù)庫(kù)角色概念如何運(yùn)作？
- 在寫入密集型的負(fù)載下它的表現(xiàn)如何？
- 我們可以多快地對(duì)新功能進(jìn)行原型設(shè)計(jì)？
- 當(dāng)我們不滿意查詢性能，該如何調(diào)整它？
- 怎樣實(shí)現(xiàn)數(shù)據(jù)庫(kù)的備份與恢復(fù)，以及它的工作原理？
- 如何使用PHP和NodeJS與現(xiàn)有的系統(tǒng)進(jìn)行整合？

鏡像模式？我們失敗了！

公司的分析引擎項(xiàng)目取得了成功，我們非常喜歡Postgres，但仍然無(wú)法遷移整個(gè)應(yīng)用程序，我們嘗試了另一種方法：
在我們的ORM中創(chuàng)建連接到Postgres的鏡像模型。在每次保存MySQL模型后，我們將觸發(fā)相同的保存到Postgres數(shù)據(jù)庫(kù)中。在理論上聽起來(lái)很好，實(shí)踐中總是有空白或某些區(qū)域沒有被正確的更新，我們不得不放棄這種方法。

繼續(xù)小的嘗試？我們做到了！

2015年夏季時(shí)，我們決定把一些記錄表遷移到Postgres；但是這些數(shù)據(jù)只是些無(wú)關(guān)緊要的臨時(shí)數(shù)據(jù)，所以放棄了數(shù)據(jù)遷移工作，直接遷移到新的數(shù)據(jù)庫(kù)，并停用刪除了相關(guān)的老數(shù)據(jù)庫(kù)。

緊接著的嘗試

在2015年8月左右，我們的團(tuán)隊(duì)再次對(duì)MySQL的使用表示無(wú)奈，并且嘗試遷移應(yīng)用程序代碼庫(kù)。這次嘗試比第一次更加先進(jìn)，一次真正“使用”幾乎完整的應(yīng)用程序。
只有一個(gè)“輕微”缺點(diǎn)：性能落后于MySQL！這在我們開發(fā)人員VM中甚至可以很容易地觀測(cè)到。為此，我們進(jìn)行了微型優(yōu)化（例如禁用SSL連接），并開始大修查詢。但是我們得出的主要問題是：我們的應(yīng)用程序在頁(yè)面加載期間（有時(shí)）會(huì)有成百上千個(gè)小型SQL語(yǔ)句的操作，而且每一個(gè)在Postgres中都有一點(diǎn)點(diǎn)慢，但是這一切還是在可容忍的范圍內(nèi)。
請(qǐng)注意，這個(gè)結(jié)論是未來(lái)數(shù)據(jù)庫(kù)優(yōu)化的主攻點(diǎn)：
- 在數(shù)據(jù)庫(kù)端執(zhí)行更多操作（諸如：使用更合理的的復(fù)合查詢等）
- 更多的使用數(shù)據(jù)庫(kù)（很多業(yè)務(wù)能夠在客戶端完成，但并不能表明它一定要在客戶端完成 ）。

令人尷尬的是，直到一年以后，我們才發(fā)現(xiàn)了這個(gè)罪魁禍?zhǔn)祝–akePHP2 Postgres驅(qū)動(dòng)程序開銷問題），給我們帶來(lái)如此多的需要優(yōu)化的地方，我們深知，還有許多幫助改進(jìn)的事情可以做，但是這在當(dāng)時(shí)超出了我們的能力范圍。

不幸的是，這次嘗試在幾個(gè)月內(nèi)都沒啥進(jìn)展，最后幾乎被遺忘。

挑戰(zhàn)一切，永不放棄！

不過，隨著時(shí)間的推移，新內(nèi)部項(xiàng)目中就出現(xiàn)了一個(gè)問題：

我們是要在MySQL還是在Postgres中創(chuàng)建新的表？

使用InnoDB數(shù)據(jù)庫(kù)引擎的外鍵功能來(lái)保持?jǐn)?shù)據(jù)一致并不總是很容易，往往在不同數(shù)據(jù)庫(kù)間同步數(shù)據(jù)時(shí)容易出錯(cuò)。但是感到慶幸的是，這些MySQL中的主要痛點(diǎn)都不復(fù)存在了：
- 不管你的表有多大，100MB也好，100GB也好，一旦新字段允許為NULL，并且沒有默認(rèn)值的情況下，數(shù)據(jù)庫(kù)默認(rèn)添加一個(gè)空操作；
- 添加新索引變動(dòng)輕而易舉，只要將它們?cè)O(shè)置成CONCURRENTLY ，然后你就完事了；
由于我們不僅擴(kuò)展了數(shù)據(jù)存儲(chǔ)，而且還擴(kuò)展了代碼庫(kù)，2016年前后，在引入新后臺(tái)(基于基礎(chǔ)API的JSONAPI)之后，寫測(cè)試達(dá)到了一個(gè)峰值，對(duì)于在測(cè)試中寫各種DML語(yǔ)句和DDL事務(wù)語(yǔ)句，我們有著豐富的經(jīng)驗(yàn)，但是最終不得不放棄這些，因?yàn)榇鎯?chǔ)了主要數(shù)據(jù)的MySQL不支持這些功能；

遷移的黎明

Postgres的流行趨勢(shì)仍在繼續(xù)，MySQL的問題越來(lái)越多，夜間的維護(hù)時(shí)間越來(lái)越長(zhǎng)，因?yàn)槲覀兿朐诖蟊碇刑砑訋讉€(gè)字段，開始時(shí)需要2個(gè)小時(shí)，到最后變動(dòng)完全不可測(cè)了，導(dǎo)致超出了我們宣布的停機(jī)維護(hù)時(shí)長(zhǎng)。

2016春夏以后，我們?cè)俅螄L試將整個(gè)應(yīng)用遷移到Postgres?；谇耙荒晏岢龅哪繕?biāo)和一些新嘗試，管理層經(jīng)過仔細(xì)考慮，一直給我們開綠燈。

我們?cè)?016年的幾次嘗試為我們帶來(lái)了大量的性能優(yōu)化經(jīng)驗(yàn)，并且?guī)椭覀兘怄i了一些發(fā)現(xiàn)bug隱藏屬性，這在之前我們團(tuán)隊(duì)聞所未聞的。經(jīng)過幾個(gè)月的努力奮斗以及大量測(cè)試之后，我們擬定在2017年2月份，準(zhǔn)備遷移工作。

雖然數(shù)據(jù)本身的遷移是成功的，但我們的應(yīng)用程序的關(guān)鍵部分存在性能問題。這導(dǎo)致我們不得不回滾，然而我們并沒有放棄，一直渴望找到問題根本原因。

在我們通往Postgres的道路上，我們得到了Markus Winand的use-the-index-luke和modern-sql框架的完美支持，他們對(duì)多種數(shù)據(jù)庫(kù)的全面深入了解令我們驚嘆不已。在我們團(tuán)隊(duì)的共同努力下，我們攻克了性能問題，最終被證明是由于數(shù)據(jù)庫(kù)的“冷卻”對(duì)性能造成了影響。遷移的最后一步，我們?cè)黾觩g_prewarm上選擇的關(guān)系，因而提高了對(duì)面向用戶的關(guān)鍵部件的首次體驗(yàn)。

2017年3月的第二次嘗試終于成功了。雖然數(shù)據(jù)遷移時(shí)間比預(yù)期的長(zhǎng)，但是當(dāng)流量到Postgres時(shí)，所有的一切都運(yùn)行正常。

我最終遷移數(shù)量

• 使用pgloader遷移120GB的數(shù)據(jù)，使用自定義腳本在tmux中手動(dòng)并行使用每個(gè)可用內(nèi)核（無(wú)法使單個(gè)pgloader實(shí)例可靠地工作，因此為每個(gè)表使用了專用的）
• 遷移本身花了大約4個(gè)小時(shí)：

  最終，我們并行運(yùn)行了32個(gè)SQL腳本來(lái)加快速度（雖然不是最好的辦法，但是確實(shí)很好用）

  1. 遷移數(shù)據(jù)
  2. 在Postgres中添加需要的新索引，充分發(fā)揮Postgres的性能潛力
  3. 創(chuàng)建新外鍵
  4. 在所有表上使用VACUUM ANALYZE
• 為了確保遷移成功，能夠正常工作且應(yīng)對(duì)一定的并發(fā)量，在準(zhǔn)備期間可能執(zhí)行了約20-30次，跨時(shí)間約為兩個(gè)月。
• 不得不轉(zhuǎn)換8個(gè)代碼庫(kù)（其中有三個(gè)很大的代碼庫(kù)）
• 所有pull requests中凡是超過250次comments，我們就要定位問題，跟蹤狀態(tài)
• 超過150次的代碼提交，并花費(fèi)了很長(zhǎng)時(shí)間仔細(xì)審查所有代碼更改
• 我們沒有開辟很多的git分支，但是猜想下，應(yīng)該有50左右
• 大約有400個(gè)文件被改動(dòng)
• 添加了大約7000行代碼，大約刪除了4000行
• 做了3次主要的攻關(guān)嘗試
• 需要進(jìn)行兩次遷移嘗試;
• 在第一次失敗和第二次成功遷移的過程中，我們只在代碼庫(kù)中發(fā)現(xiàn)了兩個(gè)真正意義上的bug。我們假定我們?cè)谀承O端情況下仍然存在bug，但這些經(jīng)驗(yàn)給了解決問題的信心，我們正變得越來(lái)越好。

經(jīng)驗(yàn)教訓(xùn)和收獲

• 批量導(dǎo)入數(shù)據(jù)后，使用PostgreSQL 的vacuum analyze，能夠更新統(tǒng)計(jì)信息，讓PG的planner能夠算出更準(zhǔn)確的執(zhí)行計(jì)劃。
• datetime with time zone類型顯然返回一個(gè)帶時(shí)區(qū)的字符串格式，但是這個(gè)類型常常帶來(lái)一些不必要的麻煩（建議使用這個(gè)）
• 一些占主導(dǎo)地位的代碼庫(kù)仍然基于CakePHP2，并且有以下一些問題：
  1. Postgres驅(qū)動(dòng)程序?qū)?shù)據(jù)庫(kù)進(jìn)行了額外的調(diào)用，以便為每個(gè)收到的結(jié)果獲取列元數(shù)據(jù)。這導(dǎo)致了許多即使小的開銷查詢的延遲。不幸的是，我們只是發(fā)現(xiàn)這個(gè)罪魁禍?zhǔn)自陂_發(fā)的晚期，但是，最終做到了！以下是我們?nèi)绾谓鉀Q這個(gè)問題。請(qǐng)注意，啟用應(yīng)用程序內(nèi)SQL記錄并未顯示此開銷。必須更深入地激活log_statement。
  2. 我們遇到了遷移后幾天才發(fā)現(xiàn)的某些數(shù)據(jù)的問題。我們已經(jīng)有一個(gè)專門的Postgres驅(qū)動(dòng)程序，用來(lái)更好的兼容數(shù)據(jù)泄露問題；
  3. 在某些地方使用了CakePHP ORM來(lái)查詢已經(jīng)有很長(zhǎng)列名稱的模型的63個(gè)字符的標(biāo)識(shí)符限制。由于ORMs內(nèi)部的混疊，字段將被截?cái)啵虼嗽诜祷貞?yīng)用程序后會(huì)相互覆蓋。該部分必須在純SQL中重寫，并將結(jié)果轉(zhuǎn)換為與CakePHP兼容的結(jié)構(gòu)。
• 切換到Laravel倉(cāng)庫(kù)是件令人快樂的事：
  1. 它允許我們擺脫大量的手工布爾類型轉(zhuǎn)換，因?yàn)樗脑愋偷闹С植紶栃汀?/li>
  2. 一旦遇到一個(gè)異常，為了解決問題不得不改框架代碼。現(xiàn)在所有更改的都是在配置文件中體現(xiàn)，數(shù)據(jù)庫(kù)遷移和測(cè)試（主要支持適用的原生類型）。
• 能夠優(yōu)化我們的“線程注釋” - 將代碼從“n * m”查詢轉(zhuǎn)換為單個(gè)查詢（盡管非常大和復(fù)雜），即使在具有十萬(wàn)條注釋的線程上也能獲得很好的性能（使用CTE和窗體函數(shù)）
• 與MySQL相比，PostgreSQL中的EXPLAIN（ANALYZE，BUFFERS）的輸出更加實(shí)用性
• MySQL與Postgres的結(jié)果字母順序是不同的。MySQL使用自己的機(jī)制排列，Postgres則取決于操作系統(tǒng)的特性。（提示：PostgreSQL 10中有變化）
• 在MySQL中，要使用UTC（為了在整個(gè)系統(tǒng)中使用沒有異常），首先必須導(dǎo)入時(shí)區(qū)；而在Postgres，它是開箱即用的；
• 文本搜索主要由我們的ElasticSearch服務(wù)器驅(qū)動(dòng)，但有時(shí)您不需要它的全部功能，Postgres全文搜索能力也是足夠好的，這一切還得益于GIN索引的高可用性；
• 部分索引（使用WHERE -clauses）并且能夠在索引中使用表達(dá)式，這種新方法使得我們的生活變得更容易；
• MySQL中也有觸發(fā)器這個(gè)功能，但對(duì)我們來(lái)說(shuō)，它在具有WHEN功能的Postgres中更有用，能夠簡(jiǎn)化觸發(fā)函數(shù)本身并將其與應(yīng)用它的條件分離；
• 通過使用可用的新功能（部分索引，窗口函數(shù)），我們可以將系統(tǒng)平均負(fù)載減少一半（從8降低到4）。我們知道，這就像比較蘋果和橘子，因?yàn)榧词乖趹?yīng)用程序中也需要調(diào)整許多部分; 但是衡量這種變化過程本身是非常有趣的；
• WAL歸檔可能會(huì)讓你感到驚訝。我們有一些看似平常的更新語(yǔ)句突然產(chǎn)生100GB的WAL文件的情況。你最好需要一個(gè)可以在線擴(kuò)展的文件系統(tǒng)；
• 重構(gòu)是我們團(tuán)隊(duì)的靈魂，特別是命名。為了快速理解所命名字段的意思和加強(qiáng)系統(tǒng)命名的規(guī)范和一致性。Postgres使我們可以輕松重命名數(shù)百個(gè)索引和外鍵，而不用讓服務(wù)器停機(jī)。
• 與世間萬(wàn)物一樣 ，調(diào)試復(fù)雜的系統(tǒng)也是一門藝術(shù)。相比之下，我們發(fā)現(xiàn)Postgres的文檔是非常通俗易懂的。根據(jù)我們的經(jīng)驗(yàn)，正是由于Postgres充分解釋每個(gè)復(fù)雜概念和技術(shù)細(xì)節(jié)才使得MySQL的一些技術(shù)信息變得更加容易理解。

結(jié)語(yǔ)

令人精力紊亂的兩種數(shù)據(jù)庫(kù)和諧同步（由于終究都服務(wù)于同一個(gè)領(lǐng)域）和令人頭痛的MySQL保護(hù)對(duì)我們團(tuán)隊(duì)產(chǎn)生了極大的負(fù)面影響，萬(wàn)幸的是Postgres總算完畢了這一切。完結(jié)搬遷的那一刻，我們的滿足感難以言表。

過去的已成前史，展望2017年，進(jìn)步功能，非常好地處理拓展需要以及進(jìn)步客戶滿意度將變成我們的奮斗目標(biāo)。