PG 14新特性汇总

DB宝 2023-06-28

2115

PostgreSQL 14 ALTER TABLE DETACH 支持 CONCURRENTLY：分区表下线不再阻塞其它会话

从PG1开始，ALTER TABLE DETACH 支持 CONCURRENTLY，避免因ALTER TABLE DETACH忘记设置statement_timeout
参数而长时间锁表。

在PG 14版本之前，执行alter table pt_table detach partition part_name;
命令会阻塞该分区表（及父表）的所有操作，包括SELECT操作，这是非常严重的。从PG14开始，加上CONCURRENTLY后，就不再阻塞其它会话了，只是其它会话会报错而已。

模拟过程：

1、新显式开启事务，插入一条数据，别提交

2、执行alter table pt_table detach partition part_name;

3、新开事务，做任何操作都哈卡住

PG 14的语法为：

1ALTER TABLE [ IF EXISTS ] name
2    DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

1)DETACH PARTITION时允许非阻塞方式进行detach
2)新增加了FINALIZE选项

在2个运行的事务中，允许一个分区从他的分区表中分离而不阻塞当前查询。因为在2个事务中运行，所以不能在一个事务块中使用。如果第2个事务取消或发生崩溃，则有ALTER TABLE…DETACH PARTITION…FINALIZE，执行最后的步骤。

在PG14中，上述语句分离目标表的指定分区，分离的分区继续作为一个独立的表存在，但不再与分离它的表有任何联系；
附加到目标表的索引的任何索引都被分离；
任何作为目标表中触发器的克隆创建的触发器都将被删除；
在外键约束中引用此分区表的任何表上都可以获得share lock；
如果指定CONCURRENTLY，它会使用降低的锁级别运行以避免阻塞可能正在访问分区表的其他会话，在这种模式下，内部使用两个事务。
在第一个事务期间，父表和分区都有SHARE UPDATE EXCLUSIVE锁，并将分区标记为正在分离(undergoing detach)；此时，事务被提交，所有其他使用分区表的事务都将被等待。
一旦所有这些事务完成，第二个事务在分区表上获得SHARE UPDATE EXCLUSIVE锁，在分区上获得ACCESS EXCLUSIVE锁，分离过程完成。从分区约束复制而来的约束被添加到分区
CONCURRENTLY不能运行在事务块中，也不能用于分区表含有默认分区的条件下。
如果FINALIZE被指定，前一次被中断或者被取消的detach concurrently调用会被完成。
在一个分区表中，一次只能有一个partition被detach。

实验过程：

 1/*创建测试表*/
 2create table test_list(id serial, flag text, location text, create_date date) partition by list(flag);    
 3
 4-- 利用匿名块快速创建多个list分区子表
 5do                              
 6$$
 7declare base text; sqlstring text; i int;
 8begin
 9base = 'create table test_list_%s partition of test_list for values in (''%s'')';
10for i in 0..9 loop
11    sqlstring = format(base, 'flag' || i, 'flag' || i);
12    --raise notice '%', sqlstring;
13    execute sqlstring;
14end loop;
15end
16$$language plpgsql;
17
18
19\d+ test_list
20alter table test_list detach partition test_list_flag0 concurrently ;
21
22
23
24-- 单独窗口开启事务
25psql -U postgres -h172.18.0.14 -p 54327 -c lhrdb
26begin;
27INSERT INTO test_list(flag) VALUES('flag3');
28
29
30-- 继续测试
31alter table test_list detach partition test_list_flag3 concurrently;
32alter table test_list detach partition test_list_flag2;
33
34-- 当CONCURRENTLY被取消后，detach的分区表会显示DETACH PENDING， 需要使用ALTER TABLE … DETACH PARTITION … FINALIZE去完成被取消的DETACH， 才能去detach其它分区。
35alter table test_list detach partition test_list_flag3 finalize;
36
37
38
39-- 可以再开一个窗口测试加和不加concurrently时的效果
40
41-- 以下为加了concurrently时的效果
42postgres=# INSERT INTO test_list(flag) VALUES('flag1');
43ERROR:  no partition of relation "test_list" found for row
44DETAIL:  Partition key of the failing row contains (flag) = (flag1).
45postgres=# select * from test_list_flag1;
46 id | flag  | location | create_date 
47----+-------+----------+-------------
48  6 | flag1 |          | 
49(1 row)
50
51-- 以下为没有加concurrently时的效果，会卡住
52postgres=# INSERT INTO test_list(flag) VALUES('flag1');

参考：https://www.xmmup.com/pg-14xintexingzhialter-table-detach-zhichi-concurrently.html

LZ4压缩可以用于TOAST数据

在 PostgreSQL 中，TOAST（The Oversized-Attribute Storage Technique）是一种用于处理大型或超过特定大小限制的数据的存储技术。它主要应用于存储行或列中的大型数据值，例如大型文本（text
）、二进制数据（bytea
）或变长字符数据（varchar
）等。
TOAST 数据是被自动压缩和分解的大型数据值。当某个数据值的大小超过 TOAST 阈值（默认为 2KB），PostgreSQL 将自动将其转换为 TOAST 格式，并将其存储在特殊的 TOAST 表中。原始表中存储的是 TOAST 数据的引用（指针），而不是实际的数据内容。这样可以节省存储空间并提高性能。
TOAST 数据的主要特点如下：
压缩存储：TOAST 数据会经过压缩处理，以减少所需的存储空间。
分解存储：如果一个数据值超过 TOAST 阈值，它将被分解成多个块，并存储在 TOAST 表中。这样可以提高读取和处理大型数据的效率。
透明访问：PostgreSQL 在访问 TOAST 数据时会自动处理数据的压缩和分解，对应用程序而言是透明的，无需显式的操作。
数据一致性：TOAST 数据的存储和访问过程会保持数据的一致性，即使是在并发环境下也能正确处理。
TOAST 技术使得 PostgreSQL 能够有效地存储和处理大型数据值，同时对应用程序开发者来说是透明的，无需特别的处理。

LZ4压缩可以用于TOAST数据：可以在列级别设置或者通过default_toast_compression设置默认值，编译时必须--with-lz4编译。默认仍是PGLZ；LZ4的压缩性能比PGLZ更好，使用更少CPU。测试表明，性能可以提升2倍以上，空间大小仅比PGLZ稍大。

PG 14为TOAST列添加了LZ4压缩，同时保留对pglz压缩的支持。toast列的压缩算法在默认情况下，是由default_toast_compression配置参数决定的，该配置参数值默认为pglz，可以使用alter table tab_name ALTER COLUMN c2 SET COMPRESSION lz4;
进行压缩算法的修改，但是修改对已经存在的tuple是无效的。

在CREATE TABLE (LIKE) 语句中，指定’LIKE table name INCLUDING COMPRESSION’子句会复制源表的压缩定义。pg_attribute系统表的attcompression列存储了压缩的定义，p代表pglz，l代表LZ4，可以使用pg_column_compression
函数去查看使用的压缩方法。

PG中，页是存储数据的单位，默认是8KB。一般情况下，一行数据不允许跨页存储。然而，有一些变长的数据类型，存储的数据可能超出一页大小。为了克服整个限制，大字段域会被压缩或者分割成多个物理行。这个技术就是TOAST：

https://www.postgresql.org/docs/14/storage-toast.html

默认情况下，如果表中有变长列，行数据的大小超过TOAST_TUPLE_THRESHOLD（默认2KB）就会触发TOAST。首先，会先压缩数据；压缩后如果仍然太大，会溢出存储。需要注意，如果列的存储策略指定EXTERNAL/PLAIN，压缩会被禁止。

PG14之前版本，TOAST仅支持一个压缩算法PGLZ（PG内置算法）。但是其他压缩算法可能比PGLZ更快或者有更高的压缩率。PG14中有了新压缩选项LZ4压缩，这是一个以速度著称的无损压缩算法。因此我们可以期望它有助于提高TOAST压缩和解压缩的速度。

与PGLZ相比，LZ4压缩和解压缩TOAST数据更加高效，并提供很好的性能。和未压缩数据相比，查询速度几乎一样，和PGLZ相比，插入快80%。当然某些场景下压缩率不太好，但如过你想要提升执行速度，强烈推荐使用LZ4算法。

同样需要注意，需要考虑表中的数据是否合适压缩。如果压缩率不好，它仍然会尝试压缩数，然后放弃。这将导致额外的内存资源浪费，并极大影响插入数据的速度。

安装操作系统包：

1yum install lz4
2yum install lz4-devel

PG14编译安装时需要带 —with-lz4编译选项，

 1lhrdb=# SELECT * FROM pg_config ;
 2       name        |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   setting
 3-------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 4 BINDIR            | /usr/lib/postgresql/14/bin
 5 DOCDIR            | /usr/share/doc/postgresql-doc-14
 6 HTMLDIR           | /usr/share/doc/postgresql-doc-14
 7 INCLUDEDIR        | /usr/include/postgresql
 8 PKGINCLUDEDIR     | /usr/include/postgresql
 9 INCLUDEDIR-SERVER | /usr/include/postgresql/14/server
