Reuven 评测了 PostgreSQL 10 中最新且最有趣的功能。

长期以来，PostgreSQL 一直声称是最先进的开源关系数据库。对于我们这些已经使用它相当一段时间的人来说，毫无疑问这是真的；PostgreSQL 始终如一地展示了其处理高负载和复杂查询的能力，同时提供了丰富的功能集和坚如磐石的稳定性。

但是，尽管 PostgreSQL 提供了所有令人惊叹的功能，但长期以来一直存在差距和漏洞。我曾与目前使用 Oracle 或 Microsoft SQL Server 并且正在考虑使用 PostgreSQL 的咨询客户开会，他们向我询问诸如分区或查询并行化等主题。多年来，我一直不得不对他们说：“嗯，这是真的。PostgreSQL 在这方面的功能仍然相当薄弱。”

因此，当 PostgreSQL 10.0 于 2017 年 10 月发布时，我感到非常兴奋，它带来了一系列新功能和增强功能。诚然，其中一些功能仍然不如您在商业数据库中找到的那么复杂或精巧。但是，它们确实表明，随着时间的推移，PostgreSQL 正在为任何数据库（更不用说开源项目）提供令人惊叹的功能量。而且，在几乎所有情况下，当前的功能只是开发人员将继续遵循的长期路线图的第一部分。

在本文中，我回顾了 PostgreSQL 10 中一些最新且最有趣的功能——不仅包括它们现在可以为您做什么，还包括您可以期望在未来从它们身上看到什么。如果您还没有使用过 PostgreSQL，我猜您会被最新版本的功能所震撼和惊叹。请记住，所有这些都来自一个开源软件包，该软件包非常稳定，通常几乎不需要或根本不需要管理，并且继续体现了不仅是高质量的软件，而且还是高质量的开源项目和社区。

PostgreSQL 基础

如果您是 PostgreSQL 的新手，这里有一个快速概述：PostgreSQL 是一个客户端-服务器关系数据库，具有大量数据类型、强大的事务处理系统以及涵盖各种任务的功能（从正则表达式到日期计算，再到字符串操作和位运算）。您可以使用多种插件语言编写新函数，最常见的是 PL/PgSQL，它大致模仿 Oracle 的 PL/SQL，但您也可以使用 Python、JavaScript、Tcl、Ruby 和 R 等语言。使用其中一种扩展语言编写函数不仅为您提供了插件语言的语法，还提供了其库，这意味着如果您使用 R，例如，您可以在数据库内部运行统计分析。

PostgreSQL 的事务处理使用称为多版本并发控制 (MVCC) 的系统，该系统减少了数据库必须锁定行的次数。这并不意味着死锁永远不会发生，但它们往往很少发生并且相对容易避免。在 PostgreSQL 的 MVCC 中，需要理解的关键是，删除一行实际上并没有删除它，而只是通过指示它在特定事务之后不应再可见来将其标记为已删除。当所有事务 ID 都大于该数字时，行的空间可以被回收和/或重用，这个过程称为“清理”。该系统还意味着不同的事务可以同时看到同一行的不同版本，从而减少了锁。MVCC 可能有点难以理解，但它是 PostgreSQL 成功的一部分，允许您并行运行许多事务，而无需担心谁在读取或写入哪一行。

PostgreSQL 项目始于 20 多年前，这要归功于“Postgres”数据库（由当时的伯克利教授、数据库领域的专家和先驱 Michael Stonebreaker 创建）和 SQL 查询语言之间的合并。该数据库试图在很大程度上遵循 SQL 标准，并且文档指出了命令、函数和数据类型不符合该标准的地方。

二十年来，PostgreSQL “全球开发组” 大约每年发布一个新版本的数据库。正如您对成熟的开源项目所期望的那样，开发过程既透明又对新贡献者开放。也就是说，数据库是一个非常复杂的软件，如果它要继续拥有用户，就不能损坏数据或崩溃，因此开发往往是渐进式的，而不是革命性的。开发人员确实有一个长期路线图，他们通常会在各个版本中逐步推出功能，直到它们完成。除了核心开发人员之外，PostgreSQL 还有一个庞大而活跃的社区，并且该社区的大部分交流都通过电子邮件列表进行。

PostgreSQL 10

开源项目通常避免对软件发布大肆宣传。毕竟，几乎每个程序的每个版本都修复了错误、提高了性能并添加了功能。如果它被称为 3.5 或 2.8 或 10.0，又有什么关系呢？

