作者

digoal

日期

2021-05-13

标签

PostgreSQL , 14

---

背景

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

包含PG 14 的 160余项新功能或增强特性的详细讲解.

由于PG 14还没有正式release, 可能还有变数, 请参考git, 有些特性可能会出现revert的情况:
- https://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=commit&s=revert

i. 重要特性

PG 14 特性总览 - 直播回放
PostgreSQL 14 新特性场景与价值-性能篇
PostgreSQL 14 新特性场景与价值-数据类型与SQL篇
PostgreSQL 14 新特性场景与价值-管理篇
PostgreSQL 14 新特性场景与价值-流与备份恢复篇
PostgreSQL 14 新特性场景与价值-安全篇

一、性能

1、大量连接高并发优化(无论active和idle), 上万连接无性能损耗.
- 场景: SaaS场景, 微服务架构下的中心库场景. 客户端多、在线用户多、数据库并发连接非常多.
- 价值: 上万连接无性能损耗. 比连接池网络少1跳性能更好、比连接池支持全部功能, 例如连接池会话模式无法支持绑定变量.

2、索引增强
缓解高频更新负载下的btree索引膨胀,
- 场景: 数据频繁更新. 游戏、交易、共享出行、IoT
- 价值: 减少膨胀, 降低存储、内存使用率, 提高效率

支持sort接口, 大幅提升Create GiST和SP-GiST索引的速度,
- 场景: 地理信息、物联网、共享出行等使用了地理信息、JSON、range等数据类型的业务
- 价值: GiST和SP-GiST索引创建速度提升一个数量级

支持SP-GiST覆盖索引功能满足任意维度聚集存储,
- 场景: 地理信息、物联网、共享出行等使用了地理信息、JSON、range等数据类型的业务
- 价值: 将value集成到索引叶子结点, 减少block扫描, 提高基于空间、JSON、range的范围搜索的响应速度
- DEMO: - create index idx_tbl_1 on tbl using spgist (gis) include col1,col2; -- 叶子结点加入col1,col2内容 - select col1,col2,gis from tbl where gis xx; -- VM clearly heap page 可以不需要回表, 提高性能

BRIN支持布隆过滤 和 multi range
- 场景: IoT,时序类场景
- 价值: 提高BRIN索引的过滤精度, 减少返回的block数. 通过布隆过滤器支持任意字段组合条件过滤

3、并行计算增强
并行顺序扫描支持chunk 大IO利用prefetch能力大幅提升顺序IO扫描吞吐性能, 解决小IO无法打满块设备吞吐指标的问题.
- 场景: 数据分析类业务
- 价值: 提高大范围数据扫描的IO吞吐, 逼近块设备IO吞吐极限

PL/pgSQL RETURN QUERY支持并行计算,
- 场景: 数据分析类业务
- 价值: 在plpgsql函数内使用return query返回结果时支持query的并行计算来提升性能
- DEMO: - create or replace function xx.... return query select xx from xx ... -- 这里到select xx query可以支持并行计算

刷新物化事务支持并行计算
- 场景: 数据分析类业务、预计算场景
- 价值: 物化视图的刷新支持并行计算, 大幅度提高刷新速度
- DEMO: - REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name 支持并行计算

4、内置sharding功能接口 postgres_fdw 持续增强
支持外部表异步并行计算,
- 场景: sharding 分库分表. 数据分析场景, 多个实例或sharding分片的数据需要并行分析的场景
- 价值: 提高多个实例并行计算的效率
- DEMO: - create foreign table ft1 AT Server1; - ... - create foreign table ftn AT ServerN; - create partition table tbl partition by .... - alter table ft1 attach to tbl ... - ... - alter table ftn attach to tbl ... - select count(*),avg(x),max(x)... from tbl group by xxxx -- 支持N个Server同时计算.

bulk insert,
- 场景: sharding 分库分表
- 价值: 提高数据写入速度

远程分区表的子分区可以import foreign schema生成外部表,
- 场景: sharding 分库分表
- 价值: 简化一键生成shard的步骤, 同时支持按分区生成shard
- DEMO: - IMPORT FOREIGN SCHEMA remote_schema - [ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ] - table_name 支持子分区.

支持truncate外部表,
- 场景: sharding 分库分表
- 价值: 支持更丰富的语法, truncate清理远程不产生redo, 更快
- DEMO: - truncate 外部表;

支持会话级持久化foreign server连接
- 场景: sharding 分库分表
- 价值: 提高sharding场景的OLTP性能 (以往的版本每一个远程事务都需要重新创建一次远程库连接, 无法支持oltp高频小事务)

5、分区表性能趋近完美
分区裁剪能力提升减少子分区subplan和重复的cached plans,
- 场景: 大数据量, 使用了分区表, 分区表高频操作, OLTP类业务场景
- 价值: 减少内存使用, 同时大幅提升涉及少量分区的SQL性能

增减分区时使用alter table detach|attach PARTITION concurrently模式完全避免锁冲突,
- 场景: 核心在线业务
- 价值: 绑定和解绑分区支持无锁操作, 消除加减分区对在线业务的影响.
- DEMO: - ALTER TABLE [ IF EXISTS ] name - DETACH PARTITION partition_name [ CONCURRENTLY | FINALIZE ]

6、窗口函数性能提升, 支持增量sort, 提升带窗口查询的场景的排序性能
- 场景: 分析场景
- 价值: 提高窗口查询排序性能
- DEMO: +-- Test incremental sorting +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT depname, + empno, + salary, + enroll_date, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date) AS first_emp, + row_number() OVER (PARTITION BY depname ORDER BY enroll_date DESC) AS last_emp + FROM empsalary) emp +WHERE first_emp = 1 OR last_emp = 1; + QUERY PLAN +----------------------------------------------------------------------------------- + Subquery Scan on emp + Filter: ((emp.first_emp = 1) OR (emp.last_emp = 1)) + -> WindowAgg + -> Incremental Sort + Sort Key: empsalary.depname, empsalary.enroll_date + Presorted Key: empsalary.depname + -> WindowAgg + -> Sort + Sort Key: empsalary.depname, empsalary.enroll_date DESC + -> Seq Scan on empsalary +(10 rows)

7、自定义扩展统计信息增强, 支持多个表达式为组合的扩展柱状图信息收集, 提升以表达式为组合过滤条件的选则性评估精度
- 场景: SaaS业务, 带有复杂条件SQL的业务, 很多表达式的过滤条件
- 价值: 解决很多表达式作为过滤组合条件的SQL的数据选择性评估精度, 提高SQL优化器的执行计划生成效果
- DEMO: ``` CREATE STATISTICS [ IF NOT EXISTS ] statistics_name ON ( expression ) FROM table_name

CREATE STATISTICS [ IF NOT EXISTS ] statistics_name [ ( statistics_kind [, ... ] ) ] ON { column_name | ( expression ) }, { column_name | ( expression ) } [, ...] FROM table_name

URL: https://www.postgresql.org/docs/14/sql-createstatistics.html ```

8、支持 multiple extended statistics , 增强or, and多条件的选择性评估精度
- 场景: SaaS业务, 带有复杂条件SQL的业务
- 价值: 解决很多and , or的where过滤组合条件的SQL的数据选择性评估精度, 提高SQL优化器的执行计划生成效果

9、大表 search IN ( consts ) 优化, 支持 linear search TO hash table probe (当 in里面的consts 个数>= 9个时)
- 场景: SaaS业务, 电商, 社交等业务, IN的输入条件很多, 导致in过滤慢
- 价值: 提高IN语句的处理性能, 在条件多、被过滤掉的数据量大的SQL中性能提升非常明显
- DEMO: select x from tbl where id in (1,2,3,4,5,6,7,8,9);

10、TOAST 支持 lz4 压缩算法
- 场景: 通用业务
- 价值: 提高压缩性能
- DEMO: 2 -- test creating table with compression method 3 CREATE TABLE cmdata(f1 text COMPRESSION pglz); 4 CREATE INDEX idx ON cmdata(f1); 5 INSERT INTO cmdata VALUES(repeat('1234567890', 1000)); 6 \d+ cmdata 7 Table "public.cmdata" 8 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 9 --------+------+-----------+----------+---------+----------+-------------+--------------+------------- 10 f1 | text | | | | extended | pglz | | 11 Indexes: 12 "idx" btree (f1) 13 14 CREATE TABLE cmdata1(f1 TEXT COMPRESSION lz4); 15 INSERT INTO cmdata1 VALUES(repeat('1234567890', 1004)); 16 \d+ cmdata1 17 Table "public.cmdata1" 18 Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description 19 --------+------+-----------+----------+---------+----------+-------------+--------------+------------- 20 f1 | text | | | | extended | lz4 | | 21

11、libpq驱动 支持pipeline 模式, SQL请求支持异步化通信, 大幅度提升性能, 降低RT
- 场景: 时序、IoT类、日志appendonly类业务. 跨网段、跨公网的业务与数据库网络访问延时较高的场景, 同时有较大的写入吞吐需求
- 价值: 提高网络环境较差的数据写入吞吐, 可以达到接近本地网络的数据写入吞吐性能. 性能提升数量级.
- DEMO: \set size 10000000 \set r random_zipfian(1, :size, 2) \set k permute(:r, :size) \startpipeline insert into tbl values (:r, :k); insert into tbl values (:r, :k); insert into tbl values (:r, :k); insert into tbl values (:r, :k); insert into tbl values (:r, :k); \endpipeline

详见

1、This release has significant improvements in transaction throughput for PostgreSQL systems that have large numbers of connections to the database, regardless if they are in an active or idle state.
- 《PostgreSQL 14 GetSnapshotData 高并发优化, 以及 64位xid避免xid wrap优化》

2、PostgreSQL 14 also contains additional gains in reducing B-tree indexing overhead, including :
reducing the bloat on tables with frequently updated indexes. (频繁更新索引列引起的索引分裂和膨胀问题大幅度缓解 Enhance nbtree index tuple deletion - bottom-up index deletion)
- 《PostgreSQL 14 preview - 频繁更新索引列引起的索引分裂和膨胀问题大幅度缓解, Enhance nbtree index tuple deletion - bottom-up index deletion》

3、GiST indexes can now presort data during their build process, allowing both for faster index creation and smaller indexes. (gist和sp-gist索引AM支持sort接口, 大幅加速GiST和SP-GiST 索引build速度, 同时降低生成的索引空间大小.)
- 《PostgreSQL 14 preview - gist和sp-gist索引AM支持sort接口, 大幅加速GiST和SP-GiST 索引build速度》
- 《PostgreSQL 14 preview GiST 索引新增openapi: sortsupport - 加速gist索引创建, 紧凑组织gist index page.》

