PostgreSQL 10 的新功能

一般链接

大数据

原生分区

表分区：PostgreSQL 10 分区的背景和局限性

在 10 中，分区表现在是表的属性

   CREATE TABLE table_name ( ... )
   [ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }

   CREATE TABLE table_name
   PARTITION OF parent_table [ (
   ) ] FOR VALUES partition_bound_spec

示例

之前

   CREATE TABLE padre (
      id         SERIAL,
      pais       INTEGER,
      fch_creado TIMESTAMPTZ NOT NULL
   );

   CREATE TABLE hija_2017 (
     CONSTRAINT pk_2017 PRIMARY KEY (id),
     CONSTRAINT ck_2017 CHECK (fch_creado < DATE '2015-01-01' )
   ) INHERITS (padre);
   CREATE INDEX idx_2017 ON hija_2017 (fch_creado);

今天

   CREATE TABLE padre (
     id         SERIAL NOT NULL,
     nombre     TEXT NOT NULL,
     fch_creado TIMESTAMPTZ NOT NULL
    )
    PARTITION BY RANGE ( id );

   CREATE TABLE hijo_0
      PARTITION OF padre (id, PRIMARY KEY (id), UNIQUE (nombre))
      FOR VALUES FROM (MINVALUE) TO (10);

   CREATE TABLE hijo_1
      PARTITION OF padre (id, PRIMARY KEY (id), UNIQUE (nombre))
      FOR VALUES FROM (10) TO (MAXVALUE);

这意味着用户不再需要创建触发器来路由数据；这一切都由系统处理。

另一个示例

例如，我们可能决定对 `book_history` 表进行分区，这可能是一个好主意，因为该表可能会永远累积数据。由于它是一个日志表，我们将对其进行范围分区，每个月一个分区。

首先，我们创建一个“主”分区表，该表不保存数据，但为其他分区提供模板

   libdata=# CREATE TABLE book_history (
               book_id INTEGER NOT NULL,
               status BOOK_STATUS NOT NULL,
               period TSTZRANGE NOT NULL )
               PARTITION BY RANGE ( lower (period) );

然后我们创建几个分区，每个月一个

   libdata=# CREATE TABLE book_history_2016_09
               PARTITION OF book_history
               FOR VALUES FROM ('2016-09-01 00:00:00') TO ('2016-10-01 00:00:00');
   CREATE TABLE
   libdata=# CREATE TABLE book_history_2016_08
               PARTITION OF book_history
               FOR VALUES FROM ('2016-08-01 00:00:00') TO ('2016-09-01 00:00:00');
   CREATE TABLE
   libdata=# CREATE TABLE book_history_2016_07
               PARTITION OF book_history
               FOR VALUES FROM ('2016-07-01 00:00:00') TO ('2016-09-01 00:00:00');
   ERROR:  partition "book_history_2016_07" would overlap partition "book_history_2016_08"

如您所见，系统甚至可以防止意外重叠。新行将自动存储在正确的分区中，而 SELECT 查询将搜索相应的分区。

commit
文档
Hubert Lubaczewski 表分区示例 (depesz.com)
Keith Fiske 内置分区
Robert Haas v11 中的分区计划和关于分区下一步的电子邮件（即 v10 中的重要局限性）

查询执行中的额外并行化

（来自 Robert Haas 的博客文章的措辞，链接如下）

并行合并连接：在 PostgreSQL 9.6 中，只有哈希连接和嵌套循环可以在计划的并行部分执行。在 PostgreSQL 10 中，合并连接也可以在计划的并行部分执行。
并行位图堆扫描：一个进程扫描索引并在共享内存中构建一个数据结构，指示所有需要扫描的堆页面，然后所有协作进程可以并行执行堆扫描。
并行索引扫描和仅索引扫描：现在可以利用索引扫描或仅索引扫描来扫描驱动表。
收集合并：如果每个工作进程都在生成排序的输出，则以保留排序顺序的方式收集这些结果。
与子计划相关的改进：具有非关联子计划的表可以出现在计划的并行部分。
将查询文本传递给工作进程：与并行工作进程关联的查询文本将显示在 pg_stat_activity 中。
过程语言

示例

例如，如果我们想通过索引列搜索财务交易历史记录，我现在可以通过使用四个并行工作进程将执行时间缩短到原来的四分之一

   accounts=# \timing
   Timing is on.
   accounts=# SELECT bid, count(*) FROM account_history
              WHERE delta > 1000 group by bid;
   ...
   Time: 324.903 ms

   accounts=# set max_parallel_workers_per_gather=4;
   SET
   Time: 0.822 ms
   accounts=# SELECT bid, count(*) FROM account_history
              WHERE delta > 1000 GROUP BY bid;
   ...
   Time: 72.864 ms