10 LIBDIR            | /usr/lib/x86_64-linux-gnu
11 PKGLIBDIR         | /usr/lib/postgresql/14/lib
12 LOCALEDIR         | /usr/share/locale
13 MANDIR            | /usr/share/postgresql/14/man
14 SHAREDIR          | /usr/share/postgresql/14
15 SYSCONFDIR        | /etc/postgresql-common
16 PGXS              | /usr/lib/postgresql/14/lib/pgxs/src/makefiles/pgxs.mk
17 CONFIGURE         |  '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=${prefix}/include' '--mandir=${prefix}/share/man' '--infodir=${prefix}/share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-option-checking' '--disable-silent-rules' '--libdir=${prefix}/lib/x86_64-linux-gnu' '--runstatedir=/run' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--mandir=/usr/share/postgresql/14/man' '--docdir=/usr/share/doc/postgresql-doc-14' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/14' '--bindir=/usr/lib/postgresql/14/bin' '--libdir=/usr/lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra-version= (Debian 14.7-1.pgdg110+1)' '--enable-nls' '--enable-thread-safety' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with-gssapi' '--with-ldap' '--with-pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' 'AWK=mawk' 'MKDIR_P=/bin/mkdir -p' 'PROVE=/usr/bin/prove' 'PYTHON=/usr/bin/python3' 'TAR=/bin/tar' 'XSLTPROC=xsltproc --nonet' 'CFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-z,relro -Wl,-z,now' '--enable-tap-tests' '--with-icu' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config-11' 'CLANG=/usr/bin/clang-11' '--with-lz4' '--with-systemd' '--with-selinux' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security'
18 CC                | gcc
19 CPPFLAGS          | -Wdate-time -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2
20 CFLAGS            | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer
21 CFLAGS_SL         | -fPIC
22 LDFLAGS           | -Wl,-z,relro -Wl,-z,now -L/usr/lib/llvm-11/lib -Wl,--as-needed
23 LDFLAGS_EX        |
24 LDFLAGS_SL        |
25 LIBS              | -lpgcommon -lpgport -lselinux -llz4 -lxslt -lxml2 -lpam -lssl -lcrypto -lgssapi_krb5 -lz -lreadline -lpthread -lrt -ldl -lm
26 VERSION           | PostgreSQL 14.7 (Debian 14.7-1.pgdg110+1)
27(23 rows)
28
29
30lhrdb=#
31
32lhrdb=# CREATE TABLE compress1(c1 INT, c2 TEXT COMPRESSION lz4) ;
33CREATE TABLE
34lhrdb=# show default_toast_compression ;
35 default_toast_compression
36---------------------------
37 pglz
38(1 row)
39
40
41lhrdb=# SET default_toast_compression=lz4;
42SET
43
44lhrdb=# show default_toast_compression ;
45 default_toast_compression
46---------------------------
47 lz4
48(1 row)
49
50lhrdb=# \d+ compress1
51                                        Table "public.compress1"
52 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
53--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
54 c1     | integer |           |          |         | plain    |             |              |
55 c2     | text    |           |          |         | extended | lz4         |              |
56Access method: heap
57
58
59lhrdb=# ALTER TABLE compress1 ALTER COLUMN c2 SET COMPRESSION pglz ;
60ALTER TABLE
61lhrdb=# \d+ compress1
62                                        Table "public.compress1"
63 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
64--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
65 c1     | integer |           |          |         | plain    |             |              |
66 c2     | text    |           |          |         | extended | pglz        |              |
67Access method: heap

其它示例：

 1CREATE TABLE tbl (id int,
 2col1 text COMPRESSION pglz,
 3col2 text COMPRESSION lz4,
 4col3 text);
 5
 6\d+ tbl
 7
 8
 9lhrdb=# CREATE TABLE tbl (id int,
10lhrdb(# col1 text COMPRESSION pglz,
11lhrdb(# col2 text COMPRESSION lz4,
12lhrdb(# col3 text);
13CREATE TABLE
14lhrdb=#
15lhrdb=# \d+ tbl
16                                           Table "public.tbl"
17 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description
18--------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
19 id     | integer |           |          |         | plain    |             |              |
20 col1   | text    |           |          |         | extended | pglz        |              |
21 col2   | text    |           |          |         | extended | lz4         |              |
22 col3   | text    |           |          |         | extended |             |              |
23Access method: heap

我们使用\d+命令可以看到所有列的压缩算法。如果列不支持或者没有指定压缩算法，那么会在Compression列显示空格。上面的例子中，id列不支持压缩算法，col1列使用PGLZ，col2使用LZ4，col3没有指定压缩算法，那么它会使用默认的压缩算法。

可以通过ALTER TABLE修改列压缩算法，但需要注意，修改后的算法仅影响执行整个命令后的insert数据。

 1INSERT INTO tbl VALUES (1, repeat('abc',1000), repeat('abc',1000),repeat('abc',1000));
 2ALTER TABLE tbl ALTER COLUMN col1 SET COMPRESSION lz4;
 3INSERT INTO tbl VALUES (2, repeat('abc',1000), repeat('abc',1000),repeat('abc',1000));
 4
 5
 6SELECT id,
 7       pg_column_compression(id)   AS compression_colid,
 8       pg_column_compression(col1) AS compression_col1,
 9       pg_column_compression(col2) AS compression_col2,
10       pg_column_compression(col3) AS compression_col3
11FROM tbl;
12id | compression_colid | compression_col1 | compression_col2 | compression_col3
13---+-------------------+------------------+------------------+------------------
14 1 |                   | pglz             | lz4              | lz4
15 2 |                   | lz4              | lz4              | lz4
16(2 rows)

可以看到在修改压缩算法前插入的行，col1仍使用PGLZ压缩算法，即使将压缩算法从PGLZ修改到了LZ4。（那么，修改后进行解压时使用哪个算法呢？）

需要注意，如果从其他表扫数据插入本表，例如CREATE TABLE …AS…或者INSERT INTO…SELECT…，插入的数据使用的压缩算法仍然使用原始数据的压缩方法。pg_dump和pg_dumpall也添加了选项--no-toast-compuression，使用整个选项后，不会dump出TOAST压缩选项。

参考：
https://mp.weixin.qq.com/s?__biz=MzU1OTgxMjA4OA==&mid=2247484439&idx=1&sn=0aba55afbcba7f2c19ae3b01575b490b&chksm=fc10d880cb675196ff069650e2840ef1de147b78110b46b8fb53b6dd481b1887ca3bc1fb3c68&token=642725105&lang=zh_CN#rd

新增视图pg_stat_wal，跟踪wal日志的生成与写入磁盘情况

 1postgres=# \x
 2Expanded display is on.
 3postgres=#  SELECT * FROM pg_stat_wal ;
 4-[ RECORD 1 ]----+------------------------------
 5wal_records      | 1009479     wal records的总数量
 6wal_fpi          | 36          wal full page images的总数量
 7wal_bytes        | 103725567   生成的wal的总大小
 8wal_buffers_full | 9889        由于wal buffers被写满而导致的wal data被写入disk的次数。
 9wal_write        | 9927       wal buffers被写入的次数
10wal_sync         | 41         wal file 被同步到disk的次数
11wal_write_time   | 0           写wal buffers耗费的总时间
12wal_sync_time    | 0           sync wal files耗费的总时间
13stats_reset      | 2021-05-21 22:23:21.059275+08        本统计信息最后一次被重置的时间。

pg_prepared_statements新增统计软/硬解析次数

pg_prepared_statements新增统计软/硬解析次数。PostgreSQL中prepare statement可以用来cache plan，用来减少plan的次数。默认是前5次调用生成custom plan，然后生成generic plan。PG14中在pg_prepared_statements视图中新增了generic_plans和custom_plans两列，用来统计generic plan和custom plan的次数。

 1lhrdb=>PREPARE pr1 AS SELECT * FROM pg_class WHERE  relname = $1;
 2PREPARE
 3
 4lhrdb=>EXECUTE  pr1('t1');
 5
 6lhrdb=>select * from pg_prepared_statements;
 7 name |                    statement                     |         prepare_time          | parameter_types | from_sql | generic_plans | custom_plans
 8------+--------------------------------------------------+-------------------------------+-----------------+----------+---------------+--------------
 9 pr1  | PREPARE pr1 AS SELECT * FROM pg_class WHERE     +| 2021-05-13 10:17:28.429238+08 | {name}          | t        |             0 |            1
10      | relname = $1;                                    |                               |                 |          |               |
11(1 row)
12
13
14-- 执行多次后再查看：
15lhrdb=>select * from pg_prepared_statements;
16-[ RECORD 1 ]---+-------------------------------------------------
17name            | pr1
18statement       | PREPARE pr1 AS SELECT * FROM pg_class WHERE     +
19                | relname = $1;
20prepare_time    | 2021-05-13 10:17:28.429238+08
21parameter_types | {name}
22from_sql        | t
23generic_plans   | 2
24custom_plans    | 5

PostgreSQL 14新特性--减少索引膨胀

允许添加 btree 索引以删除过期的索引条目以防止页面拆分，有利于减少索引列频繁更新的表上的索引膨胀。Btree索引“自底向上”（Bottom-up index tuple deletion）的索引条目去除功能，在即将发生索引页分裂之前删除指向死元组的索引项。这可以减少索引条目的数量，避免昂贵的页面分割，以及稍后VACUUM清理时会出现的膨胀。

PG12中索引的存储更加高效，PG13添加索引条目去重功能进一步提升存储效率。PG14将带来“自底向上”的索引条目去除功能，旨在减少不必要的页面分裂、索引膨胀和更新大量索引带来的碎片。

为什么会出现索引膨胀

对于B-tree索引，表中每个行版本都有一个未死的索引条目（对所有人可见）。执行vacuum删除死记录时，也会删除对应的索引条目。和表一样，同样会在索引页中创建空的空间。这样的空间可以重用，但是如果没有新元组插入该页，这样的空间会保持为空。

这种膨胀在某种程度上是不可避免的，也是正常的。但如果膨胀太多，索引效率就会降低：

1) 对于索引范围扫描，必须扫描更多的页

2) RAM中缓存了索引页，意味着缓冲膨胀，就是浪费了RAM

3) 每个页中更少的索引条目意味着更少的“fan out”，索引树的层级将更高

如果频繁更新相同行，就会发生这种情况。VACUUM清理老元组前，表和索引会维护相同行的很多版本。如果索引页填满，将令人很烦：然后PG会将索引页分裂成2个。这是一个昂贵的操作，VACUUM执行完清理，我们最终会得到2个臃肿的页面而不是一个。

当前用于改善索引膨胀和性能的特性

HOT元组

HOT元组的创建可能是PG对抗索引中不必要条目的强大武器。使用此功能UPDATE创建产生的元组不会被索引条目引用，它还会引用元组的老版本。通过这种方法，不需要创建新的索引条目，可以避免索引膨胀。HOT参考：

https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/

杀死索引条目

当索引扫描遇到一个指向死元组的条目时，标记该条目“killed”。后续索引扫描会在VACUUM删除他们之前跳过这些条目。此外，PG可以在索引页面已满时删除这样的条目，以避免页分裂。

PG14如何进一步减少索引膨胀

自下而上的索引元组删除比之前方法更进一步：他在索引页分裂即将发生前就删除指向死元组的索引条目。这可以减少索引条目的数量并避免昂贵的分裂，以及稍后VACUUM清理参数的膨胀。

在某种程度上，这执行了之前VACUUM的部分公众，在这点上可以避免索引膨胀。

请参考：https://mp.weixin.qq.com/s?__biz=MzU1OTgxMjA4OA==&mid=2247484500&idx=1&sn=ce7a307edb5fa35679dfe1b868c7bb9b&chksm=fc10d8c3cb6751d563533111dbc91a2347a32d64cd87387a3bd593d7c1886f4109eab214538f&scene=178&cur_album_id=2141739597477773312#rd

示例

 1\c lhrdb
 2
 3CREATE TABLE testtab (
 4   id        bigint
 5   CONSTRAINT testtab_pkey PRIMARY KEY,
 6   unchanged integer,
 7   changed   integer
 8);
 9
