PostgreSQL 18 preview - pg_dump 优化导出统计信息功能

PostgreSQL 18 preview - pg_dump 优化"导出统计信息"功能

PostgreSQL 18 支持统计信息的导出和导入, 解决了大版本升级后需要通过vacuum analyze重新生成统计信息的问题(未生成统计信息贸然让应用连上来, 可能导致执行计划不准确, 性能低下甚至数据库Hang的风险.) .

• 《PostgreSQL 18 preview - 支持统计信息导出导入, 将来用pg_upgrade大版本升级后不需要analyze了》

同时支持一键启用全库逻辑订阅, 可以使用standby进行大版本升级, 然后快速同步原主库增量数据. 总之大版本升级的停机时间窗口被压缩到了极致, 体验很好.

• 《PostgreSQL 18 preview - pg_createsubscriber增加--all选项, 方便全实例逻辑订阅》

我之前的相关吐槽可以撤掉了.

下面这个patch则是对pg_dump 导出统计信息的功能优化, 解决了pg_dump导出统计信息大量占用内存问题，同时通过批量查询大幅降低了获取统计信息的总时间。

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

pg_dump: Reduce memory usage of dumps with statistics.  
author Nathan Bossart <[email protected]>   
Fri, 4 Apr 2025 19:51:08 +0000 (14:51 -0500)  
committer Nathan Bossart <[email protected]>   
Fri, 4 Apr 2025 19:51:08 +0000 (14:51 -0500)  
commit 7d5c83b4e90c7156655f98b7312a30ae5eeb4d27  
tree 3720c57d402fc3c497eae60e26291d5ae70202c2 tree  
parent e3cc039a7d930ffe86e706944a2b3368bd3ef506 commit | diff  
pg_dump: Reduce memory usage of dumps with statistics.  
Right now, pg_dump stores all generated commands for statistics in
memory.  These commands can be quite large and therefore can  
significantly increase pg_dump's memory footprint.  To fix, wait  
until we are about to write out the commands before generating  
them, and be sure to free the commands after writing.  This is  
implemented via a new defnDumper callback that works much like the  
dataDumper one but is specifically designed for TOC entries.  

Custom dumps that include data might write the TOC twice (to update  
data offset information), which would ordinarily cause pg_dump to  
run the attribute statistics queries twice.  However, as a hack, we  
save the length of the written-out entry in the first pass and skip  
over it in the second.  While there is no known technical issue  
with executing the queries multiple times and rewriting the  
results, it's expensive and feels risky, so let's avoid it.  

As an exception, we _do_ execute the queries twice for the tar  
format.  This format does a second pass through the TOC to generate  
the restore.sql file.  pg_restore doesn't use this file, so even if
the second round of queries returns different results than the  
first, it won't corrupt the output; the archive and restore.sql  
file will just have different content.  A follow-up commit will  
teach pg_dump to gather attribute statistics in batches, which our  
testing indicates more than makes up for the added expense of  
running the queries twice.  

Author: Corey Huinker <[email protected]>  
Co-authored-by: Nathan Bossart <[email protected]>  
Reviewed-by: Jeff Davis <[email protected]>  
Discussion: https://postgr.es/m/CADkLM%3Dc%2Br05srPy9w%2B-%2BnbmLEo15dKXYQ03Q_xyK%2BriJerigLQ%40mail.gmail.com  

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

pg_dump: Retrieve attribute statistics in batches.  
author Nathan Bossart <[email protected]>   
Fri, 4 Apr 2025 19:51:08 +0000 (14:51 -0500)  
committer Nathan Bossart <[email protected]>   
Fri, 4 Apr 2025 19:51:08 +0000 (14:51 -0500)  
commit 9c02e3a986daa865ecdc2e3d8183e2d83b8f4824  
tree 956d3b6745fa775bfefad57f09b3816e61a3f119 tree  
parent 7d5c83b4e90c7156655f98b7312a30ae5eeb4d27 commit | diff  
pg_dump: Retrieve attribute statistics in batches.  
Currently, pg_dump gathers attribute statistics with a query per  
relation, which can cause pg_dump to take significantly longer,  
especially when there are many relations.  This commit addresses  
this by teaching pg_dump to gather attribute statistics for 64  
relations at a time.  Some simple tests showed this was the optimal  
batch size, but performance may vary depending on the workload.  
Our lookahead code determines the next batch of relations by  
searching the TOC sequentially for relevant entries.  This approach  
assumes that we will dump all such entries in TOC order, which
unfortunately isn't true for dump formats that use  
RestoreArchive().  RestoreArchive() does multiple passes through  
the TOC and selectively dumps certain groups of entries each time.  
This is particularly problematic for index stats and a subset of  
matview stats; both are in SECTION_POST_DATA, but matview stats  
that depend on matview data are dumped in RESTORE_PASS_POST_ACL,  
while all other stats are dumped in RESTORE_PASS_MAIN.  To handle  
this, this commit moves all statistics data entries in  
SECTION_POST_DATA to RESTORE_PASS_POST_ACL, which ensures that we  
always dump them in TOC order.  A convenient side effect of this  
change is that we can revert a decent chunk of commit a0a4601765,  
but that is left for a follow-up commit.  
Author: Corey Huinker <[email protected]>  
Co-authored-by: Nathan Bossart <[email protected]>  
Reviewed-by: Jeff Davis <[email protected]>  
Discussion: https://postgr.es/m/CADkLM%3Dc%2Br05srPy9w%2B-%2BnbmLEo15dKXYQ03Q_xyK%2BriJerigLQ%40mail.gmail.com  

