起因
收到运维告警，数据库磁盘容量100%，一段时间后又降了下去，使用该数据库的服务是因为人员变动后流转到我手里维护的，当时听说过因为PostgreSQL问题进行了对应优化，优化前的表现也是类似将数据库临时表空间占满，所以我也想着从这方面入手排查，后续排查确实也验证了我的猜想。
问题梳理
该系统是用户关系的服务，用户与用户之间的粉丝关系形成一个树结构，使用PostgreSQL的递归查询返回用户的粉丝关系，查询语句也限制了最多只查询3层粉丝，避免深度问题压爆栈空间。
通过查询系统日志，发现某用户多次查询自己的粉丝列表，都超时了，PostgreSQL默认没有做限制，单个递归查询粉丝总数的语句执行了半小时将磁盘写满后抛出异常，异常抛出后也将临时文件回收了，这也符合运维描述。这就很尴尬，幸好平时该数据库压力较小，如果是其他重要的库就要引起大问题，因为该库只有一个服务在使用，报错的查询都是由同一个用户查询引起的，当即决定先配置语句执行超时时间为10秒，避免扩大影响范围。
日志的异常信息：
ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp2466359.17": No space left on device;
下面通过报错语句与表结构进行分析，表结构设计很简洁明了，关键字段只有user_id和parent_id，这里删减了多余字段
CREATETABLE user_relation ( id bigserial NOTNULL, user_id int8NULL,--用户id parent_id int8NULL,--上级用户id create_time timestamp(0)NULL, update_time timestamp(0)NULL, CONSTRAINT hsrj_user_relation_pkey PRIMARY KEY (id));CREATE INDEX idx_user_relation_parent_id ON public.user_relation USING btree (parent_id);CREATE INDEX idx_user_relation_create_time ON public.user_relation USING btree (create_time);CREATE INDEX idx_user_relation_user_id ON public.user_relation USING btree (user_id);
通过WITH语句进行递归
-- 递归字段WITH RECURSIVE T (user_id, parent_id,deep)-- 递归入口AS(SELECT user_id, parent_id,1as deep FROM user_relation WHERE channel_user_id = #{userId}-- UNION后面的语句就是递归语句，T为上次递归的数据-- 就是通过上次查询到数据的用户id作为当次的上级用户id查询UNION ALL SELECT d.user_id, d.parent_id,T.deep+1as deep FROM user_relation D JOIN T ON D.parent_id= T.channel_user_id)-- 递归完成后，筛选想要的数据，这里只统计数据总数SELECTcount(1)FROM T where channel_user_id != #{userId}and is_delete =0and deep<4
通过上面的语句可以看出，出现问题的语句限制了deep小于4，deep从1开始，每次递归加1，也就是3层，看完语句，好像没什么问题（不是），先看看前人做了什么优化吧。
先前优化
之前我是听说过该服务，使用PostgreSQL递归调用的时候也出现过一样的问题，之前的同事从业务和架构设计上进行了优化：
- 第一是从业务上优化，因为我们服务用户是分成两种角色的，可以简单理解成高级和普通会员，升为高级会员的时候会将下属所有粉丝都打上自己的标签，所以可以通过标签反查粉丝数据的，可以不用递归。
- 第二不查PostgreSQL，我们的用户数据都会写到ES上，所以这部分高级用户就查ES，剩下的普通会员因为大部分因为粉丝数量比较少无法升级为高级会员，所以性能上也不会有什么问题（大问题）
问题分析
陷入惯性
因为之前的优化，所以高级会员是不会出问题的，只是个ES简单查询，问题就是，异常的用户拥有有1000+粉丝，且是普通用户，为什么没有升级为高级会员，我们咨询了业务，说是有其他限制，和我们这次优化无关，可以忽略。
- 方案一：那按之前的优化，粉丝只有高级用户的标签的，普通用户没办法做到不递归查询，那我就想到，既然是小部分用户，我们将这部分用户记录下来，缓存他们的粉丝数据，定时更新缓存就行了，那可不美哉，转头又想到不行了，那查询一次不就将临时表压满了吗，这不尬住了吗。
- 方案二：过了几分钟，立刻又想到优化方案，既然是递归的问题，那我自己循环查询就好了，最多就循环3次，每次查询几毫秒最多十几毫秒，怎么也不会超过1秒，果真大聪明如我啊，新建一个表，将粉丝数超过1000的写到表里，查询的时候这部分用户循环查询，避免影响其他可以递归查询的用户，还想好了顾全大局的方案，公司没我真的不行了（bushi）。
醒悟
想好方案美美地我就下班了，下班路上隐隐觉得有什么不妥，既然分开执行性能是没问题的，为什么递归性能这么拉胯，这明显不符合逻辑啊！还有个致命问题，为什么查询单个用户的1000+个粉丝会压满300GB的数据库磁盘空间，递归3次怎么压栈也不可能压爆，隔天上班仔细看了几遍SQL，问题其实很简单，下面是我修改后的SQL：
-- 递归字段WITH RECURSIVE T (user_id, parent_id,deep)-- 递归入口AS(SELECT user_id, parent_id,1as deep FROM user_relation WHERE channel_user_id = #{userId}-- UNION后面的语句就是递归语句，T为上次递归的数据-- 就是通过上次查询到数据的用户id作为当次的上级用户id查询UNION ALL SELECT d.user_id, d.parent_id,T.deep+1as deep FROM user_relation D JOIN T ON D.parent_id= T.channel_user_id)and D.is_delete=0and deep <3-- 递归完成后，筛选想要的数据，这里只统计数据总数SELECTcount(1)FROM T where channel_user_id != #{userId}
变更的部分是and D.is_delete = 0 and deep < 3，原来的SQL是递归完成后筛选统计，会递归完全部粉丝，前面一直提到的递归3次根本就没起效果，会一直递归到查询不到数据为止，如果该用户在树的靠上层，公司亿级的用户数据绝对可以压爆数据库。
复盘
其实问题很简单，多加留心一下就能发现，因为排查的时候知道之前同事已经处理过类似问题了，该同事职级比我高，当时立项开发也有其他更高级的领导跟进过，我相信大佬们一定是都研究过相关方案的了，所以一直没有仔细去看SQL，看了看能走索引就完事了。
处理问题一定要从根源排查，数据说话，互相印证，不能人云亦云，要有独立的思考分析能力，这是这个BUG带来的思考，期望也能带给看到这篇文章的你们一点启发。