10INSERT INTO testtab
11   SELECT i, i, 0
12   FROM generate_series(1, 10000) AS i;
13
14CREATE INDEX testtab_unchanged_idx ON testtab (unchanged);
15CREATE INDEX testtab_changed_idx ON testtab (changed);
16
17
18cat > bench.sql <<"EOF"
19\set id random_gaussian(1, 10000, 10)
20UPDATE testtab SET changed = changed + 1 WHERE id = :id;
21UPDATE testtab SET changed = changed + 1 WHERE id = :id;
22UPDATE testtab SET changed = changed + 1 WHERE id = :id;
23UPDATE testtab SET changed = changed + 1 WHERE id = :id;
24UPDATE testtab SET changed = changed + 1 WHERE id = :id;
25UPDATE testtab SET changed = changed + 1 WHERE id = :id;
26UPDATE testtab SET changed = changed + 1 WHERE id = :id;
27UPDATE testtab SET changed = changed + 1 WHERE id = :id;
28UPDATE testtab SET changed = changed + 1 WHERE id = :id;
29UPDATE testtab SET changed = changed + 1 WHERE id = :id;
30EOF
31
32
33-- 运行脚本 60000 次（6 个客户端 10000 次迭代），如下所示：
34pgbench -n -c 6 -f bench.sql -t 10000 lhrdb
35
36
37
38CREATE EXTENSION pgstattuple;
39
40SELECT i.indexrelid::regclass AS index,
41       s.index_size,
42       s.avg_leaf_density
43FROM pg_index AS i
44   CROSS JOIN LATERAL pgstatindex(i.indexrelid) AS s
45WHERE indrelid = 'testtab'::regclass;

这是我们在 v13 中得到的：

1         index         │ index_size │ avg_leaf_density
2═══════════════════════╪════════════╪══════════════════
3 testtab_pkey          │     319488 │             66.6
4 testtab_unchanged_idx │    4022272 │             5.33
5 testtab_changed_idx   │    4505600 │            13.57
6(3 rows)

对于 v14，结果是：

1         index         │ index_size │ avg_leaf_density
2═══════════════════════╪════════════╪══════════════════
3 testtab_pkey          │     245760 │            87.91
4 testtab_unchanged_idx │     532480 │            39.23
5 testtab_changed_idx   │    4038656 │            14.23
6(3 rows)

改进最大的是testtab_unchanged_idx。在13中，索引膨胀严重，而在14中仅有60%的膨胀（这对索引来说还不错）。在这里我们看到了新功能的最大影响。UPDATE不扫扫描那个索引，因此没有killed的索引条目，“自底向上的删除”可以删除足够的这样的条目避免分裂。

也可以衡量testtab_pkey。由于UPDATE扫描该索引，死的索引元组被killed，新特性在分裂前删除这些元组。与13相比，效果不太明显，因为13已经很好地避免索引膨胀了。

索引testtab_changed_idx无法从新特性中获益。因为这进解决了UPDATE不修改索引值的情况。如果想知道为什么testtab_unchanged_idx叶子密度比13低：删除了索引重复数据。

Pg_upgrade后我们可以使用这项功能吗？
索引的存储格式没有变，所以pg_upgrade PG12及之后版本创建的索引后会自动公众。但之前版本创建的索引，需要REINDEX后获益。记住，pg_upgrade仅拷贝索引文件，不会更改内部索引版本。

总结

PG14继续改进B-tree索引。这个特性虽不是革命性的，但有望为许多公众负载提供改进的性能，尤其是那些有大量更新的工作负载。

原文 https://www.cybertec-postgresql.com/en/index-bloat-reduced-in-postgresql-v14/

PostgreSQL 14中提升Nested Loop Joins性能的enable_memoize

最近在PG14中发现新增一个配置参数enable_memoize（默认为on），通过此参数可以提升嵌套循环连接的性能，有人测试性能竟然能提升1000倍！

1postgres=# show enable_memoize;
2 enable_memoize
3----------------
4 on
5(1 row)

将查询提升1000倍暗示整个语句非常烂，而memoize可能有很大帮助，那么对于普通join是否也有用呢？

什么是memoization？

memoization表示：给定y=f(x)关系，可以在任何计算中使用y替代f(x)。例如，不论计算多少此upper(x)，总会得到X。如果整个函数计算代价非常高，并且输入值仅几种，那么为什么不维护一个映射输入值的哈希表，并通过它来查找已知值而不是计算他们呢？

正如我之前的博文https://blog.jooq.org/oracle-scalar-subquery-caching/，Oracle 11已经引入了一个标量子查询缓存的特性，可以在jOOQ中激活，避免代价高昂的PL/SQL上下文切换。

enable_memoize在PostgreSQL嵌套循环join中非常有用，参考上述推文，lateral join通常由嵌套循环连接来执行。

开启/关闭该参数

创建一个表：

1CREATE TABLE t AS
2SELECT i, i % 5 AS j
3FROM generate_series(1, 100000) AS t(i);
4
5CREATE TABLE u AS
6SELECT i, i % 20000 as j
7FROM generate_series(1, 100000) AS t(i);
8
9CREATE INDEX uj ON u(j);

总之，表t和u有100000条记录；t.j仅有5个不同的记录，每个重复20000次；u.j有20000个不同记录，每个重复5次。在PG14中查看enable_memoize：

1SELECT current_setting('enable_memoize');
2|current_setting|
3|---------------|
4|on             |

整个特性已开启，看下explain：

 1lhrdb=# EXPLAIN SELECT * FROM t JOIN u ON t.j = u.j;
 2                               QUERY PLAN
 3------------------------------------------------------------------------
 4 Nested Loop  (cost=0.30..8945.41 rows=498430 width=16)
 5   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)
 6   ->  Memoize  (cost=0.30..0.41 rows=5 width=8)
 7         Cache Key: t.j
 8         Cache Mode: logical
 9         ->  Index Scan using uj on u  (cost=0.29..0.40 rows=5 width=8)
10               Index Cond: (j = t.j)
11(7 rows)
12
13
14lhrdb=#

如果没有memoization，进行join时，对于t中的100000行记录，需要执行100000次匹配u表中的这5个值，但使用memoization后，查询仅需要执行5次，因为t.j仅有5个不同的值。

关闭后，PG似乎在我的机器上选择hash join或merge join（多次执行，执行计划可能会变）

 1lhrdb=# set enable_memoize=off;
 2SET
 3lhrdb=# EXPLAIN SELECT * FROM t JOIN u ON t.j = u.j;
 4                             QUERY PLAN
 5---------------------------------------------------------------------
 6 Hash Join  (cost=3084.00..11559.30 rows=498430 width=16)
 7   Hash Cond: (t.j = u.j)
 8   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)
 9   ->  Hash  (cost=1443.00..1443.00 rows=100000 width=8)
10         ->  Seq Scan on u  (cost=0.00..1443.00 rows=100000 width=8)
11(5 rows)

使用benchmark

1）在模式A和模式B中重复操作25次并进行比较

2）重复上述5次以减轻预热和其他缓存效果

 1DO $$
 2DECLARE
 3  v_ts TIMESTAMP;
 4  v_repeat CONSTANT INT := 25;
 5  rec RECORD;
 6BEGIN
 7
 8  -- Repeat the whole benchmark several times to avoid warmup penalty
 9  FOR r IN 1..5 LOOP
10    v_ts := clock_timestamp();
11    SET enable_memoize = OFF;
12
13    FOR i IN 1..v_repeat LOOP
14      FOR rec IN (
15        SELECT t.*
16        FROM t JOIN u ON t.j = u.j
17      ) LOOP
18        NULL;
19      END LOOP;
20    END LOOP;
21
22    RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts);
23    v_ts := clock_timestamp();
24    SET enable_memoize = ON;
25
26    FOR i IN 1..v_repeat LOOP
27      FOR rec IN (
28        SELECT t.*
29        FROM t JOIN u ON t.j = u.j
30      ) LOOP
31        NULL;
32      END LOOP;
33    END LOOP;
34
35    RAISE INFO 'Run %, Statement 2: %', r, (clock_timestamp() - v_ts);
36    RAISE INFO '';
37  END LOOP;
38END$$;

在我机器上，结果一致：

 1Run 1, Statement 1: 00:00:03.763426
 2Run 1, Statement 2: 00:00:03.401346
 3
 4Run 2, Statement 1: 00:00:03.769419
 5Run 2, Statement 2: 00:00:03.375677
 6
 7Run 3, Statement 1: 00:00:03.771465
 8Run 3, Statement 2: 00:00:03.374413
 9
10Run 4, Statement 1: 00:00:03.769136
11Run 4, Statement 2: 00:00:03.398734
12
13Run 5, Statement 1: 00:00:03.772544
14Run 5, Statement 2: 00:00:03.375272

即有10%的加速。在整个系统中，仅此一项就已经值得。

优化LATERAL

使用LATERAL替代，执行下面查询：

1explain
2SELECT *
3FROM
4  t,
5  LATERAL (
6    SELECT count(*)
7    FROM u
8    WHERE t.j = u.j
9  ) AS u(j)

