掌握PostgreSQL新特性学习笔记二：PostgreSQL 10

原创多米爸比 2021-10-06

1561

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

PostgreSQL 10

活动连接展示更多的信息

对管理员最重要的特性应该是pg_stat_activity视图增加了更丰富的信息。PostgreSQL 10以前pg_stat_activity视图只包括用户后台服务进程。现在增加了一些系统服务进程信息。

test=# \x
Expanded display is on.

test=# SELECT pid, wait_event_type, wait_event, backend_type
 FROM pg_stat_activity ;
-[ RECORD 1 ]---+--------------------
pid | 12159
wait_event_type | Activity
wait_event | AutoVacuumMain
backend_type | autovacuum launcher
-[ RECORD 2 ]---+--------------------
pid | 12161
wait_event_type | Activity
wait_event | LogicalLauncherMain
backend_type | background worker
-[ RECORD 3 ]---+--------------------
pid | 12628
wait_event_type |
wait_event |
backend_type | client backend
-[ RECORD 4 ]---+--------------------
pid | 12156
wait_event_type | Activity
wait_event | BgWriterMain
backend_type | background writer
-[ RECORD 5 ]---+--------------------
pid | 12155
wait_event_type | Activity
wait_event | CheckpointerMain
backend_type | checkpointer
-[ RECORD 6 ]---+--------------------
pid | 12157
wait_event_type | Activity
wait_event | WalWriterMain
backend_type | walwriter

引入SCRAM-SHA-256

我们习惯使用MD5口令加密方式连接数据库来进行管理安全。但是MD5已经不再安全，因此需要新的身份验证方式。从10版开始，PostgreSQL支持SCRAM-SHA-256方式，它比MD5更安全。

虽然MD5的身份验证方法仍然是兼容支持的。但是强烈建议升级MD5到SCRAM-SHA-256。

流复制特性改进

PostgreSQL 10开始支持逻辑复制：表级别的订阅与发布。

发布数据，使用CREATE PUBLICATION命令。

test=# \h CREATE PUBLICATION
Command: CREATE PUBLICATION
Description: define a new publication
Syntax:
CREATE PUBLICATION name
 [ FOR TABLE [ ONLY ] table_name [ * ] [, ...]
 | FOR ALL TABLES ]
 [ WITH ( publication_parameter [= value] [, ... ] ) ]

一旦数据已经发布，远端服务器可以进行订阅并接收发布的数据集：

test=# \h CREATE SUBSCRIPTION
Command: CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_name
 CONNECTION 'conninfo'
 PUBLICATION publication_name [, ...]
 [ WITH ( subscription_parameter [= value] [, ... ] ) ]

逻辑复制设计的精妙之处在于服务器既可以发布数据集又可同时订阅数据集，这里不存在主从角色一说。逻辑复制允许我们更加便利的分发数据。

引入quorum commit

PostgreSQL在10版本之前只能有一台服务器充当同步节点。现在引入了仲裁提交。这个设计实际非常简单。假设我们希望七台服务器中的五台来确认事务提交，这就是仲裁提交要表达的想法：它允许开发人员或者管理员以更优雅的方式来定义事务提交。

仲裁提交配置参数synchronous_standby_names的语法进行了扩展，下面是两个简单例子：

synchronous_standby_names = ANY 1 (s1, s2)
synchronous_standby_names = ANY 2 (s1, s2, s3)

synchronous_standby_names参数支持FIRST和ANY两种模式指定同步复制节点。

数据分区

PostgreSQL 10是引入分区技术的第一个版本。我们曾经使用继承的方式来实现，PostgreSQL 10是第一个提供内置声明式分区功能的版本。

当前分区功能支持：1.自动创建合适的子表约束 2.父表的修改会路由到子表。

创建跨列统计信息(CREATE STATISTICS)

在实际生产环境中，我们可以使用CREATE STATISTICS创建统计信息来帮助客户。它为什么会起作用呢？当我们运行SQL时，优化器会做出智能判断来加快查询速度。然而优化器在很大程度上依赖某个子句或操作返回数据的估值。PostgreSQL 10版本之前只有单列的统计信息，它并不知道表字段之间的相关性。让我们看一个例子：

SELECT * FROM car WHERE vendor = 'Ford' AND model = 'Mini Clubman';

优化器将简单地对两个字段的选择性相乘，这在生产案例中会得到错误的执行计划，影响SQL性能。

使用CREATE STATISTICS创建统计信息存储多列统计数据会让优化器了解实际情况，帮助优化器处理跨列依赖关系。

并行特性提升

PostgreSQL 9.6是第一个支持并行查询的版本。并行特性支持是一项持续性的大工程。PostgreSQL 10引入了并行索引，现在完全支持并行b-tree扫描和bitmap扫描。除了索引，PostgreSQL社区还在努力支持并行merge joins，并允许更多函数运行在并行模式上。

引入ICU编码库

当创建PostgreSQL数据库时，管理员可以选择设置编码，编码决定存储的字符集和字符的显示顺序。例如de_AT@UTF-8表示我们使用Unicode字符集，字符显示的顺序按照Austrian 排序(Austrians人讲German语言，按German语言进行排序)，de_AT定义了数据排序的顺序。

为了实现这种排序，PostgreSQL依赖于操作系统实现。麻烦在于，如果操作系统中字符的排序顺序在某些时间内发生变化（可能是因为bug或其他原因），PostgreSQL的索引将产生问题。正常的b-tree索引是一个排序列表，如果排序顺序发生改变了，索引自然就有问题了。

引入外部ICU库可以解决这个问题。ICU提供比操作系统更坚固的保障，适合长期存储数据。随着PostgreSQL 10的引入，可以启用ICU编码。