（这假设对 bid、delta 进行了索引）

链接

Robert Haas 并行查询 v2

额外的 FDW 下推

在 postgres_fdw 中，在更多情况下将连接和聚合函数下推到远程服务器。这减少了必须从远程服务器传递的数据量，并将聚合计算从请求服务器卸载。

链接

等待 PostgreSQL 10 - postgres_fdw：将聚合下推到远程服务器

更快的分析查询

复制和扩展

逻辑复制

流式复制是一种快速、安全且完美的机制，用于满足高可用性/灾难恢复需求。由于它在整个实例上运行，因此无法仅复制主服务器的一部分，也不能在从属服务器上写入。逻辑复制将使我们能够解决这些用例。

示例

假设我决定要将公共图书馆数据库中的罚款和贷款表复制到计费系统，以便他们可以处理欠款。我将使用此命令从这两个表中创建一个发布

   libdata=# CREATE PUBLICATION financials FOR TABLE ONLY loans, ONLY fines;
   CREATE PUBLICATION

然后，在计费数据库中，我将创建两个看起来与我正在复制的表相同的表，并且具有相同的名称。它们可以有额外的列和其他一些差异。特别地，由于我没有复制 patrons 或 books 表，因此我将想要删除原始数据库中的一些外键。我还需要为这些表创建任何特殊的数据类型或其他数据库工件。通常，最简单的方法是选择性地使用 `pg_dump` 和 `pg_restore` 备份实用程序

   origin# pg_dump libdata -Fc -f /netshare/libdata.dump

   replica# pg_restore -d libdata -s -t loans -t fines /netshare/libdata.dump

之后，我可以开始对这两个表进行订阅

   libdata=# CREATE SUBSCRIPTION financials
               CONNECTION 'dbname=libdata user=postgres host=172.17.0.2'
               PUBLICATION financials;
   NOTICE:  synchronized table states
   NOTICE:  created replication slot "financials" on publisher
   CREATE SUBSCRIPTION

这将首先复制表中当前数据的快照，然后从事务日志开始追赶。一旦追赶上，您就可以在 pg_stat_subscription 中检查状态

   libdata=# SELECT * FROM pg_stat_subscription;
   -[ RECORD 1 ]---------+---------------------
   subid                 | 16475
   subname               | financials
   pid                   | 167
   relid                 |
   received_lsn          | 0/1FBEAF0
   last_msg_send_time    | 2017-06-07 00:59:44
   last_msg_receipt_time | 2017-06-07 00:59:44
   latest_end_lsn        | 0/1FBEAF0
   latest_end_time       | 2017-06-07 00:59:44

博客

PostgreSQL 10 中的逻辑复制

同步复制的仲裁提交

虽然 9.6 版本引入了基于仲裁的同步复制，但

   synchronous_commit = 'remote_apply'

10 版本通过添加 FIRST 和 ANY 关键字改进了 synchronous_standby_names GUC

   synchronous_standby_names = ANY 2(node1,node2,node3);
   synchronous_standby_names = FIRST 2(node1,node2);

FIRST 是之前的行为，并且节点优先级遵循列表顺序，以便获得仲裁。ANY 现在意味着列表中的任何节点现在都可以提供所需的仲裁。这将为复杂的复制设置提供额外的灵活性。

临时复制槽

在会话结束时自动删除；在降低风险的情况下防止落后。

libpq 中的连接故障转移和路由

Postgres 10 允许应用程序定义多个连接点并定义对后端服务器的预期的一些属性。这简化了应用程序级别的逻辑：它不需要确切地知道哪个节点是主节点，哪些是备用节点。新的参数也可以通过环境变量控制。

链接

物理复制

改进 2 阶段提交重放的性能

在备用服务器上对对象持有独占锁时，改进重放的性能。这可以在使用临时表的情况下显著提高性能。

管理

pg_receivewal 的压缩支持

pg_stat_activity 中的后台进程

pg_stat_activity 现在包含有关后台进程的信息（包括等待事件），包括

辅助进程
工作进程
WAL 发送器

通过事务 ID 追踪提交/状态

PostgreSQL 10 现在支持在网络连接丢失或崩溃后恢复时找出最近事务的状态，而无需使用重量级的 2 阶段提交。它也适用于查询备用服务器。

链接

SQL 功能

标识列

PostgreSQL 10 标识列解释

崩溃安全、可复制的哈希索引

（来自 Bruce Momjian 的 pg10 通用演示文稿的措辞）

