[DB]PostgreSQL 與 MySQL 相比，優勢何在？

weixin_34290000發表於2016-10-28
MySql

PostgreSQL 與 MySQL 相比，優勢何在？ - 資料庫 - 知乎
https://www.zhihu.com/question/20010554
Pg 沒有 MySQL 的各種坑

MySQL 的各種 text 欄位有不同的限制, 要手動區分 small text, middle text, large text... Pg 沒有這個限制, text 能支援各種大小.

按照 SQL 標準, 做 null 判斷不能用 = null, 只能用 is null
the result of any arithmetic comparison with NULL is also NULL
但 pg 可以設定 transform_null_equals 把 = null 翻譯成 is null 避免踩坑

不少人應該遇到過 MySQL 裡需要 utf8mb4 才能顯示 emoji 的坑, Pg 就沒這個坑.

MySQL 的事務隔離級別 repeatable read 並不能阻止常見的併發更新, 得加鎖才可以, 但悲觀鎖會影響效能, 手動實現樂觀鎖又複雜. 而 Pg 的列裡有隱藏的樂觀鎖 version 欄位, 預設的 repeatable read 級別就能保證併發更新的正確性, 並且又有樂觀鎖的效能. 附帶一個各資料庫對隔離級別的行為差異比較調查: http://www.cs.umb.edu/~poneil/iso.pdf

MySQL 不支援多個表從同一個序列中取 id, 而 Pg 可以.

MySQL 不支援 OVER 子句, 而 Pg 支援. OVER 子句能簡單的解決 "每組取 top 5" 的這類問題.

幾乎任何資料庫的子查詢 (subquery) 效能都比 MySQL 好.

更多的坑:
http://blog.ionelmc.ro/2014/12/28/terrible-choices-mysql/

不少人踩完坑了, 以為換個資料庫還得踩一次, 所以很抗拒, 事實上不是!!!

Pg 不僅僅是 SQL 資料庫

它可以儲存 array 和 json, 可以在 array 和 json 上建索引, 甚至還能用表示式索引. 為了實現文件資料庫的功能, 設計了 jsonb 的儲存結構. 有人會說為什麼不用 Mongodb 的 BSON 呢? Pg 的開發團隊曾經考慮過, 但是他們看到 BSON 把 ["a", "b", "c"] 存成 {0: "a", 1: "b", 2: "c"} 的時候就決定要重新做一個 jsonb 了... 現在 jsonb 的效能已經優於 BSON.

現在往前端偏移的開發環境裡, 用 Pg + PostgREST 直接生成後端 API 是非常快速高效的辦法:
begriffs/postgrest · GitHub
postgREST 的效能非常強悍, 一個原因就是 Pg 可以直接組織返回 json 的結果.

它支援伺服器端指令碼: TCL, Python, R, Perl, Ruby, MRuby ... 自帶 map-reduce 了.

它有地理資訊處理擴充套件 (GIS 擴充套件不僅限於真實世界, 遊戲裡的地形什麼的也可以), 可以用 Pg 搭尋路伺服器和地圖伺服器:
PostGIS — Spatial and Geographic Objects for PostgreSQL

它自帶全文搜尋功能 (不用費勁再裝一個 elasticsearch 咯):
Full text search in milliseconds with PostgreSQL 不過一些語言相關的支援還不太完善, 有個 bamboo 外掛用調教過的 mecab 做中文分詞, 如果要求比較高, 還是自己分了詞再存到 tsvector 比較好.

它支援 trigram 索引.
trigram 索引可以幫助改進全文搜尋的結果: PostgreSQL: Documentation: 9.3: pg_trgm
trigram 還可以實現高效的正則搜尋 (原理參考 https://swtch.com/~rsc/regexp/regexp4.html )

MySQL 處理樹狀回覆的設計會很複雜, 而且需要寫很多程式碼, 而 Pg 可以高效處理樹結構:
Scaling Threaded Comments on Django at Disqus
http://www.slideshare.net/quipo/trees-in-the-database-advanced-data-structures

它可以高效處理圖結構, 輕鬆實現 "朋友的朋友的朋友" 這種功能:
http://www.slideshare.net/quipo/rdbms-in-the-social-networks-age

它可以把 70 種外部資料來源 (包括 Mysql, Oracle, CSV, hadoop ...) 當成自己資料庫中的表來查詢:
Foreign data wrappers

心動不如行動

Converting MySQL to PostgreSQL

相關文章