4、SP-GiST indexes now support covering indexes that allow users to add additional nonsearchable columns to the index through the INCLUDE clause. (支持任意维度聚集)
- 《PostgreSQL 14 preview - SP-GiST 索引新增 index 叶子结点 include column value 功能 支持》

5、BRIN 索引支持多区间, 支持布隆过滤器. 性能和适应场景增强
- 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 bloom filter - 随机,大量distinct value, 等值查询》
- 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 multi-range min-max - 分段范围索引》

6、There are many improvements to query parallelism in PostgreSQL 14 :
In addition to overall performance improvements for parallel sequential scans, (给worker分配任务时, 按chunk来进行分配, 而不是每次分配1个block. 使得prefetch可以更加有效, 而且每个worker的IO是顺序IO, 更加有效.)
- 《PostgreSQL 14 并行优化 - seqscan io chunk优化, 避免离散IO》

7、the RETURN QUERY directive in PL/pgSQL can now execute queries with parallelism.

8、REFRESH MATERIALIZED VIEW can now use query parallelism as well.
- 《PostgreSQL 14 preview - Enable parallelism in REFRESH MATERIALIZED VIEW》

9、FDW:
PostgreSQL 14 also introduces the ability to leverage query parallelism when querying remote databases using foreign data wrappers. The PostgreSQL foreign data wrapper, postgres_fdw, added support for this in PostgreSQL 14 when the async_capable flag is set.
- 《PostgreSQL 14 preview - postgres_fdw 异步append - sharding 性能增强 - 实现真正意义上跨库并行计算》

postgres_fdw also supports bulk inserts
- 《PostgreSQL 14 preview - FDW 支持bulk insert API(GetForeignModifyBatchSize,ExecForeignBatchInsert) - postgres_fdw 外部表实现bulk insert接口》

and can import table partitions using IMPORT FOREIGN SCHEMA
- 《PostgreSQL 14 preview - 支持通过import foreign schema 导入指定远端分区表的子分区 - postgres_fdw , import foreign schema 支持 limit to (子分区)》

and can now execute TRUNCATE on foreign tables.
- 《PostgreSQL 14 preview - FDW 外部表接口支持 truncate only|cascade , postgres_fdw已实现, 基于FDW的sharding 功能更进一步》

• 《PostgreSQL 14 preview - postgres_fdw 支持 hold foreign server 长连接, fdw sharding再进一步 - postgres_fdw: Add option to control whether to keep connections open》

10、分区表:
This release also has several improvements to the partitioning system, including :
- 《PostgreSQL 14 preview - 分区表性能增强, Avoid creating duplicate cached plans for inherited FK constraints. 节约cache》

performance gains when updating or deleting rows on tables where only a few partitions are affected.
- 《PostgreSQL 14 preview - 分区表性能再次增强 - ExecInitModifyTable 分区裁剪精细化》
- 《PostgreSQL 14 preview - 优化器增强 Rework planning and execution of UPDATE and DELETE. (减少传导不必要的列value、避免为每个分区生成subplan)》

In PostgreSQL 14, partitions can now be detached in a non-blocking manner using the ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY command. (分区表attach, detach分区支持无锁(短暂锁) - 基于2阶段routines管理)
- 《PostgreSQL 14 preview - 分区表attach, detach分区支持无锁(短暂锁) - 基于2阶段routines管理》

• 《PostgreSQL 14 preview - alter table 支持两阶段 routine 管理, 未来将基于此支持 无(短暂)锁 rewrite table(例如vacuum full, alter table rewrite table)》

11、Incremental sorting, introduced in the previous release, can now be used by window functions in PostgreSQL 14.
- 《PostgreSQL 14 preview - 支持window function的incremental sort》

12、This new release adds more capabilities to extended statistics, which can now be applied to expressions.
Extended statistics on expressions 优化器增强: 自定义扩展统计信息支持 指定 表达式 - 按 表达式 收集统计信息组合
- 《PostgreSQL 14 preview - Extended statistics on expressions 优化器增强: 自定义扩展统计信息支持 指定 表达式 - 按 表达式 收集统计信息组合》

13、增强or, and多条件选择性评估 - 使用multiple extended statistics
- 《PostgreSQL 14 preview - 增强or, and多条件选择性评估 - 使用multiple extended statistics》

14、大表search IN ( consts ) - linear search TO hash table probe (consts 个数>= MIN_ARRAY_SIZE_FOR_HASHED_SAOP 9)
- 《PostgreSQL 14 preview - 大表search IN ( consts ) - linear search TO hash table probe (consts 个数>= MIN_ARRAY_SIZE_FOR_HASHED_SAOP)》

15、PostgreSQL has supported compression on its "oversized data" columns (i.e. the TOAST system) for decades, but this release adds the ability to now choose to use LZ4 compression for column compression.
TOAST 支持 lz4 压缩算法 - --with-lz4 , 新增GUC default_toast_compression
- 《PostgreSQL 14 preview - TOAST 支持 lz4 压缩算法 - --with-lz4 , 新增GUC default_toast_compression》

16、libpq驱动 支持pipeline 模式
- 《PostgreSQL 14 preview - libpq驱动 支持pipeline 模式, SQL请求支持异步化通信, 大幅度提升性能, 降低RT》

二、数据类型和SQL

1、支持multi range类型, 兼容range类型已知的所有操作符, 索引.
- 场景: 物联网
- 价值: 存储传感器的指标波动范围, 允许在一个value里面存储多个范围区间. 同时支持范围的包含、相交、左、右、相邻等逻辑查询(支持索引加速)
- DEMO: SELECT '{}'::int4multirange; SELECT '{[3,7)}'::int4multirange; SELECT '{[3,7), [8,9)}'::int4multirange;

2、支持 jsonb 下标语法, 大幅度简化JSON类型的使用, 支持set原子操作
- 场景: 通用场景
- 价值: 大幅度简化JSON类型的使用.
- DEMO: +select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']; + jsonb +--------------- + {"a2": "aaa"} +(1 row)

3、支持存储过程OUT参数
- 场景: 通用场景
- 价值: 支持存储过程返回VALUE

4、支持group by grouping sets,rollup,cube distinct语法过滤重复group组合
- 场景: 分析场景
- 价值: 自动重复组合过滤, 支持更友好的多组聚合

5、递归(CTE)图式搜索增加广度优先、深度优先语法, 循环语法
- 场景: 社交、风控、图式数据、图谱、TOP等场景
- 价值: 简化 广度优先、深度优先语法, 循环检测的SQL写法
- DEMO: ``` WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH DEPTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_tree(id, link, data) AS ( SELECT t.id, t.link, t.data FROM tree t UNION ALL SELECT t.id, t.link, t.data FROM tree t, search_tree st WHERE t.id = st.link ) SEARCH BREADTH FIRST BY id SET ordercol SELECT * FROM search_tree ORDER BY ordercol;

WITH RECURSIVE search_graph(id, link, data, depth) AS ( SELECT g.id, g.link, g.data, 1 FROM graph g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph g, search_graph sg WHERE g.id = sg.link ) CYCLE id SET is_cycle USING path SELECT * FROM search_graph; ```

6、增加date_bin函数, 支持任意起点, 按任意interval切分bucket, 输入一个时间戳返回这个时间戳所在的bucket timestamp
- 场景: 时序场景、分析场景
- 价值: 按时间间隔打点聚合, 简化打点的算法
- DEMO: ``` date_bin(stride, source, origin)

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01'); Result: 2020-02-11 15:30:00

SELECT date_bin('15 minutes', TIMESTAMP '2020-02-11 15:44:17', TIMESTAMP '2001-01-01 00:02:30'); Result: 2020-02-11 15:32:30 ```

7、支持tid range scan扫描方法, 允许用户输入需要扫描指定数据块的范围
- 场景: 通用场景, 高并发场景, 大数据吞吐计算场景
- 价值: 增加了一种新的搜索算法, 按HEAP表的物理存储数据块ctid地址进行搜索. 支持自定义业务层的并行数据扫描逻辑、随机数据扫描逻辑. 结合rr snapshot export支持一致性单表并行导出. 全表并行无锁冲突的更新. - DEMO: select * from tidtest where ctid >= '(10,0)' and ctid < '(11,0)';

8、ecpg支持declare statement - 场景: C语言嵌套SQL, 通常在金融领域大量使用, 早期由于cobar流行. Oracle PROC等相关. 用于迁移Oracle相关PROC程序 - 价值: 提高Oracle PROC兼容性, 降低迁移门槛 - DEMO: +#include <stdio.h> + +EXEC SQL BEGIN DECLARE SECTION; +char dbname[128]; +char *dym_sql = "SELECT current_database()"; +EXEC SQL END DECLARE SECTION; + +int main(){ + EXEC SQL CONNECT TO postgres AS con1; + EXEC SQL CONNECT TO testdb AS con2; + EXEC SQL AT con1 DECLARE stmt STATEMENT; + EXEC SQL PREPARE stmt FROM :dym_sql; + EXEC SQL EXECUTE stmt INTO :dbname; + printf("%s\n", dbname); + + EXEC SQL DISCONNECT ALL; + return 0; +}

详见

1、Building upon its existing support for range types, PostgreSQL 14 adds the new multirange type that lets you specify an ordered list of noncontiguous ranges, e.g.:
SELECT 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') );
The new multirange type supports the existing operations available to range types.
- 《PostgreSQL 14 preview - multirange type》

2、PostgreSQL 14 now adds a general subscripting framework for retrieving information in nested objects.
For example, you can now retrieve nested info in the JSONB data type using subscript syntax, e.g.:
SELECT ('{ "this": { "now": { "works": "in postgres 14!" }}}'::jsonb)['this']['now']['works'];
- 《PostgreSQL 14 preview - 支持 jsonb 下标语法, 支持set原子操作, 类似array下标》

3、PostgreSQL 14 also adds support for OUT parameters in stored procedures,

4、and allows for the GROUP BY clause to use the DISTINCT keyword to remove duplicate combinations.
- 《PostgreSQL 14 preview - 支持 GROUP BY GROUPING SET DISTINCT》

5、For recursive common table expressions (WITH queries), PostgreSQL 14 adds the syntactic conveniences of SEARCH and CYCLE to help with ordering and cycle detection respectively.
- 《PostgreSQL 14 preview - SQL标准增强 (SEARCH and CYCLE clauses), 递归(CTE)图式搜索增加广度优先、深度优先语法, 循环语法 - breadth- or depth-first search orders and detect cycles》

