掌握PostgreSQL新特性学习笔记三：PostgreSQL 11

国庆前夕PG14新版本发布了，当前主要还再使用PG12，趁着假期温习了一遍9.6到13的新特性，追一下新版本的差距。

PostgreSQL 11

数据库管理功能

数据库管理功能中让数据库更高效运行的功能之一应该是能够配置数据库实例运行时的大小，通常认为最应该被调整是WAL段文件。

可配置的WAL段大小

自PostgreSQL推出以来，单个WAL文件的大小始终如此是16MB。最开始，它甚至是一个可编译限制项，后来改为编译时的选项。从PostgreSQL 11开始，WAL段文件的大小可以在实例创建时更改，这为管理员提供了额外的配置和优化PostgreSQL的方法。

以下示例说明在数据库initdb的时候如何配置WAL段大小：

initdb -D /pgdata --wal-segsize=32

在大多数情况下，使用更大的段大小用来提高性能是显而易见的。除非你是在一个嵌入式系统中运行非常小的数据库实例。

那么WAL段大小真正产生的性能影响会是什么？如果您正在运行一个99%都是读的数据库系统，大的WAL段产生的影响将为零。如果面对的是写应用系统负载，并且95%空闲，没有严重负载，影响仍为零或接近零。只有您运行着一个写密集型工作负载的系统，你才会看到效果，这样改变WAL段大小才有价值。该新特性只有在产生大量WAL的场景才会显示其优势。

pg_stat_statements视图64位queryid

如果我们想深入理解PostgreSQL的性能，pg_stat_statements就是最好的工具。如果真的想弄清楚系统中发生了什么，它是一个黄金标准。一旦PostgreSQL服务启动，pg_stat_statements命令就会通过加载shared_preload_libraries并汇总有关服务器中运行的查询统计信息。如果出现问题，它会立即显示。

pg_stat_statements 命令提供了一个名为queryid的字段，该字段过去一直是32位长度。在某些情况下，这会导致问题，因为在某些情况下键值可能会发生冲突。Magnus Hagander在他的一篇论文中计算过，在运行30亿个不同的查询，预计会发生大约50,000次冲突。通过引入一个64位长度的queryid，在30亿种不同类型的查询之后，这个数字预计会下降到大约0.25个冲突，这是一个重大改进。

如果要迁移到 PostgreSQL11并且已使用pg_stat_statements来跟踪性能问题，则可能需要更新脚本。

索引方面优化改进

PostgreSQL 11索引的功能也有所改进。其中一个最重要的功能是与索引和统计信息相关。

表达式索引统计信息

如果您正在运行一个简单的查询，PostgreSQL将通过查看内部统计信息来优化它。如下示例：

SELECT * FROM person WHERE gender='female';

在这种情况下，PostgreSQL将查询内部统计数据并估算表中女性的数量。如果数量很少，PostgreSQL会考虑走索引查找。如果大多数记录是女性，PostgreSQL将考虑顺序扫描而不是索引扫描。表的每列都有统计信息数据。此外，还可以查看PostgreSQL 10版本中引入的跨列统计信息。PostgreSQL还会跟踪基于函数的索引统计信息：

CREATE INDEX idx_cos ON t_data (cos(data)); 

目前尚无法实现的是对函数索引使用更复杂的统计信息数据。

请考虑以下包含各种列的索引示例：

CREATE INDEX coord_idx ON measured (x,y,(z+t));
ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000;

在这个案例中，索引除了前面的x和y两列，它还提供由expression表示的虚拟第三列。新功能允许我们为第三列显式创建更多统计信息。在这个案例中，我们将明确告诉PostgreSQL我们希望第三列在系统统计信息中有1000个条目。它将允许优化器作出更好的估计，从而可能创建更好的执行计划。这将对一些特殊的应用提高效率。

include索引或covering索引

考虑以下示例，该示例只从表中选择两列：

SELECT id, name FROM person WHERE id = 10;

假设我们只有id列上的索引。在这种情况下，PostgreSQL将查找索引并在表中执行查找以得到额外的其他字段。这通常被称为索引扫描。它包括检查索引和基础表以组成一行。这里可以通过创建一个由两列组成的索引去解决。这个方法允许 PostgreSQL 执行仅索引扫描而不是索引扫描。如果索引具有所需的所有列，则无需再在表中执行额外的查找。

上面的问题是，如果您需要对id进行主键约束，并且仍希望在读取其他列时执行仅索引扫描。新特性能实现方式如下：

CREATE UNIQUE INDEX some_name ON person USING btree (id) INCLUDE (name);

PostgreSQL将确保id值是唯一的，但仍会在索引中存储其他字段，以便在查询两列时执行仅索引扫描。在大容量OLTP环境中，这将显著提高性能。PostgreSQL 11将为我们提供更多选项来实现更多的仅索引扫描。

并行索引创建

在PostgreSQL中创建索引时，数据库习惯使用一个CPU核心来完成工作。在许多情况下，这不是问题。但是对于持续增长的系统，索引创建开始成为一个问题。首先是允许并行创建btrees，这已在PostgreSQL 11版本实现，同时社区也在努力改善排序的性能，PostgreSQL的未来版本也将允许为正常操作提供并行排序功能，当前PostgreSQL 11尚未支持。

更好的缓存管理

PostgreSQL 11还为我们提供更好的方法来管理I/O缓存（共享缓冲区）。

pg_prewarm命令允许我们在重新启动数据库实例后恢复PostgreSQL I/O缓存的内容。该扩展已被PostgreSQL用户组广泛使用。在PostgreSQL 11中，pg_prewarm已被扩展并允许以固定间隔时间自动转储缓冲区内容列表。

