掌握PostgreSQL新特性学习笔记五：PostgreSQL 13

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

PostgreSQL 13

SQL及开发相关

psql改进

版本13中psql可以跟踪会话的状态，我们可以看到一个事务是否运行成功，参考如下：

test=# BEGIN;
BEGIN
test=*# SELECT 1;
 ?column?
----------
 1
(1 row)
test=*# SELECT 1 / 0;
ERROR: division by zero
test=!# SELECT 1 / 0;
ERROR: current transaction is aborted, commands ignored until end of transaction
block
test=!# COMMIT;
ROLLBACK

psql客户端提示符PROMPT1和PROMPT2增加了%x，我们可以查看事务运行的状态。

• 如果没在事务中，显示空字符
• 如果在事务中，显示*
• 当事务失败，显示!
• 当事务状态不确定，显示?

pgbench改进

版本13中pgbench初始化数据集时可以使用数据分区，参考如下：

$ pgbench -i -s 100 --partitions=10 
...
done in 19.70 s (drop tables 0.00 s, create tables 0.03 s, generate 7.34 s, vacuum 10.24 s, primary keys 2.08 s).
test=# \d+
 List OF relations
 Schema | Name | TYPE | ...
--------+---------------------+-------------------+ ...
 public | pgbench_accounts | partitioned TABLE | ...
 public | pgbench_accounts_1 | TABLE | ...
 public | pgbench_accounts_10 | TABLE | ...
 public | pgbench_accounts_2 | TABLE | ...
 public | pgbench_accounts_3 | TABLE | ...
...

内置UUIDs

在以前的版本使用UUIDs需要加载扩展，才能处理UUIDs。版本13不需要扩展可以直接使用，参考如下：

test=# SELECT gen_random_uuid(); 
gen_random_uuid
-------------------------------------- 
960d6103-090e-472e-901e-daac7b73a3a3 
(1 row)

更便捷的drop数据库

先看看下面的问题：

postgres=# DROP DATABASE test;
ERROR: database "test" is being accessed by other users
DETAIL: There is 1 other session using the database.

如果数据库有用户正在连接，则不能drop。对于大多数用户来说，这个条件其实很难满足，通常一直都会有新连接不断建立，如果我们通过alter database阻塞新连接同时删除已存在的连接，这样的处理有点粗暴。下面的语法使用with(force)选项会更便捷。

postgres=# DROP DATABASE test WITH (force); 
DROP DATABASE

可删除运算存储列的表达式

PostgreSQL可以对表达式运算输出的结果进行物化存储，下面展示运算存储列的使用：

test=# CREATE TABLE t_test (
 a int,
 b int,
 c int GENERATED ALWAYS AS (a * b) STORED
);
CREATE TABLE
test=# INSERT INTO t_test (a, b) VALUES (10, 20);
INSERT 0 1

可以看到，添加的字段c

test=# SELECT * FROM t_test;
 a | b | c
----+----+-----
 10 | 20 | 200
(1 row)

现在的问题是：如何移除字段c的表达式属性呢？PostgreSQL 13给出如下答案：

test=# ALTER TABLE t_test ALTER COLUMN c DROP EXPRESSION ;
ALTER TABLE

c现在仅仅只是一个普通的字段了。

test=# \d t_test;
 Table "public.t_test"
 Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a | integer | | |
 b | integer | | |
 c | integer | | |

我们现在可以对c直接插入值而不会报错。

性能相关

B-tree索引去重优化

先来看下面的示例

test=# CREATE TABLE tab (a int, b int);
CREATE TABLE
test=# INSERT INTO tab SELECT id, 1 FROM generate_series(1, 5000000) AS id;
INSERT 0 5000000

上面生成了五百万行数据，字段a有五百万种不同的值而字段b是五百万条同样的值。下面对这两个字段分别创建索引：

test=# CREATE INDEX idx_a ON tab (a); 
CREATE INDEX 
test=# CREATE INDEX idx_b ON tab (b); 
CREATE INDEX

我们再来看看索引的大小

