张的博客

1. 版本策略与生命周期

每年发布一次包含新特性的主要新版本。每个主要版本都会收到错误修复，如果需要的话，还会收到安全修复，这些修复至少每三个月发布一次，称之为“次要版本”

主要版本发布后的5年内支持该版本。

升级: 从一个主板本升级到另一个主板本，无需通过中间版本

使用版本选择: 建议选择上一年度, 超过1年的版本号, 如2024年6月, 可以选择16; 保守一点可以选择2年的版本, 15;

版本	发布日期
17-beta	2024-05-23
16	2023-09-14
15	2022-10-13
14	2021-09-30
13	2020-09-24
12	2019-10-03
11	2018-10-18
10	2017-10-05

2. postgre17

2.1. 新特性总结

查询和操作性能改进
分区和分布式工作负载增强, 增加了拆分和合并分区的功能，并增加了对分区表的标识列和排除约束的支持。
开发人员体验, 增加了对JSON_TABLE可以将 JSON 转换为标准 PostgreSQL 表的功能以及 SQL/JSON 构造函数（JSON、JSON_SCALAR、JSON_SERIALIZE）和查询函数（JSON_EXISTS、JSON_QUERY、JSON_VALUE）的支持
MERGE命令现在支持RETURNING子句
COPY用于高效地批量加载和导出 PostgreSQL 数据，现在使用 PostgreSQL 17，导出大行时性能可提高 2 倍。
安全功能, 添加了一个新的连接参数 sslnegotiation，它允许 PostgreSQL 在使用 ALPN 时执行直接 TLS 握手，从而消除了网络往返。
备份和导出管理, 引入了使用执行增量备份的功能pg_basebackup
监控, 规范化了中的参数pg_stat_statements，减少了频繁调用的存储过程的条目数。引入了一个新视图，pg_wait_events
附加功能, https://www.postgresql.org/docs/17/release-17.html

3. postgre16

3.1. 新特性总结

性能提升 - 新的查询规划器, 可以并行执行FULL和 RIGHT连接
性能提升 - COPY进行批量加载的改进, 提升高达300%
性能提升 - 增加了对使用libpq的客户端的负载均衡支持
性能提升 - 引入了在x86和ARM架构上使用 SIMD 的CPU加速
逻辑复制 - 用户可以从备节点（standby）执行逻辑复制
逻辑复制 - 支持双向逻辑复制功能
开发者体验 - 添加了更多SQL/JSON标准的语法，包括构造函数和谓词
开发者体验 - 提供更多 psql 命令，包括\bind
开发者体验 - 规定如何排序文本的文本排序规则（text collations）进行了改进
监控 - 关键性新指标pg_stat_io，用于详细分析I/O访问模式。
监控 - pg_stat_all_tables视图中添加了一个新字段，该字段记录了最后一次扫描表或索引的时间戳。
访问控制与安全性 - 对pg_hba.conf和pg_ident.conf的管理做了改进，包括允许使用正则表达式匹配用户和数据库名称，并支持使用include指令来引入外部配置文件
访问控制与安全性 - 添加了几个有关安全性的客户端连接参数
访问控制与安全性 - 增加了对 Kerberos 信任委托的支持

3.1.1. 性能提升

PostgreSQL 16通过新的查询规划器优化提升了现有PostgreSQL功能的性能。在该最新版本中，查询规划器可以并行执行FULL和 RIGHT连接，为使用带有DISTINCT或ORDER BY子句的聚合函数的查询生成更优的执行计划，利用增量排序来处理SELECT DISTINCT查询，并优化窗口函数，使其执行更加高效。它还改进了RIGHT和OUTER“反连接（anti-joins）”，使用户能够识别出不在已连接表中的数据行。
该版本包含单一和并发操作中使用COPY进行批量加载的改进，测试显示在某些情况下性能提升高达300%。PostgreSQL 16增加了对使用libpq的客户端的负载均衡支持，并改进了vacuum策略，减少全表冻结的必要性。此外，PostgreSQL 16引入了在x86和ARM架构上使用 SIMD 的CPU加速，从而在处理ASCII和JSON字符串以及执行数组和子事务搜索时，性能有所提升。

3.1.2. 逻辑复制

逻辑复制允许用户将数据流复制到其他可以解析PostgreSQL逻辑复制协议的节点或订阅者。在PostgreSQL 16中，用户可以从备节点（standby）执行逻辑复制，这意味着备节点可以将逻辑变更发布到其他服务器。这为开发者提供了新的工作负载分布选项——例如，使用备节点而不是更繁忙的主节点通过逻辑复制将更改应用到下级订阅端。
此外，PostgreSQL 16中对逻辑复制进行了多项性能改进。订阅者现在可以使用并行方式来处理大型事务。对于没有主键的表，订阅者可以使用B-tree索引而不是顺序扫描来查找行。在某些条件下，用户还可以使用二进制格式加速初始表同步。
PostgreSQL 16逻辑复制的访问控制做了多项改进，包括新的预定义角色 pg_create_subscription，该角色允许用户新建逻辑订阅。
该版本开始支持双向逻辑复制功能，可以在两个不同发布者的表之间进行数据复制。

3.1.3. 开发者体验

