最近的一套postgresql（12.6）数据库在进行uat测试的时候，发现服务器空间爆满，导致了postgresql异常关闭。问题发生后，现场的dba让我配合做了如下的一些分析，这里去除敏感信息，分享给大家。
问题发生的时候，现场的dba其实经过检查发现了暴涨的源头是这个pgsql_tmp目录,
而且现场的dba在postgresql异常关闭前，用strings去看pgsql_temp里面的文件，确认是对应的数据，而且根据文件的命名方式，通过pid查询到了产生临时文件的sql，sql是一个select语句，并且涉及到fdw远程表，这方便了我们后续对问题的分析。
select * from pg_stat_activity where pid='24518';
通过查看这个sql的执行计划，我们可以看到有两个sort环节，而且都是在postgresql端执行的。
然后看一下涉及的sort列的表的情况，发现这几列都是字符类型的。要知道oracle_fdw的排序字段是字符类型的时候是不会不下推到oracle端的，但是数字和时间类型都是可以的，因此这个sql的排序他会先把数据拿到postgresql的内存里，即work_mem里去做这个sort操作。
下推：fdw采用一种称为 pushdown 的机制，它允许远程端执行 where、order by 和 join 子句。下推 where 和 join 减少了本地和远程服务器之间传输的数据量，避免了网络通信瓶颈。
到这里，我和现场dba确认了下他们work_mem的大小，经过确认，他们work_mem的值为4mb,也就是默认值，对于这个sql来说，work_mem明显太小了，这个参数是写入临时文件之前内部排序操作和散列表使用的内存量，超出后，就会溢出到磁盘写到pgsql_tmp下的临时文件里，当开始将临时文件写入磁盘时，显然会比内存慢得多，而且临时文件的太大也可能把空间填满。
其实可以在启用log_temp_files后，在postgresql日志中搜索temporary file来查看是否溢出到了磁盘。如果看到了temporary file，就意味着可能需要增加work_mem了。
而且不容忽视的一点是：work_mem这个参数和max_connection是有联系的，假设你设置为10mb，则500个用户同时执行查询排序，很快就会使用5gb的实际内存。或者涉及复杂计算，涉及几张表的合并，就要用到几倍的work_mem。因此work_mem是不适宜设置的很大的，如果设置超出256mb，其实很容易因为瞬间的大并发操作导致oom问题
此外，其实可以通过使用explain analyze 这个sql去查看这个它执行所需要的内存大小，但生产的环境基本不允许我们随意explain analyze，因为它会实际执行sql，如果是问题sql，不仅可能影响数据库性能、严重甚至造成宕机。而且explain analyze命令跟insert、update、delete的操作更是会修改数据，这是我们绝对不能做的。
下边我在自己的测试环境给大家演示一下，work_mem对于执行计划的影响，我原本的work_mem值也是4mb没有修改：
可以看到，这个sql的执行计划里，sort method部分是external merge disk，代表了外部合并磁盘，也就是溢出到了磁盘，生成了临时文件。可以看到这个sql的执行计划里disk：25864kb，大概有25mb，那我work_mem的值改成128mb，会怎样呢 ,如下所示，相同的sql，sort method部分变成了quicksort memory ，此时sort的数据都是放在内存里的，而没有溢出磁盘。
通过上面的分析，我们知道这次的问题，主要是出在了oracle_fdw处理sql的sort操作的的时候字符类型不能下推，而且在postgresql本地进行sort的时候，超出了work_mem的设置，导致溢出到磁盘，我们可以把work_mem的值稍微提升一点，但一定不要过大，否则业务的瞬间高并发很可能导致oom。而且对于work_mem的大小我们肯定是要根据平时的业务以及并发量来综合评估的，基本确定了一个值不会去轻易改动，不能对于个别sql进行调整，这样也不利于数据库的稳定性。因此，最好的方式还是结合业务看是否这条sql能进行调整，修改sort的列的类型，或者调整写法让其能下推到oracle端执行，减少传输到postgresql本地的数据量。除此之外我们也可以使用temp_file_limit这个参数对此类临时文件的大小做一个限制，避免数据目录的爆满。