Explain的结果：

 1lhrdb=# explain
 2lhrdb-# SELECT *
 3lhrdb-# FROM
 4lhrdb-#   t,
 5lhrdb-#   LATERAL (
 6lhrdb(#     SELECT count(*)
 7lhrdb(#     FROM u
 8lhrdb(#     WHERE t.j = u.j
 9lhrdb(#   ) AS u(j);
10                                   QUERY PLAN
11---------------------------------------------------------------------------------
12 Nested Loop  (cost=4.39..443693.00 rows=100000 width=16)
13   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)
14   ->  Aggregate  (cost=4.39..4.40 rows=1 width=8)
15         ->  Index Only Scan using uj on u  (cost=0.29..4.38 rows=5 width=0)
16               Index Cond: (j = t.j)
17 JIT:
18   Functions: 5
19   Options: Inlining false, Optimization false, Expressions true, Deforming true
20(8 rows)

因此，对于5个不同的t.j值作为入参，可以再次缓存COUNT(*)值，而不是每次都重复计算

 1DO $$
 2DECLARE
 3  v_ts TIMESTAMP;
 4  v_repeat CONSTANT INT := 25;
 5  rec RECORD;
 6BEGIN
 7  -- Repeat the whole benchmark several times to avoid warmup penalty
 8  FOR r IN 1..5 LOOP
 9    v_ts := clock_timestamp();
10    SET enable_memoize = OFF;
11
12    FOR i IN 1..v_repeat LOOP
13      FOR rec IN (
14        SELECT *
15        FROM
16          t,
17          LATERAL (
18            SELECT count(*)
19            FROM u
20            WHERE t.j = u.j
21          ) AS u(j)
22      ) LOOP
23        NULL;
24      END LOOP;
25    END LOOP;
26
27    RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts);
28    v_ts := clock_timestamp();
29    SET enable_memoize = ON;
30
31    FOR i IN 1..v_repeat LOOP
32      FOR rec IN (
33        SELECT *
34        FROM
35          t,
36          LATERAL (
37            SELECT count(*)
38            FROM u
39            WHERE t.j = u.j
40          ) AS u(j)
41      ) LOOP
42        NULL;
43      END LOOP;
44    END LOOP;
45    RAISE INFO 'Run %, Statement 2: %', r, (clock_timestamp() - v_ts);
46    RAISE INFO '';
47  END LOOP;
48END$$;

这次有了巨大提升：

 1Run 1, Statement 1: 00:00:03.419728
 2Run 1, Statement 2: 00:00:01.083941
 3
 4Run 2, Statement 1: 00:00:03.404954
 5Run 2, Statement 2: 00:00:01.098404
 6
 7Run 3, Statement 1: 00:00:03.425725
 8Run 3, Statement 2: 00:00:01.093883
 9
10Run 4, Statement 1: 00:00:03.441691
11Run 4, Statement 2: 00:00:01.127837
12
13Run 5, Statement 1: 00:00:03.420172
14Run 5, Statement 2: 00:00:01.097943

这真是一个好消息，这也适用普通的相关子查询吗？因为上面的LATERAL相关子查询也可以改写成：

1explain
2SELECT
3  t.*,
4  (
5    SELECT count(*)
6    FROM u
7    WHERE t.j = u.j
8  ) j
9FROM t;

遗憾的是，该计划并没有显示使用memoization：

 1lhrdb=# explain
 2lhrdb-# SELECT
 3lhrdb-#   t.*,
 4lhrdb-#   (
 5lhrdb(#     SELECT count(*)
 6lhrdb(#     FROM u
 7lhrdb(#     WHERE t.j = u.j
 8lhrdb(#   ) j
 9lhrdb-# FROM t;
10                                   QUERY PLAN
11---------------------------------------------------------------------------------
12 Seq Scan on t  (cost=0.00..441693.00 rows=100000 width=16)
13   SubPlan 1
14     ->  Aggregate  (cost=4.39..4.40 rows=1 width=8)
15           ->  Index Only Scan using uj on u  (cost=0.29..4.38 rows=5 width=0)
16                 Index Cond: (j = t.j)
17 JIT:
18   Functions: 7
19   Options: Inlining false, Optimization false, Expressions true, Deforming true
20(8 rows)

 1Run 1, Statement 1: 00:00:03.617562
 2Run 1, Statement 2: 00:00:03.605765
 3
 4Run 2, Statement 1: 00:00:03.610084
 5Run 2, Statement 2: 00:00:03.682064
 6
 7Run 3, Statement 1: 00:00:03.725952
 8Run 3, Statement 2: 00:00:03.705622
 9
10Run 4, Statement 1: 00:00:03.672669
11Run 4, Statement 2: 00:00:03.644612
12
13Run 5, Statement 1: 00:00:03.645741
14Run 5, Statement 2: 00:00:03.642717

似乎有了整个新功能，将来可以将相关子查询重写成嵌套循环外连接？其他优化器已经这么做了，我们在这里将拥有和Oracle标量子查询缓存相同的功能。

结论

该功能在PG14中开启，除了一些额外的内存消耗，看不出其他任何缺点。相关子查询是一个函数，他的入参是outer查询列的谓词和引用。相关子查询的结果可以缓存和memoized。如上所示，这对您的SQL查询将带来巨大影响。只需升级到PG14即可从中获利。

原文

https://blog.jooq.org/postgresql-14s-enable_memoize-for-improved-performance-of-nested-loop-joins/

数据类型

基于已有的range类型添加对multirange类型的支持（Paul Jungwirth、Alexander Korotkov），允许指定多个、有序、不重复的列表。为每个range类型自动创建关联的multirange类型。

1SELECT datemultirange( daterange('2021-07-01', '2021-07-31'), daterange('2021-09-01', '2021-09-30'), daterange('2021-11-01', '2021-11-30'), daterange('2022-01-01', '2022-01-31'), daterange('2022-03-01', '2022-04-07') );
2datemultirange                                                                                                           |
3-------------------------------------------------------------------------------------------------------------------------+
4{[2021-07-01,2021-07-31),[2021-09-01,2021-09-30),[2021-11-01,2021-11-30),[2022-01-01,2022-01-31),[2022-03-01,2022-04-07)}|

允许扩展和内置数据类型来实现下标(Dmitry Dolgov)
以前下标处理是硬编码到服务器中的，下标只能应用于数组类型。此更改允许使用下标表示法提取或分配有意义的任何类型的值的部分。
允许订阅JSONB(德米特里·多尔戈夫) JSONB下标可以用于提取和分配JSONB文档的部分。
添加对亚美尼亚语、巴斯克语、加泰罗尼亚语、印地语、塞尔维亚语和意第绪语词干的支持
允许tsearch数据文件有无限的行长度(Tom Lane),之前的限制是4K字节。同时删除函数t_readline()。
在数字数据类型中添加对Infinity和-Infinity值的支持(Tom Lane),浮点数据类型已经支持它们。
添加点操作符<<|和|>>表示严格上/下测试(Emre Hasegeli)以前，这些被称为>^ 和 <^，但这种命名与其他几何数据类型不一致。旧的名字仍然可用，但有一天可能会被删除。
添加运算符，用于LSN和数字(字节)值的加减运算符
允许二进制数据传输更宽容的数组和记录OID不匹配(Tom Lane)
为系统编目创建复合阵列类型，用户定义的关系早就有与之关联的复合类型，以及这些复合类型上的数组类型。系统目录现在也可以。此更改还修复了在单用户模式下创建用户定义表将无法创建复合数组类型的不一致性。

函数

PostgreSQL 14 增加了新的 date_bin 函数，可以用于将时间戳按照指定的间隔进行对齐（一种称为“装箱”的技术）。例如：

 1- SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01');
 2date_bin           |
 3-------------------+
 42020-02-11 15:30:00|
 5
 6SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30');
 7date_bin           |
 8-------------------+
 92020-02-11 15:32:30|
10123456789

允许SQL语言函数和过程使用SQL标准函数体 (Peter Eisentraut)，以前只支持字符串文字函数体。使用 SQL 标准语法编写函数或过程时，会立即解析主体并存储为解析树。这允许更好地跟踪函数依赖关系，并且可以具有安全优势。
PostgreSQL 14 支持存储过程的 OUT 参数，以及允许在 GROUP BY 子句中使用 DISTINCT 关键字删除 GROUPING SET 组合中的重复分组
允许一些数组函数在兼容数据类型的混合上操作(Tom Lane)，函数array_append()、array_prepend()、array_cat()、array_position()、array_positions()、array_remove()、array_replace()和width_bucket()现在接受anycompatiblelearray而不是anyarray参数。这使得他们对参数类型的精确匹配条件不那么苛刻。
添加sql标准的trim_array()函数
添加函数bit_count()返回位或字节串中设置的位数
更新的正则表达式substring()语法，新的sql标准语法是SUBSTRING(文本类似模式ESCAPE escapechar)。之前的标准语法是SUBSTRING(text FROM pattern FOR escapechar)，它仍然被PostgreSQL接受。
导致exp()和power()对于负无穷指数返回0 (Tom Lane)，以前，它们经常返回下溢错误。

管理功能

添加系统视图pg_stat_progress_copy
，报告COPY进度

 1-- 第一个session 建立测试数据：
 2appdb1=# create table tbl_test (id int, info text, c_time timestamp);
 3CREATE TABLE
 4appdb1=# insert into tbl_test select generate_series(1,1000000),md5(random()::text),clock_timestamp();
 5INSERT 0 1000000
 6appdb1=# copy tbl_test to '/tmp/tbl_test.csv';
 7COPY 1000000
 8appdb1=#
 9
10-- 第二个session查询：
11postgres=# SELECT * FROM pg_stat_progress_copy ;
12pid | datid | datname | relid | command | type | bytes_processed | bytes_total | tuples_processed | tuples_excluded 
13-----+-------+---------+-------+---------+------+-----------------+-------------+------------------+-----------------
14633 | 16385 | appdb1  | 16386 | COPY TO | FILE |        29013034 |           0 |           435412 |               0
15(1 row)
16
17postgres=#
18
19pid： backend 进程的进程pid
20datid： database的oid
21dataname database的name
22relid 正在运行copy命令的相对应的table的oid
23command 命令名称，要么是copy from ，要么是copy to
24type IO 类型
25bytes_processed 已经处理过的bytes数
26bytes_total copy from语句对应的源文件的大小
27tuple_processed 已经处理过的记录行数
28tuple_excluded 没有被处理的记录数（因为这些记录被排除）

PostgreSQL 14 增加了连接管理相关的几个参数，包括 idle_session_timeout（默认为0，单位是毫秒，用于自动kill掉那些空闲时间超过本参数值且不是在事务中空闲的backend process）以及 client_connection_check_interval （允许 PostgreSQL 在客户端断开后取消长时间运行的查询）。
REINDEX 命令现在支持分区表的子索引，同时 PostgreSQL 14 增加了一个新的使用工具 pg_amcheck ，用于检查数据损坏。
向pg_prepared_语句添加列，以报告通用和自定义计划计数

复制和恢复

允许备用服务器通过pg_rewind被重卷
PostgreSQL 14 改善了 PostgreSQL 从故障恢复状态中启动的速度，同时还支持针对 standby 模式下的 PostgreSQL 实例使用 pg_rewind 命令同步数据。
添加服务器参数log_recovery_conflict_await来报告长时间的恢复冲突等待时间
PostgreSQL 14 增强了逻辑复制的各种性能，包括将正在进行中的事务传输到订阅服务器的能力，而不需要等待事务完成。The ALTER SUBSCRIPTION 使得增加/删除订阅更加简单。
在具有大量共享缓冲区的集群上进行恢复时，对小表的快速截断
增强逻辑复制API，允许流处理正在进行的大型事务
允许逻辑复制将正在进行的长事务流到订阅者
使用逻辑复制时，在命令完成期间向WAL添加缓存失效消息
允许控制是否将逻辑解码消息发送到复制流

逻辑复制的改进

PG14中对逻辑复制进行了几项增强：

正在进行中的事务中支持逻辑复制 。可以将正在进行的事务以流复制( Logical Streaming Replication)形式传输给订阅者，这对订阅大型事务的应用具有显著的性能优势；该功能的作用是无需等到事务commit完成的时候对WAL进行抓取。之前版本PG的逻辑复制是等到事务commit完成的时候对WAL进行抓取。实现本新特性的方法是，对SUBSCRIPTION的定义进行修改。默认值是streaming=off，参考指令如下：
```
1postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remhost1 dbname=postgres user=postgres password=lhr' PUBLICATION pub1 WITH (streaming = on) ;
2CREATE SUBSCRIPTION
3postgres=# SELECT subname, substream FROM pg_subscription;
4WHERE subname = 'sub1' ;
5subname | substream
6---------+-----------
7sub1 | t
8(1 row)
```
有助于减少大型事务的回放延迟，这里详细进行了介绍：
http://amitkapila16.blogspot.com/2021/07/logical-replication-of-in-progress.html
Prepared事务解码
帮助构建多主解决方案的两阶段提交以及帮助减小回放延迟。注意目前订阅方的工作尚未完成，但核心解决方案可以使用它作为输出插件。通过次特性，用户可以构建无冲突复制。详情可参考：
https://www.postgresql.fastware.com/blog/logical-decoding-of-two-phase-commits
对包含DDL的事务进行逻辑解码的性能提升
据观察，有1000个分区的表，对其进行truncate。该事务的解码仅花费1秒，PG14版本前需要4-5分钟。

逻辑复制可以以二进制形式传输数据，（如果稍微不需要那么健壮的话）；之前都是文本传输。使用二进制传输的功能需要在SUBSCRIPTION的定义中进行设置:binary = on；若是SUBSCRIPTION中没有设置binary=on,那就默认是使用文本进行传输；参考指令如下：

1postgres=# CREATE SUBSCRIPTION sub2 CONNECTION 'host=remhost1dbname=postgres user=postgres password=<<PASSWORD>>' PUBLICATION pub2 WITH (binary = on) ;
2CREATE SUBSCRIPTION
3postgres=# SELECT subname, subbinary FROM pg_subscription
4WHERE subname = 'sub2' ;
5subname | subbinary
6---------+-----------
7sub2 | t
8(1 row)

逻辑复制中进行表同步期间允许多个事务，带来的好处：
如果在同步阶段发生错误，将不再需要再次复制整个表，避免了超过CID限制的风险，在整个同步完成之前，不再需要保留WAL
大表的初始化同步阶段花费很长时间，基于这些修改，逻辑复制进行了改进。
通过ADD PUBLICATION和DROP PUBLICATION选项，ALTER SUBSCRIPTION语句现在很容易添加或移除发布
添加了pg_stat_replication_slots系统视图，报告复制槽的活动信息。帮助用户监控spill或stream的活动以及通过特定复制槽解码的总字节数。当希望从本视图中清理掉逻辑复制槽时，请使用pg_stat_reset_replication_slot函数。
sql postgres=> SELECT * FROM pg_stat_replication_slots ; -[ RECORD 1 ]+---------- slot_name | sub1 spill_txns | 3 spill_count | 9 spill_bytes | 416939406 stream_txns | 0 stream_count | 0 stream_bytes | 0 total_txns | 0 total_bytes | 0 stats_reset | slot_name 复制槽的标识符 spill_txns 溢出到disk的事务的数量 spill_count事务被溢出到disk中的次数 spill_bytes解码后的事务数据被溢出到disk中的大小 stream_txns ：Number of in-progress transactions streamed 进行中的流式事务的数量 stream_count正在运行中的事务的次数 stream_bytes：Amount of decoded in-progress transaction data streamed.进行中的被解码的流式事务数据的大小 total_txns 解码后的事务的数量 total_bytes 解码后的事务数据的大小 stats_reset 本统计最后一次被重置的时间

逻辑复制新增了4个WaitEvent：

等待事件	描述
LogicalChangesRead	Waiting for reading from a logical change file
LogicalChangesWrite	Waiting for writing to the logical change file.
LogicalSubxactRead	Waiting for a read from a logical sub-transaction file
LogicalSubxactWrite	Waiting to write to a logical sub-transaction file

索引优化

允许BRIN索引使用布隆过滤器，这使得BRIN索引可以有效地用于堆中没有很好本地化的数据。
允许通过对数据进行预排序来构建一些GiST索引，预排序是自动进行的，允许更快的索引创建和更小的索引。
可以通过预排序数据构建一些GiST索引自动预排序，允许创建更快的索引和更小的索引。仅支持浮点类型。
允许BRIN索引在每个范围内记录多个最小值/最大值，如果每个页面范围中都有一组值的时候非常有用。允许更加有效地处理异常值。可以指定每个页面范围值的个数，要么是单点，要么是一个边界间隔：
```
1CREATE TABLE table_name (a int);
2CREATE INDEX ON table_name USING brin (a int4_min max_multi_ops(values_per_range=16));
```
SP-GiST可以使用INCLUDE列
允许对SP-GiST索引进行更多的仅索引扫描

1postgres=# create table t_test1 (c1 text,c2 text);
2CREATE TABLE
3postgres=# create index idx1_t_test1 on t_test1 using spgist(c1) include(c2);
4CREATE INDEX

REINDEX现在可以处理分区表的所有子表或索引
REINDEX现在可以改变新索引的表空间，通过指定TABLESPACE子句来完成。添加--tablespace选项到reindexdb中控制该行为

SQL特性

PG14引入和增强了一些有用的特性，其中许多将有助于从其他数据库迁移。

CREATE FUNCTION和CREATE PROCEDURE语句现在支持SQL语言
因此函数主体符合SQL标准，可以移植到其他实现。现在可以编写构成不带引号的SQL语句主体，而不是使用PG特定的语法
AS ...
```
1CREATE PROCEDURE insert_val (value1 integer, value2 integer)
2LANGUAGE SQL
3BEGIN ATOMIC
4INSERT INTO tbl1 VALUES (value1);
5INSERT INTO tbl1 VALUES (value2);
6END;
```

存储过程可以有OUT参数 支持这种参数模式将使得从其他数据库迁移变得更加容易

 1CREATE OR REPLACE PROCEDURE pro_out(
 2  p_id in integer,
 3  p_out out integer)
 4AS BEGINpE95Eout:E61EpE95Eid+10END<annotation encoding="application style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;" encoding=""application"></annotation encoding="application>
 5BEGIN
 6  p_out := p_id+10;
 7END; 
 8
 9<span class="katex-html" aria-hidden="true" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="strut" style="height:0.8777699999999999em;vertical-align:-0.19444em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.05017em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">B<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">G<span class="mord mathit" style="margin-right:0.07847em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">I<span class="mord mathit" style="margin-right:0.10903em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">N<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">p<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">o<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">u<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">t<span class="mspace" style="margin-right:0.2777777777777778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mspace" style="margin-right:0.2777777777777778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="strut" style="height:0.8888799999999999em;vertical-align:-0.19444em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">p<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">i<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">d<span class="mspace" style="margin-right:0.2222222222222222em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mspace" style="margin-right:0.2222222222222222em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="strut" style="height:0.8777699999999999em;vertical-align:-0.19444em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mspace" style="margin-right:0.16666666666666666em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.10903em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">N<span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">D
10LANGUAGE plpgsql;
11
12
13
14
15DO
16DECLAREresultintBEGINCALLproE95Eout(100result)RAISENOTICEEND<annotation encoding="application style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;" encoding=""application"></annotation encoding="application>
17DECLARE
18result int;
19BEGIN
20  CALL pro_out(100,result);
21  RAISE NOTICE '%', result;
22END
23<span class="katex-html" aria-hidden="true" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="strut" style="height:1.051892em;vertical-align:-0.25em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">D<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.07153em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">C<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">L<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">A<span class="mord mathit" style="margin-right:0.00773em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">R<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">r<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">e<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">s<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">u<span class="mord mathit" style="margin-right:0.01968em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">l<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">t<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">i<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">n<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">t<span class="mspace" style="margin-right:0.16666666666666666em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.05017em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">B<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">G<span class="mord mathit" style="margin-right:0.07847em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">I<span class="mord mathit" style="margin-right:0.10903em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">N<span class="mord mathit" style="margin-right:0.07153em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">C<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">A<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">L<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">L<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">p<span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">r<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">o<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">o<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">u<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">t<span class="mspace" style="margin-right:0.16666666666666666em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">r<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">e<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">s<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">u<span class="mord mathit" style="margin-right:0.01968em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">l<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">t<span class="mspace" style="margin-right:0.16666666666666666em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mathit" style="margin-right:0.00773em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">R<span class="mord mathit" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">A<span class="mord mathit" style="margin-right:0.07847em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">I<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">S<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.10903em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">N<span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">O<span class="mord mathit" style="margin-right:0.13889em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">T<span class="mord mathit" style="margin-right:0.07847em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">I<span class="mord mathit" style="margin-right:0.07153em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">C<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="vlist" style="height:0.801892em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span style="top:-3.113em;margin-right:0.05em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="pstrut" style="height:2.7em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="sizing reset-size6 size3 mtight" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mtight" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;"><span class="mord mtight" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">′<span class="mord mathit" style="margin-right:0.05764em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">E<span class="mord mathit" style="margin-right:0.10903em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">N<span class="mord mathit" style="margin-right:0.02778em;" style="color: inherit;line-height: inherit;font-size: 0.9em;overflow-wrap: inherit !important;word-break: inherit !important;">D;
24</span class="mord mathit" style="margin-right:0.02778em;"></span class="mord mathit" style="margin-right:0.10903em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mtight"></span class="mord mtight"></span class="sizing reset-size6 size3 mtight"></span class="pstrut" style="height:2.7em;"></span style="top:-3.113em;margin-right:0.05em;"></span class="vlist" style="height:0.801892em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.07153em;"></span class="mord mathit" style="margin-right:0.07847em;"></span class="mord mathit" style="margin-right:0.13889em;"></span class="mord mathit" style="margin-right:0.02778em;"></span class="mord mathit" style="margin-right:0.10903em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.07847em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.00773em;"></span class="mspace" style="margin-right:0.16666666666666666em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.01968em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.02778em;"></span class="mspace" style="margin-right:0.16666666666666666em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.02778em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.07153em;"></span class="mord mathit" style="margin-right:0.10903em;"></span class="mord mathit" style="margin-right:0.07847em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05017em;"></span class="mspace" style="margin-right:0.16666666666666666em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.01968em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.02778em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.00773em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.07153em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.02778em;"></span class="strut" style="height:1.051892em;vertical-align:-0.25em;"></span class="katex-html" aria-hidden="true"></span class="mord mathit" style="margin-right:0.02778em;"></span class="mord mathit" style="margin-right:0.10903em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mspace" style="margin-right:0.16666666666666666em;"></span class="strut" style="height:0.8777699999999999em;vertical-align:-0.19444em;"></span class="mspace" style="margin-right:0.2222222222222222em;"></span class="mspace" style="margin-right:0.2222222222222222em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="strut" style="height:0.8888799999999999em;vertical-align:-0.19444em;"></span class="mspace" style="margin-right:0.2777777777777778em;"></span class="mspace" style="margin-right:0.2777777777777778em;"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.10903em;"></span class="mord mathit" style="margin-right:0.07847em;"></span class="mord mathit"></span class="mord mathit" style="margin-right:0.05764em;"></span class="mord mathit" style="margin-right:0.05017em;"></span class="strut" style="height:0.8777699999999999em;vertical-align:-0.19444em;"></span class="katex-html" aria-hidden="true">

CREATE TRIGGER语法进行了扩展支持OR REPLACE
允许现有的触发器进行有条件的替换，并使迁移更加容易
详细请参考：https://www.postgresql.fastware.com/blog/create-or-replace-trigger
使用postgres_fdw模块，Truncate可以在外部表上执行，参考：https://www.xmmup.com/pg-14zhongdepostgres_fdwzengqianggongneng.html

改进了下标
扩展和内置数据类型可以完成下标。例如，jsonb可以使用下标:

 1-- 之前
 2SELECT jsonb_column->'key' FROM table;
 3UPDATE table
 4SET jsonb_column = jsonb_set(jsonb_column, '{"key"}', '"value"');
 5
 6
 7-- PG14
 8SELECT jsonb_column['key'] FROM table;
 9UPDATE table
10SET jsonb_column['key'] = '"value"';

8) 支持了multirange数据类型
和range数据类型类似，但是允许指定多个、有序、不重叠的range。所有现在的range类型都支持multirange类型
如下所示，在PG14之前，只能指定一个日期范围，而现在可以使用datamultirange函数指定多个日期范围
早期

sql -- 早期 SELECT daterange(CURRENT_DATE, CURRENT_DATE + 1); daterange ¨K105K ¨K137K -- PG14 SELECT datemultirange( daterange(CURRENT_DATE , CURRENT_DATE + 2), daterange(CURRENT_DATE + 5, CURRENT_DATE + 8)); ¨K138K ¨K105K {[2021-07-27,2021-07-29),[2021-08-01,2021-08-04)}

ECPG现在支持DECLARE STATEMENT结构
允许ECPG标识符链接到指定连接。当动态SQL语句使用这个标识符时，通过关联的连接来执行。通过DECALARE…STATEMENT完成：

 1EXEC SQL BEGIN DECLARE SECTION;
 2  char dbname[128];
 3  char *dym_sql = "SELECT current_database()";
 4 EXEC SQL END DECLARE SECTION;
 5
 6int main()
 7{ 
 8EXEC SQL CONNECT TO postgres AS conn1; 
 9EXEC SQL CONNECT TO testdb   AS conn2;
10EXEC SQL AT conn1 DECLARE stmt STATEMENT; 
11EXEC SQL PREPARE stmt FROM :dym_sql; 
12EXEC SQL EXECUTE stmt INTO :dbname;
13printf("%s\n", dbname); 
14EXEC SQL DISCONNECT ALL; 
15return 0;
16}
17

上面的例子展示了用户如何声明一个简单语句：SELECT current_database(),然后定义连接到不同的database。从而，通过DECLARE语句，可以使用一个连接，执行连接上的语句。这对于想在不同连接上执行语句的应用来说非常有用。

数据损坏

PG现在提供一些工具，可以用来检测数据库是否损坏；还有一些小工具帮助用户修复损坏的数据。

1) amcheck模块提供函数允许检查heap页，之前仅能检测B-tree索引页

2) 添加了命令行工具pg_amcheck，简化在表上运行contrib/amcheck操作。有很多选项供选择检测哪个表、执行什么检查。可以并行执行检查

3) 添加了pg_surgery模块，该模块允许更改行可见信息。这对于纠正数据库损坏很有用。但如果使用不当，很容易损坏以前未损坏的数据库，进一步损坏数据库。需要强调的是，必须谨慎使用此工具，并只能由了解自己在做什么的用户使用。

VACUUM增强

PostgreSQL 14 针对 VACUUM 命令进行了许多改进，包括索引的优化。

1) 当可移动索引条目数量微不足道时，可以跳过索引清理，减少了vacuum时间
2) 如果表接近xid或者multixact回卷，vacuum操作更加激进
由vacuum_failsafe_age和vacuum_multixact_failsafe_age参数控制。Autovacuum开始很长时间后，这种机制总会触发以阻止回卷。
3) 使用现有统计信息，可以加快有很多表的database的vacuum
Benchmark显示20000个表，10个autovacuum进程并发执行，可以将性能提高三倍以上。
4) Vacuum可以激进地将新删除的B-tree页添加到空闲空间映射表中，以便重用。
之前版本，vacuum只能将之前已存在的被删除的页添加到空闲空间映射表中。这个改进可以减少B-tree索引新页的空间分配，优化空间大小。
5) Vacuum可以回收位置有的heap line指针使用的空间
避免了某些负载的行指针膨胀，尤其时涉及在同一个表中进行持续范围删除和批量插入的操作
6) 在CREATE INDEX CONCURRENTLY和REINDEX CONCURRENTLY操作期间，vacuum可以积极地删除死记录。
7) Autovacuum 现在可以分析分区表，并且能够将数据行的统计信息关联到父表。
8) 通过调整 maintenance_io_concurrency
参数可以改善 ANALYZE 命令的性能。