PostgreSQL 16 添加了更多SQL/JSON标准的语法，包括构造函数和谓词，比如 JSON_ARRAY()、JSON_ARRAYAGG() 和 IS JSON。该版本允许使用下划线作为千位分隔符（例如 5_432_000），并支持非十进制整数常量（如 0x1538、0o12470和0b1010100111000）。
PostgreSQL 16 为开发者提供更多 psql 命令，包括\bind，该命令允许用户使用带参数的查询，并使用 \bind 来代替变量（例如 SELECT $1::int + $2::int \bind 1 2 \g）。
PostgreSQL 16 对规定如何排序文本的文本排序规则（text collations）进行了改进。PostgreSQL 16构建（Build）时默认启用ICU（国际化组件），并从系统环境中确定默认的ICU区域设置，允许用户自定义ICU排序规则。

3.1.4. 监控

理解I/O操作对系统的影响是优化数据库工作负载性能的一个关键方面。PostgreSQL 16 引入了一项与I/O操作相关的关键性新指标pg_stat_io，用于详细分析I/O访问模式。
此外，该版本在pg_stat_all_tables视图中添加了一个新字段，该字段记录了最后一次扫描表或索引的时间戳。PostgreSQL 16通过记录语句中传进来的参数值，提升了auto_explain的可读性，以及pg_stat_statements和pg_stat_activity使用查询跟踪算法的准确性。

3.1.5. 访问控制与安全性

PostgreSQL 16 提供了更精细的访问控制选项，并增强了相关安全功能。该版本对pg_hba.conf和pg_ident.conf的管理做了改进，包括允许使用正则表达式匹配用户和数据库名称，并支持使用include指令来引入外部配置文件。
该版本添加了几个有关安全性的客户端连接参数，包括require_auth，它允许客户端指定可接受的来自服务器端的身份验证参数，以及sslrootcert="system"，该参数表示PostgreSQL将使用客户端操作系统提供的可信证书（CA）。此外，该版本增加了对 Kerberos 信任委托的支持，允许诸如 postgres_fdw 和 dblink 这样的扩展（extension）使用经过身份验证的凭证连接到受信任的服务。

4. postgre15

4.1. 新特性总结

4.1.1. 改进的排序性能和压缩

在最新版本中，PostgreSQL 改进了其内存和磁盘上的排序算法。测试基准显示：根据不同的数据类型，此次排序优化大概可加速 25% - 400%。
在 PostgreSQL 15 中，使用 row_number ()、rank ()、dense_rank () 和 count () as 窗口函数也具有性能优势。
使用 SELECT DISTINCT 的查询可以并行执行。
PostgreSQL 15 添加了对 LZ4 和 Zstandard (zstd) 压缩到预写日志 (WAL) 文件的支持，对于某些工作负载可以同时具有空间和性能优势。
在某些操作系统上，PostgreSQL 15 增加了对 WAL 中引用的预取页面的支持。
PostgreSQL 的内置备份命令 pg_basebackup 现在支持备份文件的服务器端压缩，可选择 gzip、LZ4 和 zstd 等压缩方法。
PostgreSQL 15 拥有使用自定义模块进行归档的能力，消除了使用 shell 命令的开销。

4.1.2. 新的开发者功能

PostgreSQL 15 包含 SQL 标准 MERGE 命令。MERGE 允许编写条件 SQL 语句，这些语句可以在单个语句中包含 INSERT、UPDATE 和 DELETE 操作。
PostgreSQL 15 还增加了使用正则表达式检查字符串的新函数：regexp_count ()、regexp_instr ()、regexp_like () 和 regexp_substr ()。
PostgreSQL 15 还扩展了 range_agg 函数，可聚合多范围数据类型。
PostgreSQL 15 允许用户使用调用者（view caller）来创建查询数据的视图，而不是视图创建者的权限。这个选项称为 security_invoker，它增加了一个额外的保护层，以确保视图调用者拥有处理底层数据的正确权限。

4.1.3. 更多逻辑复制选项

PostgreSQL 15 为管理逻辑复制提供了更大的灵活性。

这个版本为发布者引入了行筛选和列列表，允许用户选择从表中复制数据的子集。
PostgreSQL 15 增加了简化冲突管理的功能，包括跳过重播冲突事务的能力，以及在检测到错误时自动禁用订阅的能力。
该版本还支持在逻辑复制中使用两阶段提交 (2PC)。

4.1.4. 日志和配置增强

PostgreSQL 15 引入了一种新的日志格式：jsonlog。这种新格式使用定义的 JSON 结构输出日志数据，允许在结构化日志系统中处理 PostgreSQL 日志。
在用户管理 PostgreSQL 配置方面，PostgreSQL 15 版本为数据库管理员提供了更大的灵活性，增加了向用户授予更改服务器级配置参数的权限的能力。
此外，用户现在可以使用 psql 命令行工具中的 \dconfig 命令搜索有关配置的信息。

4.1.5. 其他变化

PostgreSQL 15 使 ICU 排序规则成为集群或单个数据库的默认排序规则成为一种可能。
该版本添加了一个新的内置扩展 pg_walinspect，它允许用户直接从 SQL 接口检查预写日志文件的内容。
PostgreSQL 15 还从公共 (或默认) 模式的所有用户（数据库所有者之外）撤销 CREATE 权限。
PostgreSQL 15 从 PL/Python 包中删除了长期被弃用的 “独占备份” 模式和对 Python 2 的支持。

4.2. 排序性能改进

我还想强调在内存和磁盘上引入排序的重要性能改进。

4.2.1. 内存排序

不仅提高了内存中排序的性能，而且还对其进行了优化以减少内存消耗。

