你可能會問自己 “為什么選擇PostgreSQL ?” 開源數(shù)據(jù)庫我們有好幾種選擇 (本文參考 MySQL， MariaDB 和 Firebird )， 那么PostgreSQL具有哪些其它開源數(shù)據(jù)庫不具備的特性呢? PostgreSQL宣稱它是 “世界上最先進的開源數(shù)據(jù)庫。” 我們將會給出PostgreSQL這么宣稱的原因。本系列將帶我們一起看看數(shù)據(jù)存儲 – 數(shù)據(jù)模型， 結(jié)構(gòu)， 數(shù)據(jù)類型， 和大小限制、數(shù)據(jù)操作和檢索。

數(shù)據(jù)模型

PostgreSQL 不僅僅是關(guān)系型，它也是對象關(guān)系型，這使它一定程度優(yōu)于其他一些開源數(shù)據(jù)庫，例如 MySQL，MariaDB 和 Firebird。一個對象 - 關(guān)系數(shù)據(jù)庫的一個基本特征是支持用戶自定義對象和它的屬性，包括數(shù)據(jù)類型、函數(shù)、操作符，域和索引。這使得 PostgreSQL 非常靈活和健壯，除此之外，復(fù)雜的數(shù)據(jù)結(jié)構(gòu)可以被創(chuàng)建，存儲和檢索，下面的例子可以看到標(biāo)準 RDBMS 不支持的嵌套和復(fù)合結(jié)構(gòu)。

數(shù)據(jù)類型和結(jié)構(gòu)

PostgreSQL 有著廣泛的被支持數(shù)據(jù)類型列表，除了 numeric， floating-point， string， boolean 和你能想到的數(shù)據(jù)類型 (并且支持各種選項)，PostgreSQL 還引以為傲地支持 uuid， monetary， enumerated， geometric， binary， network address，bit string， text search， xml， json， array， composite 和 range 數(shù)據(jù)類型，以及一些內(nèi)部對象標(biāo)識和日志位置類型。公平地說，MySQL，MariaDB 和 Firebird 在不同程度上支持上面部分數(shù)據(jù)類型，但僅僅 PostgreSQL 支持以上全部數(shù)據(jù)類型。

讓我們仔細看看其中幾個數(shù)據(jù)類型：

網(wǎng)絡(luò)地址類型

PostgreSQL 提供用于存儲不同網(wǎng)絡(luò)地址的類型， CIDR (Classless Internet Domain Routing) 數(shù)據(jù)類型適合 IPv4 和 IPv6 網(wǎng)絡(luò)地址，CIDR 的一些例子：

• 192.168.100.128/25
• 10.1.2.3/32
• 2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128
• ::ffff:1.2.3.0/128

也可用于網(wǎng)絡(luò)地址存儲的是 INET 數(shù)據(jù)類型， 用于 IPv4 和 IPv6 主機，子網(wǎng)是可選的，MACADDR 數(shù)據(jù)類型用于存儲硬件標(biāo)識的 MAC 地址，例如 08-00-2b-01-02-03。MySQL 和 MariaDB 提供一些 INET 函數(shù)用于網(wǎng)絡(luò)地址轉(zhuǎn)換，但不直接提供用于存儲網(wǎng)絡(luò)地址的數(shù)據(jù)類型， Firebird 也沒有網(wǎng)絡(luò)地址類型。

多維數(shù)組

因為 PostgreSQL 是對象關(guān)系數(shù)據(jù)庫，數(shù)組的元素可以存儲大多數(shù)現(xiàn)有的數(shù)據(jù)類型，通過將方括號附加到使用數(shù)組類型的字段后就能定義數(shù)組，可以指定數(shù)組大小，但不是必需的。讓我們通過一個假日野餐菜單展示數(shù)組的使用：(譯者注：建表腳本有錯誤，創(chuàng)建表會報錯，作者大概只是展示數(shù)組數(shù)據(jù)類型的使用。)