6、There is also the new date_bin function in PostgreSQL 14 that can align timestamps with specified intervals, a technique known as "binning."
- 《PostgreSQL 14 preview - date_bin 任意起点, 任意bucket(interval) split bucket align 统计 - 时序场景(iot, 金融等), 关联timescaledb》

7、ctid range scan 扫描方法
- 《PostgreSQL 14 preview - tid range scan方法, 一种page级别应用可自控的并行扫描、处理方法, 结合RR snapshot export功能》

8、《PostgreSQL 14 preview - ECPG 语法增强 - 兼容Oracle PRO*C declare statement 语法》

三、管理

1、垃圾回收增强
concurrently模式创建索引不会引起垃圾膨胀,
- 场景: OLTP业务
- 价值: 降低膨胀概率
- DEMO: create index CONCURRENTLY abc ... reindex CONCURRENTLY xxx ... 以上操作不管持续多久, 不影响vacuum回收这之间产生的垃圾

增加一个全速vacuum模式在触发后会忽略索引和sleep参数执行vacuum以最快速度完成vacuum freeze,
- 场景: OLTP业务
- 价值: 加速vacuum freeze, 降低xid wrapped风险
- DEMO: postgres=# select * from pg_settings where name ~ 'failsafe'; -[ RECORD 1 ]---+------------------------------------------------------------------------------------ name | vacuum_failsafe_age setting | 1600000000 unit | category | Client Connection Defaults / Statement Behavior short_desc | Age at which VACUUM should trigger failsafe to avoid a wraparound outage. extra_desc | context | user vartype | integer source | default min_val | 0 max_val | 2100000000 enumvals | boot_val | 1600000000 reset_val | 1600000000 sourcefile | sourceline | pending_restart | f -[ RECORD 2 ]---+------------------------------------------------------------------------------------ name | vacuum_multixact_failsafe_age setting | 1600000000 unit | category | Client Connection Defaults / Statement Behavior short_desc | Multixact age at which VACUUM should trigger failsafe to avoid a wraparound outage. extra_desc | context | user vartype | integer source | default min_val | 0 max_val | 2100000000 enumvals | boot_val | 1600000000 reset_val | 1600000000 sourcefile | sourceline | pending_restart | f

在表里的垃圾占用的PAGE较少时跳过index vacuum从而提高vacuum 效率
- 场景: OLTP业务
- 价值: 加速垃圾回收效率

2、analyze 提升(支持父表的全局视角统计信息, 支持IO prefetch加速analyze)
- 场景: 通用场景
- 价值: 提高统计信息数据采集速度

3、系统视图和管理函数增强
新增pg_stat_progress_copy视图 , 支持COPY 导入数据进度监控 , 导入多少行, 排除多少行(where filter)
- 场景: DBA效能
- 价值: 掌握copy倒入进度和速度
- DEMO: postgres=# \d pg_stat_progress_copy View "pg_catalog.pg_stat_progress_copy" Column | Type | Collation | Nullable | Default ------------------+---------+-----------+----------+--------- pid | integer | | | datid | oid | | | datname | name | | | relid | oid | | | command | text | | | type | text | | | bytes_processed | bigint | | | bytes_total | bigint | | | tuples_processed | bigint | | | tuples_excluded | bigint | | |

新增pg_stat_wal统计信息视图, 跟踪wal日志统计信息
- 场景: DBA效能
- 价值: 掌握数据库WAL日志相关的统计信息
- DEMO: postgres=# \d pg_stat_wal View "pg_catalog.pg_stat_wal" Column | Type | Collation | Nullable | Default ------------------+--------------------------+-----------+----------+--------- wal_records | bigint | | | wal_fpi | bigint | | | wal_bytes | numeric | | | wal_buffers_full | bigint | | | wal_write | bigint | | | wal_sync | bigint | | | wal_write_time | double precision | | | wal_sync_time | double precision | | | stats_reset | timestamp with time zone | | |

新增 replication slot 统计信息视图 - pg_stat_replication_slots
- 场景: DBA效能
- 价值: 掌握每个SLOT的统计信息
- DEMO: postgres=# \d pg_stat_replication_slots View "pg_catalog.pg_stat_replication_slots" Column | Type | Collation | Nullable | Default --------------+--------------------------+-----------+----------+--------- slot_name | text | | | spill_txns | bigint | | | spill_count | bigint | | | spill_bytes | bigint | | | stream_txns | bigint | | | stream_count | bigint | | | stream_bytes | bigint | | | total_txns | bigint | | | total_bytes | bigint | | | stats_reset | timestamp with time zone | | |

pg_locks 增加 wait_start 字段, 跟踪锁等待开始时间
- 场景: DBA效能
- 价值: 掌握更多锁等待细节, 例如等待时长
- DEMO: postgres=# \d pg_locks View "pg_catalog.pg_locks" Column | Type | Collation | Nullable | Default --------------------+--------------------------+-----------+----------+--------- locktype | text | | | database | oid | | | relation | oid | | | page | integer | | | tuple | smallint | | | virtualxid | text | | | transactionid | xid | | | classid | oid | | | objid | oid | | | objsubid | smallint | | | virtualtransaction | text | | | pid | integer | | | mode | text | | | granted | boolean | | | fastpath | boolean | | | waitstart | timestamp with time zone | | |

pg_stat_database 增加 active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, sessions_killed统计 指标, 指导如何分析数据库有没有性能瓶颈, 瓶颈是什么? top瓶颈是什么? 如何优化?
- 场景: DBA效能
- 价值: 掌握更多数据库级别的活跃时间、空闲时间、会话量等统计信息, 了解数据库是否达到瓶颈
- DEMO: postgres=# \d pg_stat_database View "pg_catalog.pg_stat_database" Column | Type | Collation | Nullable | Default --------------------------+--------------------------+-----------+----------+--------- datid | oid | | | datname | name | | | numbackends | integer | | | xact_commit | bigint | | | xact_rollback | bigint | | | blks_read | bigint | | | blks_hit | bigint | | | tup_returned | bigint | | | tup_fetched | bigint | | | tup_inserted | bigint | | | tup_updated | bigint | | | tup_deleted | bigint | | | conflicts | bigint | | | temp_files | bigint | | | temp_bytes | bigint | | | deadlocks | bigint | | | checksum_failures | bigint | | | checksum_last_failure | timestamp with time zone | | | blk_read_time | double precision | | | blk_write_time | double precision | | | session_time | double precision | | | active_time | double precision | | | idle_in_transaction_time | double precision | | | sessions | bigint | | | sessions_abandoned | bigint | | | sessions_fatal | bigint | | | sessions_killed | bigint | | | stats_reset | timestamp with time zone | | |

pg_prepared_statements 增加硬解析、软解析次数统计
- 场景: DBA效能
- 价值: 掌握SQL的软解、硬解次数, 分析SQL解析层消耗和优化方法
- DEMO: postgres=# \d pg_prepared_statements View "pg_catalog.pg_prepared_statements" Column | Type | Collation | Nullable | Default -----------------+--------------------------+-----------+----------+--------- name | text | | | statement | text | | | prepare_time | timestamp with time zone | | | parameter_types | regtype[] | | | from_sql | boolean | | | generic_plans | bigint | | | custom_plans | bigint | | |