单列排序的性能提高了 25% 以上。这仅适用于结果包含单个列的情况。
例如，此改进将适用于SELECT col1 from mytab ORDER BY col1；, 但不是SELECT col1, col2 from mytab ORDER BY col1; .
通过使用生成内存上下文减少了内存使用量。
我们使用的内存分配方案将请求四舍五入为 2 的下一个幂。性能改进取决于元组大小，但已观察到高达 40% 的改进。
通过为常见数据类型添加专门的排序例程，减少了函数调用开销。
观察到的改进约为 5%。

4.2.2. 超过 work_mem 的排序

通过切换到使用比以前更多的输出流的批量排序算法，超过 work_mem 的排序性能也得到了改进。改进取决于 work_mem – 值越小，改进越大，观察者有 ~40% 的改进。

5. postgre14

官方链接: PostgreSQL: Documentation: 14: E.13. Release 14
其它总结文章:
https://blog.csdn.net/Hehuyi_In/article/details/104244500
https://developer.aliyun.com/article/849253

5.1. 新特性总结

性能增强, 大量连接高并发优化, 索引增强, 并行计算增强, 内置sharding功能接口，postgres_fdw 持续增强, 分区表性能趋近完美, 大表 search IN (consts) 优化, TOAST 支持 lz4 压缩算法, 引入管道查询模式
数据类型和 SQL, 支持multi range类型, 兼容range类型已知的所有操作符和索引, 支持JSONB下标语法, 支持存储过程OUT参数, 递归(CTE)图式搜索增加广度优先、深度优先语法和循环语法, 增加date_bin函数, 支持tid range scan扫描方法, SQL命令增强
管理功能, 垃圾回收增强, 提高统计信息数据采集速度, 系统视图和管理函数增强, 新增 GUC 参数
流复制与备份恢复, 逻辑复制增强, 允许hot standby 作为pg_rewind的源库, 增加 remove_temp_files_after_crash GUC参数, standby wal receiver 接收时延优化
安全增强, 新增 pg_read_all_data 和 pg_write_all_data 角色, 默认使用 SCRAM-SHA-256 认证方法,

5.1.1. 数据类型

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

 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') );
datemultirange                                                                                                           |
-------------------------------------------------------------------------------------------------------------------------+
{[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)
为系统编目创建复合阵列类型，用户定义的关系早就有与之关联的复合类型，以及这些复合类型上的数组类型。系统目录现在也可以。此更改还修复了在单用户模式下创建用户定义表将无法创建复合数组类型的不一致性。

5.1.2. 函数

PostgreSQL 14 增加了新的 date_bin 函数，可以用于将时间戳按照指定的间隔进行对齐（一种称为“装箱”的技术）。例如：
允许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)，以前，它们经常返回下溢错误。

5.1.3. 管理功能

添加系统视图pg_stat_progress_copy，报告COPY进度
PostgreSQL 14 增加了连接管理相关的几个参数，包括 idle_session_timeout（设置关闭空闲连接的等待时间）以及 client_connection_check_interval （允许 PostgreSQL 在客户端断开后取消长时间运行的查询）。
REINDEX 命令现在支持分区表的子索引，同时 PostgreSQL 14 增加了一个新的使用工具 pg_amcheck ，用于检查数据损坏。
向pg_prepared_语句添加列，以报告通用和自定义计划计数
PostgreSQL 14 针对 VACUUM 命令进行了许多改进，包括索引的优化。Autovacuum 现在可以分析分区表，并且能够将数据行的统计信息关联到父表。另外，通过调整 maintenance_io_concurrency 参数可以改善 ANALYZE 命令的性能。that can be controlled with parameter.

5.1.4. 复制和恢复

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

5.1.5. 索引

允许添加 btree 索引以删除过期的索引条目以防止页面拆分，有利于减少索引列频繁更新的表上的索引膨胀。
允许BRIN索引在每个范围内记录多个最小值/最大值，如果每个页面范围中都有一组值的时候非常有用。
允许BRIN索引使用布隆过滤器，这使得BRIN索引可以有效地用于堆中没有很好本地化的数据。
允许通过对数据进行预排序来构建一些GiST索引，预排序是自动进行的，允许更快的索引创建和更小的索引。
允许SP-GiST索引包含INCLUDE’d 列

6. postgre13

6.1. 新特性总结

对vacumm增加了并行的功能
改变流复制的配置可以不用重启数据库了
更多的一些情况下可以对分区进行裁剪和智能join
如原先智能join必须两个分区的范围精确相同，现在可以更智能了。
三个表的full outer join也可以走wise join
分区智能join是从PostgreSQL 11版本添加的功能
分区表可以支持before trigger(不允许改变插入数据的目标分区）
分区表可以支持逻辑复制了
之前只能把分区表的各个分区单独的做为复制源，现在可以把分区表直接做为复制源。
先前订阅者只能把数据同步到非分区表，现在可以把数据同步到分区表
Allow whole-row variables (that is, table.) to be used in partitioning expressions (Amit Langote)
支持异构分区表逻辑复制
索引中重复的项做了优化处理，更节省空间。重复的项只存储一次
聚合时使用hash算法可以使用磁盘做溢出存储
增量排序（Incremental sort）的功能
提升了PL/pgSQL中简单表达式的性能
pg_stat_statements插件增加了选项可以跟踪SQL的planning time，而不仅仅是执行时间

6.2. 分区表智能join

6.2.1. 不要求分区的范围完全相等

具体可见：advanced partition matching algorithm for partition-wise join

看例子：

text

create table t1(id int) partition by range(id);
create table t1_p1 partition of t1 for values from (0) to (100);
create table t1_p2 partition of t1 for values from (150) to (200);
create table t2(id int) partition by range(id);
create table t2_p1 partition of t2 for values from (0) to (50);
create table t2_p2 partition of t2 for values from (100) to (175);

然后我们分别在PostgreSQL 12版本和PostgreSQL 13执行下面的SQL:

text

explain select * from t1, t2 where t1.id=t2.id;

对比如下：

PostgreSQL13版本和PostgreSQL12版本的分区表范围对比

6.2.2. 三个分区表full outer join也智能join

看例子：

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);
set enable_partitionwise_join to on;

