内容提要
本文对比PostgreSQL 19与18:19版核心新增原生REPACK(CONCURRENTLY)在线整理表膨胀、pg_plan_advice查询计划稳定、INSERT…ON CONFLICT DO SELECT原子获取或创建、并行索引autovacuum及自适应异步I/O;默认改为jit关闭、TOAST用lz4、开启锁等待日志,逻辑复制无需重启。SQL/PGQ图查询因目录稳定性问题被撤回,最早20版。升级需删除io_workers等旧参数、处理btree_gist索引并迁移MD5认证。
延伸解读
原生 REPACK 的在线整理机制与适用边界
PostgreSQL 19 将在线表整理能力内置到核心,REPACK (CONCURRENTLY) 在复制阶段仅持有 SHARE UPDATE EXCLUSIVE 锁,允许常规读写继续,并通过临时逻辑复制槽捕获增量,最后短暂获取 ACCESS EXCLUSIVE 完成文件交换。这为高膨胀 OLTP 集群提供了无需外部扩展的零停机整理选项。但文章也指出,该命令主要针对标准在线表和索引压缩,并未声称覆盖所有 pg_repack 场景,实际使用前仍需评估复制槽开销与切换瞬间的锁影响。
pg_plan_advice 如何降低升级中的计划回退风险
大版本升级后,个别关键查询的执行计划可能因统计信息变化而翻转,例如从 Hash Join 变为嵌套循环,导致 CPU 饱和。PostgreSQL 19 引入 pg_plan_advice 和 pg_stash_advice,允许 DBA 按查询标识附加 JOIN_ORDER、HASH_JOIN、SEQ_SCAN 等计划建议,而无需修改 ORM 生成的 SQL。这为升级期间稳定关键报表查询提供了核心层手段,但文章强调应基于生产查询日志预先建立基线,而非盲目依赖建议覆盖所有查询。
默认值变更对生产负载的直接影响
PostgreSQL 19 将 jit 默认关闭,原因是 JIT 编译在短 OLTP 查询上常因成本误估而增加延迟;同时默认 TOAST 压缩改为 lz4,新写入的 jsonb 和大文本压缩解压更快,已有行需重写后才生效。此外 log_lock_waits 默认开启,超过 deadlock_timeout 的锁等待会自动记录,提升可观测性。这些默认值调整意味着从 18 升级后,OLTP 延迟更可预测,但重 OLAP 聚合负载需显式测试 jit = on 是否仍有收益。
升级前必须处理的配置与兼容性阻断项
从 18 升级到 19 时,若 postgresql.conf 中保留已移除的 io_workers 或 escape_string_warning 参数,实例将因配置错误无法启动。此外,遗留的 btree_gist inet/cidr 索引会导致 pg_upgrade 中止,对象名中的回车或换行符会被拒绝,旧转储需用 v19 工具重新生成以适配强制开启的 standard_conforming_strings。安全方面,MD5 登录会发出警告,UDP RADIUS 认证被移除,应迁移至 SCRAM-SHA-256
Q&A
PostgreSQL 19 相比 18 最大的变化是什么?
PostgreSQL 19 是运维导向的版本,核心新增包括:原生 REPACK (CONCURRENTLY) 在线整理表膨胀、pg_plan_advice 查询计划稳定、INSERT ... ON CONFLICT DO SELECT 原子获取或创建、并行索引 autovacuum、自适应异步 I/O(io_min_workers/io_max_workers),以及默认值调整(jit 关闭、TOAST 用 lz4、开启锁等待日志、逻辑复制无需重启)。
PostgreSQL 19 的 REPACK (CONCURRENTLY) 如何实现在线整理表膨胀?
REPACK (CONCURRENTLY) 先获取 SHARE UPDATE EXCLUSIVE 锁,允许正常读写;然后创建临时逻辑复制槽捕获并发写入;复制完成后重放增量,仅在最后交换表文件时短暂持有 ACCESS EXCLUSIVE 锁。整个过程无需外部 pg_repack 扩展,实现零停机整理。
pg_plan_advice 能解决什么问题?
pg_plan_advice 用于在大版本升级后稳定查询计划,避免关键查询因统计信息变化而执行计划翻转(如从 Hash Join 变为嵌套循环)。DBA 可以按查询标识符附加计划建议(JOIN_ORDER、HASH_JOIN、SEQ_SCAN、INDEX_SCAN),无需修改应用代码即可让优化器遵循预期策略。
INSERT ... ON CONFLICT DO SELECT 有什么好处?
它实现了原子性的“获取或创建”操作,只需一次服务器往返即可完成插入或返回已存在行,并可选行级锁(FOR UPDATE/FOR SHARE)。相比之前先 INSERT ... ON CONFLICT DO NOTHING 再 SELECT 或使用异常处理的方式,减少了竞态条件和子事务开销。
PostgreSQL 19 为什么将 jit 默认关闭?
自 PostgreSQL 12 起 JIT 默认开启,但其成本估算在短 OLTP 查询上经常误判,增加编译开销却无收益。关闭 jit 可使 OLTP 延迟更可预测;对于重分析型 OLAP 查询,升级后可显式设置 jit = on 并做基准测试。
从 PostgreSQL 18 升级到 19 时,pg_upgrade 有哪些常见陷阱?
主要陷阱包括:必须从 postgresql.conf 中删除已移除的 io_workers 和 escape_string_warning 参数,否则启动失败;需删除或重建基于 inet/cidr 的旧 btree_gist 索引(gist_inet_ops/gist_cidr_ops);检查数据库/角色名是否含回车或换行;用 v19 pg_dump 重新生成转储以应对 standard_conforming_strings 强制开启;计划迁移 MD5 认证到 SCRAM-SHA-256,并注意 UDP RADIUS 认证已移除。
SQL/PGQ 图查询在 PostgreSQL 19 中可用吗?
不可用。尽管 SQL/PGQ(CREATE PROPERTY GRAPH 和 GRAPH_TABLE)在 PostgreSQL 19 早期开发中已提交并在早期 beta 中测试,但核心提交者在 Beta 4 前回退了约 47 个提交,以避免过早锁定目录复杂性。原生 SQL/PGQ 最早计划于 PostgreSQL 20 发布。目前需要图查询可使用 Apache AGE 或专用图数据库。