话虽如此，此版本的 PostgreSQL 中大量重要功能几乎不可避免地使其被称为 10.0，而不是 9.7（紧随之前的版本 9.6 之后）。是什么如此值得这个大的整数呢？

两个重要功能是主要原因：逻辑复制和更好的表分区。当然，还有许多其他改进，但在本文中，我重点介绍这些重大变化。

在继续之前，我应该注意到安装 PostgreSQL 10 非常容易，许多操作系统（包括各种 Linux 发行版）的端口都已准备就绪。转到 PostgreSQL 主站点，然后单击“下载”链接。这将提供您需要的说明，以将 PostgreSQL 发行版添加到相应的软件包存储库，您可以从中下载并安装它。当然，如果您是从以前的版本升级，您应该更加保守，仔细检查以确保数据已正确升级。

我还应该注意到，就我服务器上运行的 Ubuntu 而言，PostgreSQL 10 可用的软件包数量非常庞大。通常只安装基本服务器和客户端软件包，但还有一些额外的软件包用于某些深奥的数据类型、外部数据包装器、测试您的查询，甚至包括内部 cron 系统、查询预处理器和许多复制选项。您不必安装所有这些软件包，而且您可能也不想这样做，但是软件包的庞大数量证明了 PostgreSQL 多年来变得多么复杂和庞大，以及它做了多少事情。

逻辑复制

多年来，PostgreSQL 缺乏合理的复制选项。您能做的最好的事情就是获取“预写日志”，这些二进制文件描述了事务并提供了 PostgreSQL 传奇般稳定性的部分原因，并将它们复制到另一台服务器。随着时间的推移，这成为拥有从属服务器的标准方法，直到几年前，您可以将这些预写日志 (WAL) 文件流式传输到另一台服务器。因此，主从复制成为 PostgreSQL 的标准功能，被全球许多组织使用——既可以将负载分布到多台服务器上，又可以在服务器发生故障时提供备份。一台机器（主服务器）将处理读写查询，而一台或多台其他机器（从服务器）将处理只读查询。

尽管流式传输 WAL 确实有效，但在许多方面都受到限制。它要求主服务器和从服务器都使用相同版本的 PostgreSQL，并且整个服务器的内容都必须复制到从服务器上。出于性能、隐私、安全和维护的原因，这些事情阻止了许多地方使用 PostgreSQL 的主从流式传输。

因此，“逻辑复制”被包含在 PostgreSQL 10 中时引起了很大的轰动。逻辑复制背后的思想是，服务器可以使用描述发布数据库中更改的协议而不是二进制文件来广播（“发布”）所做的更改。此外，可以发布有关数据库子集的详细信息；没有必要将绝对所有内容都从主服务器发送到每个从服务器。

为了使这项工作正常进行，发布服务器必须创建一个“发布”。这描述了将发送到订阅服务器的内容。您可以使用新的 CREATE PUBLICATION 命令来执行此操作。

正如我在上面写的那样，WAL 文件的复制意味着需要复制整个数据库服务器（或 PostgreSQL 术语中的“集群”）。在逻辑复制的情况下，复制是在每个数据库的基础上完成的。然后，您可以决定创建一个发布，为所有表提供服务

CREATE PUBLICATION mydbpub FOR ALL TABLES;

请注意，当您说 FOR ALL TABLES 时，您表示您不仅要发布当前此数据库中存在的所有表，还要发布您将来将创建的表。PostgreSQL 足够智能，可以在创建表时将表添加到发布中。但是，订阅者不会自动知道它们（稍后会详细介绍）。

如果您想限制某些内容，以便仅复制特定表，则可以使用此方法

CREATE PUBLICATION MyPeoplePub FOR TABLE People;

您还可以复制多个表

CREATE PUBLICATION MyPeopleFooPub FOR TABLE People, Foo;

如果您要发布一个或多个特定表，则这些表必须在创建发布时已经存在。

默认情况下，发布在已发布表上发生的所有操作。但是，发布可以指定它将仅发布插入、更新和/或删除。所有这些都可以在创建发布时配置，并且可以使用 ALTER PUBLICATION 命令稍后更新。