test=# \di+
 List of relations
 Schema | Name | Type | Owner | Table | Persistence | Size | Description
--------+-------+-------+-------+-------+-------------+--------+-------------
 public | idx_a | index | hs | tab | permanent | 107 MB |
 public | idx_b | index | hs | tab | permanent | 33 MB |
(2 rows)

可以看到字段b的索引占用更小的存储空间，更小的存储空间消耗相当于会有更高缓存命中，因而能获得更好的性能。

如果我们迁移到PostgreSQL 13，基于考虑索引去重对性能提升的影响，我们应该完成后重建索引。

增量排序

先来看下面的示例，我们对上面的例子先删除字段b的索引，只保留字段a的索引。

test=# DROP INDEX idx_b; 
DROP INDEX 

字段a的索引已经是排序好的，接下来我们看看如下的查询

test=# explain SELECT * FROM tab ORDER BY a, b;
 QUERY PLAN
----------------------------------------------------------------------
 Incremental Sort (cost=0.47..376979.43 rows=5000000 width=8)
 Sort Key: a, b
 Presorted Key: a
 -> Index Scan using idx_a on tab (cost=0.43..151979.43 rows=5000000 width=8)
(4 rows)

我们按a和b同时排序，因为字段a的索引已经是预先排好序的，所以PostgreSQL会基于idx_a索引对b做增量排序。

下面实际执行

test=# explain analyze SELECT * FROM tab ORDER BY a, b;
 QUERY PLAN
---------------------------------------------------------------------
 Incremental Sort (cost=0.47..376979.43 rows=5000000 width=8)
 (actual time=0.167..1297.696 rows=5000000 loops=1)
 Sort Key: a, b
 Presorted Key: a
 Full-sort Groups: 156250 Sort Method: quicksort
 Average Memory: 26kB Peak Memory: 26kB
 -> Index Scan using idx_a on tab (cost=0.43..151979.43 rows=5000000 width=8)
 (actual time=0.069..773.260 rows=5000000 loops=1)
 Planning Time: 0.068 ms
 Execution Time: 1437.362 ms
(7 rows)

为了对比以前版本的区别，我们可以临时关闭增量排序功能

test=# SET enable_incremental_sort TO off;
SET
test=# explain analyze SELECT * FROM tab ORDER BY a, b;
 QUERY PLAN
-----------------------------------------------------------------
 Sort (cost=765185.42..777685.42 rows=5000000 width=8)
 (actual time=1269.250..1705.754 rows=5000000 loops=1)
 Sort Key: a, b
 Sort Method: external merge Disk: 91200kB
 -> Seq Scan on tab (cost=0.00..72124.00 rows=5000000 width=8)
 (actual time=0.032..311.777 rows=5000000 loops=1)
 Planning Time: 0.057 ms
 Execution Time: 1849.416 ms
(6 rows)

可以看到比上面多消耗400ms，同时我们可以看到增量排序使用的内存消耗更低。所以增量排序不仅仅提升了性能，也极大降低了内存消耗。

reindexdb命令支持并行

reindexdb现在支持-j选项，可以使用CPU的多核特性。

下面的示例可以看到性能的差异

$ time reindexdb -j 8 database_name
real 0m6.789s
user 0m0.012s
sys 0m0.008s

$ time reindexdb database_name
real 0m24.137s
user 0m0.001s
sys 0m0.004s

注意这个特性只有再我们的单表上有足够多的索引时才有加速效果，如果我们每个表都只有一个索引，则不会有提升作用。所以只有我们的表足够大，索引足够多，结果才会越明显。

其它

允许哈希聚合溢出到磁盘
PL/pgSQL处理循环提速
vacuum对单表的多个索引支持并行处理
全表写时允许跳过WAL
…

监控相关

增加了如下视图

• pg_stat_progress_basebackup 跟踪pg_basebackup操作的进度
• pg_stat_progress_analyze 跟踪analyze操作的进度
• pg_shmem_allocations 跟踪共享内存分配情况
• pg_stat_slru

保持联系

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

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