崩溃安全
可复制
在桶拆分期间减少锁定
更快的查找
更均匀的索引增长
单页剪枝

触发器转换表

此功能通过适当地将旧行和新行暴露给查询，使 AFTER STATEMENT 触发器既有用又高效。在此功能之前，AFTER STATEMENT 触发器无法直接访问这些行，而解决方法非常复杂且性能低下。现在，许多触发器逻辑可以编写为 AFTER STATEMENT，从而避免了在每个行上进行 FOR EACH ROW 触发器所需的昂贵上下文切换。

XML 和 JSON

XMLTable

XMLTABLE 是一个 SQL 标准功能，它允许将 XML 文档转换为表格格式，从而使在数据库中处理 XML 数据变得更加容易。结合指向外部 XML 数据的外部表，这可以极大地简化 ETL 处理。

对 JSON 和 JSONB 的全文搜索支持

您现在可以在 JSON 和 JSONB 列上创建全文索引。

这涉及将 JSONB 字段转换为 `tsvector`，然后在其上创建特定语言的全文索引。

   libdata=# CREATE INDEX bookdata_fts ON bookdata
               USING gin (( to_tsvector('english',bookdata) ));
   CREATE INDEX

设置完成后，您可以对 JSON 文档中的所有值进行全文搜索。

   libdata=# SELECT bookdata -> 'title'
             FROM bookdata
             WHERE to_tsvector('english',bookdata) @@ to_tsquery('duke');            
   ------------------------------------------
    "The Tattooed Duke"
    "She Tempts the Duke"
    "The Duke Is Mine"
    "What I Did For a Duke"

安全

SCRAM 身份验证

SCRAM 比 MD5 更安全，并且已成为进行身份验证的标准方法。它是一种带盐的质询响应身份验证方法。

要切换到 PostgreSQL 中的 SCRAM 身份验证，需要客户端支持。

用于权限授予的新“监控”角色

现在可以在更多情况下避免使用超级用户。

pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_monitor

行级安全限制策略

性能

跨列统计

现实世界中的数据经常包含表列中的相关数据，这很容易欺骗查询规划器，使其认为 WHERE 子句比实际情况更具选择性，这会导致某些查询变得非常缓慢。多元统计对象可用于让规划器了解这一点，从而防止其犯这样的错误。此手册部分更详细地解释了该功能，而此部分展示了一些示例。PostgreSQL 中的此功能代表了所有 SQL 数据库技术水平的进步。

pg_stat_activity 中的等待事件大幅扩展

PostgreSQL 9.6 代码共包含 69 个等待事件。PostgreSQL 10 扩展了代码仪表化，现在包含 184 个等待事件。特别是，添加了 67 个以上的 I/O 相关事件和 31 个以上的闩锁相关事件。

在 Postgres 9.6 中添加到 pg_stat_activity 视图的 wait_event_type 和 wait_event 列为我们提供了一个重要的窗口，用于找出系统哪些部分导致查询延迟，并为我们提供了关于我们在哪里丢失性能的非常准确的统计数据。

Bruce Momjian 等待事件报告
Robert Haas pgbench 与等待事件

查询规划器改进

在连接规划中，检测连接内部是否只能为每个外部行产生一行。在执行过程中，这允许在找到匹配项后立即跳到下一个外部行。这也可能消除合并连接期间对标记和恢复的要求，在某些情况下可以显着提高性能。

其他功能

file_fdw 可以执行程序

示例：(来自 Magnus Hagander 的新功能演示)

   CREATE FOREIGN TABLE
   test(a int, b text)
   SERVER csv
   OPTIONS (program 'gunzip -c /tmp/data.czv.gz');

ICU 排序规则支持

编译时配置选项，使用 ICU 库而不是依赖于操作系统提供的国际化库（该库容易出现意外行为）

PostgreSQL 10 中使用 ICU 支持的更健壮的排序规则

amcheck B 树一致性/损坏检查工具

PostgreSQL 10 amcheck 文档

向后不兼容的更改

版本 10 有几个向后不兼容的更改，这些更改可能会影响系统管理，尤其是围绕监控和备份自动化的更改。与往常一样，PostgreSQL 用户应在生产环境中升级之前仔细测试不兼容性。

版本编号更改

从版本 10 开始，PostgreSQL 不再使用三部分版本号，而是转向两部分版本号。这意味着版本 10.1 将是 PostgreSQL 10 的第一个补丁更新，而不是一个新的主要版本。检测 PostgreSQL 版本的脚本和工具可能会受到影响。