如果您正在使用交互式“psql”shell，则可以使用 \dRp 查看当前发布，这是“describe replication publications”的缩写。这不是最容易记住的命令，但它们很久以前就用完了单字母命令的逻辑候选者。此命令将显示已定义的发布以及它们具有的权限（稍后会详细介绍）。如果您想知道发布中包含哪些表，可以使用 \dRp+。

设置好发布后，您可以使用（毫不奇怪）CREATE SUBSCRIPTION 命令设置订阅。在这里，事情有点棘手，因为数据实际上正在到达订阅者的数据库，这意味着可能会出现冲突或问题。

首先，创建订阅要求您在发布者的系统上具有有效的登录名（用户名和密码）。有了这个，您就可以说

CREATE SUBSCRIPTION mysub CONNECTION 'host=mydb user=myuser'
 ↪PUBLICATION MyPeoplePub;

请注意，您使用标准的 PostgreSQL “连接字符串”来连接到服务器。如果需要，您可以使用其他选项，包括设置端口号和连接超时。由于数据库可能具有多个发布，因此您必须指示要订阅的发布名称，如此处所示。另请注意，此连接字符串中指示的用户必须在数据库中具有“replication”权限。

创建订阅后，数据将从其在发布者上的当前状态复制。

我已经提到，将 FOR ALL TABLES 选项与 CREATE PUBLISHER 一起使用意味着即使添加了新表，它们也将被包括在内。但是，对于订阅者而言，情况并非完全如此。在订阅者端，您需要指示发布者端发生了更改，并且您要刷新您的订阅

ALTER SUBSCRIPTION testsub REFRESH PUBLICATION;

如果您在以前的 PostgreSQL 版本中进行过任何二进制复制，您已经可以看到这是一个多么大的改进。您不必担心 WAL，也不必担心它们被擦除，或者不必担心使订阅服务器启动并运行等等。

现在，谈论复制当然很好，但总是存在出现问题的可能性。例如，如果传入数据违反了一个或多个约束会发生什么？在这种情况下，复制将停止。

关于实际复制哪些对象，还有许多注意事项——例如，仅复制表，诸如视图和序列之类的对象不复制。

表分区

假设您正在使用 PostgreSQL 来跟踪发票。您可能想要有一个“发票”表，您可以按客户 ID、日期、价格或其他因素查询该表。这很好，但是如果您的业务变得非常受欢迎，并且您突然每月处理的客户不是几十个，而是成千上万甚至数百万个，会发生什么情况？将所有这些发票数据保存在单个数据库表中将会导致问题。不仅许多旧发票占用了主文件系统上的空间，而且您对该表的查询将比必要的时间更长，因为正在扫描这些旧行。

数据库世界中解决此问题的标准解决方案是分区。您将表划分为一个或多个子表，称为“分区”。每个分区可以存在于不同的文件系统上。您可以获得在单个数据库上拥有单个表的好处，但您也可以享受使用较小表的好处。

不幸的是，这种分区在以前版本的 PostgreSQL 中是可用的——尽管它有效，但安装、配置和维护都很困难。PostgreSQL 10 添加了“声明式分区”，允许您指示应将表分解为单独的分区——这意味着当您将数据插入分区表时，PostgreSQL 会查找适当的分区并在其中插入数据。

PostgreSQL 支持两种类型的分区方案。在这两种情况下，您都必须指示将要进行分区的一个或多个列。您可以根据“范围”进行分区，在这种情况下，每个分区将包含来自值范围的数据。这种分区类型的典型用例是日期，例如上面的发票示例。

但是，您也可以通过“列表”值进行分区，这意味着您可以根据值来划分事物。例如，您可能希望为美国每个州或可能仅为不同地区设置单独的分区。无论哪种方式，列表都将确定哪个分区接收数据。

例如，您可以按如下方式实现上述日期发票示例。首先，创建一个 Invoices 表

postgres=# CREATE TABLE Invoices (
  id SERIAL,
  issued_at TIMESTAMP NOT NULL,
  customer_name TEXT NOT NULL,
  amount INTEGER NOT NULL,
  product_bought TEXT NOT NULL
) partition by range (issued_at);
CREATE TABLE

（是的，在实际的发票系统中，您将使用外键来跟踪客户和产品。）

请注意，在 CREATE TABLE 命令的结尾，我添加了一个“partition by range”语句，该语句指示此表的分区将根据 issued_at（时间戳）上的范围工作。