PostgreSQL13版本和PostgreSQL12版本的分区表智能join

6.3. 索引消除重复项

PostgreSQL 13中对索引的重复的项做了优化处理，更节省空间。重复的项只存储一次。

看例子：

PG13索引的大小：

text

postgres=# create table test01(id int, id2 int);
CREATE TABLE
postgres=# insert into test01 select seq, seq / 1000 from generate_series(1, 1000000) as seq;
INSERT 0 1000000
postgres=# create index idx_test01_id2 on test01(id2);
CREATE INDEX
postgres=# \timing
Timing is on.
postgres=# select pg_relation_size('idx_test01_id2');
 pg_relation_size
------------------
 7340032
(1 row)

如果是PG9.6:

text

postgres=# select pg_relation_size('idx_test01_id2');
 pg_relation_size
------------------
 22487040
(1 row)

可以看到索引的大小是以前的三分之一。

索引中去除重复项的原理：

类似倒排索引GIN，一个索引的key值，对应多个物理行。
pg_upgrade升级数据库后，需要reindex才能让旧索引使用到此特性

有一些情况可能无法去除重复项：

numeric不能使用去重
jsonb类型不能使用去重
float4和float8不能使用去重
INCLUDE indexes不能使用去重
text, varchar, and char 类型的索引使用了非确定性排序（nondeterministic collation)
Container types (such as composite types, arrays, or range types) cannot use deduplication.

给索引增加了存储参数deduplicate_items以支持这个功能。

6.4. 聚合时使用hash算法可以使用磁盘做溢出存储

以前当表特别大时，hash表超过work_mem的内存时，聚合时就走不到hash，只能走排序的算法，而排序聚合比hash聚合通常慢几倍的性能，现在有了用磁盘存储溢出的hash表，聚合的性能大大提高
同时增加了参数hash_mem_multiplier，hasn聚合的内存消耗现在变成了work_mem* hash_mem_multiplier，默认此参数hash_mem_multiplier为1，即hash表的大小还是以前的大小

现在使用了 HyperLogLog算法来估算唯一值的个数，减少了内存占用。

请看例子：

text

CREATE TABLE t_agg (x int, y int, z numeric);
INSERT INTO t_agg SELECT id % 2, id % 10000, random() 
    FROM    generate_series(1, 10000000) AS id;
VACUUM ANALYZE; 
SET max_parallel_workers_per_gather TO 0;
SET work_mem to '1MB';
explain analyze SELECT x, y, avg(z) FROM t_agg GROUP BY 1,2;

在12.4版本中聚合使用了排序算法，时间花了14.450秒，如下图所示：

PostgreSQL12.4版本中，聚合使用了排序算法花费的时间图

而在13版本中，走了hash聚合，时间花了6.186秒，时间缩短了一半还多，如下图所示：

PostgreSQL13版本中，走了hash聚合花费的时间图

6.5. 增量排序（Incremental sort）的功能

官方手册中也有例子：https://www.postgresql.org/docs/13/using-explain.html#USING-EXPLAIN-BASICS

见我们的例子：

text

create table test01(n1 int, n2 int);
insert into test01 select seq/3, (seq / 97) % 100 from generate_series(1, 4000000) as seq;
create index idx_test01_n1 on test01(n1);
analyze test01;

然后分别在PostgreSQL 12版本和PostgreSQL 13版本下看下面SQL的执行计划和执行时间：

text

explain analyze select * from test01 order by n1, n2;

可以看到使用了增量排序后，速度更快了。在PG13中为1.447秒，在PG12中为2.015秒:

分别在PostgreSQL 12版本和PostgreSQL 13版本下看SQL的执行计划和执行时间

6.6. vacumm增加了并行的功能

具体实现是SQL命令vacuum上增加了parallel的选项：

text

vacuum (parallel 5);

命令行工具vacuumdb增加了选项—parallel=：

text

vacuumdb -P 3

主要是实现了对索引的并行vacuum
并行度受到max_parallel_maintenance_workers参数的控制
索引的大小至少要大于参数min_parallel_index_scan_size的值(512KB)，才会并行vacuum
具体可以见：https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=40d964ec997f64227bc0ff5e058dc4a5770a70a9

6.7. 其它的一些功能增强

增强的功能如下：