AI 解读

这两个相关的 pg_dump 补丁。它们都旨在优化 pg_dump 处理“扩展统计信息”（attribute statistics，即 pg_statistic 表中的数据，用于查询优化器）的方式。

Patch 1: pg_dump: Reduce memory usage of dumps with statistics. (pg_dump：减少包含统计信息备份的内存使用)

• 解决了什么问题？

• 在旧版本中，pg_dump 会在备份开始阶段就生成所有用于恢复统计信息的 SQL 命令（主要是 ALTER TABLE ... SET STATISTICS ... 这样的语句）。
• 如果数据库中表和列很多，或者统计信息本身很复杂，这些生成的 SQL 命令字符串会占用大量的内存。
• pg_dump 会一直持有这些命令的内存，直到将它们写入备份文件的相应位置。这导致在处理大型数据库或包含大量统计信息的数据库时，pg_dump 进程的内存峰值会非常高，甚至可能导致内存不足的问题。

• 如何解决？(核心机制)

• 延迟生成 (Lazy Generation): 修改了 pg_dump 的内部逻辑。不再提前生成所有统计信息命令，而是等到即将要将这些命令写入输出文件（或归档）时才去查询数据库并生成它们。
• 及时释放 (Prompt Freeing): 一旦这些命令被写入输出，立即释放它们占用的内存。
• 新的回调机制: 为了实现这一点，引入了一个新的内部回调函数类型 defnDumper。这个回调函数与处理表数据的 dataDumper 回调类似，但专门用于处理像统计信息这样的“定义类”对象（TOC entries，即备份目录条目）。当 pg_dump 处理到 TOC 中标记为统计信息的条目并准备输出时，就会调用这个新的回调函数来执行生成和写入操作。

• 处理复杂情况 (边角案例):

• 自定义格式 (Custom/Directory Format) 的双 TOC 写入: 对于自定义格式或目录格式的备份，pg_dump 有时会写入两次目录（TOC）。第一次写入基础结构，第二次可能在写入数据后更新数据块在归档中的偏移量信息。如果简单地在每次写入 TOC 条目时都执行 defnDumper 回调，就会导致统计信息的查询和生成被执行两次。
• 避免重复查询 (Hack): 为了避免这种昂贵的重复操作（查询统计信息可能很耗时），补丁采用了一个“小技巧”：在第一次写入统计信息命令时，记录下写入内容的长度。在第二次处理 TOC 时，如果发现这个条目已经写入过（通过检查记录的长度），就直接跳过，不再重新执行查询和生成。作者认为重复执行查询虽然技术上可能没问题，但代价高昂且感觉有风险。
• Tar 格式的例外: 对于 tar 格式的备份，这个避免重复查询的技巧没有被应用，统计信息查询确实会被执行两次。原因是：tar 格式在内部会生成一个 restore.sql 文件，这需要第二次遍历 TOC。但 pg_restore 在从 tar 文件恢复时，并不使用这个 restore.sql 文件，而是直接解析归档内容。因此，即使第二次查询的结果与第一次略有不同（理论上可能，但不常见），也只会影响 restore.sql 的内容，不会破坏实际的备份数据。
• 对 Tar 格式代价的说明: 作者提到，虽然 tar 格式会查询两次，但一个后续的提交（也就是我们接下来要看的第二个 Patch）将引入批量获取统计信息的功能，这足以弥补 tar 格式下执行两次查询带来的额外开销。