MySQL， MariaDB， 和 Firebird 不具備這種能力，如果想把類似這樣的數(shù)組存儲在傳統(tǒng)的關(guān)系數(shù)據(jù)庫中，替代的解決方法是為數(shù)組值每一行創(chuàng)建單獨的表。

幾何類型

地理數(shù)據(jù)正迅速成為很多應(yīng)用程序的核心需求， PostgreSQL 一直支持各種各樣的幾何數(shù)據(jù)類型，如點、線、圓、多邊形。路徑數(shù)據(jù)類型就是其中之一。路徑中包含多個點序列，可以開放 (開始和結(jié)束點是沒有連接的) 或封閉 (開始和結(jié)束點連接)。讓我們用一個徒步旅行的例子作為一個路徑，在這個例子中我的徒步旅行路線是循環(huán)的，開始點和結(jié)束點相連，所以我的路徑是閉環(huán)的。坐標(biāo)內(nèi)的圓括號意味著一個封閉的路徑而方括號表示開放的路徑。

PostGIS 擴展增強了 PostgreSQL 現(xiàn)有的幾何數(shù)據(jù)特性，例如額外的空間類型，函數(shù)，操作符和索引，它支持位置特性以及柵格和矢量數(shù)據(jù)數(shù)據(jù)。它還提供了與各種第三方開源和專有的地理空間處理工具的互操作性，例如映射和呈現(xiàn)數(shù)據(jù). 今年一月份我們?yōu)?Compose PostgreSQL 部署提供了 PostGIS：為所有 Compose PostgreSQL 部署的 PostGIS。

注意在 MySQL 5.7.8 和 MariaDB 5.3.3，才添加了支持 OpenGIS 地理信息標(biāo)準的數(shù)據(jù)類型擴展， 這個版本的 MySQL 和之后的 MariaDB 版本提供了和 類似 PostgreSQL 方便使用的幾何數(shù)據(jù)類型的數(shù)據(jù)類型存儲。 然而，在 MySQL 和 MariaDB，數(shù)據(jù)值必須先使用簡單的命令轉(zhuǎn)換為幾何格式之后才能插入到表中，F(xiàn)irebird 目前并不提供地理數(shù)據(jù)類型。

JSON 支持

PostgreSQL 的 JSON 支持在 SQL 數(shù)據(jù)庫中支持非結(jié)構(gòu)化數(shù)據(jù)，當(dāng)數(shù)據(jù)結(jié)構(gòu)由于處在開發(fā)中需要靈活性或數(shù)據(jù)對象包含了未知的字段時是有用的。

JSON 數(shù)據(jù)類型強制檢查 JSON 有效性，這讓你可以使用專門的 JSON 操作符和 PostgreSQL 提供的內(nèi)置函數(shù)用于查詢和操作數(shù)據(jù)。也可用 JSONB 類型 – JSON 的二進制形式，與 JSON 不同的是它刪除了數(shù)據(jù)中的空格，保存對象的順序不一樣，存儲層面做了優(yōu)化，只有最后一個重復(fù)的鍵值保留。JSONB 通常是首選的格式因為它需要更少的空間存儲對象，可以被索引，處理速度更快，因為它不需要被解析，要了解更多，請查看： Is PostgreSQL Your Next JSON Database?

在 MySQL 5.7.8 和 MariaDB 10.0.1 支持 JSON 對象， 雖然目前在這些數(shù)據(jù)庫中有不同的函數(shù)和運算符支持 JSON，它們的索引方式與 PostgreSQL 的 JSONB 不同。 Firebird 目前僅支持文本對象的 JSON。

創(chuàng)新的數(shù)據(jù)類型

如果 PostgreSQL 提供的數(shù)據(jù)類型列表還不夠，您還可以使用 CREATE TYPE 命令創(chuàng)建新的數(shù)據(jù)類型，例如復(fù)合類型，枚舉，范圍等。 這里是一個創(chuàng)建并且使用新創(chuàng)建的復(fù)合類型的例子。

MySQL，MariaDB，和 Firebird 不提供這種強大的功能，因為它們不是面向?qū)ο蟮摹?/p>