增加参数autovacuum_vacuum_insert_threshold、 autovacuum_vacuum_insert_scale_factor:
原先如果对于只有insert的表（append only table）不会触发vacuum，这时会一直累积到aggressive vacuum，这样会导致vacuum太不及时，现在有这个参数，解决了这个不及时的问题。
为了实现这个功能在pg_stat_all_tables表中增加了列n_ins_since_vacuum，记录自上一次vacuum以来这个表插入了多少行。
reindexdb增加了—jobs，可以建多个数据库连接来并发来重建索引。
wal_skip_threshold
Skip WAL for new relfilenodes, under wal_level=minimal.
提升了PL/pgSQL中简单表达式的性能，如”x+1”或”x>0”，性能提升大致2倍
effective_io_concurrency参数
默认值改为1，与原先一样。如果设置大于1的值，则为实际的并发IO
测试发现PostgreSQL在bitmap index scan时，如果要读入大量堆page，读IO的速度会远低于正常的顺序读，影响性能，这时可以把此值设置大。
允许的范围是 1 到 1000，或 0 表示禁用异步 I/O 请求。当前这个设置仅影响位图堆扫描
jsonb Less-silly selectivity for JSONB matching operators
pg_stat_slru 查看slru的统计信息

7. postgre12

7.1. 新特性总结

PostgreSQL 12开始取消了recovery.conf，把配置项移动到postgresql.conf中
为了表明此库是备库，需要在$PGDATA下建standby.signal 空文件。去掉了配置项standby_mode
配置项trigger_file改名为promote_trigger_file
PostgreSQL 12 只能同时配置恢复目标项的一项，不能同时配置：recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, recovery_target_xid
pg_stat_replication中增加了应用延迟时间字段： reply_time
减少了在创建GiST,GIN，SP-GiST索引的WAL日志量
max_wal_senders 连接数从 max_connections 剥离
支持在线重建索引：REINDEX CONCURRENTLY
在Btree索引中减少了不必要的多版本数据，提升了性能。
PG12默认开启了JIT
提升了position函数的性能
SERIALIZABLE事物事物隔离级别也可以并行查询
VACUUM增加了选项TRUNCATE，有可能不需要vacuum full也能释放部分空间到操作系统
分区表的性能得到了加强。

7.2. 对VACUUM的增强

text

osdba=# \h vacuum
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

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

如上所示，增加了一些选项：

DISABLE_PAGE_SKIPPING：通常，VACUUM将基于可见性映射跳过页面。如果.vm文件损坏，可以把这个参数设置为true.
SKIP_LOCKED：跳过一给锁定的，防止vacuum被hang
INDEX_CLEANUP: 默认是YES。
TRUNCATE：把一些未用连续的数据块空间释放给文件系统，相当与数据文件是一个稀疏文件，即在一些情况下不需要VACUUM FULL也能释放一些空间给文件系统。

其它的一些变化：

PostgreSQL 12版本之后：max_wal_senders 连接数从 max_connections 剥离
PostgreSQL 12版本之后支持：REINDEX CONCURRENTLY
PostgreSQL 12版本之后：减少了在创建GiST,GIN，SP-GiST索引的WAL日志量
PostgreSQL 12版本只能配置一个：recovery_target, recovery_target_lsn, recovery_target_name, recovery_target_time, recovery_target_xid.
pg_basebackup从PostgreSQL 10之后可以支持限流

8. postgre11

8.1. 新特性总结

JIT即时编译功能，提升一些批计算如SUM的性能，通常提升在10%左右。
存储过程中可以加commit或rollback事物
声明式分区表功能大大增强：分区表可以加主键、外键、索引，支持hash分区表
CREATE INDEX可以并行
增加非空列也是瞬间完成，不需要rewrite表
hash join支持并行
vacuum增强：空闲空间可以更快的被重用，跳过一些没有必要的索引扫描
提升了多个并发事务commit的性能
逻辑复制支持truncate的同步
支持存储过程(CREATE PROCEDURE)，并可以在存储过程中嵌入事务
CREATE INDEX使用INCLUDE可以非键值列放到索引中，以便走Covering indexes而不必回表
以前触发toast的压缩都需要插入的数据大于1996个字节时才会触发，这个1996字节是固定的，不能改，现在给表加了存储参数toast_tuple_target，可以设置更新的值就可以触发toast的压缩机制
允许在initdb时改变 WAL文件的大小，以前是需要重新编译程序才能改变WAL文件的大小
现在在WAL日志中会把使用的部分填0，这样可以提高压缩率

8.2. PostgreSQL 11版本的jit

即时编译功能：

常用于CPU密集型SQL(分析统计SQL)，执行很快的SQL使用JIT由于产生一定开销，反而可能引起性能下降
jit的参数：
jit = on
jit_provider = ‘llvmjit’
jit_above_cost= 100000

8.3. PostgtreSQL一些其它增强

新的变化：

可以手工调整复制槽的记录的位置：
Allow replication slots to be advanced programmatically, rather than be consumed by subscribers (Petr Jelinek)
This allows efficient advancement of replication slots when the contents do not need to be consumed. This is performed by pg_replication_slot_advance().
以前给表加有默认值的列时需要重写文件，现在不需要了
Allow ALTER TABLE to add a column with a non-null default without doing a table rewrite (Andrew Dunstan, Serge Rielau)
This is enabled when the default value is a constant.

PostgreSQL 11版本的一些新特性

PostgreSQL11: 新增三个默认角色
PostgreSQL11: 可通过GRNAT权限下放的四个系统函数
PostgreSQL11: Initdb/pg_resetwal支持修改WAL文件大小
PostgreSQL11: 新增非空默认值字段不需要重写
–ALTER TABLE table_name ADD COLUMN flag text DEFAULT ‘default values’;
PostgreSQL11: Indexs With Include Columns

text

CREATE TABLE t_include(a int4, name text);
CREATE INDEX idx_t_include ON t_include USING BTREE (a) INCLUDE (name);

PostgreSQL11: initdb/pg_resetwal支持修改WAL文件大小，以前需要重新编译程序，才能改变。