• 总结 (Patch 1): 主要目标是显著降低 pg_dump 在备份包含统计信息时的内存消耗。通过将统计信息命令的生成推迟到写入前、并在写入后立即释放内存来实现。引入了新的回调机制，并处理了自定义/目录格式下的 TOC 双写问题（通过跳过第二次生成），同时说明了为何 tar 格式下允许两次查询及其影响有限，并预告了后续的批量处理优化。

Patch 2: pg_dump: Retrieve attribute statistics in batches. (pg_dump：批量检索属性统计信息)

• 解决了什么问题？

• 在之前的版本（包括应用了第一个 Patch 之后），pg_dump 获取每个关系（表、物化视图等）的统计信息时，是为每个关系单独执行一次查询。
• 当数据库中有成千上万甚至更多关系时，这种逐个查询的方式会导致大量的数据库交互和查询开销，使得 pg_dump 在处理统计信息阶段耗时很长，显著增加备份总时间。

• 如何解决？(核心机制)

• 批量查询: 修改 pg_dump 的逻辑，使其不再为每个关系单独查询统计信息，而是一次性查询一批关系（默认为 64 个）的统计信息。
• 批处理逻辑:pg_dump 会有一个“向前看”（lookahead）的机制，按顺序扫描内部的备份目录（TOC），找出接下来需要备份统计信息的 64 个关系，然后用一个组合查询（可能是在 pg_statistic 上使用 IN 子句或其他方式）一次性获取这批关系的统计数据。
• 性能提升: 通过将 N 次小查询合并为大约 N/64 次大查询，显著减少了查询的往返次数和固定开销，从而加快了统计信息的导出速度。测试表明 64 是一个较优的批次大小，但实际效果可能因工作负载而异。

• 处理复杂情况 (与备份顺序相关):

• TOC 顺序与实际转储顺序: 批量处理的“向前看”逻辑依赖一个假设：pg_dump 会严格按照 TOC 中统计信息条目的顺序来转储它们。
• RestoreArchive() 的多遍处理: 对于使用 RestoreArchive() 逻辑的格式（自定义、目录、tar），实际的转储过程并非严格按 TOC 顺序一次性完成。RestoreArchive() 会分多个“遍”（pass）来处理 TOC，每一遍选择性地转储特定类型的对象（例如，先是表结构，然后是数据，然后是约束，最后是索引、统计信息等后置数据）。
• 统计信息分散在不同 Pass 的问题: 问题在于，不同类型的统计信息（如普通表统计、索引统计、某些物化视图统计）虽然都在 TOC 的 SECTION_POST_DATA（后置数据段）中，但之前它们可能被分配到不同的恢复 Pass 中进行转储（例如，一些在 RESTORE_PASS_MAIN，依赖数据的物化视图统计在 RESTORE_PASS_POST_ACL）。这就破坏了“按 TOC 顺序转储”的假设，使得简单的顺序向前看批量处理逻辑无法正确工作。
• 解决方案: 为了解决这个问题，该补丁做了一个调整：将所有位于 SECTION_POST_DATA 的统计信息条目（无论其原始类型）统一移动到 RESTORE_PASS_POST_ACL 这个恢复 Pass 中。这样保证了所有统计信息都在同一个 Pass 中被处理，并且在该 Pass 内它们是按照 TOC 的顺序被转储的，从而使得批量处理的向前看逻辑能够正确、可靠地工作。
• 附带效果: 这个修改统一了统计信息的处理时机，使得之前为了处理不同 Pass 中统计信息而引入的一些复杂代码（来自 commit a0a4601765）可以被简化或移除。作者提到这部分清理工作会留给另一个后续提交。

• 总结 (Patch 2): 主要目标是显著提升 pg_dump 备份包含大量关系时导出统计信息的速度。通过将原来逐个关系的查询改为批量查询（默认一次 64 个）来实现。为了让批量查询的逻辑正确工作，解决了因 RestoreArchive() 多遍处理导致统计信息转储顺序与 TOC 顺序不一致的问题，方法是将所有统计信息强制安排在同一个恢复 Pass (RESTORE_PASS_POST_ACL) 中处理。

两个 Patch 的关联:

• Patch 1 解决了内存问题，但留下了 tar 格式下可能查询两次统计信息的代价。
• Patch 2 解决了性能问题（查询次数过多），通过批量查询大幅降低了获取统计信息的总时间。这个性能提升也使得 Patch 1 中提到的 tar 格式下查询两次的代价变得不那么显著，甚至可以被批量带来的收益所抵消。同时，Patch 2 的实现需要对统计信息的转储时机进行调整，确保它们能按 TOC 顺序被批量处理。