增强的窗口函数

PostgreSQL 11现在完全支持SQL：2011提出的标准。

参考下面的案例。该代码包含两个窗口函数。它们解释如下：

• 第一个使用 PostgreSQL 10 及之前的版本。
• 第二个array_agg排除当前行，这是 PostgreSQL 11提供的新功能。

下代码生成五行并包含两个窗口函数：

test=# SELECT *,
array_agg(x) OVER (ORDER BY x ROWS BETWEEN
1 PRECEDING AND 1 FOLLOWING),
array_agg(x) OVER (ORDER BY x ROWS BETWEEN
1 PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) 
FROM generate_series(1, 5) AS x;
x | array_agg | array_agg
1 | {1,2}     | {2} 
2 | {1,2,3}   | {1,3} 
3 | {2,3,4}   | {2,4} 
4 | {3,4,5}   | {3,5} 
5 | {4,5}     | {4} 
(5 rows) 

排除当前行是一个非常常见的需求。

引入即时编译

实时编译（Just-in-time, JIT）实际上是PostgreSQL 11的亮点之一。PostgreSQL 11是第一个充分利用这一现代技术的版本。在我们深入了解JIT之前，我们需要知道JIT编译是关于什么的？在运行一个查询时，很多东西实际上只在运行时才会知道，而不是在编译时。因此，传统的编译器总是处于劣势，因为它不知道在运行时会发生什么。但是JIT编译器已经知道更多内容并且可以做出相应的反应。

从PostgreSQL 11开始，我们可以使用JIT编译，这对于大型的查询尤其有用。

增强的表分区

PostgreSQL 10是PostgreSQL引入了分区技术的第一个版本。PostgreSQL 11将通过引入一些新亮点为表分区功能添加一些新特性，例如，如果现有分区都不匹配，则可以创建默认分区。

下面是它的工作原理：

postgres=# CREATE TABLE default_part PARTITION OF some_table DEFAULT;
CREATE TABLE

在这个案例中，所有在任何分区条件都不匹配的行将最终出现在默认分区中。不仅如此。在PostgreSQL中，一行不能从一个分区移动到另一个分区。我们必须删除旧的行并插入一个新的行。在PostgreSQL 11中，这个问题已经解决了。现在可以以完全透明的方式将行从一个分区移动到另一个分区。

PostgreSQL 仍然还有很多缺点。在旧版本中，所有分区都必须单独创建索引。无法为所有分区创建单个索引。在PostgreSQL 11中，为父表创建的索引将自动确保所有子表都被使用。这可以降低索引被遗忘创建的可能性。此外在PostgreSQL 11中，我们实际上可以添加全局唯一索引。因此，分区表可以强制执行唯一约束。

截止到 PostgreSQL 10，我们实现了范围分区和列表分区。PostgreSQL 11增加了哈希分区的功能。

还有很多新性能：分区裁剪现在要快得多，PostgreSQL还能够智能的处理跨分区关联以及跨分区聚合，这也正是数据分析和数据仓库所需要的。

添加对存储过程的支持

PostgreSQL一直支持函数功能，这些函数通常被称为存储过程。但是，存储过程和函数之间存在区别。如前所述，直到PostgreSQL 10，只有函数而没有存储过程。关键点是函数是一个结构体的部分，一个单独的事务。一个存储过程可以包含多个事务。因此，它不能被更大的事务调用，而是一个独立的结构体。

以下存储过程说明了如何在同一过程中执行两个事务：

test=# CREATE PROCEDURE test_proc() 
         	LANGUAGE plpgsql 
AS $$ 
  BEGIN 
    CREATE TABLE a (aid int); 
    CREATE TABLE b (bid int); 
    COMMIT; 
   CREATE TABLE c (cid int); 
    ROLLBACK; 
  END; 
$$; 
CREATE PROCEDURE 

请注意，在第二个事务中止时，前两个语句已提交。在下面我们可以看到效果。

要运行该过程，我们可以使用 CALL：

test=# CALL test_proc(); 
CALL 

前面创建的两个表已提交，由于存储过程内部的回滚，第三个表未创建：

test=# \d 
List of relations 
 Schema | Name | Type  | Owner 
--------+------+-------+------- 
 public | a    | table | hs
 public | b    | table | hs 
(2 rows) 

改进的ALTER TABLE

ALTER TABLE 命令可用于更改表的定义。在PostgreSQL 11中，ALTER TABLE…ADD COLUMN的执行得到了显著改善。我们来看看细节。以下示例显示了如何将列添加到表中以及PostgreSQL如何处理这些新列：

ALTER TABLE x ADD COLUMN y int;
ALTER TABLE x ADD COLUMN z int DEFAULT 57;

列表中的第一个命令一直很快，原因是在PostgreSQL中，列的默认值为NULL。所以PostgreSQL所做的就是在系统目录中添加一列而不实际存储。即使我们对一个10TB表添加列，操作也会非常快，因为不必更新磁盘上的行。

在第二个案例中，情况则完全不同。DEFAULT 57确实在行中添加了实际数据，在PostgreSQL 10及更早版本中，这意味着数据库必须重写整个表来添加这个新的默认值。如果是一张小表，这不是什么大问题。但是，如果表包含数十亿行记录，则无法将其锁定并重写：在线事务处理（OLTP）系统中停机时间是不可能的。

从PostgreSQL 11开始，可以在不重写整个表的情况下向表中添加固定的默认值，这大大减轻了更改数据结构的负担。

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。

如果群二维码失效可以加我微信。