1postgres=# show maintenance_io_concurrency;
2 maintenance_io_concurrency
3----------------------------
4 10
5(1 row)

扩展统计信息

PG14一个增强功能是扩展统计方面。帮助我们对使用表达式的各种查询获取更好的统计信息，帮助产生更好的查询计划。

扩展统计现在在表达式中使用：
sql<br >CREATE TABLE table_name (a int);<br >CREATE STATISTICS statistics_name ON mod(a,10), mod(a,20) FROM table_name;<br >ANALYZE table_name;<br >

采集的统计信息对带有WHERE或GROUP BY子句中，该子句使用表达式，进行评估非常有用：

1explain SELECT * FROM table_name WHERE mod(a,10) = 0 AND mod(a,20) = 0;
2explain SELECT 1 FROM table_name GROUP BY mod(a,10), mod(a,20);

查询中使用表达式时，可以获得更好的查询计划。

2) 增加了可用于OR子句评估的扩展统计信息的位置数量。

性能改进

1) 多CPU和高会话计数的系统上计算MVCC可见性快照的速度得到改进：当有许多空闲会话时，这也可以提高性能。对于只读查询的大量连接，大约有2倍的增益。
2) 当只有少数分区受到影响时，分区表上的更新/删除性能得到改进：允许分区表上执行删除/更新时使用execution-time分区修剪；对于继承的UPDATE/DELETE，不是为每个目标关系生成单独的子计划，而是生成一个与SELECT计划完成相同的单个子计划，然后在其上添加ModifyTable。
3) 引用多个外部表的查询，现在可以并行执行外部表扫描：目前唯一可以同时运行的阶段类型是ForeignScan，他是Append的直接字节点；一个ForeignScan访问不同远程服务器上数据时，可以并行执行ForeignScan，重叠操作改进性能；如果设置了async_enable，postgres-fdw支持这种类型的扫描
5) libpq中改进了pipeline模式：允许发送多个查询，并仅当发送了指定的同步消息时等待完成；它增加了客户端应用程序的复杂性，并且需要格外小心以防止客户端/服务器死锁，但管道模式可以提供相当大的性能改进，以换取内存使用量的增加，从而使状态保持更长时间；管道模式在服务器距离较远时最有用，即当网络延迟ping 时间较长时，以及许多小操作正在快速连续执行时。引入管道查询可以显著提升高延迟连接或具有许多小量写（INSERT/UPDATE/DELETE）操作的工作负载性能。由于这是客户端功能，因此只要使用版本14的客户端或使用通过版本14的libpq构建的客户端驱动程序，就可以对任何现代 PostgreSQL数据库使用管道模式。
6) Executor方法添加到了nextloop join的inner表缓冲结果中：如果在inner检查一小部分行时很有用，由enable_memorize控制；当查找的不同值较少且每个值的查找次数较大时，使用带有结果缓存的参数化嵌套循环的好处会增加
7) FDW API 和 postgres_fdw 已扩展为允许批量插入外部表：如果FDW支持批量，并且请求了批量，那么累积行并以批量形式插入，否则每次插入一行；由于到外部服务器的每次往返都有很高的延迟，因此批处理通常比插入单个行更有效
8) 改进了带有表达式IN（const-1,const-2,等）子句的查询性能：通过hash表查询替换当前顺序查询达到改进性能的目的
9) 改进了在具有大量共享缓冲区的集群上恢复期间对小表执行 CREATE TABLE 操作的截断、删除或中止性能。在许多情况下，当几个小表（用 1,000 个关系测试）被截断，并且服务器配置有大量共享缓冲区（大于等于 100 GB）时，这将性能提高了 100 倍以上
10) 改进了恢复、备机回放、大量更新的vacuum的性能：性能提升来自于压缩页面的算法优化，我们需要在大更新后使用它
11) 改进了并行顺序扫描的 I/O 性能：以组的形式将块分配给并发进程，从而提升性能。
详情参考：https://www.postgresql.org/docs/release/14.0/

