乍一看，這篇文章看起來像是標題黨。可是咱們不能否定一個事實，公司從MySQL到PostgreSQL遷移現已開端，由于MySQL越來越雜亂了。

回想公司剛成立那會兒，我們團隊只有三個人，沒有太多的資源和精力投入到技術的基礎架構上；如果你正處在一個小團隊，那么遷移到PostgreSQL就是個不錯的選擇。

不可否認，MySQL有許多優點，沒有它，我們公司就不會發展到今天這一步。但是隨著時間的推移，它的缺點慢慢的掩蓋了其優點。由于缺乏更深入的專業知識，我們始終依賴于Percona MySQL和Ubuntu LTS 14.04的機帶版本，從未使用過外部工具，因為一旦使用這些，就必須有人學習，了解以及維護這些工作流程，這將是一個巨大的人力成本的投入。

我們對MySQL 5.6不滿意的最主要原因

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

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

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

開啟輕量級的Postgres？我們做到了！

我們遵循了建議，開始更深入的考察Postgres。為了簡單起見，我們打算通過更改驅動程序來達到遷移的目的。但是，同志們，我們錯了，2014年11月，我們提出了第一個完全遷移到Postgres的愿景的方案，標題為“[數據庫]勇敢的新世界”。然而，這個方案有太多的問題，最終在半年后被拋棄了。

但是我們一直沒有放棄。經過一番討論和規劃，我們決定將在新的swat.io分析引擎項目中引入Postgres。事實證明，這是一個絕妙的機會：一切從零開始，與現在的系統無關。唯一的缺點就是我們主要的數據仍然在MySQL中。要遷移到Postgres，就需要同步數據。雖然不是很完美，但是從商業角度來講可以接受，從而為我們遷移的道路邁出了第一步。

這個項目需要花費了幾個月時間完成，除了開發新的后端和UI代碼之外，還給了我們足夠的時間來學習新的數據庫系統：
- 如何將其集成到我們的開發人員虛擬機堆棧中？
- 數據庫角色概念如何運作？
- 在寫入密集型的負載下它的表現如何？
- 我們可以多快地對新功能進行原型設計？
- 當我們不滿意查詢性能，該如何調整它？
- 怎樣實現數據庫的備份與恢復，以及它的工作原理？
- 如何使用PHP和NodeJS與現有的系統進行整合？

鏡像模式？我們失敗了！

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

繼續小的嘗試？我們做到了！

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

緊接著的嘗試

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

令人尷尬的是，直到一年以后，我們才發現了這個罪魁禍首（CakePHP2 Postgres驅動程序開銷問題），給我們帶來如此多的需要優化的地方，我們深知，還有許多幫助改進的事情可以做，但是這在當時超出了我們的能力范圍。

不幸的是，這次嘗試在幾個月內都沒啥進展，最后幾乎被遺忘。

挑戰一切，永不放棄！

不過，隨著時間的推移，新內部項目中就出現了一個問題：

我們是要在MySQL還是在Postgres中創建新的表？

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

遷移的黎明

Postgres的流行趨勢仍在繼續，MySQL的問題越來越多，夜間的維護時間越來越長，因為我們想在大表中添加幾個字段，開始時需要2個小時，到最后變動完全不可測了，導致超出了我們宣布的停機維護時長。

2016春夏以后，我們再次嘗試將整個應用遷移到Postgres。基于前一年提出的目標和一些新嘗試，管理層經過仔細考慮，一直給我們開綠燈。

我們在2016年的幾次嘗試為我們帶來了大量的性能優化經驗，并且幫助我們解鎖了一些發現bug隱藏屬性，這在之前我們團隊聞所未聞的。經過幾個月的努力奮斗以及大量測試之后，我們擬定在2017年2月份，準備遷移工作。

雖然數據本身的遷移是成功的，但我們的應用程序的關鍵部分存在性能問題。這導致我們不得不回滾，然而我們并沒有放棄，一直渴望找到問題根本原因。

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

2017年3月的第二次嘗試終于成功了。雖然數據遷移時間比預期的長，但是當流量到Postgres時，所有的一切都運行正常。

我最終遷移數量

• 使用pgloader遷移120GB的數據，使用自定義腳本在tmux中手動并行使用每個可用內核（無法使單個pgloader實例可靠地工作，因此為每個表使用了專用的）
• 遷移本身花了大約4個小時：

  最終，我們并行運行了32個SQL腳本來加快速度（雖然不是最好的辦法，但是確實很好用）

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

經驗教訓和收獲

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

結語

令人精力紊亂的兩種數據庫和諧同步（由于終究都服務于同一個領域）和令人頭痛的MySQL保護對我們團隊產生了極大的負面影響，萬幸的是Postgres總算完畢了這一切。完結搬遷的那一刻，我們的滿足感難以言表。

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