支持查看当前会话和其他会话的内存上下文, 诊断内存消耗问题
- 场景: DBA效能
- 价值: 了解会话的内存使用情况, 分析内存问题
- DEMO: postgres=# select * from pg_backend_memory_contexts; name | ident | parent | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes --------------------------+-------------------------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------ TopMemoryContext | | | 0 | 68704 | 5 | 14144 | 23 | 54560 TopTransactionContext | | TopMemoryContext | 1 | 8192 | 1 | 7736 | 0 | 456 Record information cache | | TopMemoryContext | 1 | 8192 | 1 | 1576 | 0 | 6616 TableSpace cache | | TopMemoryContext | 1 | 8192 | 1 | 2088 | 0 | 6104 Type information cache | | TopMemoryContext | 1 | 24376 | 2 | 2616 | 0 | 21760 Operator lookup cache | | TopMemoryContext | 1 | 24576 | 2 | 10752 | 3 | 13824 RowDescriptionContext | | TopMemoryContext | 1 | 8192 | 1 | 6888 | 0 | 1304 MessageContext | | TopMemoryContext | 1 | 65536 | 4 | 31208 | 0 | 34328 Operator class cache | | TopMemoryContext | 1 | 8192 | 1 | 552 | 0 | 7640 smgr relation table | | TopMemoryContext | 1 | 32768 | 3 | 16824 | 8 | 15944 TransactionAbortContext | | TopMemoryContext | 1 | 32768 | 1 | 32504 | 0 | 264 Portal hash | | TopMemoryContext | 1 | 8192 | 1 | 552 | 0 | 7640 TopPortalContext | | TopMemoryContext | 1 | 8192 | 1 | 7656 | 0 | 536 PortalContext | <unnamed> | TopPortalContext | 2 | 1024 | 1 | 568 | 0 | 456 ExecutorState | | PortalContext | 3 | 49208 | 4 | 14760 | 7 | 34448 printtup | | ExecutorState | 4 | 8192 | 1 | 7928 | 0 | 264 Table function arguments | | ExecutorState | 4 | 8192 | 1 | 7880 | 0 | 312 ExprContext | | ExecutorState | 4 | 8192 | 1 | 3496 | 0 | 4696 Relcache by OID | | TopMemoryContext | 1 | 16384 | 2 | 3504 | 2 | 12880 CacheMemoryContext | | TopMemoryContext | 1 | 1048576 | 8 | 432048 | 2 | 616528 relation rules | pg_backend_memory_contexts | CacheMemoryContext | 2 | 8192 | 4 | 1384 | 0 | 6808 index info | pg_toast_2619_index | CacheMemoryContext | 2 | 2048 | 2 | 528 | 1 | 1520 index info | pg_attrdef_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_attrdef_adrelid_adnum_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 1 | 1360 index info | pg_am_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 920 | 0 | 1128 index info | pg_am_name_index | CacheMemoryContext | 2 | 2048 | 2 | 904 | 0 | 1144 index info | pg_statistic_ext_relid_index | CacheMemoryContext | 2 | 2048 | 2 | 904 | 0 | 1144 index info | pg_class_tblspc_relfilenode_index | CacheMemoryContext | 2 | 2048 | 2 | 576 | 1 | 1472 index info | pg_index_indrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_db_role_setting_databaseid_rol_index | CacheMemoryContext | 2 | 2048 | 2 | 496 | 1 | 1552 index info | pg_opclass_am_name_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1000 | 2 | 2072 index info | pg_foreign_data_wrapper_name_index | CacheMemoryContext | 2 | 2048 | 2 | 952 | 2 | 1096 index info | pg_enum_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_class_relname_nsp_index | CacheMemoryContext | 2 | 2048 | 2 | 448 | 1 | 1600 index info | pg_foreign_server_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_publication_pubname_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_statistic_relid_att_inh_index | CacheMemoryContext | 2 | 3072 | 2 | 808 | 1 | 2264 index info | pg_cast_source_target_index | CacheMemoryContext | 2 | 2048 | 2 | 448 | 1 | 1600 index info | pg_language_name_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_transform_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_collation_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_amop_fam_strat_index | CacheMemoryContext | 2 | 3072 | 2 | 696 | 1 | 2376 index info | pg_index_indexrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_ts_template_tmplname_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_ts_config_map_index | CacheMemoryContext | 2 | 3072 | 2 | 1160 | 2 | 1912 index info | pg_opclass_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_foreign_data_wrapper_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 952 | 2 | 1096 index info | pg_event_trigger_evtname_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_statistic_ext_name_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_publication_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_ts_dict_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_event_trigger_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_conversion_default_index | CacheMemoryContext | 2 | 3072 | 2 | 1096 | 2 | 1976 index info | pg_operator_oprname_l_r_n_index | CacheMemoryContext | 2 | 3072 | 2 | 696 | 1 | 2376 index info | pg_trigger_tgrelid_tgname_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_enum_typid_label_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_ts_config_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_user_mapping_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_opfamily_am_name_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1160 | 2 | 1912 index info | pg_foreign_table_relid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_type_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_aggregate_fnoid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_constraint_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_rewrite_rel_rulename_index | CacheMemoryContext | 2 | 2048 | 2 | 528 | 1 | 1520 index info | pg_ts_parser_prsname_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_ts_config_cfgname_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_ts_parser_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_publication_rel_prrelid_prpubid_index | CacheMemoryContext | 2 | 2048 | 2 | 656 | 2 | 1392 index info | pg_operator_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_namespace_nspname_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_ts_template_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_amop_opr_fam_index | CacheMemoryContext | 2 | 3072 | 2 | 840 | 2 | 2232 index info | pg_default_acl_role_nsp_obj_index | CacheMemoryContext | 2 | 3072 | 2 | 1128 | 1 | 1944 index info | pg_collation_name_enc_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 840 | 2 | 2232 index info | pg_publication_rel_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_range_rngtypid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_ts_dict_dictname_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_type_typname_nsp_index | CacheMemoryContext | 2 | 2048 | 2 | 448 | 1 | 1600 index info | pg_opfamily_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_statistic_ext_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_statistic_ext_data_stxoid_index | CacheMemoryContext | 2 | 2048 | 2 | 952 | 2 | 1096 index info | pg_class_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_proc_proname_args_nsp_index | CacheMemoryContext | 2 | 3072 | 2 | 1000 | 2 | 2072 index info | pg_partitioned_table_partrelid_index | CacheMemoryContext | 2 | 2048 | 2 | 952 | 2 | 1096 index info | pg_range_rngmultitypid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_transform_type_lang_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_attribute_relid_attnum_index | CacheMemoryContext | 2 | 2048 | 2 | 448 | 1 | 1600 index info | pg_proc_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_language_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_namespace_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_amproc_fam_proc_index | CacheMemoryContext | 2 | 3072 | 2 | 696 | 1 | 2376 index info | pg_foreign_server_name_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_attribute_relid_attnam_index | CacheMemoryContext | 2 | 2048 | 2 | 608 | 1 | 1440 index info | pg_conversion_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_user_mapping_user_server_index | CacheMemoryContext | 2 | 2048 | 2 | 656 | 2 | 1392 index info | pg_subscription_rel_srrelid_srsubid_index | CacheMemoryContext | 2 | 2048 | 2 | 656 | 2 | 1392 index info | pg_sequence_seqrelid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_conversion_name_nsp_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_authid_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_auth_members_member_role_index | CacheMemoryContext | 2 | 2048 | 2 | 656 | 2 | 1392 index info | pg_subscription_oid_index | CacheMemoryContext | 2 | 1024 | 1 | 0 | 0 | 1024 index info | pg_tablespace_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_shseclabel_object_index | CacheMemoryContext | 2 | 3072 | 2 | 1160 | 2 | 1912 index info | pg_replication_origin_roname_index | CacheMemoryContext | 2 | 2048 | 2 | 952 | 2 | 1096 index info | pg_database_datname_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_subscription_subname_index | CacheMemoryContext | 2 | 2048 | 2 | 688 | 2 | 1360 index info | pg_replication_origin_roiident_index | CacheMemoryContext | 2 | 2048 | 2 | 952 | 2 | 1096 index info | pg_auth_members_role_member_index | CacheMemoryContext | 2 | 2048 | 2 | 656 | 2 | 1392 index info | pg_database_oid_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 index info | pg_authid_rolname_index | CacheMemoryContext | 2 | 2048 | 2 | 824 | 0 | 1224 WAL record construction | | TopMemoryContext | 1 | 49768 | 2 | 6360 | 0 | 43408 PrivateRefCount | | TopMemoryContext | 1 | 8192 | 1 | 2616 | 0 | 5576 MdSmgr | | TopMemoryContext | 1 | 8192 | 1 | 7256 | 0 | 936 LOCALLOCK hash | | TopMemoryContext | 1 | 16384 | 2 | 4592 | 2 | 11792 Timezones | | TopMemoryContext | 1 | 104120 | 2 | 2616 | 0 | 101504 ErrorContext | | TopMemoryContext | 1 | 8192 | 1 | 7928 | 5 | 264 (116 rows) ``` postgres=# select pid,query from pg_stat_activity ; pid | query
-------+------------------------------------------- 49554 | 49556 | 79203 | select pid,query from pg_stat_activity ; 78507 | select *, first_value(x) over W from tbl + | ; 49552 | 49551 | 49553 | (7 rows)

postgres=# select pg_log_backend_memory_contexts(49554); pg_log_backend_memory_contexts

---

t (1 row)

