Postgres 11升级至15后CREATE DATABASE WITH TEMPLATE性能下降问题咨询

已知耗时增加的原因 #

PostgreSQL 15相对于11在CREATE DATABASE WITH TEMPLATE操作上变慢，主要源于以下几个明确的版本特性变更：

• WAL日志机制调整：PostgreSQL 14及以后版本为强化时间点恢复（PITR）的可靠性，默认会为CREATE DATABASE操作生成完整WAL日志；而PG11在wal_level为minimal时，会直接采用文件硬链接方式复制数据库文件，几乎不产生WAL，速度远快于日志写入模式。
• 表空间处理逻辑变更：若源数据库使用非默认表空间，PG15跨表空间复制数据时不再优先使用硬链接，而是采用实际数据拷贝，这会大幅增加耗时。
• 并行拷贝策略调整：PG11的CREATE DATABASE操作默认并行度更高，而PG15对维护操作的并行参数默认值更保守，单线程拷贝大数据库时速度明显下降。

优化提速技巧 #

要让PG15的CREATE DATABASE操作恢复到2分钟左右的耗时，可尝试以下方法：

• 切换WAL级别为minimal：若测试环境无需PITR功能，将postgresql.conf中的wal_level设置为minimal，重启数据库后，CREATE DATABASE会再次采用硬链接拷贝文件，速度将大幅提升。
• 确保源库与目标库同文件系统：将源数据库和测试数据库放在同一挂载的文件系统下，PostgreSQL会自动使用硬链接代替数据拷贝，这是最快的复制方式；若使用自定义表空间，需确保表空间存储路径也在同一文件系统。
• 调大并行维护工作线程数：根据CPU核心数调整max_parallel_maintenance_workers参数（比如4核机器设为3），让数据库在复制时并行处理数据文件，减少整体耗时。该参数支持动态调整，无需重启：

  ALTER SYSTEM SET max_parallel_maintenance_workers = 3;
  SELECT pg_reload_conf();
  

• 临时关闭同步提交：执行CREATE DATABASE前临时关闭同步提交，避免WAL写入的等待时间：

  SET synchronous_commit = off;
  CREATE DATABASE test WITH OWNER = postgres TEMPLATE = origin ENCODING = 'UTF8' CONNECTION LIMIT = -1;
  SET synchronous_commit = on;
  

• 预优化源模板库：在业务低峰期对源数据库origin执行VACUUM FULL，清理碎片并整理数据文件，让复制时的数据更紧凑，减少拷贝的数据量。

内容的提问来源于stack exchange，提问作者Mario P