Dinesh Kumar：ALTER FUNCTION SET work_mem：无需全局变更即可修复 PostgreSQL 磁盘溢出
内容提要
订单API在无部署变更时吞吐量骤降，原因是fetch_all_products()函数排序超宽产品行时超出默认work_mem，每天向磁盘溢出150GB。全局调高work_mem会危及服务器内存，因此使用ALTER FUNCTION ... SET work_mem TO '128MB'仅对该函数生效，磁盘写入降为零，延迟恢复。
延伸解读
为何全局调整 work_mem 风险高
work_mem 并非每个连接的总内存限制，而是每个排序或哈希操作可用的内存上限。一个复杂查询可能同时进行多个排序或哈希操作，从而使用数倍于 work_mem 的内存。若为单个函数全局调高 work_mem，所有并发连接都会获得相同额度，在高峰负载下极易导致服务器内存耗尽，引发更严重的故障。因此，将调整限定在特定函数内，是避免影响其他会话的稳妥做法。
函数级 work_mem 的作用域与验证
通过 ALTER FUNCTION ... SET work_mem，配置仅在该函数执行期间生效，函数退出后即恢复原值。文章中的演示表明，同一语句内会话的 work_mem 仍为 4MB，而函数内部为 128MB。要确认设置已附加，可查询 pg_proc 的 proconfig 列；如需移除，使用 ALTER FUNCTION ... RESET work_mem。这种作用域隔离确保调整不会泄漏到其他查询或会话。
索引为何在此场景无效
该查询的排序发生在 jsonb_agg 内部，排序对象是查询过程中动态构建的行，包含所有产品列、base64 编码的描述以及计算出的折扣、标签等字段。这些行并非直接来自表，而是由 CTE、子查询和 row_to_json 生成，因此没有现成索引可供规划器使用。即使 ORDER BY 仅针对一个整数列，PostgreSQL 也必须排序整个宽行，导致内存需求远超默认 work_mem，最终溢出到磁盘。
从磁盘溢出到零写入的验证方法
文章通过对比调整前后 24 小时的临时文件写入量来验证效果：函数调用次数均为 1,135 次，但临时文件写入从 150 GB 降至 0 GB。这种基于实际指标的对比，能直观证明函数级 work_mem 是否解决了溢出问题。若发现某个函数贡献了大部分临时文件写入，可优先考虑用 ALTER FUNCTION ... SET work_mem 进行针对性修复，而非全局调整。
Q&A
PostgreSQL 中如何只针对某个函数调整 work_mem，而不影响全局配置？
可以使用 ALTER FUNCTION 语句为特定函数设置 work_mem，例如：ALTER FUNCTION fetch_all_products() SET work_mem TO '128MB'; 该设置只在该函数执行期间生效，函数退出后恢复原值，不会影响其他会话或全局配置。
为什么全局提高 work_mem 可能带来风险？
work_mem 不是每个连接的限制，而是每个排序或哈希操作的限制。一个复杂查询可能使用多个 work_mem，且每个并发连接都会获得相同的额度。全局提高 work_mem 可能导致服务器在高峰负载下内存压力过大，引发新的问题。
如何确认函数级别的 work_mem 设置已经生效？
可以查询 pg_proc 目录表，例如：SELECT proname, proconfig FROM pg_proc WHERE proname = 'fetch_all_products'; 如果设置成功，proconfig 列会显示 {work_mem=128MB}。
PostgreSQL 中排序操作何时会溢出到磁盘？
当排序所需内存超过 work_mem 限制时，PostgreSQL 会将排序数据写入临时文件并在磁盘上继续处理。例如，对宽行（如包含 base64 大字段）进行排序时，即使只按一个整数列排序，也会因为需要排序整行而消耗大量内存，容易超出默认的 4MB work_mem。
函数级 work_mem 设置的作用范围是什么？
该设置仅在该函数执行期间生效，函数内部的所有查询都会使用新的 work_mem 值。函数退出后，会话的 work_mem 恢复为之前的值，其他会话不受任何影响。
如何移除函数上设置的 work_mem？
可以使用 ALTER FUNCTION 语句重置，例如：ALTER FUNCTION fetch_all_products() RESET work_mem; 这将移除该函数的 work_mem 设置，恢复为默认值。