但也许更有趣的是，id，即 SERIAL（即序列）值，未定义为主键。这是因为您不能在分区表上设置主键；这将需要在各个分区之间检查约束，而 PostgreSQL 无法保证。

有了分区表，您现在可以创建各个分区

postgres=# CREATE TABLE issued_at_y2018m01 PARTITION OF Invoices
 FOR VALUES FROM ('2018-jan-01') to ('2018-jan-31');
CREATE TABLE

postgres=# CREATE TABLE issued_at_y2018m02 PARTITION OF Invoices
postgres-#  FOR VALUES FROM ('2018-feb-01') to ('2018-feb-28');
CREATE TABLE

请注意，这些分区没有任何列定义。这是因为列由分区表决定。在 psql 中，我可以请求描述第一个分区。有关示例，请参见表 1。

表 1. public.issued_at_y2018m01

列	类型	排序规则	可空	默认值
id	integer		不为空	nextval('invoices_id_seq'::regclass)
issued_at	timestamp without time zone		不为空
customer_name	text		不为空
amount	integer		不为空
product_bought	text		不为空

Partition of: invoices FOR VALUES FROM ('2018-01-01 00:00:00')
 ↪TO ('2018-01-31 00:00:00')

您可以从表 1 中显示的示例中看到，分区不仅像常规表一样工作，而且它非常清楚其值的范围。看看我现在将行插入到父“发票”表中会发生什么

postgres=# insert into invoices (issued_at , customer_name,
 ↪amount, product_bought)
postgres-# values ('2018-jan-15', 'Jane January', 100, 'Book');
INSERT 0 1
postgres=# insert into invoices (issued_at , customer_name,
 ↪amount, product_bought)
values ('2018-jan-20', 'Jane January', 200, 'Another book');
INSERT 0 1
postgres=# insert into invoices (issued_at , customer_name,
 ↪amount, product_bought)
values ('2018-feb-3', 'Fred February', 70, 'Fancy pen');
INSERT 0 1
postgres=# insert into invoices (issued_at , customer_name,
 ↪amount, product_bought)
values ('2018-feb-15', 'Fred February', 60, 'Book');
INSERT 0 1

到目前为止，一切顺利。但是，现在如何查询“发票”

postgres=# select * from invoices;
 id |      issued_at      | customer_name | amount | product_bought
----+---------------------+---------------+--------+----------------
  3 | 2018-02-03 00:00:00 | Fred February |     70 | Fancy pen
  4 | 2018-02-15 00:00:00 | Fred February |     60 | Book
  1 | 2018-01-15 00:00:00 | Jane January  |    100 | Book
  2 | 2018-01-20 00:00:00 | Jane January  |    200 | Another book
(4 rows)

如果我愿意，我也可以直接查询其中一个分区

postgres=# select * from issued_at_y2018m01 ;
 id |      issued_at      | customer_name | amount | product_bought
----+---------------------+---------------+--------+----------------
  1 | 2018-01-15 00:00:00 | Jane January  |    100 | Book
  2 | 2018-01-20 00:00:00 | Jane January  |    200 | Another book
(2 rows)

虽然您不必这样做，但最好在每个分区的分区键上设置索引

postgres=# create index on issued_at_y2018m01(issued_at);
CREATE INDEX
postgres=# create index on issued_at_y2018m02(issued_at);
CREATE INDEX

这将有助于 PostgreSQL 查找和更新适当的分区。

并非所有事情都是自动或神奇的；您将必须添加分区，甚至可以在不再需要时删除它们。但这比过去容易得多，并且也提供了更大的灵活性。毫不奇怪，这是 PostgreSQL 10 中最受推崇的功能之一。

结论

我个人使用 PostgreSQL 大约 20 年了——这么多年来，人们都说：“真的吗？那是您首选的开源数据库？” 但是，现在越来越多的人正在采用和使用 PostgreSQL。它已经充满了强大的功能，但总是有改进的空间——借助 PostgreSQL 10，有更多理由选择它而不是其他替代方案。

资源

要了解有关 PostgreSQL 的更多信息，请下载代码、阅读文档并注册社区电子邮件列表，请访问 https://postgresql.ac.cn。

关于作者

Reuven Lerner 在世界各地的公司教授 Python、数据科学和 Git。他的免费、每周“更好的开发者”电子邮件列表每周覆盖数千名开发者；在此处订阅 here。Reuven 与他的妻子和孩子住在以色列的莫迪因。