postgresql 14新特性整理

最新推荐文章于 2024-09-28 22:42:44 发布

Hehuyi_In

最新推荐文章于 2024-09-28 22:42:44 发布

阅读量4.1k

点赞数

分类专栏： PostgreSQL 新特性文章标签：数据库 postgresql pg 14 新特性

原文链接：https://www.postgresql.org/docs/14/release-14.html

版权

PostgreSQL 同时被 2 个专栏收录

141 篇文章 125 订阅

订阅专栏

新特性

22 篇文章 0 订阅

订阅专栏

本文整理一些工作中可能会用到的PostgreSQL 14新特性，有删减，完整版请参考官方文档PostgreSQL: Documentation: 14: E.7. Release 14。

一、 Server

新增预定义角色 pg_read_all_data 和 pg_write_all_data，授予所有 tables, views, sequences的读、写权限
新增预定义角色pg_database_owner ，包含当前db的owner权限，在临时数据库中特别有用

PostgreSQL: Documentation: 14: 22.5. Predefined Roles

后台进程崩溃后，删除相关临时文件。之前版本会保留这些文件用于debug，可由remove_temp_files_after_crash参数控制
如果客户端断开连接，允许长时间运行的SQL被取消（Allow long-running queries to be canceled if the client disconnects），由client_connection_check_interval参数控制
pg_terminate_backend()函数新增了可选的超时参数
允许宽元组（wide tuples）总是被加入到近乎为空的heap page。在之前的版本，超出页的fill factor参数后，元组会被插入到新页

1. Vacuum

当索引中可清理的entries非常少，vacuum可以跳过这些index提高速度，该功能由INDEX_CLEANUP参数控制
允许vacuum更迫切地将被删除的btree page加入fsm文件，之前版本只能将被上次vacuum标记为deleted的页加入fsm文件（14版本则是本次vacuum标记的也可以）。
允许vacuum回收无用的尾部堆指针（trailing heap line pointers）
允许vacuum在CREATE INDEX CONCURRENTLY,REINDEX CONCURRENTLY等minimal-locking index操作时清理死元组
加速对有非常多表的db的vacuum操作
减小vacuum_cost_page_miss的默认值，以更好地反应当前硬件能力
新增跳过vacuum TOAST 表的能力，VACUUM新增了PROCESS_TOAST选项，而vacuumdb新增了--no-process-toast选项
COPY FREEZE可以在导入数据时相应更新页的可见性bit
Vacuum操作在表的xid或multixact 接近回卷时会更激进，由vacuum_failsafe_age和vacuum_multixact_failsafe_age参数控制
增加了xid或multixact 接近回卷时的告警时间
在autovacuum logging output中增加了每个索引（per-index）信息

2. 分区

提升了具有大量分区的分区表的update和delete性能
可以在线进行detach操作 ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, and FINALIZE
分区边界值忽略COLLATE子句

3. 索引

btree索引可以删除过期的index entries，避免页分裂，防止频繁更新的索引快速膨胀
BRIN索引（Block Range Index）可以在每个range记录多个 min/max values
PostgreSQL黑科技BRIN索引 - 墨天轮
BRIN索引可以使用bloom filters
GiST索引可以在创建过程中预排序数据
SP-GiST索引可以使用include子句包含列

4. 优化器

in子句包含大量常量时，可以用hash查找（以前只能顺序扫描值列表）
or子句、表达式也可以使用扩展统计信息，可以查询pg_stats_ext_exprs视图
更高效的TIDs堆范围扫描
修复EXPLAIN CREATE TABLE AS 和 EXPLAIN CREATE MATERIALIZED VIEW的问题，之前如果对象已存在，则会报错

5. 通用性能

提升了系统在有大量CPU和高连接数时计算MVCC可见性快照的速度，也提升了有大量空闲会话时的性能
新增执行器方法以记录nested-loop join内侧的结果集（如果内侧结果集小效果会更好），由enable_memoize参数配置是否启用
允许窗口函数使用增量排序
提升了并行顺序扫描的IO性能
允许查询引用多个外部表，以实现外部表并行扫描，postgres_fdw需要设置async_capable参数
允许analyze命令做page prefetching ，由maintenance_io_concurrency参数控制
提升正则表达式查询性能
极大提升了Unicode normalization性能
TOAST 数据新增了LZ4压缩算法，编译pg时必须指定--with-lz4

6. 监控

如果设置了compute_query_id参数，将在pg_stat_activity, EXPLAIN VERBOSE, csvlog, log_line_prefix中展示查询id PostgreSQL 14数据库监控和日志功能的加强 - abce - 博客园
如果设置了track_io_timing参数，将在 auto-vacuum 和auto-analyze 中记录IO时间，并在auto-analyze 中记录buffer read和脏读占比
在log_connections输出中增加客户端原始用户名

7. 系统视图

新增pg_stat_progress_copy 视图记录COPY进度
新增pg_stat_wal视图记录WAL活动