PG14 监控和日志功能提升

PostgreSQL 14与2021年9月发布，其中包括很多性能改进和功能增强，包括监控方面的功能提升。下面是一些关键内容。

查询标识符

查询标识符用于标识查询，可以在各个扩展之中交叉使用。PG14之前，扩展需要使用一个算法计算query_id。通常情况下使用同一个算法计算，但任何扩展都可以使用自己的算法。现在PG14提供在内核中计算query_id。现主PG的监控工具比如pg_stat_activity、explain、pg_stat_statments使用这个query_id，不再使用算法进行计算。指定log_line_prefix后，旧会在csvlog中看到query_id。在用户角度，有2个好处：

1) 所有工具/扩展都使用内核中计算的query_id，为工具/扩展交叉使用提供便利。之前的版本需要使用同样的算法计算才能达到这样的功能。

2) 扩展/工具使用这个query_id，无需再重复计算，这是一个性能提升。

PG 14引入了一个新的GUC控制参数compute_query_id来启用这个特性，默认是开启状态。可以在postgresql.conf中设置也可以使用SET命令。

pg_stat_activity

SET compute_query_id=off;

1SELECT datname, query, query_id FROM pg_stat_activity;
2 datname  |                                 query                                 | query_id
3----------+-----------------------------------------------------------------------+----------
4 postgres | select datname, query, query_id from pg_stat_activity;                |         
5 postgres | UPDATE pgbench_branches SET bbalance = bbalance + 2361 WHERE bid = 1; |

SET compute_query_id=on;

1SELECT datname, query, query_id FROM pg_stat_activity;
2 datname  |                                 query                                 |      query_id       
3----------+-----------------------------------------------------------------------+---------------------
4 postgres | select datname, query, query_id from pg_stat_activity;                |  846165942585941982
5 postgres | UPDATE pgbench_tellers SET tbalance = tbalance + 3001 WHERE tid = 44; | 3354982309855590749

