PG 14新特性汇总

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

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

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

模拟过程：

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

2、执行alter table pt_table detach partition part_name;

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

PG 14的语法为：

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

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

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

实验过程：

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

LZ4压缩可以用于TOAST数据

在 PostgreSQL 中，TOAST（The Oversized-Attribute Storage Technique）是一种用于处理大型或超过特定大小限制的数据的存储技术。它主要应用于存储行或列中的大型数据值，例如大型文本（text）、二进制数据（bytea）或变长字符数据（varchar）等。

TOAST 数据是被自动压缩和分解的大型数据值。当某个数据值的大小超过 TOAST 阈值（默认为 2KB），PostgreSQL 将自动将其转换为 TOAST 格式，并将其存储在特殊的 TOAST 表中。原始表中存储的是 TOAST 数据的引用（指针），而不是实际的数据内容。这样可以节省存储空间并提高性能。

TOAST 数据的主要特点如下：

1. 压缩存储：TOAST 数据会经过压缩处理，以减少所需的存储空间。
2. 分解存储：如果一个数据值超过 TOAST 阈值，它将被分解成多个块，并存储在 TOAST 表中。这样可以提高读取和处理大型数据的效率。
3. 透明访问：PostgreSQL 在访问 TOAST 数据时会自动处理数据的压缩和分解，对应用程序而言是透明的，无需显式的操作。
4. 数据一致性：TOAST 数据的存储和访问过程会保持数据的一致性，即使是在并发环境下也能正确处理。

TOAST 技术使得 PostgreSQL 能够有效地存储和处理大型数据值，同时对应用程序开发者来说是透明的，无需特别的处理。

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

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

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

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

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

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

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

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

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

安装操作系统包：

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

其它示例：

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

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

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

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

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

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

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

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

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

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

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

为什么会出现索引膨胀

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

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

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

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

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

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

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

HOT元组

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

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

杀死索引条目

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

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

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

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章！

验证码：

请先关注本站微信公众号，然后回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。