如何利用 PostgreSQL 新特性提升递归查询性能
TuBrief 편집팀
2026년 8월 7일
0
Computing/Software원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
커뮤니티의 다른 글
댓글 (0)
Log in to leave a comment
아직 작성된 글이 없습니다
원본 영상을 바탕으로 AI의 도움을 받아 작성했습니다. 원본 영상이 기준입니다.
Log in to leave a comment
아직 작성된 글이 없습니다
在传统系统中，用于探索父子关系或多条路径的递归 CTE 在数据变深时就会失效。一旦索引扇出发生动摇，数据库引擎就会在每个步骤中创建临时表并重复进行自连接。结果集填满内存并溢出到磁盘，从而导致内存溢出（Spillover）。此时 I/O 被阻塞，CPU 耗尽。虽然 PostgreSQL 14 中引入的原生 CYCLE 语法降低了数组搜索成本，但随着搜索深度的增加，重复的辅助索引查找以及大容量元组内存复制的成本依然存在。
为了提高查询速度并节省 CPU，必须切换到基于 SQL/PGQ 的原生图查询语法。首先，在保持现有关系表物理结构不变的情况下，使用 CREATE PROPERTY GRAPH 语句。将单实体表指定为 VERTEX TABLES，将多对多交集表指定为 EDGE TABLES。其次，由于作为键指定的列不会自动包含在图属性中，因此应在 PROPERTIES 子句中写入字段，以确保 GRAPH_TABLE 的过滤权限。最后，对于搜索深度超过 3 层、平均分支率大于 10 个或元组数超过 1000 万的查询，应将其更改为 GRAPH_TABLE MATCH 模式。复杂的递归代码将被简化为单行模式，内存缓冲区的使用量也会显著减少。
在分布式环境中，当多个线程同时写入数据时产生的重复插入错误，无法通过应用程序锁来解决。这会产生网络 RTT 开销，并且在事务提交前的空隙中会爆发竞态条件（Race Condition）。必须通过 INSERT ON CONFLICT 和 MERGE 语句将行级锁内化。ON CONFLICT 语句使用投机插入（Speculative Insertion）技术对唯一索引页加锁并尝试插入。如果发生冲突，则立即分支到 DO UPDATE 或 DO NOTHING，从而降低死锁会话的频率。
在使用原子语句时，更改事务隔离级别会产生巨大的连锁反应。在 READ COMMITTED 下，后续事务会等待先前事务提交后，重新读取最新元组来进行安全处理。但是，如果将其提高到 REPEATABLE READ 或 SERIALIZABLE，当与先前的事务发生冲突时，系统会不经等待直接抛出序列化失败错误并进行回滚。为了减少回滚成本并防止连接池耗尽，应该只对 40001 和 40P01 错误代码设置重试循环。将随机抖动值（Jitter）添加到基本等待时间中以散开尝试时间点，并将最大重试次数限制在 3 到 5 次之间，然后抛给上层业务层。
捕获由于块碎片和死元组堆积而导致索引扫描效率下降的时刻，是存储管理的开始。由于 MVCC 架构的特性，通过 UPDATE 或 DELETE 产生的死元组不会立即返回给操作系统，而是作为碎片保留下来。当同时查看 pg_stat_user_tables 视图的 n_dead_tup 指标和 pgstattuple 扩展时，如果 dead_tuple_ratio 超过 20% 或者 free_space 比例占到 30%，则必须回收磁盘空间。如果置之不理，I/O 块读取将会增加，并且缓冲区缓存效率会崩溃。
要在不停止服务的情况下在后台回收存储，可以使用 pg_repack 工具。首先，为目标源表创建一个影子表（Shadow Table）并附加日志触发器以跟踪更改。其次，将有效元组大量复制到影子表中，以异步并行方式重新生成索引，然后更改系统目录。最后，直接在终端中执行后台进程命令。
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
为了防止锁竞争，可以在会话级别设置 SET lock_timeout = '3s';，这样如果在 3 秒内无法获取锁，它就不会停留在队列中而是直接报错。通过在此基础上附加 --wait-timeout=10 选项，可以切断后续查询被连锁阻塞的情况。
如果统计信息收集周期错位导致查询计划突然改变，就会导致生产环境故障。在集中处理大容量作业的地方，统计信息与实际数据分布会产生偏差，从而发生计划翻转（Plan Flip）。如果优化器选择嵌套循环连接（Nested Loop Join）而不是索引扫描，就会导致 CPU 尖峰和 I/O 瓶颈。必须将 auto_explain 模块放入 shared_preload_libraries 中，并将 log_min_duration 设置为 500 毫秒，将慢查询的实际执行计划记录到服务器日志中，这样才能进行诊断。
如果要强制固定特定查询的执行路径，可以使用提示模块（Hint Module）。在日志中捕获问题查询后，加载 pg_hint_plan 扩展，在 SQL 语句前添加注释，或者将查询注册到 hint_plan.hints 目录表中。如果在难以分发源代码的情况下，可以直接在目录端注入静态提示字符串，从而无需重新分发即可立即固定计划。这是在因重新收集统计信息或重新生成索引而需要花费时间的紧急情况下，保护查询响应速度的最快方法。