PostgreSQL 10、11增加了一些系统角色，方便监控用户的权限：

PostgreSQL 11 新增三个默认系统角色，如下：
pg_read_server_files
pg_write_server_files
pg_execute_server_program
PostgreSQL 10
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_monitor
PostgreSQL9.6只有一个系统角色：
pg_signal_backend

PostgreSQL 11 版本的psql中增加了命令\gdesc可以查看执行结果的数据类型：

text

osdba=# select * from test01 \gdesc
 Column | Type
--------+---------
 id     | integer
 id2    | integer
 t      | text
(3 rows)

PostgreSQL 11版本psql增加了五个变量更容易查询SQL执行失败的原因：

ERROR
SQLSTATE
ROW_COUNT
LAST_ERROR_MESSAGE
LAST_ERROR_SQLSTATE

使用示例如下：

text

osdba=# select * from test01;
 id |  t
----+-----
 1 | 111
 2 | 222
(2 rows)

osdba=# \echo :ERROR
false
osdba=# \echo :SQLSTATE
00000
osdba=# \echo :ROW_COUNT
2
osdba=# select * from test02;
ERROR:  relation "test02" does not exist
LINE 1: select * from test02;
 ^
osdba=# \echo :ERROR
true
osdba=# \echo :SQLSTATE
42P01
osdba=# \echo :LAST_ERROR_MESSAGE
relation "test02" does not exist
osdba=# \echo :LAST_ERROR_SQLSTATE
42P01

9. postgre10

9.1. 新特性总结

支持同步复制多个standby:Quorum Commit
PostgreSQL 10开始增加声明式分区
PostgreSQL 10增加了并行功能
PostgreSQL 10之后 hash索引可以走流复制，从此可以大胆的使用hash索引了。
PostgreSQL 10之后提供了逻辑复制的功能：发布订阅的功能
PostgreSQL 10可以把多列组合在一起再建直方图，让一些关联列上的执行计划更准确
可以支持同步复制到多个standby，即Quorum Commit
以前的密码验证式md5，现在增加了安全级别更高的密码验证的方式：SCRAM-SHA-256
分区索引手动创建，不能基于分区父表创建

9.2. 并行查询功能

实际上从9.6开始就有并行查询功能，但功能比较弱，到PostgreSQL 10版本之后，功能大大增强，后续的每个大版本或多或少都有功能增强。
并行的参数
max_parallel_workers=16;
max_parallel_workers_per_gather =4;
min_parallel_table_scan_size：只有表的大小大于此值时才需要并行，默认为8M，可以设置为1G或更大的值。
保持与9.X相同的行为，可以关闭并行
set max_parallel_workers_per_gather = 0
当需要并行时，可以手工设置max_parallel_workers_per_gather的值
max_parallel_maintenance_workers

9.3. 逻辑复制功能

逻辑解码实际上是在PostgreSQL 9.4开始准备的功能，在9.X时代，支持内置了逻辑解码的功能，如果要做两个数据库之间表数据的逻辑同步，需要自己写程序或使用一些开源的软件来实现。到PostgreSQL 10版本，原生提供了逻辑复制的功能，实现了逻辑发布和订阅的功能，逻辑复制的功能变化如下：

PostgreSQL 10版本不支持truncate的同步，导致在10版本中，作为逻辑同步的表不能做truncate。从PostgreSQL 11版本之后可以支持truncate功能。

不过PostgreSQL自带的逻辑复制功能有以下限制：

逻辑解码是在主库上完成的，会消耗主库的CPU
必须建逻辑复制槽。但是逻辑复制槽会把主库的WAL给hold住，很多新手配置了逻辑复制，后来停掉了，但是忘记把逻辑复制槽给删除掉，最后把主库空间给撑爆
逻辑复制槽不支持备库，如果使用流复制的高可用方案，主备库切换后，逻辑复制就废了。
大事务会在主库中会生成一个临时文件，如果这个事务很大，这个临时文件也很大。
需要把wal_level级别设置logical，这会导致更多的WAL日志生成。

实际上中启乘数科技开发的有商业版的逻辑复制软件CMiner，解决了以上问题。CMiner本身是一个独立的程序，连接到主库上通过流复制协议拉取WAL日志，然后在本地解码，不会消耗主库的CPU，也不使用逻辑复制槽，没有把主库空间撑爆的风险，也可以方便的支持基于流复制的高可用方案，同时wal_level级别不需要设置为logical就可以完成解码。目前这套解决方案已经在银行中使用，有兴趣同学可以加微信 osdba0，或邮件 services@csudata.com 。

9.4. 相关列上建组合的直方图统计信息

用实例说明这个功能：

text

create table test_t( a int4, b int4);
insert into test_t(a,b) select n%100,n%100 from generate_series(1,10000) n;

上面的两个列a和b的数据相关的，即基本是相同的，而PostgreSQL默认计算各列是按非相关来计算了，所以算出的的COST值与实际相差很大：

text

osdba=# explain analyze select * from test_t where a=1 and b=1;
                       QUERY PLAN
----------------------------------------------------------------------------------------------------
 Seq Scan on test_t (cost=0.00..195.00 rows=1 width=8) (actual time=0.034..0.896 rows=100 loops=1)
 Filter: ((a = 1) AND (b = 1))
 Rows Removed by Filter: 9900
 Planning Time: 0.185 ms
 Execution Time: 0.916 ms

