PostgreSQL 19第一部分 或 CommitFest 2025-07来源habr.com原文地址https://postgr.es/p/7tA我们正在启动一个关于 PostgreSQL 19 即将到来变化的新系列文章。第一篇文章聚焦于去年夏天的七月 CommitFest 中的更新内容。 目录连接服务文件支持 libpq 参数和 psql 变量regdatabase用于数据库标识符的类型pg_stat_statements通用计划与自定义计划计数器pg_stat_statementsFETCH 命令规范化pg_stat_statementsIN 子句中的参数列表命令规范化EXPLAINMemoize 节点估算btree_gin整数类型的比较运算符pg_upgrade优化大对象迁移优化临时表截断规划器Append 和 MergeAppend 节点中的增量排序域约束验证不再阻塞 DML 操作CHECKPOINT 命令参数COPY FROM跳过初始行pg_dsm_registry_allocations动态共享内存使用 连接服务文件支持 libpq 参数和 psql 变量提交[092f3c63efc6], [6b1c4d326b06]当使用连接服务文件连接到服务器时现在可以通过连接字符串中的新参数servicefile指定文件名。例如当前目录下有一个demo.conf文件$cat./demo.conf[demo]hostlocalhostport5401userpostgresdbnamedemooptions-csearch_pathbookings你可以使用它进行连接$ psqlservicefile./demo.conf servicedemopsql(19devel)Typehelpforhelp. postgresdemo(19.0)#在 psql 中新增了一个SERVICEFILE变量用于指定服务文件名postgresdemo(19.0)# \echo :SERVICEFILE./demo.conf️ regdatabase用于数据库标识符的类型提交[bd09f024a]对象标识符别名类型家族的新成员。regdatabase类型支持数据库名称与其标识符之间的双向转换。SELECTcurrent_database()::regdatabase,current_database()::regdatabase::oid \gx-[RECORD1]----------current_database|demo current_database|16561 pg_stat_statements通用计划与自定义计划计数器提交[3357471cf]pg_stat_statements视图现在新增两列用于跟踪查询执行中选择通用计划和自定义计划的次数SELECTpg_stat_statements_reset();SELECT*FROMbookingsWHEREbook_ref$1\bindNWQI2S\g/dev/nullSELECT*FROMbookingsWHEREbook_ref$1\bindWF2DGZ\g/dev/nullSELECTquery,calls,generic_plan_calls,custom_plan_callsFROMpg_stat_statementsWHEREquery~bookings\gx-[RECORD1]-------------------------------------------------query|SELECT*FROMbookingsWHEREbook_ref$1calls|2generic_plan_calls|0custom_plan_calls|2 pg_stat_statementsFETCH 命令规范化提交[bee23ea4d]在规范化FETCH命令时获取行数现在被替换为常量。这意味着获取不同行数的FETCH命令将共享相同的查询标识符并在pg_stat_statements中显示为单一条目SELECTpg_stat_statements_reset();BEGIN;DECLAREcurCURSORFORSELECT*FROMflights;FETCH1cur\g/dev/nullFETCH2cur\g/dev/nullFETCH-1cur\g/dev/nullCOMMIT;SELECTqueryid,query,callsFROMpg_stat_statementsWHEREquery~^FETCH\gx-[RECORD1]----------------queryid|4164749676997500190query|FETCH$1cur calls|3 pg_stat_statementsIN 子句中的参数列表命令规范化提交[c2da1a5d6]带有IN子句中常量列表的命令已在版本 18 中实现了规范化。在版本 19 中这一规范化不仅适用于常量还适用于参数列表SELECTpg_stat_statements_reset();SELECT*FROMflightsWHEREflight_idIN(1,2)\g/dev/nullSELECT*FROMflightsWHEREflight_idIN(1,2,3)\g/dev/nullSELECT*FROMflightsWHEREflight_idIN($1,$2)\bind1112\g/dev/nullSELECT*FROMflightsWHEREflight_idIN($1,$2,$3)\bind212223\g/dev/nullSELECT*FROMflightsWHEREflight_idIN($1,$2,$3,$4)\bind31323334\g/dev/nullSELECTqueryid,query,callsFROMpg_stat_statementsWHEREquery~flights-[RECORD1]-----------------------------------------------------queryid|-5928905334469394952query|SELECT*FROMflightsWHEREflight_idIN($1/*, ... */)calls|5这对于使用扩展查询协议的应用程序特别有用。 EXPLAINMemoize 节点估算提交[4bc62b86849]为了帮助理解规划器为何选择使用 Memoize 节点EXPLAIN输出现在包含该节点的规划器估算值显示在Estimates行中EXPLAINSELECT*FROMroutes rJOINairports_data aONr.departure_airporta.airport_codeWHEREr.days_of_week{1,2,3,4,5,6,7};QUERYPLAN--------------------------------------------------------------------------------------------------------NestedLoop(cost0.29..212.71rows898width293)-Seq Scanonroutes r(cost0.00..111.12rows898width103)Filter:(days_of_week{1,2,3,4,5,6,7}::integer[])-Memoize(cost0.29..1.08rows1width190)CacheKey: r.departure_airport CacheMode: logical Estimates: capacity73distinctkeys73lookups898hitpercent91.87%-IndexScanusingairports_data_pkeyonairports_data a(cost0.28..1.07rows1width190)IndexCond:(airport_coder.departure_airport)(9rows)此行显示规划器对以下指标的估算哈希表大小哈希表中唯一元素的数量哈希表被查找的次数命中率使用EXPLAIN的analyze参数运行可显示估算与实际结果之间的差异。以下是同一查询执行计划中的两个相关行… Estimates: capacity73 distinct keys73 lookups898 hit percent91.87% Hits: 830 Misses: 68 Evictions: 0 Overflows: 0 Memory Usage: 19kB … btree_gin整数类型的比较运算符提交[e2b64fcef35], [fc896821c44]为btree_gin扩展的用户提供的小便利。CREATEEXTENSION btree_gin;CREATETABLEt(abigint,btext);INSERTINTOtSELECTrandom(1,100),random()::textFROMgenerate_series(1,100000);CREATEINDEXidxONtUSINGGIN(a,b gin_trgm_ops);VACUUMANALYZEt;SELECT*FROMtWHEREa42ANDbLIKE*42*;以前对于此类查询规划器只会使用列 b 条件的索引而不会将a 42纳入索引搜索条件因为列 a 的类型是bigint而42默认为int。这需要在查询中显式指定a 42::bigint。现在btree_gin已得到改进int2、int4和int8将自动转换为适当的类型以供索引使用。EXPLAINSELECT*FROMtWHEREa42ANDbLIKE*42*;QUERYPLAN--------------------------------------------------------------------Bitmap Heap Scanont(cost229.49..233.51rows1width27)Recheck Cond:((a42)AND(b~~*42*::text))-BitmapIndexScanonidx(cost0.00..229.49rows1width0)IndexCond:((a42)AND(b~~*42*::text))(4rows) pg_upgrade优化大对象迁移提交[161a3e8b682], [3bcfcd815e1]当从版本 12 及以上版本升级时带有--binary-upgrade参数的pg_dump会为大对象生成COPY命令将数据直接插入pg_largeobject_metadata和pg_shdepend表中。从这样的转储中恢复比执行ALTER LARGE OBJECT命令分配所有权和GRANT命令设置权限要快得多。因此pg_upgrade升级包含大量大对象的服务器将花费更少的时间。从版本 16 及更高版本升级第二个提交将更快因为pg_largeobject_metadata表文件将像普通用户表文件一样被迁移。不带--binary-upgrade参数的pg_dump继续像往常一样使用 SQL 命令导出大对象元数据。⚡ 优化临时表截断提交[78ebda66bf2]以前截断临时表需要为每个分支主关系、可见性映射和空闲空间映射扫描本地缓冲区缓存temp_buffers三次。现在一次扫描就足够了。这对于大量使用临时表的应用程序来说是一个非常有价值的优化。普通表的类似优化早在 PostgreSQL 13 中就已实现。 规划器Append 和 MergeAppend 节点中的增量排序提交[55a780e9476]规划器现在可以为嵌套在Append或MergeAppend中的节点利用增量排序。当嵌套节点可以产生部分排序的数据时规划器将倾向于增量完成排序而不是对整个结果集执行完整排序。 域约束验证不再阻塞 DML 操作提交[16a0039dc0d]ALTER DOMAIN … VALIDATE CONSTRAINT …命令现在使用较轻的SHARE UPDATE EXCLUSIVE锁而不是之前的SHARE锁。这允许对使用该域的表进行并发修改而不会阻塞。⚙️ CHECKPOINT 命令参数提交[cd8324cc89a], [bb938e2c3c7], [a4f126516e6], [2f698d7f4b7], [8d33fbacbac]CHECKPOINT命令现在支持参数# \h checkpointCommand:CHECKPOINTDescription:forceawrite-ahead logcheckpointSyntax:CHECKPOINT[(option[,...])]whereoptioncan be oneof: FLUSH_UNLOGGED[boolean]MODE{ FAST|SPREAD } URL: https://www.postgresql.org/docs/devel/sql-checkpoint.html当启用FLUSH_UNLOGGED时未记录对象表、索引等的修改缓冲区将被刷新到磁盘。此参数默认禁用。此参数有助于在大量使用未记录表时减少服务器重启时间。在关闭服务器之前可以手动运行CHECKPOINT以便在关闭期间后续的检查点完成得更快。以前未记录对象的脏缓冲区不会被检查点进程刷新到磁盘——这仅在关闭期间发生。现在通过手动运行带有FLUSH_UNLOGGED ON的检查点可以在服务器停止前卸载更多工作。为了避免给系统带来过多的写入负载可以使用另一个新参数MODE SPREAD运行预备检查点。检查点将随时间分散进行并考虑checkpoint_completion_target值。这类似于pg_basebackup工具中的--checkpoint{fast|spread}参数。 COPY FROM跳过初始行提交[bc2f348e87c]COPY命令的header参数现在除了接受true/false和match之外还可以接受一个正数值。这个数字指定命令在开始将数据加载到表之前应跳过的初始行数。CREATETABLEtest(idint);# COPY test FROM stdin (header 3);Enterdatatobe copied followedbya newline.Endwitha backslashanda periodona linebyitself,oran EOF signal.firstheaderrowsecondheaderrowthird,thendata12\.COPY2SELECT*FROMtest;id----12(2rows)请注意在这种情况下您将无法使用header match选项在文件和表之间映射列。 pg_dsm_registry_allocations动态共享内存使用提交[167ed8082f4]新的系统视图pg_dsm_registry_allocations提供有关动态共享内存分配的信息。# \d pg_dsm_registry_allocationsViewpg_catalog.pg_dsm_registry_allocationsColumn|Type|Collation|Nullable|Default----------------------------------------------name|text|||type|text|||size|bigint|||