Log

之前版本中，内核中没有机制计算query_id。在log file中query_id非常有用。需要配置log_line_prefix启用这项功能。query_id中加了“%Q”：

1log_line_prefix = 'query_id = [%Q] -> '
2query_id = [0] -> LOG:  statement: CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
3query_id = [-6788509697256188685] -> ERROR:  return type mismatch in function declared to return record
4query_id = [-6788509697256188685] -> DETAIL:  Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING.
5query_id = [-6788509697256188685] -> CONTEXT:  SQL function "ptestx"
6query_id = [-6788509697256188685] -> STATEMENT:  CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;

Explain

如果compute_query_id为true,则EXPLAIN VERBOSE会显示query_id。

SET compute_query_id=off;

1EXPLAIN VERBOSE SELECT * FROM foo;
2                          QUERY PLAN                          
3--------------------------------------------------------------
4 Seq Scan on public.foo  (cost=0.00..15.01 rows=1001 width=4)
5   Output: a
6(2 rows)

SET compute_query_id=on;

1EXPLAIN VERBOSE SELECT * FROM foo;
2                          QUERY PLAN                          
3--------------------------------------------------------------
4 Seq Scan on public.foo  (cost=0.00..15.01 rows=1001 width=4)
5   Output: a
6 Query Identifier: 3480779799680626233
7(3 rows)

自动vacuum和自动analyze 日志改进

PG14改进了auto-vacuum和auto-analyze的logging功能。在日志中看下IO时间，显示读写花费了多少时间：

 1automatic vacuum of table "postgres.pg_catalog.pg_depend": index scans: 1
 2pages: 0 removed, 67 remain, 0 skipped due to pins, 0 skipped frozen
 3tuples: 89 removed, 8873 remain, 0 are dead but not yet removable, oldest xmin: 210871
 4index scan needed: 2 pages from table (2.99% of total) had 341 dead item identifiers removed
 5index "pg_depend_depender_index": pages: 39 in total, 0 newly deleted, 0 currently deleted, 0 reusable
 6index "pg_depend_reference_index": pages: 41 in total, 0 newly deleted, 0 currently deleted, 0 reusable
 7
 8I/O timings: read: 44.254 ms, write: 0.531 ms
 9
10avg read rate: 13.191 MB/s, avg write rate: 8.794 MB/s
11buffer usage: 167 hits, 126 misses, 84 dirtied
12WAL usage: 85 records, 15 full page images, 78064 bytes
13system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.07 s

仅track_io_timing启动时，才能显示这样的日志。

连接日志

log_connections/log_disconnections开启时，会记录connection/disconnection到日志中。因此会记录真实用户名。若使用其他认证插件以及pg_ident.conf中映射，定位真实用户名就会很困难。PG14之前，仅能看到映射用户。

pg_ident.conf

1# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
2pg              vagrant                 postgres

pg_hba.conf

1# TYPE  DATABASE     USER        ADDRESS         METHOD
2# "local" is for Unix domain socket connections only
3local   all         all                         peer map=pg

PG 14之前

1LOG:  database system was shut down at 2021-11-19 11:24:30 UTC
2LOG:  database system is ready to accept connections
3LOG:  connection received: host=[local]
4LOG:  connection authorized: user=postgres database=postgres application_name=psql

PG14

1LOG:  database system is ready to accept connections
2LOG:  connection received: host=[local]
3LOG:  connection authenticated: identity="vagrant" method=peer (/usr/local/pgsql.14/bin/data/pg_hba.conf:89)
4LOG:  connection authorized: user=postgres database=postgres application_name=psql

窗口函数incremental sort