Data Size

PostgreSQL 可以處理大量的數(shù)據(jù)。下面列出了當(dāng)前的大小限制：

在 Compose 平臺我們會自動部署擴展，所以您不必擔(dān)心數(shù)據(jù)增長。但是，正如每位 DBA 知道的，最好警惕容量上的限制，我們建議您在創(chuàng)建表和索引時遵從常規(guī)性的指導(dǎo)。

相比之下， MySQL 和 MariaDB 行大小限制為 65535 字節(jié)，F(xiàn)irebird 宣稱最大行大小為 64KB ，通常數(shù)據(jù)大小被操作系統(tǒng)文件大小限制。因為 PostgreSQL 可以將表數(shù)據(jù)存儲在多個小文件，它可以繞過這個限制 – 不過需要注意的是太多的文件可能對性能造成負面影響。然而，MySQL 和 MariaDB 確實比 PostgreSQL 單表支持更多的列 (最多 4096 列，與數(shù)據(jù)類型有關(guān)) 和更大的單表大小，但在罕見的情況下，現(xiàn)有的 PostgreSQL 限制需要被超過。

數(shù)據(jù)完整性

PostgreSQL 毫無疑問符合 ANSI-SQL:2008 標(biāo)準，完全遵從 ACID (Atomicity， Consistency， Isolation and Durability) ，并且它因穩(wěn)定性和事務(wù)完整性而聞名。它支持的主鍵，約束，外鍵，唯一約束，非空約束，以及其它數(shù)據(jù)完整性特性確保只有合法的數(shù)據(jù)被存儲。

MySQL 和 MariaDB 使用合 InnoDB / XtraDB 存儲引擎可兼容更多的 SQL 標(biāo)準，他們現(xiàn)在為 SQL 模式提供一個 STRICT 選項，SQL 模式?jīng)Q定了使用的數(shù)據(jù)檢查方法。然而，基于使用的模式，非法和截斷的數(shù)據(jù)可能會被插入或更新時創(chuàng)建。這些數(shù)據(jù)庫現(xiàn)在都不支持檢查約束，外鍵約束也存在許多附加說明。此外，數(shù)據(jù)的完整性可能會大大取決于所選擇的存儲引擎。 MySQL ，MariaDB 長期側(cè)重于速度和效率甚于遵從完整性和遵從性。

總結(jié)

PostgreSQL 有很多功能。使用一個對象 - 關(guān)系模型，它支持復(fù)雜的結(jié)構(gòu)和內(nèi)置的豐富用戶定義的數(shù)據(jù)類型，它提供了廣闊的數(shù)據(jù)容量和可信的數(shù)據(jù)完整性，你可能不需要我這里回顧的所有高級特性，但由于數(shù)據(jù)需求發(fā)展很快，擁有所有這些毫無疑問具有明顯的好處。

如果 PostgreSQL 不能完全滿足你的需求，或者你更傾向于更多選型， 那么看看我們在 Compose 平臺提供的 NoSQL 數(shù)據(jù)庫或其他開源 SQL 數(shù)據(jù)庫，它們每個都有自己的優(yōu)勢，Compose 堅信選擇合適的數(shù)據(jù)庫為當(dāng)務(wù)之急，作為解決方案，有時候這也意味著需要選擇多個數(shù)據(jù)庫。

準備好了看更多關(guān)于 PostgreSQL 的內(nèi)容嗎?剛剛我們介紹了存儲數(shù)據(jù)，包括數(shù)據(jù)模型、數(shù)據(jù)結(jié)構(gòu)、類型、大小限制，給出了一些 PostgreSQL 為何如此聲稱的理由，接下來我們將介紹數(shù)據(jù)操作和檢索，包括索引、虛擬表特性和查詢能力。

索引

PostgreSQL 提供其他開源數(shù)據(jù)庫所不具備的索引功能。PostgreSQL 除了標(biāo)準索引類型之外，還支持局部、表達式、GiST、GIN 索引。我們來看上述這些特殊索引。

