掌握PostgreSQL新特性学习笔记四：PostgreSQL 12

原创多米爸比 2021-10-06

2053

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

PostgreSQL 12

SQL及开发相关

psql及doc改进

PostgreSQL 12 引入了两个大的改进：

文档中开始添加图形
psql添加帮助命令的文档链接

在过去，官方文档中没有任何图形或图解说明。文档是纯文本的，这使得一些人很难快速理解数据库的某些特性。第二个变化是psql中帮助命令在以前并没有关联官方文档的直接链接，如下：

上面的URL确实非常有用，可以避免我们进行一些不必要的查找和挖掘。

增加CSV的输出格式

postgres=# \pset format csv
Output format is csv.
postgres=# select id,id from generate_series(1,4) as id;
id,id
1,1
2,2
3,3
4,4

如果我们总是想让数据保持csv输出格式，我们可以将pset命令保存到.psqlrc文件中。

并行重建索引

一般情况下我们很少会需要重新创建索引。新建索引时，PostgreSQL提供了CREATE INDEX CONCURRENT，该命令允许用户在表写负载很重时也能同时创建索引。当我们使用正常的CREATE INDEX语句新建索引时会阻塞表，因此，很难在7 x 24的OLTP数据库中创建大型索引。

但是，在某些情况下需要在数据库或某个模式中重建所有索引。PostgreSQL 12允许在整个数据库、模式或表上并行执行REINDEX。

postgres=# \h reindex
Command:     REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name

URL: https://www.postgresql.org/docs/12/sql-reindex.html

运算存储列

在某些情况下，一个列值需要从其它列进行运算动态产生，通常需要使用触发器来实现。然而编写触发器需要额外的编码及手动工作。PostgreSQL提供了更好的解决方案。

假如我们以公里和海里为单位存储数据。一海里约等于1.852km，为了确保海里能自动生成，可以使用下面的语法：

test=# CREATE TABLE t_measurement (
t    timestamp,
km   numeric,
nm   numeric GENERATED ALWAYS AS (km * 1.852) STORED
);
CREATE TABLE

使用GENERATE ALWAYS AS语句来预计算列的值。下面的插入语句可以看到符合我们的预期：

test=# insert into  t_measurement (t,km) values(now(),100) returning *;
t                         |km  | nm
2021-10-03 13:26:01.100111|100  |185.200
(1 row)

INSERT 0 1

GENERATED ALWAYS AS IDENTITY声明的字段，插入语句不能对该字段显式赋值，可以用来确保字段的值始终按照系统设置而不会被修改。

枚举类型处理增强

PostgreSQL 12对枚举类型进行了增强，可以在单个事务内修改枚举类型，但是需要注意一个限制，新建的类型不能使用，在事务内访问新建的枚举类型会提示错误并回滚。

postgres=# create type currency as enum ('USD','EUR','GBP');
CREATE TYPE
postgres=# begin ;
BEGIN
postgres=# alter type currency add value 'CHF' after 'EUR';
ALTER TYPE
postgres=# select 'USD'::currency;
 currency 
----------
 USD
(1 row)

postgres=# select 'CHF'::currency;
ERROR:  unsafe use of new value "CHF" of enum type currency
LINE 2: select 'CHF'::currency;
               ^
HINT:  New enum values must be committed before they can be used.
postgres=# commit;
ROLLBACK
Time: 0.133 ms

JSONPATH

JSONPATH是与开发人员高度相关性的一个特性，PostgreSQL 12提供了大量的新功能函数来更快捷地解析JSON类型。这也极大的促进PostgreSQL处理NoSQL风格的负载的能力。

JSONPATH使用可以参考我的这篇文章：<<PostgreSQL如何使用json>>(https://www.modb.pro/db/21948)

备份恢复相关

PostgreSQL 12最重要的变化是recovery.conf文件不再使用，相关的恢复参数直接合并到postgresql.conf文件中，同时废弃了standby_mode参数，使用空的signal文件来进行触发。

性能相关

CTE优化

Common table expressions (CTEs)也就是WITH语句，可以帮我们把大的复杂查询分解为简小的片段，从而更易阅读和理解SQL语句。使用WITH语句可以“物化”缓存多次重复的计算，降低冗余子查询及函数的副作用。在PostgreSQL 12之前我们需要注意CTEs的常见缺陷：与普通查询相比，优化器无法将父查询的条件下推到外层查询中。

参考下面的示例：

create table foo (id int, padding text);

insert into foo (id, padding)
    select id, md5(random()::text)
    from generate_series(1, 1000000) as id
    order by random();

create index foo_id_ix on foo (id);

analyze foo;

explain analyze
with cte as (
    select * from foo
) select * from cte where id = 500000;

下面对比11和12的执行计划