这些特性，Postgre SQL秒杀其他数据库

你可能会问自己 “为什么选择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刚刚宣布了9.5版本的Beta 1发行版（译者注：目前已发布9.5 Beta 2）。我们已经着手于它，学习所有新特性以便使9.5版本一旦稳定就第一时间提供出来。在过去几个月我们查看了9.5带来的几个特性，比如前面提及的BRIN索引。 阅读 PostgreSQL's Future Is Looking Up-sert和Beyond Upsert - Coming in PostgreSQL 9.5以预览即将到来的9.5。

总结

PostgreSQL有丰富的内置特性和大量的方式可以定制或扩展来满足需求。另外，它是可靠和成熟的，这是一个值得任何企业致力于的数据库解决方案。即便如此，它仍对刚起步的开发项目保持易用性和高效性。我们仅仅涉及了少数PostgreSQL不同于其他开源SQL数据库的功能，还有更多的其他功能未涉及（在9.5版本还将带来更多）。我们希望这两篇文章能提供一个为什么选择PostgreSQL的坚实概述。

本文章源自三墩IT人订阅号，经作者同意由DBA+社群进行合并整理

本文来自云栖社区合作伙伴"DBAplus"，原文发布时间：2016-02-04