局部索引

當(dāng)你僅僅想為一張表的子集添加索引就可以創(chuàng)建局部索引(Partial Indexes)，比如某列的值符合一個特定條件的所有行。這個有利特性讓你保持合理的索引大小，并達成提高性能和減少磁盤空間的目標(biāo)。局部索引的一個關(guān)鍵是被索引的列可以與提供子集約束條件的列不同。比如，你可能只想索引那些支付客戶的帳號而不包括為內(nèi)部測試而創(chuàng)建的帳號。

說明重要的一點，有時候 MySQL 的局部索引(Partial Indexes 有時也被翻譯為部分索引)術(shù)語用來指截取被索引的列值至一定數(shù)量的字節(jié)數(shù)，而不是基于一個條件去限制被索引行的數(shù)量。我們這里描述的局部索引 MySQL 不支持。

表達式索引

創(chuàng)建表達式索引用來索引通過函數(shù)預(yù)計算得到的一個列。這些新值在查詢時被索引和對待如同常量，而不是查詢每次運行時需要重新計算。舉一個例子，如果你有一個網(wǎng)頁點擊日志，采集他們接收的任何格式 URL 點擊，你可能想創(chuàng)建一個基于小寫的標(biāo)準 URL 的索引(PostgreSQL 是大小寫敏感的，compose.io 和 Compose.io 會被認為是不同的結(jié)果)：

GIST 和 GIN

GiST(Generalized Search Tree)允許聯(lián)合 B 樹、R 樹和用戶自定義索引類型來創(chuàng)建擁有先進查詢能力的定制索引。GiST 在 PostGIS(從 2015 年 1 月以來我們所有 PostgreSQL 部署的標(biāo)配)和 OpenFTS(一個開源全文搜索引擎)中使用。PostgreSQL 也支持 SP-GiST，它允許使數(shù)據(jù)檢索異?？焖俚姆謪^(qū)查找索引的創(chuàng)建。

GIN(Generalized Inverted Index)可以索引復(fù)雜數(shù)據(jù)類型。復(fù)雜數(shù)據(jù)類型允許你以不同方式聯(lián)合其他數(shù)據(jù)類型來創(chuàng)建完全定制化的數(shù)據(jù)類型。查看本系列的 Part I 以概覽復(fù)雜數(shù)據(jù)類型。

創(chuàng)建 GiST 和 GIN 索引的語法是，CREATE INDEX .. ON .. USING GIST|GIN ..。簡單!在 PostgreSQL 9.5(譯者注：目前處于 beta 2)，BRIN(Block Range Index)將被支持。BRIN 允許基于被索引的列將大表打散為一系列范圍。這意味著查詢計劃只需要掃描查詢所限定的某一個范圍。此外，范圍索引所需要的磁盤空間大小比標(biāo)準 B 樹索引要小很多。

對比

我們關(guān)注的其他 SQL 數(shù)據(jù)庫在表達式索引上正在縮小差距。在 MySQL 5.7.6，生成列(Generated Column)開始被支持，可以用作表達式索引。對于 MariaDB，虛擬列(Virtual Column，也成為生成列或計算列)在版本 5.2 中開始支持，但僅支持使用內(nèi)置函數(shù)創(chuàng)建列(無法使用用戶自定義函數(shù))。Firebird 的 2.0 版本，使用計算列(Computed Column)的表達式索引開始被支持。然而，這些數(shù)據(jù)庫不支持局部、GiST 或 GIN 索引。當(dāng)創(chuàng)建索引并希望去分析它們的性能時，別忘記去閱讀 mySidewalk 的 Matt Barr 書寫的技術(shù)文章 Simple Index Checking with PostgreSQL。

虛擬表特性

虛擬表在很多查詢中是必需的。我們對比過的所有 SQL 數(shù)據(jù)庫提供一些虛擬表功能，PostgreSQL 提供了更多。

通用表達式和遞歸