可以看到，因为在PARTITION BY id ORDER BY info) AS first_id中对id进行了排序，所以在PARTITION BY id ORDER BY info DESC便不再重复排序，而是使用的Incremental Sort

 1lhrdb=>explain (COSTS OFF)
 2lhrdb-# select *
 3lhrdb-#   from (select id,
 4lhrdb(#                info,
 5lhrdb(#                row_number() OVER(PARTITION BY id ORDER BY info) AS first_id,
 6lhrdb(#                row_number() OVER(PARTITION BY id ORDER BY info DESC) AS last_id
 7lhrdb(#           from t1) t
 8lhrdb-#  where first_id = 1
 9lhrdb-#     OR last_id = 1;
10                       QUERY PLAN
11---------------------------------------------------------
12 Subquery Scan on t
13   Filter: ((t.first_id = 1) OR (t.last_id = 1))
14   ->  WindowAgg
15         ->  Incremental Sort
16               Sort Key: t1.id, t1.info
17               Presorted Key: t1.id
18               ->  WindowAgg
19                     ->  Sort
20                           Sort Key: t1.id, t1.info DESC
21                           ->  Seq Scan on t1
22(10 rows)

并行查询多项改进

1)包括更好的并行顺序扫描性能、PL/pgSQL在使用RETURN QUERY命令时执行并行查询的能力、以及启用REFRESH MATERIALIZED VIEW执行并行查询。此外，新增的附加缓存让嵌套查询性能得到提升；

2)在postgres_fdw（与其他 PostgreSQL 数据库接口的外部数据包装器）中实现了并行查询特性，postgres_fdw支持对foreign table以并行的方式进行scan，该特性默认是关闭的，需要在定义create server时明确指定(async_capable ‘on’)。同时也可以使用IMPORT FOREIGN SCHEMA指令在外部表上批量插入数据并导入表分区。

开发功能

函数或存储过程支持SQL语言

CREATE FUNCTION和CREATE PROCEDURE语句现在支持SQL语言，现在可以编写构成不带引号的SQL语句主体，而不是使用PG特定的语法。示例：

1AS $$...$$
2CREATE PROCEDURE insert_val (value1 integer, value2 integer)
3 LANGUAGE SQL
4BEGIN ATOMIC 
5 INSERT INTO tbl1 VALUES (value1);
6 INSERT INTO tbl1 VALUES (value2);
7END;

group by distinct子句分组去重

我们通过group by子句进行分组时，经常会出现数据重复的情况。例如GROUP BY CUBE (a,b), CUBE (b,c)便可能出现数据重复的情况。PG14中支持group by distinct的语法，可以用来进行数据去重。

示例：

 1lhrdb=>select a, b, c
 2from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
 3group by  rollup(a, b), rollup(a, c)
 4order by a, b, c;
 5 a | b | c
 6---+---+---
 7 1 | 2 | 3
 8 1 | 2 |
 9 1 | 2 |
10 1 |   | 3
11 1 |   | 3
12 1 |   |
13 1 |   |
14 1 |   |
15 4 |   | 6
16 4 |   | 6
17 4 |   | 6
18 4 |   |
19 4 |   |
20 4 |   |
21 4 |   |
22 4 |   |
23 7 | 8 | 9
24 7 | 8 |
25 7 | 8 |
26 7 |   | 9
27 7 |   | 9
28 7 |   |
29 7 |   |
30 7 |   |
31   |   |
32(25 rows)
33
34
35-- group by distinct：
36lhrdb=>select a, b, c
37from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
38group by distinct rollup(a, b), rollup(a, c)
39order by a, b, c;
40 a | b | c
41---+---+---
42 1 | 2 | 3
43 1 | 2 |
44 1 |   | 3
45 1 |   |
46 4 |   | 6
47 4 |   | 6
48 4 |   |
49 4 |   |
50 7 | 8 | 9
51 7 | 8 |
52 7 |   | 9
53 7 |   |
54   |   |
55(13 rows)
56
57
58-- 需要注意并不等价于先distinct再group by：
59lhrdb=>select distinct a, b, c
60from (values (1, 2, 3), (4, null, 6), (7, 8, 9)) as t (a, b, c)
61group by rollup(a, b), rollup(a, c)
62order by a, b, c;
63 a | b | c
64---+---+---
65 1 | 2 | 3
66 1 | 2 |
67 1 |   | 3
68 1 |   |
69 4 |   | 6
70 4 |   |
71 7 | 8 | 9
72 7 | 8 |
73 7 |   | 9
74 7 |   |
75   |   |
76(11 rows)

新数据类型multirange

引入了”multirange“数据类型来支持非连续范围的数据。multirange是非重叠范围的有序列表，它让开发人员通过编写更简单的查询来处理复杂的范围序列。PostgreSQL原生的范围类型（dates, times, numbers）支持multirange，其他数据类型被扩展后也可以使用multirange。

JSON数据下标查询

可以使用下标访问JSON数据。这使PostgreSQL与从JSON数据中检索信息时的通用语法保持一致。PostgreSQL 14中添加的下标框架一般可以扩展到其他嵌套数据结构，同时也适用于本次发布的hstore数据类型。

存储过程事务控制

存储过程允许在代码块中进行事务控制，现在可以使用OUT参数返回数据。

扩展date_bin函数

可以将时间戳按照任意间隔进行分组或对齐。

扩展SQL子句

添加了符合SQL的SEARCH和CYCLE子句，以帮助递归公用表表达式的排序和循环检测。

安全提升

1.预定义角色权限管理

PostgreSQL 14可以方便地使用pg_read_all_data和pg_write_all_data预定义角色为用户分配表、视图和模式的只读和只写权限。

可以方便地使用pg_read_all_data和pg_write_all_data预定义角色为用户分配表、视图和模式的只读和只写权限。

 1lhrdb=# select * from pg_roles;
 2          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
 3---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------
 4 pg_database_owner         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 6171
 5 pg_read_all_data          | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 6181
 6 pg_write_all_data         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 6182
 7 pg_monitor                | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3373
 8 pg_read_all_settings      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3374
 9 pg_read_all_stats         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3375
10 pg_stat_scan_tables       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 3377
11 pg_read_server_files      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4569
12 pg_write_server_files     | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4570
13 pg_execute_server_program | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4571
14 pg_signal_backend         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 4200
15 postgres                  | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |   10
16(12 rows)

在pg14之前，我们创建一个只读的用户还是比较麻烦的，例如我们直接以下面的方式授权：

1grant select ON ALL tables in schema public to r2;

这看起来没什么问题，但是如果在授权之后创建了新的对象，那么该只读用户是无法查看的。

示例：
我们可以使用pg_read_all_data 角色直接授权：

 1lhrdb=>create user u_readonly;
 2CREATE ROLE
 3lhrdb=>grant pg_read_all_data to u_readonly;
 4GRANT ROLE
 5
 6lhrdb=>\c - u_readonly
 7You are now connected to database "lhrdb" as user "u_readonly".
 8u_readonly@lhrdb=>\d
 9                List of relations
10 Schema |          Name           | Type  | Owner
11--------+-------------------------+-------+-------
12 public | pg_stat_statements      | view  | lhrdb
13 public | pg_stat_statements_info | view  | lhrdb
14 public | t1                      | table | lhrdb
15(3 rows)
16
17u_readonly@lhrdb=>select * from t1;
18 id | info
19----+------
20  1 | lhrdb
21(1 row)
22即使我们新建了表，也不会有影响：
23lhrdb=>create table t2(id int,info text);
24CREATE TABLE
25
26u_readonly@lhrdb=>select * from t2;
27 id | info
28----+------
29(0 rows)

如果希望给数据库创建一个写账号, 可以写所有数据, 可用角色: pg_write_all_data。

2.修改密码管理默认设置

符合标准的SCRAM-SHA-256密码管理和身份验证系统做为新实例的默认设置。

PostgreSQL V14中更好的SQL函数

SQL 函数作为一种方便的快捷方式，一直为人所知和受到重视。PostgreSQL v14 引入了一种新的、更好的编写 SQL 函数的方法。
SQL 函数的示例
让我们使用“经典”语法创建一个简单的 SQL 函数示例，以便我们有一些演示材料：

 1CREATE EXTENSION unaccent;
 2
 3CREATE FUNCTION mangle(t text) RETURNS text
 4   LANGUAGE sql
 5   AS 'SELECT lower(unaccent(t))';
 6
 7
 8
 9lhrdb=# SELECT mangle('Schon dumm');
10   mangle
11------------
12 schon dumm
13(1 row)

您可以像使用其他数据库函数一样使用新函数：

为什么选择 SQL 函数？

你可能会问 SQL 函数有什么好处。毕竟，数据库函数的主要目的是能够在数据库中运行过程代码，这是 SQL 无法做到的。但是 SQL 函数有它们的用途：
1.不同 SQL 语句中频繁使用的表达式的代码重用；
2.通过将部分代码分解为具有有意义名称的函数来使 SQL 语句更具可读性；
3.出于语法原因需要函数，例如CREATE AGGREGATE或CREATE OPERATOR。

此外，可以内联简单的 SQL 函数，即优化器可以在查询计划时将函数调用替换为函数定义。这可以使 SQL 函数异常高效：它消除了实际函数调用的开销。因为大部分函数是优化器的黑匣子，用函数的定义替换函数通常会给你更好的估计。

如果我们EXPLAIN (VERBOSE)在示例函数上使用，我们可以看到函数内联：

1lhrdb=# EXPLAIN (VERBOSE, COSTS OFF) SELECT mangle('Schon dumm');
2                  QUERY PLAN
3-----------------------------------------------
4 Result
5   Output: lower(unaccent('Schon dumm'::text))
6 Query Identifier: 8938610809663877923
7(3 rows)

PostgreSQL函数的缺点

PostgreSQL 函数很棒。一个好的方面是您不限于单一的编程语言。PostgreSQL 支持用 SQL、C、PL/pgSQL（Oracle 的 PL/SQL 的克隆）、Perl、Python 和 Tcl 编写的函数，开箱即用。
但这还不是全部：在 PostgreSQL 中，您可以编写一个插件，允许您在数据库中使用您选择的任何语言。为了实现这种灵活性，PostgreSQL 函数的函数体只是一个字符串常量，当 PostgreSQL 执行函数时，过程语言的调用处理程序会解释该字符串常量。这有一些不良副作用：缺乏依赖跟踪。

PostgreSQL跟踪pg_depend和pg_shdepend目录表中数据库对象之间的依赖关系。这样，数据库就知道对象之间的关系：它要么阻止您删除其他对象所依赖的对象（如具有外键引用的表），要么自动删除依赖对象（如删除被删除表上的所有索引）。

由于函数体只是 PostgreSQL 无法解释的字符串常量，因此它不会跟踪函数和函数中使用的对象之间的依赖关系。过程语言可以提供一个验证器来检查函数体的语法正确性（如果check_function_bodies = on）。验证器还可以测试函数中引用的对象是否存在，但它不能阻止您以后删除函数使用的对象。

让我们用例子来证明：
DROP EXTENSION unaccent;
SELECT mangle('boom');
ERROR: function unaccent(text) does not exist
LINE 1: SELECT lower(unaccent(t))
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT lower(unaccent(t))
CONTEXT: SQL function "mangle" during inlining

我们将通过再次创建扩展来解决问题。但是，最好在DROP EXTENSION不使用CASCADE选项的情况下运行时收到错误消息。
search_path作为安全问题
由于 PostgreSQL 在查询执行时解析函数体，它使用当前设置search_path来解析对不使用模式名称限定的数据库对象的所有引用。这不仅限于表和视图，还扩展到函数和运算符。我们可以使用示例函数来演示这个问题：
SET search_path = pg_catalog;
SELECT public.mangle('boom');
ERROR: function unaccent(text) does not exist
LINE 1: SELECT lower(unaccent(t))
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
QUERY: SELECT lower(unaccent(t))
CONTEXT: SQL function "mangle" during inlining

在我们的示例中，我们可以通过在函数调用中使用public.unaccent()来避免这种烦恼。但它可能比这更糟，特别是对于SECURITY DEFINER函数。由于对每个函数和运算符进行模式限定很麻烦，推荐的解决方案是search_path在函数上强制指定模式名：
1 ALTER FUNCTION mangle(text) SET search_path = public;

请注意，search_path上的模式应该只允许用户使用CREATE特权，因此这对于 v15 之前的版本不是一个好主意！设置search_path的一个令人不快的缺点是它会阻止 SQL 函数的内联。

PostgreSQL v14 中的新 SQL 函数语法

从 PostgreSQL v14 开始，SQL 函数和过程的主体不再是字符串常量。您现在可以对函数体使用以下形式之一：
CREATE FUNCTION function_name(…) RETURNS …
RETURN expression;

CREATE FUNCTION function_name(…) RETURNS …
BEGIN ATOMIC
statement;
…
END;

第一种形式要求函数体是一个表达式。因此，如果要执行查询，则必须将其包装在括号中（将其转换为子查询，这是一个有效的表达式）。例如：
CREATE FUNCTION get_data(v_id bigint) RETURNS text
RETURN (SELECT value FROM data WHERE is = v_id);

第二种形式允许您编写具有多个 SQL 语句的函数。与过去使用多语句 SQL 函数一样，函数的结果将是最终 SQL 语句的结果。您可以使用新语法的第二种形式来创建 SQL 过程。第一种形式显然不适合过程，因为过程没有返回值。

我们可以轻松地重写示例函数以使用新语法：
CREATE OR REPLACE FUNCTION mangle(t text) RETURNS text
RETURN lower(unaccent(t));

请注意，这些新的 SQL 函数可以像旧的函数一样内联到 SQL 语句中！
新 SQL 函数语法的优点
主要区别在于：新式SQL函数和过程在函数定义时解析，并以解析后的形式存储在系统目录表pg_proc的prosqlbody列中。结果，上面提到的两个缺点就消失了：

1.使用新型 SQL 函数进行依赖跟踪
因为函数体以解析的形式提供，所以 PostgreSQL 可以跟踪依赖关系。让我们用重新定义的示例函数来试试：
DROP EXTENSION unaccent;
ERROR: cannot drop extension unaccent because other objects depend on it
DETAIL: function mangle(text) depends on function unaccent(text)
HINT: Use DROP … CASCADE to drop the dependent objects too.

2.用新型 SQL 函数修复search_path
search_path仅在解析 SQL 时才相关。由于现在在CREATE FUNCTION运行时会发生这种情况，因此我们不必担心函数执行时该参数的当前设置：
SET search_path = pg_catalog;
SELECT public.mangle('Schön besser');
mangle
══════════════
schon besser
(1 row)
交互式客户端的问题
这不仅会混淆像HeidiSQL(从来没有学过美元引用)这样的常见问题，而且对于任何将分号识别为SQL语句之间分隔符的客户机来说都是一个问题。甚至旧版本的psql也有这样的语法问题:

您可能会注意到用于定义 SQL 函数的多语句包含用于终止 SQL 语句的分号。这不仅会混淆像HeidiSQL(从来没有学过美元引用)这样的常见问题，而且对于任何将分号识别为SQL语句之间分隔符的客户机来说都是一个问题。甚至旧版本的psql也有这样的语法问题：
psql (13.7, server 15beta2)
WARNING: psql major version 13, server major version 15.
Some psql features might not work.
Type "help" for help.

test=> CREATE FUNCTION tryme() RETURNS integer
BEGIN ATOMIC
SELECT 42;
END;
ERROR: syntax error at end of input
LINE 3: SELECT 42;
^
WARNING: there is no transaction in progress
COMMIT

psql认为“ SELECT 42”之后的分号终止CREATE FUNCTION语句。被截断的语句会导致错误。最后的END被视为它自己的语句，它是COMMIT同义词并导致警告。

在 v14 及更高版本中，psql正确处理此类语句。pgAdmin 4 学习了 6.3 版的新语法。但我确信有很多客户还没有收到消息。

结论

PostgreSQL v14 引入新的 SQL 函数语法在可用性和安全性方面具有很大的优势。获取支持新语法的客户端并开始将其用于您的SQL 函数。您应该考虑重写现有函数以利用这些优势。

PG14 IO向量化

PostgreSQL14新增了一个特性，对IO进行向量化。提供pg_preadv和pg_pwritev函数，以提供同步向量化文件IO接口。这些映射到preadv和pwritev函数，并且为没有这个函数的系统提供回退实现。还提供了一个封装函数pg_pwritev_with_retry以对short write进行自动重试。

并在上面的基础上使用向量化IO填充新的WAL段文件。以往通过调用write函数以页为单位多次向WAL段文件中写入0。向量化后调用更少次数的pgwritev。真实的次数依赖于操作系统的IOV_MAX。目前PG_IOV_MAX上限是32。也就是每次典型系统调用写入256KB。后续经实践后还会继续调优。

关于readv、writev、preadv、pwritev参考https://linux.die.net/man/2/preadv

参考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=13a021f3e8c99915b3cc0cb2021a948d9c71ff32

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ce6a71fa5300cf00adf32c9daee302c523609709

参考

https://blog.csdn.net/qq_51702676/article/details/123053747

https://www.postgresql.org/docs/14/release-14.html

https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14

https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzU1OTgxMjA4OA==&action=getalbum&album_id=2141739597477773312&subscene=159&subscene=190

https://www.percona.com/blog/postgresql-14-database-monitoring-and-logging-enhancements/

https://www.cybertec-postgresql.com/en/better-sql-functions-in-postgresql-v14/

https://www.cybertec-postgresql.com/en/index-bloat-reduced-in-postgresql-v14

oracle索引 toast 索引事务管理 postgresql

文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。