社区强烈建议工具使用 GUC server_version_num（在后端），或 libpq 状态函数 PQserverVersion 在 libpq 中获取服务器版本。这将返回一个六位数的整数版本号，该版本号将在版本 9.6 和 10 之间始终可排序和可比较。

版本字符串	主要版本	更新编号	version_num
9.6.0	9.6	0	090600
9.6.3	9.6	3	090603
10.0	10	0	100000
10.1	10	1	100001

更改 Postgres 版本编号

将“xlog”全局重命名为“wal”（以及位置/lsn）

为了避免混淆导致数据丢失，以前我们使用缩写“xlog”来引用事务日志，包括目录、函数和可执行文件参数，现在我们使用“wal”。类似地，函数名称中的“location”一词，用于引用事务日志位置，已被替换为“lsn”。

这将要求许多用户重新编写自定义备份和事务日志管理脚本，以及监控复制。

两个目录已重命名

9.6 目录	10 目录
pg_xlog	pg_wal
pg_clog	pg_xact

此外，根据您的安装包的来源，默认活动日志位置可能已从“pg_log”重命名为“log”。

许多管理函数已重命名为使用“wal”和“lsn”

9.6 函数名称	10 函数名称
pg_current_xlog_flush_location	pg_current_wal_flush_lsn
pg_current_xlog_insert_location	pg_current_wal_insert_lsn
pg_current_xlog_location	pg_current_wal_lsn
pg_is_xlog_replay_paused	pg_is_wal_replay_paused
pg_last_xlog_receive_location	pg_last_wal_receive_lsn
pg_last_xlog_replay_location	pg_last_wal_replay_lsn
pg_switch_xlog	pg_switch_wal
pg_xlog_location_diff	pg_wal_lsn_diff
pg_xlog_replay_pause	pg_wal_replay_pause
pg_xlog_replay_resume	pg_wal_replay_resume
pg_xlogfile_name	pg_walfile_name
pg_xlogfile_name_offset	pg_walfile_name_offset

一些系统视图和函数已重命名属性

pg_stat_replication
- write_location -> write_lsn
- sent_location -> sent_lsn
- flush_location -> flush_lsn
- replay_location -> replay_lsn
pg_create_logical_replication_slot: wal_position -> lsn
pg_create_physical_replication_slot: wal_position -> lsn
pg_logical_slot_get_changes: location -> lsn
pg_logical_slot_peek_changes: location -> lsn

几个命令行可执行文件已重命名参数

pg_receivexlog 已重命名为 pg_receivewal。
pg_resetxlog 已重命名为 pg_resetwal。
pg_xlogdump 已重命名为 pg_waldump。
initdb 和 pg_basebackup 具有 --waldir 选项，而不是 --xlogdir。
pg_basebackup 现在具有 --wal-method 选项，而不是 --xlog-method。

放弃对 FE/BE 1.0 协议的支持

PostgreSQL 的原始客户端/服务器协议（版本 1.0）在 PostgreSQL 10 中将不再受支持。由于版本 1.0 在 1998 年被版本 2.0 取代，因此现有的客户端不太可能仍然使用它。

版本 6.3 之前的客户端可能会受到影响。

更改有关复制和 pg_basebackup 的默认设置

新的 postgresql.conf 默认设置

wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

新的 pg_hba.conf 默认设置

默认情况下使用复制连接

pg_basebackup

WAL 流式传输（-X stream）现在为默认值
默认情况下使用临时复制槽

pg_basebackup 增强功能

在 tar 模式（-Ft）中支持 WAL 流式传输
更好的排除项

来自 Magnus Hagander 的新功能演示中的措辞。

放弃对浮点时间戳的支持

浮点时间戳是一个编译时选项，在复制方面一直存在问题。据认为，只有很少一部分用户使用它们，部分原因是很少有发行版启用此选项。对于使用此选项的少数用户，将需要进行转储/恢复才能升级到 PostgreSQL 10。对于大型数据集，这可能很耗时，需要仔细计划。

删除 contrib/tsearch2

Tsearch2，我们内置全文搜索的旧版 contrib 模块版本，已从 contrib 中删除，并将不再作为 PostgreSQL 包的一部分进行构建。在版本 8.3 之前一直不断升级的用户需要在升级到 PostgreSQL 10 之前手动修改其数据库以使用内置的 tsearch 对象，或者需要从头开始编译 tsearch2 并安装它。

放弃对版本 8.0 之前的数据库的 pg_dump 支持

在 PostgreSQL 7.4 及更早版本上运行的数据库将不受 10 的 pg_dump 或 pg_dumpall 支持。如果您需要转换一个这样的旧数据库，请使用版本 9.6 或更早版本以两个阶段进行升级。

PostgreSQL 10 新功能