PostgreSQL 通過 WITH 子句支持通用表表達式(Common Table Expression，CTE)。我們在技術(shù)文章 PostgreSQL – Series Random and With 中展示過該特性。通用表表達式使你在查詢語句以內(nèi)聯(lián)方式創(chuàng)建虛擬表，邏輯上表達一系列操作的順序，這相比在其他地方使用子查詢創(chuàng)建虛擬表更容易閱讀和保證質(zhì)量。PostgreSQL 中的通用表表達式可以遞歸使用。這個方便的功能使你單步遍歷一個層次結(jié)構(gòu)，語句重復(fù)自我引用直到?jīng)]有數(shù)據(jù)被返回。這是一個遞歸通用表表達式的例子，在一個話題分類中標(biāo)識了層級、話題、父子關(guān)系： 

MySQL 和 MariaDB 不使用 WITH 子句，所以，并不正式支持通用表表達式。這些數(shù)據(jù)庫中可以使用子查詢創(chuàng)建衍生表，然而它們并不允許遞歸。Firebird 這方面比 MySQL 和 MariaDB 好，與 PostgreSQL 一樣支持使用 WITH 子句的通用表表達式并提供遞歸功能。

物化視圖

物化視圖是另一項 PostgreSQL 支持的實用的虛擬表特性。物化視圖就像普通視圖那樣代表一個經(jīng)常使用的查詢結(jié)果集，只是結(jié)果集像一個普通表那樣存儲在磁盤上。物化視圖也可以添加索引，不像普通視圖每次請求時重新生成，物化視圖是及時的快照。它們只在特定時刻刷新。這可以極大地加快使用物化視圖的查詢的執(zhí)行速度。無需在查詢中使用普通視圖或做復(fù)雜表關(guān)聯(lián)或運行聚合函數(shù)，使用一個包含所需數(shù)據(jù)在磁盤的物化視圖可以提高效率。當(dāng)你在一個物化視圖中更新數(shù)據(jù)，可以按需使用 REFRESH 命令。這是一個物化視圖的例子，生成聚合收益數(shù)據(jù)：

Firebird、MySQL 和 MariaDB 并不支持物化視圖，但可以使用一種變通方案，創(chuàng)建一張普通表并使用存儲過程或者觸發(fā)器更新它。

查詢能力

PostgreSQL 的查詢功能是豐富的。前面章節(jié)討論了 WITH 子句，現(xiàn)在來看 SELECT 語句中使用的另外兩個可選特性。

集合查詢

PostgreSQL 提供 UNION、INTERSECT 和 EXCEPT 子句用于 SELECT 語句之間的交互。UNION 將第二個 SELECT 語句的結(jié)果附加到第一個。 INTERSECT 返回兩個 SELECT 語句均有的行。EXCEPT 返回第一個 SELECT 語句有而第二個 SELECT 語句沒有的行。我們看一個使用 EXCEPT 的例子，該語句返回客戶聯(lián)系信息除非客戶一周內(nèi)已經(jīng)收到并回復(fù)郵件。

MySQL、MariaDB 和 Firebird 都支持 UNION，但都不支持 INTERSECT 和 EXCEPT。然而，通過查詢中的關(guān)聯(lián)以及 EXISTS 條件，可以獲取與 PostgreSQL 相同的結(jié)果集。當(dāng)然，這會使查詢變得更為復(fù)雜。

窗口函數(shù)

窗口函數(shù)基于結(jié)果集的部分行(一個子集一個窗口)運行聚合函數(shù)，極其有用。實質(zhì)上，它遍歷與當(dāng)前行有關(guān)的分區(qū)中的所有行，運行該函數(shù)。常用函數(shù)包括 ROW_NUMBER()、RANK()、DENSE_RANK() 和 PERCENT_RANK()。關(guān)鍵詞 OVER，與 PARTITION BY 和 ORDER BY 一起，指示使用一個窗口函數(shù)。舉一個例子，在下面的章節(jié) “函數(shù)及其他”，我們使用一個窗口函數(shù) ROW_NUMBER() OVER 來確定一系列數(shù)值的中位數(shù)。注意 WINDOW 子句并不是必需的，只是用來創(chuàng)建和命名窗口以幫助保持條理。Firebird、MySQL 和 MariaDB 現(xiàn)階段不支持窗口函數(shù)，雖然窗口函數(shù)幾年前就在 Firebird 3 的支持計劃中宣布。