如上面，估计出只返回1行，实际返回100行。这在一些复杂SQL中会导致错误的执行计划。

这时我们可以在相关列上建组合的直方图统计信息：

text

osdba=# CREATE STATISTICS stts_test_t ON a, b FROM test_t;
CREATE STATISTICS
osdba=# analyze test_t;
ANALYZE
osdba=# explain analyze select * from test_t where a=1 and b=1;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Seq Scan on test_t (cost=0.00..195.00 rows=100 width=8) (actual time=0.012..0.830 rows=100 loops=1)
 Filter: ((a = 1) AND (b = 1))
 Rows Removed by Filter: 9900
 Planning Time: 0.127 ms
 Execution Time: 0.848 ms
(5 rows)

从上面可以看出当我们建了相关列上建组合的直方图统计信息后，执行计划中估计的函数与实际一致了。

9.5. 一些其它功能

hash索引从PostgreSQL 10开始可以放心大胆的使用：

PostgreSQL 9.X 版本hash索引走不了流复制，所以基本没有人用hash索引，即如果用了hash索引，在激活备库时，需要重建hash索引。
到PostgreSQL 10.X，hash索引可以通过流复制同步到备库，所以没有这个问题了，这是可以大胆的使用hash索引了。

到PostgreSQL 10之后，很多函数都进行了改名，其中把函数名中的“xlog”都改成了“wal”，把“position”都改成了“lsn”：

pg_current_wal_lsn
pg_current_wal_insert_lsn
pg_current_wal_flush_lsn
pg_walfile_name_offset
pg_walfile_name
pg_wal_lsn_diff
pg_last_wal_receive_lsn
pg_last_wal_replay_lsn
pg_is_wal_replay_paused
pg_switch_wal
pg_wal_replay_pause
pg_wal_replay_resume
pg_ls_waldir

PostgreSQL 10对一些目录也改名：

Rename write-ahead log directory pg_xlog to pg_wal
rename transaction status directory pg_clog to pg_xact

PostgreSQL 9.X，同步复制只能支持一个同步的备库，PostgtreSQL 10 可以支持多个同步的standby，这称为“Quorum Commit”，同步复制的配置发生如下变化：

synchronous_standby_names
FIRST num_sync (standby_name)：保持前面几个备库必须与主库保持同步。
ANY num_sync (standby_name)：保证num_sync 个备库与主库保持同步。
原先的配置： synchronous_standby_names=’stb01,stb02,stb03’实际相当于： synchronous_standby_names=FIRST 1(stb01,stb02,stb03)’

索引的增强：

BRIN索引增强：
BRIN索引增加了存储选项autosummarize，可以自动计算摘要
增加了函数brin_summarize_range()和brin_desummarize_range() 可以手工为BRIN的指定块建摘要和去除摘要。以前BRIN只有函数brin_summarize_new_values()、 gin_clean_pending_list()
Improve accuracy in determining if a BRIN index scan is beneficial (David Rowley, Emre Hasegeli)
INET和CIDR类型上支持建SP-GiST类型的索引
在GiST索引的插入和更新可以更高效的重用空间
Reduce page locking during vacuuming of GIN indexes

串行隔离级别预加锁阈值可控

max_pred_locks_per_relation：当单个对象的行或者页预加锁数量达到阈值时，升级为对象预加锁。减少内存开销。
max_pred_locks_per_page：当单个页内多少条记录被加预加锁时，升级为页预加锁。减少内存开销

PostgreSQL 10提供了视图pg_hba_file_rules方便查询访问控制的黑白名单：

text

osdba=# select * from pg_hba_file_rules;
 line_number | type  |   database    | user_name | address | netmask | auth_method | options | error
-------------+-------+---------------+-----------+---------+---------+-------------+---------+-------
 80 | local | {all} | {all} | | | peer        | |
 83 | host  | {all} | {all} | 0.0.0.0 | 0.0.0.0 | md5         | |
 88 | local | {replication} | {all} | | | peer        | |

psql增加了：\if, \elif, \else, and \endif.

text

SELECT

  EXISTS(SELECT 1 FROM customer WHERE customer_id = 123) as is_customer,

  EXISTS(SELECT 1 FROM employee WHERE employee_id = 456) as is_employee

\gset

\if :is_customer

  SELECT * FROM customer WHERE customer_id = 123;

\elif :is_employee

  \echo 'is not a customer but is an employee'

  SELECT * FROM employee WHERE employee_id = 456;

\else

  \if yes

    \echo 'not a customer or employee'

  \else

    \echo 'this will never print'

  \endif

\endif

其它的一些功能：

