内容提要
PostgreSQL 19 因多次回退失去了属性图查询、分区合并等特性,但仍有多项亮点:新增 REPACK (CONCURRENTLY),基于逻辑解码在线重写表,不阻塞读写;INSERT … ON CONFLICT DO SELECT 可原子返回冲突行;窗口函数支持 IGNORE NULLS;WAIT FOR LSN 让备库等待指定 WAL 位置,避免陈旧读(存在边界 bug,建议设置超时)。此外还有查询性能优化、自动清理并行化与优先级调度、普通查询可标记全可见页,以及 pg_plan_advice 等新模块和逻辑复制改进。
延伸解读
REPACK (CONCURRENTLY) 的适用边界
REPACK (CONCURRENTLY) 基于逻辑解码实现在线表重写,不阻塞读写,但要求表有主键或 replica identity 索引,且不支持分区表、unlogged 表、物化视图和系统目录,也不能在事务块内运行。与 pg_repack 相比,它缺少按任意列排序、移动表空间和仅重建索引等功能。若这些限制可接受,它提供了核心内置的在线整理方案。
WAIT FOR LSN 的已知缺陷与使用建议
WAIT FOR LSN 让备库等待指定 WAL 位置,可缓解异步复制下的陈旧读问题。但 19.0 存在已知 bug:当 LSN 恰好落在 WAL 页边界时,等待可能挂起直到超时或无关 WAL 写入,测试中约 2000 次出现 1 次。修复已提交但未进入 19.0,预计 19.1 包含。作者建议生产环境暂不使用,或至少始终指定 TIMEOUT。
自动清理的并行化与优先级调度
PostgreSQL 19 让 autovacuum 支持并行索引清理,通过 autovacuum_max_parallel_workers 和表级 autovacuum_parallel_workers 控制,默认关闭。并行 worker 来自 max_parallel_workers 池,小索引仍由主 worker 处理。同时引入基于评分的优先级调度,可配置冻结、多事务冻结、vacuum 等权重,并通过 pg_stat_autovacuum_scores 查看分数,避免接近 wraparound 的表被小表阻塞
普通查询可标记全可见页的影响
此前只有 VACUUM 和 COPY ... FREEZE 能将页标记为 all-visible。PG19 中,简单 SELECT 的顺序扫描若确认页内所有记录可见,也可设置该位。这对以 INSERT 和 SELECT 为主的工作负载有益:新插入页无需等待 vacuum 即可被后续扫描跳过可见性检查,index-only scan 也能直接受益。但扫描只设置 all-visible,不设置 all-frozen,冻结仍由 vacuum 负责。
Q&A
PostgreSQL 19 中 REPACK (CONCURRENTLY) 和 pg_repack 有什么区别?
REPACK (CONCURRENTLY) 是核心功能,基于逻辑解码捕获变更,而 pg_repack 使用行级触发器。REPACK 在 wal_level=replica 下即可工作,无需启用逻辑复制;pg_repack 则需要在写入会话中记录变更到日志表。此外,REPACK 功能更受限,例如要求主键或 replica identity 索引,不支持分区表、无日志表、物化视图和系统目录,且不能在事务块内运行。
INSERT ... ON CONFLICT DO SELECT 有什么用处?
它允许在插入冲突时原子地返回冲突行,而不是执行更新或忽略。例如:INSERT INTO users (email, name) VALUES ('alice@example.com', 'Alice') ON CONFLICT (email) DO SELECT RETURNING id; 无论插入成功还是冲突,都会返回该用户的 id。这避免了使用虚拟更新(导致膨胀和触发器)或 CTE 组合可能返回空结果的问题。RETURNING 子句是必需的,还支持锁定子句(如 FOR UPDATE)和 WHERE 条件。
窗口函数中的 IGNORE NULLS 怎么用?
IGNORE NULLS 用于窗口函数(如 lag、lead、first_value、last_value、nth_value),在计算时忽略 NULL 值。例如:SELECT ts, temperature, lag(temperature) IGNORE NULLS OVER (ORDER BY ts) AS previous_known FROM readings; 这样 previous_known 列会跳过 NULL,显示上一个非 NULL 的温度值。这简化了之前需要复杂查询才能实现的功能。
WAIT FOR LSN 如何解决备库陈旧读问题?
WAIT FOR LSN 允许应用程序等待备库重放到指定的 WAL 位置后再执行读操作。应用在主库提交后获取当前 WAL 插入位置(pg_current_wal_insert_lsn()),然后在备库执行 WAIT FOR LSN '0/306EE20' WITH (TIMEOUT '1s'); 确保备库已应用该写入,从而避免读到旧数据。这不需要同步复制。但注意存在已知 bug:如果 LSN 正好在 WAL 页边界,等待可能挂起,建议始终设置 TIMEOUT,该修复将在 19.1 中提供。
PostgreSQL 19 的自动清理(autovacuum)有哪些改进?
主要改进包括并行索引清理和基于优先级的调度。并行索引清理允许 autovacuum 使用多个工作进程并行处理索引,通过 autovacuum_max_parallel_workers 和每表的 autovacuum_parallel_workers 设置控制。优先级调度引入了基于冻结、多事务冻结、清理、插入清理和分析等维度的评分系统,可通过权重配置,并新增 pg_stat_autovacuum_scores 视图查看评分,从而优先处理更紧急的表(如接近事务回卷的表)。
pg_plan_advice 和 pg_stash_advice 模块是做什么的?
这两个是 contrib 中的新模块,需手动加载。pg_plan_advice 允许用户控制查询规划器的决策,例如通过 EXPLAIN (PLAN_ADVICE) 生成当前计划的建议,然后使用 SET pg_plan_advice.advice 强制使用特定计划(如改变连接顺序或哈希表构建侧)。pg_stash_advice 可以按查询 ID 存储这些建议并自动应用,用于锁定不稳定的计划,未来可能支持自动查询调优。