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

數據模型

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

數據類型和結構

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

讓我們仔細看看其中幾個數據類型：

網絡地址類型

PostgreSQL 提供用于存儲不同網絡地址的類型， CIDR (Classless Internet Domain Routing) 數據類型適合 IPv4 和 IPv6 網絡地址，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

也可用于網絡地址存儲的是 INET 數據類型， 用于 IPv4 和 IPv6 主機，子網是可選的，MACADDR 數據類型用于存儲硬件標識的 MAC 地址，例如 08-00-2b-01-02-03。MySQL 和 MariaDB 提供一些 INET 函數用于網絡地址轉換，但不直接提供用于存儲網絡地址的數據類型， Firebird 也沒有網絡地址類型。

多維數組

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

MySQL， MariaDB， 和 Firebird 不具備這種能力，如果想把類似這樣的數組存儲在傳統的關系數據庫中，替代的解決方法是為數組值每一行創建單獨的表。

幾何類型

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

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

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

JSON 支持

PostgreSQL 的 JSON 支持在 SQL 數據庫中支持非結構化數據，當數據結構由于處在開發中需要靈活性或數據對象包含了未知的字段時是有用的。

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

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

創新的數據類型

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

MySQL，MariaDB，和 Firebird 不提供這種強大的功能，因為它們不是面向對象的。

Data Size

PostgreSQL 可以處理大量的數據。下面列出了當前的大小限制：

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

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

數據完整性

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

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

總結

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

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

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

索引

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

局部索引

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

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

表達式索引

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

GIST 和 GIN

GiST(Generalized Search Tree)允許聯合 B 樹、R 樹和用戶自定義索引類型來創建擁有先進查詢能力的定制索引。GiST 在 PostGIS(從 2015 年 1 月以來我們所有 PostgreSQL 部署的標配)和 OpenFTS(一個開源全文搜索引擎)中使用。PostgreSQL 也支持 SP-GiST，它允許使數據檢索異常快速的分區查找索引的創建。

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

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

對比

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

虛擬表特性

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

通用表達式和遞歸

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

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

物化視圖

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

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

查詢能力

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

集合查詢

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

MySQL、MariaDB 和 Firebird 都支持 UNION，但都不支持 INTERSECT 和 EXCEPT。然而，通過查詢中的關聯以及 EXISTS 條件，可以獲取與 PostgreSQL 相同的結果集。當然，這會使查詢變得更為復雜。

窗口函數

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

網絡地址類型橫向子查詢

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

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

函數及其他

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

讓我們看一個例子，創建一個返回復雜類型的函數：

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

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

語言擴展

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

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

總結

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