新增pg_stat_replication_slots 视图记录复制槽活动
新增pg_backend_memory_contexts 视图记录会话内存使用情况

新增pg_log_backend_memory_contexts()函数输出arbitrary backends内存上下文使用情况
pg_stat_database视图新增会话状态

pg_prepared_statements视图新增generic 和custom plan 计数

pg_locks 视图新增锁等待开始时间 waitstart
pg_stat_activity视图可以看到归档进程信息
新增等待事件WalReceiverExit，表示WAL receiver退出的等待时间

8. 验证连接

pg_hba.conf 和pg_ident.conf记录可以跨多行
密码允许使用任意长度

9. 参数设置

新增idle_session_timeout参数关闭空闲连接，跟idle_in_transaction_session_timeout的区别是会话不需要开启事务，只判断空闲超时
checkpoint_completion_target 默认值调整为 0.9，之前是0.5
log_line_prefix可以使用%P，表示parallel group leader's PID
新增huge_page_size参数，控制Linux大页使用的size

二、流复制与恢复

允许使用pg_rewind回退从库（之前不是也可以？）
restore_command 参数在 server reload后生效
新增log_recovery_conflict_waits参数，报告超长的恢复冲突等待时间
如果主库修改的参数阻止了从库replay，从库将暂停恢复操作（之前从库会立刻关闭）
新增pg_get_wal_replay_pause_state() 函数报告recovery状态，信息比pg_is_wal_replay_paused()更丰富
新增只读服务器参数in_hot_standby ，使客户端更容易检测到自己连接的是不是从库
加快有大shared buffers的数据库recovery期间小表的truncate速度
新增pg_xact_commit_timestamp_origin() 函数，返回特定事务的提交时间
新增pg_last_committed_xact()函数，返回复制源事务信息
复制源函数可以使用标准函数的权限控制（之前只有超级用户能执行）

1. 逻辑复制

目前没有用到过这块，略

2. SELECT,INSERT

减少不使用AS则不能用作列标签的关键字数量（减少了90%）
允许为JOIN的USING子句指定别名（在USING后使用AS指定）
允许将DISTINCT添加到GROUP BY删除重复的GROUPING SET组合，例如 GROUP BY CUBE (a,b), CUBE (b,c)，如果不使用DISTINCT将会有重复数据
正确处理INSERT多行VALUES时的DEFAULT值，过去常常报错
为CTE添加SQL标准 SEARCH和CYCLE子句
WHERE允许子句中的列名ON CONFLICT是表限定的，但是只能引用目标表

3. 命令使用

允许REFRESH MATERIALIZED VIEW使用并行
允许REINDEX更改新索引的表空间
允许REINDEX处理分区表的所有子表和索引
索引CONCURRENTLY不需要再等待其他命令的CONCURRENTLY（不相互阻塞）
提高COPY FROM二进制模式的性能
GRANT 和 REVOKE新增了SQL标准子句GRANTED BY
CREATE TRIGGER 新增了OR REPLACE 选项
允许TRUNCATE外部表，postgres_fdw模块也支持
允许更轻松地向订阅添加和删除发布 ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION
将主键、唯一约束和外键添加到system catalogs

4. 数据类型

允许扩展和内置数据类型实现下标，之前只能用于数组类型
允许订阅JSONB类型数据
添加对多范围数据类型（multirange data types）的支持

5. 函数

允许函数和存储过程使用标准SQL
存储过程新增OUT参数
允许一些数组函数操作混合数据类型，例如 array_append(), array_prepend()等等
新增数组截取函数trim_array()，参考PostgreSQL: Documentation: 14: 9.19. Array Functions and Operators
新增bytea函数，等于ltrim() 和rtrim() ，即去除左右空格
split_part()函数支持负数下标，参考PostgreSQL: Documentation: 14: 9.4. String Functions and Operators
新增string_to_table() 函数，将字符串拆分并转换为表，参考PostgreSQL: Documentation: 14: 9.4. String Functions and Operators
substring() 函数新增语法 SUBSTRING(text SIMILAR pattern ESCAPE escapechar)

三、客户端与服务端工具

1. 客户端应用

vacuumdb新增--no-index-cleanup 和--no-truncate选项，支持跳过索引清理和truncate
pg_dump新增—extension选项，导出特定插件
pg_dump, pg_dumpall和pg_restore新增 –v选项，输出详细过程
psql使用\df和\do可以查看特定函数参数类型
psql使用\dt和\di可以查看TOAST 表和索引类型
psql使用\dX可以查看扩展统计信息对象

2. 服务端

新增命令行工具pg_amcheck 用于检查数据损坏，简化在大量表上运行contrib/amcheck的操作。另外amcheck插件之前只能检查B-Tree索引页，pg 14中支持了检查heap pages
pg_upgrade 删除了analyze_new_cluster 脚本，在vacuumdb有类似的操作
postmaster 删除了-o选项