less postgresql-2021-05-22_000000.csv .... 554,,60a71979.c192,21,,2021-05-21 10:22:49 CST,1/782,0,LOG,00000,"level: 2; hba parser context: 17408 total in 5 blocks; 8120 free (6 chunks); 9288 used",,,,,,,,,"","autovacuum launcher",,0 2021-05-22 09:51:03.097 CST,,,49554,,60a71979.c192,22,,2021-05-21 10:22:49 CST,1/782,0,LOG,00000,"level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (5 chunks); 264 used",,,,,,,,,"","autovacuum launcher",,0 ```

4、新增 GUC 参数:
增加 log_recovery_conflict_waits GUC参数, 支持 standby query&startup process conflict 恢复冲突超时(deadlock_timeout) 日志打印
- 场景: DBA效能
- 价值: 掌握只读standby库的查询和WAL恢复进程的冲突等待时间.
- DEMO: postgres=# select * from pg_settings where name='log_recovery_conflict_waits'; -[ RECORD 1 ]---+-------------------------------------- name | log_recovery_conflict_waits setting | off unit | category | Reporting and Logging / What to Log short_desc | Logs standby recovery conflict waits. extra_desc | context | sighup vartype | bool source | default min_val | max_val | enumvals | boot_val | off reset_val | off sourcefile | sourceline | pending_restart | f

增加 track_wal_io_timing GUC参数, 支持wal日志buffer write, fsync IO等待时长统计 , 在pg_stat_wal视图中查看
- 场景: DBA效能
- 价值: 了解wal buffer io, fsync io的等待时长统计. 掌握IO资源使用情况和WAL瓶颈分析
- DEMO: postgres=# select * from pg_settings where name='track_wal_io_timing'; -[ RECORD 1 ]---+-------------------------------------------------- name | track_wal_io_timing setting | off unit | category | Statistics / Query and Index Statistics Collector short_desc | Collects timing statistics for WAL I/O activity. extra_desc | context | superuser vartype | bool source | default min_val | max_val | enumvals | boot_val | off reset_val | off sourcefile | sourceline | pending_restart | f

增加 idle_session_timeout GUC参数, 断开长时间空闲的会话
- 场景: DBA效能
- 价值: 减少空闲会话的资源占用
- DEMO: postgres=# select * from pg_settings where name='idle_session_timeout'; -[ RECORD 1 ]---+------------------------------------------------------------------------------- name | idle_session_timeout setting | 0 unit | ms category | Client Connection Defaults / Statement Behavior short_desc | Sets the maximum allowed idle time between queries, when not in a transaction. extra_desc | A value of 0 turns off the timeout. context | user vartype | integer source | default min_val | 0 max_val | 2147483647 enumvals | boot_val | 0 reset_val | 0 sourcefile | sourceline | pending_restart | f

增加 client_connection_check_interval GUC参数, 协议层支持心跳包, 如果客户端已离线, 可以快速中断这个客户端此前运行中的长SQL - Detect POLLHUP/POLLRDHUP while running queries
- 场景: 分析师、开发者、DBA效能
- 价值: 用户如果发现SQL较慢, 直接退出终端, 在执行中的不接受中断信号过程中的SQL要等执行结束才会退出, 现在不需要等执行结束, 检测到客户端推出后SQL即刻推出.
- DEMO: postgres=# select * from pg_settings where name='client_connection_check_interval'; -[ RECORD 1 ]---+------------------------------------------------------------------------------- name | client_connection_check_interval setting | 0 unit | ms category | Connections and Authentication / Connection Settings short_desc | Sets the time interval between checks for disconnection while running queries. extra_desc | context | user vartype | integer source | default min_val | 0 max_val | 2147483647 enumvals | boot_val | 0 reset_val | 0 sourcefile | sourceline | pending_restart | f

5、SQL 命令增强:
REINDEX command 增加 tablespace 选项, 支持重建索引到指定表空间
- 场景: DBA效能
- 价值: 更好的利用块设备
- DEMO: ``` Command: REINDEX Description: rebuild indexes Syntax: REINDEX [ ( option [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name

where option can be one of:

CONCURRENTLY [ boolean ]
TABLESPACE new_tablespace
VERBOSE [ boolean ]

```

REINDEX command 支持分区表, 自动重建所有子分区的索引.
- 场景: DBA效能
- 价值: 提高分区表索引重建的便捷度

6、插件功能增强:
新增 pg_surgery 插件, 可用于修复 corrupted tuple
- 场景: DBA效能
- 价值: 修复损坏数据
- DEMO: ``` 修复损坏的事务日志 test=> select * from t1 where ctid = '(0, 1)'; ERROR: could not access status of transaction 4007513275 DETAIL: Could not open file "pg_xact/0EED": No such file or directory.

test=# select heap_force_kill('t1'::regclass, ARRAY['(0, 1)']::tid[]); heap_force_kill

---

(1 row)

test=# select * from t1 where ctid = '(0, 1)'; (0 rows) ```

``` 修复错误(wrapped)的xid test=> vacuum t1; ERROR: found xmin 507 from before relfrozenxid 515 CONTEXT: while scanning block 0 of relation "public.t1"

test=# select ctid from t1 where xmin = 507; ctid

---

(0,3) (1 row)

test=# select heap_force_freeze('t1'::regclass, ARRAY['(0, 3)']::tid[]); heap_force_freeze

---

(1 row)

test=# select ctid from t1 where xmin = 2; ctid

---

(0,3) (1 row) ```

新增 old_snapshot 插件, 打印快照跟踪条目(每分钟一条, OldSnapshotTimeMapping结构)的内容 , old_snapshot_threshold 相关
- 场景: DBA效能
- 价值: 了解历史事务快照, 分析快要达到REDO快照超时的事务, 每个时间段当时数据库的未分配事务信息 - DEMO: pg_old_snapshot_time_mapping(array_offset OUT int4, end_timestamp OUT timestamptz, newest_xmin OUT xid) returns setof record

pg_amcheck插件增加heap table数据页格式错误、逻辑错误检测功能
- 场景: DBA效能
- 价值: 检查数据块逻辑错误
- DEMO: ``` postgres=# create extension amcheck ; CREATE EXTENSION

$ pg_amcheck -v postgres pg_amcheck: including database "postgres" pg_amcheck: in database "postgres": using amcheck version "1.3" in schema "public" pg_amcheck: checking heap table "postgres"."public"."tbl" pg_amcheck: checking btree index "postgres"."public"."idx_t_1" pg_amcheck: checking btree index "postgres"."public"."idx_tbl_2" ... ... ```

详见

1、PostgreSQL 14 makes numerous improvements to VACUUM :
create index concurrently 不管多久都不会影响垃圾回收了. 以前的版本有个函数getoldestxmin获取实例中最老的未结束事务号, 这个事务号以后产生的垃圾不能被回收, 所以create index如果花了1小时, 这1小时内实例所产生的垃圾要等index创建结束后才能回收.
- 《PostgreSQL 14 preview - VACUUM: ignore indexing operations with CONCURRENTLY (getoldestxmin优化, 更多垃圾可以被回收, 防止long create index导致膨胀)》

支持 vacuum 强制加速参数 vacuum_failsafe_age , vacuum_multixact_failsafe_age. 加速时将跳过index, 忽略delay配置, 全速前进, 防止 xid wraparound.
- 《PostgreSQL 14 preview - 支持 vacuum 强制加速参数 vacuum_failsafe_age , vacuum_multixact_failsafe_age. 防止 xid wraparound》

当一个表里面有dead tuple的PAGEs占比低于BYPASS_THRESHOLD_PAGES时, 将跳过此次垃圾回收对这个表的index的vacuum. 因为就算访问到了这些dead tuple对应的索引, 回表时也不会额外的访问很多heap page.
- 《PostgreSQL 14 preview - BYPASS_THRESHOLD_PAGES vacuum 优化, 避免每次都需要vacuum index, 当LP_DEAD覆盖的page较少时, 跳过index vacuum》

Autovacuum now analyzes partitioned tables and can propagate information about row counts to parent tables.

There are also performance gains in ANALYZE that can be controlled with maintenance_io_concurrency parameter.
- 《PostgreSQL 14 preview - analyze 支持prefetch 加速IO》

2、系统视图和管理函数增强
PostgreSQL 14 includes numerous improvements on what information can be monitored, including :
the ability to track progress on COPY using the pg_stat_progress_copy view.
- 《PostgreSQL 14 preview - pg_stat_progress_copy增强 , COPY 导入数据支持进度监控 , 导入多少行, 排除多少行(where filter)》

This release lets you track WAL activity from the pg_stat_wal view,
- 《PostgreSQL 14 preview - 新增pg_stat_wal统计信息视图》

and inspect replication slot statistics from the pg_stat_replication_slots view.
- 《PostgreSQL 14 preview - 新增 replication slot 监控 - pg_stat_replication_slots》

• 《PostgreSQL 14 preview - pg_locks 增加 wait_start 字段, 跟踪锁等待开始时间》
• 《PostgreSQL 14 preview - SaaS特性 - 会话建立、资源消耗时间片 统计 - pg_stat_database 指标增加》
• 《PostgreSQL 14 preview - pg_stat_database 新增会话消耗统计 - 如何分析数据库有没有性能瓶颈, 瓶颈是什么? top瓶颈是什么? 如何优化?》
• 《PostgreSQL 14 preview - 支持绑定变量语句(prepared statements)的 custom_plans 与 generic_plans 次数统计》

支持memory context内容监控:
查看backend process的memory context
打印其他会话的memory context, 诊断内存消耗问题 - pg_backend_memory_contexts(pid)
- 《PostgreSQL 14 preview - 查看backend process的memory context》
- 《PostgreSQL 14 preview - 打印其他会话的memory context, 诊断内存消耗问题 - pg_backend_memory_contexts(pid)》

3、服务端配置(GUC):

• 《PostgreSQL 14 preview - log_recovery_conflict_waits - standby query&startup process conflict 恢复冲突 超时(timeout)配置,日志打印》
• 《PostgreSQL 14 preview - 新增track_wal_io_timing参数, 支持wal日志buffer write, fsync IO等待时长统计 , pg_stat_wal》

These include idle_session_timeout, which can close idle connections after the specified period,
- 《PostgreSQL 14 preview - 支持会话超时参数 - idle_session_timeout》

增加 client_connection_check_interval 参数, 协议层支持心跳包, 如果客户端已离线 可以 快速中断 运行中的长SQL - Detect POLLHUP/POLLRDHUP while running queries
- 《PostgreSQL 14 preview - client_connection_check_interval , 协议层支持心跳包, 如果客户端已离线 可以 快速中断 运行中的长SQL - Detect POLLHUP/POLLRDHUP while running queries》

4、Utility Commands

• 《PostgreSQL 14 preview - reindex 增加 tablespace 选项, 支持重建索引到指定表空间》

The REINDEX command can now process all of the child indexes of a partitioned table,
- Allow REINDEX to process all child tables or indexes of a partitioned relation (Justin Pryzby, Michael Paquier)

5、Additional Modules

• 《PostgreSQL 14 preview - corrupted tuple 修复功能 - pg_surgery》
• 《PostgreSQL 14 preview - 新增 old_snapshot 插件, 打印快照跟踪条目(每分钟一条, OldSnapshotTimeMapping结构)的内容 , old_snapshot_threshold 相关》

and PostgreSQL 14 adds the pg_amcheck utility to help check for data corruption.
- 《PostgreSQL 14 preview - amcheck插件增加heap table数据页格式错误、逻辑错误检测功能》

四、流复制与备份恢复

1、长事务逻辑复制优化, 增加streaming接口, 逻辑复制支持流式decoder和发送, 无需等待事务结束, 大幅度降低大事务、长事务的复制延迟
- 场景: 多机房部署、单元化部署、跨库同步订阅数据场景
- 价值: 大幅度降低大事务、长事务的复制延迟

2、逻辑复制sync table data阶段支持多线程, 允许同步全量数据的同时接收wal逻辑日志, 可以缩短大表首次同步到达最终一致的耗时
- 场景: 跨库逻辑订阅、数据迁移等场景
- 价值: 缩短大表首次同步到达最终一致的耗时

3、alter subscription语法增强, 支持add/drop publication
- 场景: 逻辑订阅
- 价值: 提高订阅端的操作便捷性
- DEMO: ``` Command: ALTER SUBSCRIPTION Description: change the definition of a subscription Syntax: ALTER SUBSCRIPTION name CONNECTION 'conninfo' ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ] ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ] ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ] ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ] ALTER SUBSCRIPTION name ENABLE ALTER SUBSCRIPTION name DISABLE ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] ) ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ALTER SUBSCRIPTION name RENAME TO new_name

URL: https://www.postgresql.org/docs/14/sql-altersubscription.html ```

4、recovery 性能增强 - recovery_init_sync_method=syncfs - 解决表很多时, crash recovery 递归open所有file的性能问题 - 需Linux新内核支持
- 场景: 通用
- 价值: 提高数据库崩溃恢复效率
- DEMO: postgres=# select * from pg_settings where name='recovery_init_sync_method'; -[ RECORD 1 ]---+---------------------------------------------------------------------------- name | recovery_init_sync_method setting | fsync unit | category | Error Handling short_desc | Sets the method for synchronizing the data directory before crash recovery. extra_desc | context | postmaster vartype | enum source | default min_val | max_val | enumvals | {fsync} boot_val | fsync reset_val | fsync sourcefile | sourceline | pending_restart | f

5、允许hot standby 作为pg_rewind的源库
- 场景: 通用, 高可用
- 价值: 可以使用standby来修复坏库

6、增加 remove_temp_files_after_crash GUC参数, 在数据库crash后重启时自动清理临时文件
- 场景: 通用
- 价值: 避免数据库遇到连续性崩溃恢复问题时可能导致存储爆满的问题
- DEMO: postgres=# select * from pg_settings where name='remove_temp_files_after_crash'; -[ RECORD 1 ]---+-------------------------------------------- name | remove_temp_files_after_crash setting | on unit | category | Error Handling short_desc | Remove temporary files after backend crash. extra_desc | context | sighup vartype | bool source | default min_val | max_val | enumvals | boot_val | on reset_val | on sourcefile | sourceline | pending_restart | f

7、standby wal receiver 接收时机优化, 无需等待startup process replay结束, 大幅度降低standby在重启后的wal接收延迟
- 场景: 基于流复制的只读实例, 基于流复制的高可用等场景
- 价值: 大幅度降低standby在重启后的wal接收延迟

详见

1、PostgreSQL 14 adds numerous performance benefits for logical replication, including :
the ability to stream in-progress transactions to subscribers instead of waiting for them to be completed.
- 《PostgreSQL 14 扩展逻辑decoding API 应对长事务逻辑复制优化 - Extend the logical decoding output plugin API with stream methods.》
- 《PostgreSQL 14 preview - 逻辑复制 支持长事务 流式发送接口, 已覆盖内置pub/sub logical replication - 规避长事务需要transaction end后才能往sub端发送解析结果》
- 《PostgreSQL 14 preview - 内置逻辑复制功能增强 , Allow multiple xacts during table sync in logical replication.》

The ALTER SUBSCRIPTION makes it easier to add/remove publications using the new ADD/DROP PUBLICATION syntax.
- 《PostgreSQL 14 preview - 内置订阅功能alter subscription语法增强,支持add/drop publication》

2、There are performance improvements in PostgreSQL 14 to how PostgreSQL starts up when in crash recovery,
- 《PostgreSQL 14 preview - recovery 性能增强 - recovery_init_sync_method=syncfs - 解决表很多时, crash recovery 递归open所有file的性能问题 - 需Linux新内核支持》

3、and you can now use pg_rewind on a PostgreSQL instance that is in standby mode.
- 《PostgreSQL 14 preview - 允许hot standby 作为pg_rewind的源库》

4、自动清理临时文件 after backend crash restart - GUC : remove_temp_files_after_crash
- 《PostgreSQL 14 preview - 自动清理临时文件 after backend crash restart - GUC remove_temp_files_after_crash》

5、wal receiver 接收时机优化
- 《PostgreSQL 14 preview - wal接收优化,无需等待startup process replay结束. Unnecessary delay in streaming replication due to replay lag》

五、安全

1、新增 pg_read_all_data , pg_write_all_data 角色, 支持一次性赋予tables/views/sequences对象的读、写权限
- 场景: 通用
- 价值: 提高DBA管理效率, 支持只读角色, 只写角色.
- DEMO: ``` postgres=# select * from pg_roles ; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+------ postgres | t | t | t | t | t | t | -1 | * | | t | | 10 pg_database_owner | f | t | f | f | f | f | -1 | * | | f | | 6171 pg_read_all_data | f | t | f | f | f | f | -1 | * | | f | | 6181 pg_write_all_data | f | t | f | f | f | f | -1 | * | | f | | 6182 pg_monitor | f | t | f | f | f | f | -1 | * | | f | | 3373 pg_read_all_settings | f | t | f | f | f | f | -1 | * | | f | | 3374 pg_read_all_stats | f | t | f | f | f | f | -1 | * | | f | | 3375 pg_stat_scan_tables | f | t | f | f | f | f | -1 | * | | f | | 3377 pg_read_server_files | f | t | f | f | f | f | -1 | * | | f | | 4569 pg_write_server_files | f | t | f | f | f | f | -1 | * | | f | | 4570 pg_execute_server_program | f | t | f | f | f | f | -1 | * | | f | | 4571 pg_signal_backend | f | t | f | f | f | f | -1 | * | | f | | 4200 (12 rows)

postgres=# create role ro login; CREATE ROLE postgres=# grant pg_read_all_data to ro; GRANT ROLE postgres=# \c postgres ro You are now connected to database "postgres" as user "ro". postgres=> select * from tbl limit 1; id | col1 | ts
--------+--------------------+---------------------------- 982190 | 0.4573622822054517 | 2021-05-24 18:23:00.615677 (1 row)

postgres=> \du+ ro List of roles Role name | Attributes | Member of | Description -----------+------------+--------------------+------------- ro | | {pg_read_all_data} |

postgres=> \c postgres postgres You are now connected to database "postgres" as user "postgres". postgres=# create role rw login; CREATE ROLE postgres=# grant pg_write_all_data to rw; GRANT ROLE postgres=# \c postgres rw You are now connected to database "postgres" as user "rw". postgres=> insert into tbl values (1,'test', now()); INSERT 0 1 postgres=> select * from tbl limit 1; ERROR: permission denied for table tbl ```

2、默认使用 SCRAM-SHA-256 认证方法, 提升安全性, 完全规避md5可能的密钥泄露和协议攻击问题
- 场景: 通用
- 价值: 提高认证方式和密码认证安全性
- DEMO: ``` postgres=# select rolname, rolpassword from pg_authid; rolname | rolpassword
---------------------------+--------------------------------------------------------------------------------------------------------------------------------------- pg_database_owner | pg_read_all_data | pg_write_all_data | pg_monitor | pg_read_all_settings | pg_read_all_stats | pg_stat_scan_tables | pg_read_server_files | pg_write_server_files | pg_execute_server_program | pg_signal_backend | ro | rw | postgres | SCRAM-SHA-256$4096:HQ/MO4C1qFcdRonv2hiG0Q==$Cgif0D/DtZQwwQNtOtvDs3LQtTZs6C2mPoKT9bcjbdw=:2YD87hpH+x/FudOeFtLQAqVHlwYBo/iCR8nZdhhsP4c= (14 rows)

postgres=# select * from pg_settings where name='password_encryption'; -[ RECORD 1 ]---+------------------------------------------------ name | password_encryption setting | scram-sha-256 unit | category | Connections and Authentication / Authentication short_desc | Chooses the algorithm for encrypting passwords. extra_desc | context | user vartype | enum source | default min_val | max_val | enumvals | {md5,scram-sha-256} boot_val | scram-sha-256 reset_val | scram-sha-256 sourcefile | sourceline | pending_restart | f ```

3、pg_hba.conf clientcert 支持 verify-ca 或 verify-full 校验客户端证书真伪, 检验证书DN内容是否匹配连接的数据库用户
- 场景: 通用
- 价值: 提高客户端SSL证书检测安全性

4、SSL 客户端证书校验增加clientname CN DN 提取选项, 遵循 RFC2253
- 场景: 通用
- 价值: 增加SSL证书 CN DN内容的可用范围

5、libpq协议层支持数据库状态判断(standby or primary)
- 场景: 读写分离、多数据源场景
- 价值: 提高判断数据库角色的效率, 不需要发起SQL即可判断数据库处于 读写 还是 只读 角色

6、libpq支持target_session_attrs属性配置: "any", "read-only", "read-write", "primary", "standby", and "prefer-standby".
- 场景: 读写分离、多数据源场景
- 价值: 在多数据源场景中, 支持根据状态选择是否要连接该目标. 可以根据SQL分配不同的角色, 用于balance.
- DEMO: postgresql://host1:123,host2:456/somedb?target_session_attrs=any&application_name=myapp

详见

1、PostgreSQL 14 adds the ability to give users universal "read only" and "write only" privileges on tables/views/sequences through the use of the pg_read_all_data and pg_write_all_data predefined roles, respectively.
- 《PostgreSQL 14 preview - 新增角色 pg_read_all_data (MySQL 兼容性之 - 读写用户的只读影子用户 - 默认读权限 ) , pg_write_all_data (写权限)》

2、This release also defaults to using SCRAM-SHA-256 for password management on new PostgreSQL instances.

3、Additionally, the clientcert parameter in the pg_hba.conf must now use either the values of verify-ca or verify-full instead of the legacy values.

4、PostgreSQL 14 can use a certificate's "distinguished name" (DN) for certificated-based authentication with clientname=DN parameter in the pg_hba.conf file.
- 《PostgreSQL 14 preview - SSL 客户端证书校验增加clientname CN DN 提取选项, 遵循 RFC2253》

5、libpq 协议优化:
- 《PostgreSQL GUC_REPORT+libpq 实现客户端决策链路 - multi host + 客户端 target_session_attrs + 发起连接时即刻获取PG数据库当前状态 - libpq 协议级 驱动级 failover balance》

• Enhance libpq's target_session_attrs parameter options (Haribabu Kommi, Greg Nancarrow, Vignesh C, Tom Lane)
  • New options are "read-only", "primary", "standby", and "prefer-standby".

ii. 所有特性详情

一、Server

• 《PostgreSQL 14 preview - kill 会话增加超时等待. pg_wait_for_backend_termination(pid, ms), 发送terminate信号给进程, 并等待若干ms, 如果PID未被kill, 则返回false和warning.》
• 《PostgreSQL 14 preview - VACUUM: ignore indexing operations with CONCURRENTLY (getoldestxmin优化, 更多垃圾可以被回收, 防止long create index导致膨胀)》
• 《PostgreSQL 14 preview - System Versioned Temporal Table - flashback闪回、跟踪、审计 record/row/tuple历史版本》
• 《PostgreSQL 14 preview - BYPASS_THRESHOLD_PAGES vacuum 优化, 避免每次都需要vacuum index, 当LP_DEAD覆盖的page较少时, 跳过index vacuum》
• 《PostgreSQL 14 preview - Add "pg_database_owner" default role. 表示数据库owner》
• 《PostgreSQL 14 preview - 新增角色 pg_read_all_data (MySQL 兼容性之 - 读写用户的只读影子用户 - 默认读权限 ) , pg_write_all_data (写权限)》
• 《PostgreSQL 14 preview - client_connection_check_interval , 协议层支持心跳包, 如果客户端已离线 可以 快速中断 运行中的长SQL - Detect POLLHUP/POLLRDHUP while running queries》
• 《PostgreSQL 14 preview - 支持startup(恢复)进程与backend(用户)进程死锁检测 - backpatch到9.6版本》
• 《PostgreSQL 14 preview - 自动清理临时文件 after backend crash restart - GUC remove_temp_files_after_crash》

因为没有regress test被打回的TDE特性如下:
- 《PostgreSQL 14 preview - TDE cluster_key_command 配置例子》
- 《PostgreSQL 14 preview - 支持TDE功能, 支持加密数据文件, 支持加密WAL日志文件》

1、分区表

• 《PostgreSQL 14 preview - 分区表attach, detach分区支持无锁(短暂锁) - 基于2阶段routines管理》
• 《PostgreSQL 14 preview - 分区表性能增强, Avoid creating duplicate cached plans for inherited FK constraints. 节约cache》
• 《PostgreSQL 14 preview - 分区表性能再次增强 - ExecInitModifyTable 分区裁剪精细化》
• 《PostgreSQL 14 preview - 优化器增强 Rework planning and execution of UPDATE and DELETE. (减少传导不必要的列value、避免为每个分区生成subplan)》

2、Indexes

• 《PostgreSQL 14 preview - 频繁更新索引列引起的索引分裂和膨胀问题大幅度缓解, Enhance nbtree index tuple deletion - bottom-up index deletion》
• 《PostgreSQL 14 preview - gist和sp-gist索引AM支持sort接口, 大幅加速GiST和SP-GiST 索引build速度》
• 《PostgreSQL 14 preview - SP-GiST 索引新增 index 叶子结点 include column value 功能 支持》
• 《PostgreSQL 14 preview - brin 索引内存优化》
• 《PostgreSQL 14 preview GiST 索引新增openapi: sortsupport - 加速gist索引创建, 紧凑组织gist index page.》
• 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 bloom filter - 随机,大量distinct value, 等值查询》
• 《PostgreSQL 14 preview - BRIN (典型IoT 时序场景) 块级索引支持 multi-range min-max - 分段范围索引》

3、优化器

• 《PostgreSQL 14 preview - 增强or, and多条件选择性评估 - 使用multiple extended statistics》
• 《PostgreSQL 14 preview - Extended statistics on expressions 优化器增强: 自定义扩展统计信息支持 指定 表达式 - 按 表达式 收集统计信息组合》

4、通用性能提升

• 《PostgreSQL 14 GetSnapshotData 高并发优化, 以及 64位xid避免xid wrap优化》
• 《PostgreSQL 14 preview - 增加 结果缓存 exexutor node - GUC 开关 enable_resultcache - 提高join loop性能》
• 《PostgreSQL 14 preview - Hybrid Hash/Nested Loop joins and caching results from subplans - cache用于join的innter table中间结果》
• 《PostgreSQL 14 preview - FDW 支持异步执行接口, postgres_fdw 支持异步append - sharding 性能增强 - 未来将支持更多异步操作》
• 《PostgreSQL 14 preview - analyze 支持prefetch 加速IO》
• 《PostgreSQL 14 并行优化 - seqscan io chunk优化, 避免离散IO》
• 《PostgreSQL 14 preview - drop、truncate大量对象, recovery时需要遍历shared buffer导致的性能问题patch(崩溃恢复慢、standby延迟等问题) - DropRelFileNodeBuffers 增强》
• 《PostgreSQL 14 preview - Speed up rechecking if relation needs to be vacuumed or analyze in autovacuum.》
• 《PostgreSQL 14 preview - 支持window function的incremental sort》
• 《PostgreSQL 14 preview - 一些SQL标准函数调用parser 增强 - OVERLAPS , OVERLAY , SUBSTRING , SIMILAR , NORMALIZE , AT TIME ZONE , POSITION ...》
• 《老外惊呼: 世界变化快! PostgreSQL 14 on ARM 8.1 加上LSE patch性能飙升到140万tps》
• 《PostgreSQL 14 preview - 强制 vacuum 加速patch, 避免heap truncate多余的动作》
• 《PostgreSQL 14 preview - 支持 vacuum 强制加速参数 vacuum_failsafe_age , vacuum_multixact_failsafe_age. 防止 xid wraparound》
• 《PostgreSQL 14 preview - TOAST 支持 lz4 压缩算法 - --with-lz4 , 新增GUC default_toast_compression》
• 《PostgreSQL 14 preview - copy from 导入数据支持 batch, 大幅减少conversion/verification function calls, 支持并行处理》
• 《PostgreSQL 14 preview - copy freeze 增强, pgbench 支持copy freeze 大幅提升导入性能》
• 《PostgreSQL 14 preview - COPY 支持visibility map及时更新》
• 《PostgreSQL 14 减少系统调用 - Use pg_pread() and pg_pwrite() for data files and WAL - Use pg_pread() and pg_pwrite() in slru.c》
• 《PostgreSQL 14 preview - 向量化IO功能 (pwritev, preadv) , IO性能增强》
• 《PostgreSQL 14 preview - Improve performance of Unicode {de,re}composition in the backend》

• 《PostgreSQL 14 preview - 大表search IN ( consts ) - linear search TO hash table probe (consts 个数>= MIN_ARRAY_SIZE_FOR_HASHED_SAOP)》

• Improve the performance of COPY FROM in binary mode (Bharath Rupireddy, Amit Langote)

5、监控

• 《PostgreSQL 14 preview - 支持 SQL 指纹(规则化, 唯一ID): query id - GUC : compute_query_id》
• 《PostgreSQL 14 preview - log_connections 支持打印更多内容, pg_hba第几行, 使用什么认证方法等, 方便判断客户通过什么方式在与数据库进行登陆认证》
• 《PostgreSQL 14 preview - 查看backend process的memory context》
• 《PostgreSQL 14 preview - 打印其他会话的memory context, 诊断内存消耗问题 - pg_backend_memory_contexts(pid)》
• 《PostgreSQL 14 preview - autovacuum 打印更多信息, 每个索引的stats被打印》
• 《PostgreSQL 14 preview - log增强 : auto-vacuum and auto-analyze 记录IO timing以及analyze read|dirty rate》
• 《PostgreSQL 14 preview - automatic analyze: readahead - add "IO read time" log message》

6、系统视图

• 《PostgreSQL 14 preview - pg_stat_progress_copy增强 , COPY 导入数据支持进度监控 , 导入多少行, 排除多少行(where filter)》
• 《PostgreSQL 14 preview - 支持copy命令进度查询 - pg_stat_progress_copy》
• 《PostgreSQL 14 preview - 新增pg_stat_wal统计信息视图》
• 《PostgreSQL 14 preview - pg_stat_wal 支持实例级wal统计wal_records, wal_fpi and wal_bytes》
• 《PostgreSQL 14 preview - pg_locks 增加 wait_start 字段, 跟踪锁等待开始时间》
• 《PostgreSQL 14 preview - SaaS特性 - 会话建立、资源消耗时间片 统计 - pg_stat_database 指标增加》
• 《PostgreSQL 14 preview - pg_stat_database 新增会话消耗统计 - 如何分析数据库有没有性能瓶颈, 瓶颈是什么? top瓶颈是什么? 如何优化?》
• 《PostgreSQL 14 preview 新增 replication slot 监控 - pg_stat_replication_slots》
• 《PostgreSQL 14 支持绑定变量语句(prepared statements)的 custom_plans 与 generic_plans 次数统计》
• 《PostgreSQL 14 preview - 新增sql feature支持 - 函数,存储过程 对象依赖跟踪 - Routine usage information schema tables》
• 《PostgreSQL 14 preview - pg_stat_activity 显示 walsender 进程流复制command》
• 《PostgreSQL 14 preview - Add information of total data processed to replication slot stats - 逻辑复制槽统计信息增加处理事务数和数据量计数器 total_txns total_bytes》
• 《PostgreSQL 14 preview - 活跃会话pg_stat_activity、进程进度条pg_stat_progress* 、等待事件wait_event等 信息 从 pgstat 代码 拆出》

打回特性:
- 《PostgreSQL 14 preview - 统计 generic/custom plan in pg_stat_statements - 硬解析、软解析统计》
- 《PostgreSQL 14 preview - 支持 lwlock blocking 诊断 - 增加 pg_lwlock_blocking_pid》

7、认证

• 《PostgreSQL 14 preview - SSL 客户端证书校验增加clientname CN DN 提取选项, 遵循 RFC2253》

8、服务端配置(GUC)

• 《PostgreSQL 14 preview - 支持会话超时参数 - idle_session_timeout》
• 《PostgreSQL 14 preview - libpq: 支持 Set Server Name Indication (SNI) for SSL connections》
• 《PostgreSQL 14 preview - ssl相关 : 支持配置 ”SSL吊销证书列表文件 - crl“ 指定目录. 相关参数: ssl_crl_dir , 相关libpq配置: sslcrldir》
• 《PostgreSQL 14 preview - log_recovery_conflict_waits - standby query&startup process conflict 恢复冲突 超时(timeout)配置,日志打印》
• 《PostgreSQL 14 preview - 参数 in_hot_standby 获取当前实例是否是standby角色》
• 《PostgreSQL 14 preview - 支持 restore_command 参数修改 reload生效, 无需重启实例》
• 《PostgreSQL 14 min_dynamic_shared_memory - Preallocate some DSM space at startup 提高并行计算性能, 降低并行计算内存分配overhead》
• 《PostgreSQL 14 支持显示指定hugepage 单页大小 huge_page_size》
• 《PostgreSQL 14 preview - 自定义GUC参数规范化》

• 《PostgreSQL 14 preview - 新增track_wal_io_timing参数, 支持wal日志buffer write, fsync IO等待时长统计 , pg_stat_wal》

• Change checkpoint_completion_target default to 0.9 (Stephen Frost)

  • The previous default was 0.5.
• Reduce the default value of vacuum_cost_page_miss (Peter Geoghegan)
  • This new default better reflects current hardware capabilities.

打回的特性:
- 《PostgreSQL 14 preview - 新增GUC参数enable_parallel_insert, 支持insert并行计算优化开关》
- 《PostgreSQL 14 preview - 并行计算场景增强 - Enable parallel SELECT for "INSERT INTO ... SELECT ..."》

二、流复制与备份恢复

• 《PostgreSQL 14 preview - 逻辑复制 支持长事务 流式发送接口, 已覆盖内置pub/sub logical replication - 规避长事务需要transaction end后才能往sub端发送解析结果》
• 《PostgreSQL 14 preview - Add a xid argument to the filter_prepare callback for output plugins - logical decoding》
• 《PostgreSQL 14 preview - 内置逻辑复制功能增强 , Allow multiple xacts during table sync in logical replication.》
• 《PostgreSQL 14 preview - 允许hot standby 作为pg_rewind的源库》
• 《PostgreSQL 14 preview - recovery 性能增强 - recovery_init_sync_method=syncfs - 解决表很多时, crash recovery 递归open所有file的性能问题 - 需Linux新内核支持》
• 《PostgreSQL 14 仅保留fast promtoe》
• 《PostgreSQL 14 Cache smgrnblocks() results in recovery - 提高恢复效率, 减少lseek调用》
• 《PostgreSQL 14 内置逻辑订阅支持binary传输格式》
• 《PostgreSQL 14 preview - 逻辑订阅报错信息增强 - Display the names of missing columns in error during logical replication.》
• 《PostgreSQL 14 preview - logical decoding 增加2pc 两阶段事务,xa事务 支持》
• 《PostgreSQL 14 preview - Add option to enable two_phase commits via pg_create_logical_replication_slot》
• 《PostgreSQL 14 preview - wal接收优化,无需等待startup process replay结束. Unnecessary delay in streaming replication due to replay lag》
• 《PostgreSQL 14 扩展逻辑decoding API 应对长事务逻辑复制优化 - Extend the logical decoding output plugin API with stream methods.》
• 《PostgreSQL 14 preview - pg_logical_emit_message() 往WAL写定制消息 , pgoutput 内置decode plugin可以解析这个消息了!》
• 《PostgreSQL 14 preview - Add truncate option to subscription commands - 支持逻辑订阅truncate本地表语法》
• 《PostgreSQL 14 preview - 支持循环WAL decoding buffer , 优化逻辑decoding性能》

• 《PostgreSQL 14 preview - 注意行为变化 - 当primary修改了某些standby 必须更小的参数时, 不会导致standby shutdown 了, 改成暂停恢复》

• Immediately WAL-log subtransaction and top-level XID association (Tomas Vondra, Dilip Kumar, Amit Kapila)

  • This is useful for logical decoding.
• Add function pg_get_wal_replay_pause_state() to report the recovery state (Dilip Kumar)
  • It gives more detailed information than pg_is_wal_replay_paused(), which still exists.
• Add the replication origin to the record returned by pg_last_committed_xact() (Movead Li)
• Add function pg_xact_commit_timestamp_origin() to return the commit timestamp and replication origin of the specified transaction (Movead Li)

打回特性:
- 《PostgreSQL 14 preview - recovery 加速, 支持prefetch 预读接下来要恢复的wal record相关的data block到shared buffer, 加速wal record+data block的合并过程》

三、SELECT, INSERT 语法

• 《PostgreSQL 14 preview - insert into on conflict语句中支持表别名, Allow table-qualified variable names in ON CONFLICT ... WHERE.》
• 《PostgreSQL 14 preview - SQL标准增强 (SEARCH and CYCLE clauses), 递归(CTE)图式搜索增加广度优先、深度优先语法, 循环语法 - breadth- or depth-first search orders and detect cycles》
• 《PostgreSQL 14 preview - 支持 GROUP BY DISTINCT》
• 《PostgreSQL 14 preview - 支持 SQL:2016 feature F404 "Range variable for common column names". - Allow an alias to be attached to a JOIN ... USING》
• 《PostgreSQL 14 preview - Exploring SQL command changes in PG-14》

四、Utility Commands

• 《PostgreSQL 14 preview - Enable parallelism in REFRESH MATERIALIZED VIEW》
• 《PostgreSQL 14 preview - vacuum PROCESS_TOAST 开关 (是否 vacuum 相关 toast)》
• 《PostgreSQL 14 preview - reindex 增加 tablespace 选项, 支持重建索引到指定表空间》
• 《PostgreSQL 14 preview - 内置订阅功能alter subscription语法增强,支持add/drop publication》

• 《PostgreSQL 14 preview - alter table 支持两阶段 routine 管理, 未来将基于此支持 无(短暂)锁 rewrite table(例如vacuum full, alter table rewrite table)》

• Add OR REPLACE for CREATE TRIGGER (Takamichi Osumi)

  • This allows pre-existing triggers to be conditionally replaced.
• Allow REINDEX to process all child tables or indexes of a partitioned relation (Justin Pryzby, Michael Paquier)

打回特性:
- 《PostgreSQL 14 preview - 只读barrier模式 - alter system read only|write》

五、Data Types

• 《PostgreSQL 14 preview - multirange type》

六、Functions

• 《PostgreSQL 14 preview - date_bin 任意起点, 任意bucket(interval) split bucket align 统计 - 时序场景(iot, 金融等), 关联timescaledb》
• 《PostgreSQL 14 preview - bit_count 计算比特位1的个数》
• 《PostgreSQL 14 preview - 新增 bit_xor 聚合函数》
• 《PostgreSQL 14 preview - Support negative indexes in split_part()》
• 《PostgreSQL 14 preview - unistr 函数, 支持Unicode escapes字符串》
• 《PostgreSQL 14 preview - 支持 trim array》
• 《PostgreSQL 14 preview - 支持 jsonb 下标语法, 支持set原子操作, 类似array下标》

七、PL/PgSQL

• 《PostgreSQL 14 preview - 支持 SQL-standard function body》

• Improve performance of repeated CALLs within plpgsql procedures (Pavel Stehule, Tom Lane)

• Improve PL/pgSQL's expression and assignment parsing (Tom Lane)
  • This adds nested record and array slicing support.
• Allow plpgsql's RETURN QUERY to execute its query using parallelism (Tom Lane)

八、Client Interfaces

• [《PostgreSQL 14 preview - pageinspAllow an ECPG SQL identifier to be linked to a specific connection (Hayato Kuroda)
• 《PostgreSQL 14 preview - libpq驱动 支持pipeline 模式, SQL请求支持异步化通信, 大幅度提升性能, 降低RT》

• 《PostgreSQL GUC_REPORT+libpq 实现客户端决策链路 - multi host + 客户端 target_session_attrs + 发起连接时即刻获取PG数据库当前状态 - libpq 协议级 驱动级 failover balance》

• Enhance libpq's target_session_attrs parameter options (Haribabu Kommi, Greg Nancarrow, Vignesh C, Tom Lane)

  • New options are "read-only", "primary", "standby", and "prefer-standby".

九、Client Applications

• 《PostgreSQL 14 preview - reindexdb 客户端支持指定tablespace》

1、psql

• 《PostgreSQL 14 preview - psql 快捷命令 df do 支持参数输入 , 按参数类型筛选 函数和操作符》
• 《PostgreSQL 14 preview - psql CLI 更新, 执行多条SQL时, 默认返回所有语句的执行结果》
• 《PostgreSQL 14 preview - psql客户端支持dX快捷命令, 查看自定义统计信息》
• 《PostgreSQL 14 preview - psql客户端增强, 支持desc toast表和索引》

2、pgbench

• 《PostgreSQL 14 preview - pgbench 支持冒号常量, 例如时间,timestamp常量》
• 《PostgreSQL 14 preview - pgbench 压测工具新增 随机函数 permute》

十、Server Applications

• 《PostgreSQL 14 preview - amcheck插件增加heap table数据页格式错误、逻辑错误检测功能》

十一、Documentation

• 《PostgreSQL 14 simplehash dynahash 优缺点文档更新》

• 《PostgreSQL 14 preview - data page checksum 文档增强》

• Add documentation for the factorial() function (Peter Eisentraut)

  • With the removal of the ! operator in this release, factorial() is the only built-in way to compute a factorial.

十二、Source Code

• 《PostgreSQL 14 preview - 支持扩展ssl库》
• 《PostgreSQL 14 preview - 支持debug参数 - 强制不使用system catalog cache - debug_invalidate_system_caches_always》
• 《PostgreSQL 14 preview - Add support for abstract Unix-domain sockets》
• 《PostgreSQL 14 preview - tid range scan方法, 一种page级别应用可自控的并行扫描、处理方法, 结合RR snapshot export功能》
• 《PostgreSQL 14 preview - Use full 64-bit XIDs in deleted nbtree pages. 像蜗牛一样迭代64-bit xid》
• 《PostgreSQL 14 preview - 基于共享内存的进程间通信、进程统计信息系列Patch即将来临, archiver进程已加入.》
• 《PostgreSQL 14 preview - wait event 移除对MyProc的依赖, 使用共享内存 管理 等待事件变量 , 未来更多统计信息相关内容将通过共享内存管理》
• 《PostgreSQL 14 引入WaitLatch(), 避免每次等待时需要 epoll/kqueue 系统调用.》
• 《PostgreSQL 14 preview - PageIsVerified() 接口扩展, 支持跳过shared buffer读数据》
• 《PostgreSQL 14 preview - vacuum 状态跟踪代码简化和增强, Simplify state managed by VACUUM.》
• 《PostgreSQL 14 preview - hash 函数生成代码增强 - src/tools/PerfectHash.pm》

• 《PostgreSQL 14 preview - 增强语法解析器 例如复杂类型数组的底层结构赋值 - Add the ability for the core grammar to have more than one parse target.》

• Various improvements in valgrind detection (Álvaro Herrera, Peter Geoghegan)

• Add support for LLVM 12 (Andres Freund)
• Add "amadjustmembers" to the index access method API (Tom Lane)

十三、Additional Modules

• 《PostgreSQL 14 preview - corrupted tuple 修复功能 - pg_surgery》
• 《PostgreSQL 14 preview - 新增 old_snapshot 插件, 打印快照跟踪条目(每分钟一条, OldSnapshotTimeMapping结构)的内容 , old_snapshot_threshold 相关》
• 《PostgreSQL 14 preview - cube 支持 binary IO 接口函数 cube_recv cube_send》
• 《PostgreSQL 14 preview - FDW 外部表接口支持 truncate only|cascade , postgres_fdw已实现, 基于FDW的sharding 功能更进一步》
• 《PostgreSQL 14 preview - postgres_fdw 支持 hold foreign server 长连接, fdw sharding再进一步 - postgres_fdw: Add option to control whether to keep connections open》
• 《PostgreSQL 14 preview - postgres_fdw 新增discard cached foreign server连接的接口函数》
• 《PostgreSQL 14 preview - FDW 支持bulk insert API(GetForeignModifyBatchSize,ExecForeignBatchInsert) - postgres_fdw 外部表实现bulk insert接口》
• 《PostgreSQL 14 preview - postgres_fdw 新增 postgres_fdw_get_connections 函数, 获取当前外部表连接》
• 《PostgreSQL 14 preview - postgres_fdw 异步append - sharding 性能增强 - 实现真正意义上跨库并行计算》
• 《PostgreSQL 14 preview - postgres_fdw 自动重建链接 - when broken when starting new remote transaction.》
• 《PostgreSQL 14 preview - 支持通过import foreign schema 导入指定远端分区表的子分区 - postgres_fdw , import foreign schema 支持 limit to (子分区)》
• 《PostgreSQL 14 preview - pageinspect 内窥heap,index存储结构 , 新增对gist索引的支持》
• 《PostgreSQL 14 preview - pageinspect 新增gist索引inspect, 同时支持 lp_dead 展示》
• 《PostgreSQL 14 pg_stat_statements 支持 DDL语句 影响记录数跟踪统计》
• 《PostgreSQL 14 preview - pg_stat_statements 增加 toplevel 字段, 表示这是一条嵌套SQL还是top sql. - pg_stat_statements.track = all 时》
• 《PostgreSQL 14 preview - pg_stat_statements: Track number of times pgss entries were deallocated. - 跟踪是否需要调大 pg_stat_statements.max》

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.