Pavel Luzanov:PostgreSQL 17:第三部分或2023-11提交节

Pavel Luzanov:PostgreSQL 17:第三部分或2023-11提交节

💡 原文英文,约2600词,阅读约需10分钟。
📝

内容提要

PostgreSQL的11月commitfest中有一些新功能,包括数据库登录事件上设置触发器、REINDEX命令的事件触发器、ALTER OPERATOR命令的新参数、--filter参数用于指定导出时要包含或排除的对象列表、psql命令中默认权限的改进、pg_stat_statements的新列、pg_stat_bgwriter和pg_stats的改进、CTE材料化的优化、自连接查询的优化、范围类型列的统计信息、B-tree索引的优化、pg_upgrade迁移复制槽、复制命令的日志记录、unicode_assigned函数、AT LOCAL关键字、interval类型的无限值、ALTER SYSTEM命令的改进、源代码tar归档文件的改进。

🔎

延伸解读

登录触发器:灵活管控与潜在风险

PostgreSQL 17 允许在数据库登录事件上创建触发器,文章示例展示了在周末阻止非 postgres 用户登录。这为访问控制提供了新维度,但需注意触发器逻辑应谨慎设计,避免误拦合法用户或引入性能开销。同时,REINDEX 也支持事件触发器,扩展了 DDL 监控范围。

查询优化器改进:自连接消除与 CTE 统计

新版本能自动消除不必要的自连接,并利用物化 CTE 的列统计信息改进基数估计。文章示例中,原本因错误估计导致嵌套循环的查询,在 17 中改为哈希半连接,执行时间从约 10.8 秒降至 1.5 秒。这些优化对 ORM 生成的复杂查询尤其有益,但可通过 enable_self_join_removal 参数关闭。

运维与监控增强:统计视图与升级便利

pg_stat_bgwriter 拆分为更专注的视图,checkpointer 统计移至 pg_stat_checkpointer,后端缓冲区信息可参考 pg_stat_io。pg_stat_statements 新增 stats_since 列,便于采样监控。pg_upgrade 现在迁移复制槽,减少逻辑复制订阅者重新同步的需要。log_replication_commands 还能记录复制槽的获取与释放,有助于诊断空闲问题。

数据类型与 SQL 兼容性:无限区间与 AT LOCAL

interval 类型支持 infinity 和 -infinity,允许涉及无限区间的算术运算。AT LOCAL 关键字可替代 AT TIME ZONE 表示当前时区,提升 SQL 标准兼容性。此外,unicode_assigned 函数可检查字符串字符是否均有 Unicode 码点分配,xmltext 函数则用于安全转义 XML 特殊字符。

❓

Q&A

PostgreSQL 17 中如何限制用户在周末登录数据库?

可以通过在数据库登录事件上创建事件触发器来实现。首先创建一个返回 event_trigger 的函数,在函数中检查当前日期是否为周六或周日,如果是则抛出异常;然后使用 CREATE EVENT TRIGGER ... ON LOGIN 创建触发器。这样用户周末登录时会收到提示并拒绝连接。

pg_dump 的 --filter 参数有什么作用?

--filter 参数允许用户指定一个文件,文件中列出要包含或排除的数据库对象。例如,可以只导出某些表或排除某些表的数据。当对象列表太长而无法直接放在命令行中时,这个参数特别有用。pg_dumpall 和 pg_restore 也支持该参数。

PostgreSQL 17 对 pg_stat_bgwriter 视图做了哪些改进?

pg_stat_bgwriter 视图的列数被大幅精简。所有检查点相关的统计信息被移到了新的 pg_stat_checkpointer 视图中。同时,buffers_backend 和 buffers_backend_fsync 列被移除,因为更详细的后端进程信息现在可以在 PostgreSQL 16 引入的 pg_stat_io 视图中找到。

PostgreSQL 17 如何优化自连接查询?

PostgreSQL 17 的查询规划器能够识别并消除不必要的自连接。例如,当查询中一个表与自身进行半连接,但实际只需要扫描一次时,规划器会移除多余的连接。该优化由参数 enable_self_join_removal 控制,默认开启。关闭该参数则恢复旧版本的行为。

PostgreSQL 17 中 CTE 材料化有哪些性能提升?

PostgreSQL 17 的规划器可以利用材料化 CTE 的列统计信息,从而在外层查询中做出更准确的行数估算,选择更合适的连接方式。例如,在某个案例中,消除了不必要的分组并使用了哈希连接,使查询执行时间从约 10.8 秒降低到约 1.5 秒,提升了约 7 倍。

pg_upgrade 在 PostgreSQL 17 中如何改进逻辑复制升级?

在 PostgreSQL 17 中,pg_upgrade 现在会迁移复制槽到新版本。这样,当发布者升级后,订阅者只需调整连接字符串即可继续接收更新,而无需重新同步数据。这解决了之前复制槽不迁移导致订阅者必须重新同步的问题。

🏷️

标签

➡️

继续阅读