提升了聚合函数sum()、avg()、stddev()处理numeric类型的性能
Allow hashed aggregation to be used with grouping sets
Improve sort performance of the macaddr data type (Brandur Leach)
Add pg_stat_activity reporting of low-level wait states (Michael Paquier, Robert Haas, Rushabh Lathia)
This change enables reporting of numerous low-level wait conditions, including latch waits, file reads/writes/fsyncs, client reads/writes, and synchronous replication.
Show auxiliary processes, background workers, and walsender processes in pg_stat_activity (Kuntal Ghosh, Michael Paquier)
This simplifies monitoring. A new column backend_type identifies the process type.
Prevent unnecessary checkpoints and WAL archiving on otherwise-idle systems (Michael Paquier)
Increase the maximum configurable WAL segment size to one gigabyte (Beena Emerson)
Add columns to pg_stat_replication to report replication delay times (Thomas Munro)
The new columns are write_lag, flush_lag, and replay_lag.
Allow specification of the recovery stopping point by Log Sequence Number (LSN) in recovery.conf (Michael Paquier)
Previously the stopping point could only be selected by timestamp or XID.
Improve performance of hot standby replay with better tracking of Access Exclusive locks (Simon Riggs, David Rowley)
Speed up two-phase commit recovery performance (Stas Kelvich, Nikhil Sontakke, Michael Paquier)
Allow restrictive row-level security policies (Stephen Frost)
Add CREATE SEQUENCE AS command to create a sequence matching an integer data type (Peter Eisentraut)
Allow the specification of a function name without arguments in DDL commands, if it is unique (Peter Eisentraut)
Improve speed of VACUUM’s removal of trailing empty heap pages (Claudio Freire, Álvaro Herrera)
Add full text search support for JSON and JSONB (Dmitry Dolgov)
The functions ts_headline() and to_tsvector() can now be used on these data types.
自增列原先只有用serial和bigserial创建自增列，现在可以标准的语法创建自增列

text

CREATE TABLE test01 (

   id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,

   t text

);

增加减号为jsonb类型的删除某个key的操作符

text

postgres=# select '{"a": 1, "b": 2, "c":3}'::jsonb - '{a,c}'::text[];

 ?column?

\----------

 {"b": 2}

(1 row)

Allow specification of multiple host names or addresses in libpq connection strings and URIs (Robert Haas, Heikki Linnakangas)。
libpq will connect to the first responsive server in the list.
配合连接参数target_session_attrs=read-write，只是只会连接到一个主库上。
Allow file_fdw to read from program output as well as files (Corey Huinker, Adam Gomaa)
In postgres_fdw, push aggregate functions to the remote server, when possible (Jeevan Chalke, Ashutosh Bapat)

1. 版本策略与生命周期 ​

2. postgre17 ​

2.1. 新特性总结 ​

3. postgre16 ​

3.1. 新特性总结 ​

3.1.1. 性能提升 ​

3.1.2. 逻辑复制 ​

3.1.3. 开发者体验 ​

3.1.4. 监控 ​

3.1.5. 访问控制与安全性 ​

4. postgre15 ​

4.1. 新特性总结 ​

4.1.1. 改进的排序性能和压缩 ​

4.1.2. 新的开发者功能 ​

4.1.3. 更多逻辑复制选项 ​

4.1.4. 日志和配置增强 ​

4.1.5. 其他变化 ​

4.2. 排序性能改进 ​

4.2.1. 内存排序 ​

4.2.2. 超过 work_mem 的排序 ​

5. postgre14 ​

5.1. 新特性总结 ​

5.1.1. 数据类型 ​

5.1.2. 函数 ​

5.1.3. 管理功能 ​

5.1.4. 复制和恢复 ​

5.1.5. 索引 ​

6. postgre13 ​

6.1. 新特性总结 ​

6.2. 分区表智能join ​

6.2.1. 不要求分区的范围完全相等 ​

6.2.2. 三个分区表full outer join也智能join ​

6.3. 索引消除重复项 ​

6.4. 聚合时使用hash算法可以使用磁盘做溢出存储 ​

6.5. 增量排序（Incremental sort）的功能 ​

6.6. vacumm增加了并行的功能 ​

6.7. 其它的一些功能增强 ​

7. postgre12 ​

7.1. 新特性总结 ​

7.2. 对VACUUM的增强 ​

8. postgre11 ​

8.1. 新特性总结 ​

8.2. PostgreSQL 11版本的jit ​

8.3. PostgtreSQL一些其它增强 ​

9. postgre10 ​

9.1. 新特性总结 ​

9.2. 并行查询功能 ​

9.3. 逻辑复制功能 ​

9.4. 相关列上建组合的直方图统计信息 ​

9.5. 一些其它功能 ​

1. 版本策略与生命周期

2. postgre17

2.1. 新特性总结

3. postgre16

3.1. 新特性总结

3.1.1. 性能提升

3.1.2. 逻辑复制

3.1.3. 开发者体验

3.1.4. 监控

3.1.5. 访问控制与安全性

4. postgre15

4.1. 新特性总结

4.1.1. 改进的排序性能和压缩

4.1.2. 新的开发者功能

4.1.3. 更多逻辑复制选项

4.1.4. 日志和配置增强

4.1.5. 其他变化

4.2. 排序性能改进

4.2.1. 内存排序

4.2.2. 超过 work_mem 的排序

5. postgre14

5.1. 新特性总结

5.1.1. 数据类型

5.1.2. 函数

5.1.3. 管理功能

5.1.4. 复制和恢复

5.1.5. 索引

6. postgre13

6.1. 新特性总结

6.2. 分区表智能join

6.2.1. 不要求分区的范围完全相等

6.2.2. 三个分区表full outer join也智能join

6.3. 索引消除重复项

6.4. 聚合时使用hash算法可以使用磁盘做溢出存储

6.5. 增量排序（Incremental sort）的功能

6.6. vacumm增加了并行的功能

6.7. 其它的一些功能增强

7. postgre12

7.1. 新特性总结

7.2. 对VACUUM的增强

8. postgre11

8.1. 新特性总结

8.2. PostgreSQL 11版本的jit

8.3. PostgtreSQL一些其它增强

9. postgre10

9.1. 新特性总结

9.2. 并行查询功能

9.3. 逻辑复制功能

9.4. 相关列上建组合的直方图统计信息

9.5. 一些其它功能