網(wǎng)絡(luò)地址類型橫向子查詢

在 FROM 子句中關(guān)鍵詞 LATERAL 可以作用于子查詢，允許子查詢和之前創(chuàng)建的其他表或虛擬表之間做交叉引用。查詢語句如此可以更為簡化。它的工作方式是每一行與交叉引用的表作衡量，這意味著查詢語句執(zhí)行的速度加快。這里是一個例子，我們想要一個學(xué)生列表以了解他們最近是否閱讀面向技術(shù)的話題：

MySQL、Firebird 和 MariaDB 現(xiàn)階段不支持橫向子查詢(Lateral Subquery)。同樣地，存在變通方案，但是查詢語句將變得更為復(fù)雜。另一件事需要說明，MySQL 和 MariaDB 不支持完全外連接，但一個使用 UNION ALL 的變通方案可以用來合并兩張表的所有行。

函數(shù)及其他

PostgreSQL 提供健壯的內(nèi)置操作符和函數(shù)，包括那些支持本系列 Part I 里特定數(shù)據(jù)類型，但你可以創(chuàng)建自己的操作符和函數(shù)(包括聚合函數(shù))，如同定制的存儲過程和觸發(fā)器。我們無法提及所有這些細節(jié)，因為內(nèi)容過多，但我們可以看函數(shù)相關(guān)的兩個簡單例子。PostgreSQL 支持 4 種用戶自定義函數(shù)：查詢語言、過程語言、C 語言和內(nèi)部語言。每一種都可以傳入和返回基礎(chǔ)和復(fù)雜類型。注意在 PostgreSQL 中 CREATE FUNCTION 命令不僅可以創(chuàng)建函數(shù)也可以創(chuàng)建存儲過程。

讓我們看一個例子，創(chuàng)建一個返回復(fù)雜類型的函數(shù)：

這是一個實用的定制函數(shù)，用來找到一個數(shù)值序列中的中位數(shù)：

我們用來對比的其他開源 SQL 數(shù)據(jù)庫也允許創(chuàng)建自己的函數(shù)、存儲過程和觸發(fā)器，但它們沒有 PostgreSQL 提供的那么豐富的數(shù)據(jù)類型和自定義選項。額外的，在 PostgreSQL 你可以創(chuàng)建自己的操作符。其他數(shù)據(jù)庫并不支持用戶自定義操作符。

語言擴展

PostgreSQL 擁有大量的語言擴展，一些是發(fā)行版的一部分，更多的是第三方。

在 Compose，我們僅支持可信任的 PostgreSQL 語言擴展，以保證你的部署是安全的。我們在二月重新支持 PL/Perl，并在八月支持 PL/v8，一個基于 Javascript 的過程語言。這些語言擴展，比基于 SQL 的 PL/pgSQL 語言(Compose 的部署同樣可以使用)擁有更多內(nèi)置函數(shù)，使你可以創(chuàng)建復(fù)雜腳本來操作和處理服務(wù)器上的數(shù)據(jù)。

總結(jié)

PostgreSQL 有豐富的內(nèi)置特性和大量的方式可以定制或擴展來滿足需求。另外，它是可靠和成熟的，這是一個值得任何企業(yè)致力于的數(shù)據(jù)庫解決方案。即便如此，它仍對剛起步的開發(fā)項目保持易用性和高效性。我們僅僅涉及了少數(shù) PostgreSQL 不同于其他開源 SQL 數(shù)據(jù)庫的功能，還有更多的其他功能未涉及(在 9.5 版本還將帶來更多)。我們希望這兩篇文章能提供一個為什么選擇 PostgreSQL 的堅實概述。