ALTER FUNCTION SET work_mem: Fixing PostgreSQL Disk Spills Without a Global Change

An order API slowed to half its volume in peak season, with no deployment to blame. The cause was a sort spilling 150 GB a day to disk. The fix was one line of SQL, scoped to one function.

A slowdown that follows a deployment is easy to investigate: you roll back and compare. A slowdown with no deployment behind it is much harder, because there is no obvious change to point to.

That is what happened to one of our products. Over two days, throughput (TPS) fell sharply, and incoming orders dropped to about half their usual volume. We checked every code repository and found no deployments in the previous week. It was also the festival season, our busiest time of year, so we needed a fix within hours.

The fix turned out to be a single statement:

ALTER FUNCTION fetch_all_products() SET work_mem TO '128MB';

This article covers how we got there, why we did not raise work_mem for the whole server, and how to verify that a function-level setting is doing what you expect.

Finding the slow path#

End-to-end API validations showed one API responding well outside our accepted KPI limits.

Most of our product data is served from a CDN. When the CDN cache is invalidated, the first request goes to the database to rebuild the content. That request was where the latency came from.

It led to the database function that builds the product catalogue, fetch_all_products(). It returns products grouped by category and ordered by their ranking. Sorting by rank is routine. The data being sorted was not:

• The catalogue holds more than 10,000 products.
• Each product row is about 160 KB, because product descriptions and details are stored inline as base64-encoded strings.

This is the query that generates the CDN content:

WITH products AS (
    SELECT p.*,                                         -- every product column
           (SELECT row_to_json(d) ...)    AS discount,
           (SELECT array_agg(t.name) ...) AS tags,
           (SELECT jsonb_agg(...) ...)    AS timings
    FROM product p
    JOIN category c ON p.category_id = c.id
    WHERE c.active AND p.active
)
SELECT c.name, c.tax_type, c.view_option, c.sort, c.img,
       jsonb_agg(row_to_json(p) ORDER BY p.sort) AS products   -- the bottleneck
FROM products p
JOIN category c ON p.category_id = c.id
GROUP BY 1, 2, 3, 4, 5;

Root cause: a sort that spills to disk#

The ORDER BY p.sort inside jsonb_agg looks cheap because it sorts on one integer column. But PostgreSQL does not sort the key alone. It sorts the entire row that will go into the aggregate: every product column, the base64 payload, and the computed discount, tags and timings values.

With rows this wide, the sort needs far more memory than the default work_mem of 4 MB. Once a sort exceeds work_mem, PostgreSQL writes it to temporary files and continues on disk. So every cache invalidation caused a burst of temporary-file I/O, which pushed up API latency and slowed the rest of the application.

An index does not help here. The rows being sorted are built during the query (from a CTE, subqueries and row_to_json) rather than read directly from a table, so there is no index for the planner to use. That left two options:

1. Redesign how the CDN content is generated. This is the right long-term fix, but it was too slow and too risky in the middle of peak season.
2. Give the sort enough memory to finish in RAM by raising work_mem.

Why not raise work_mem globally?#

work_mem is not a per-connection limit. It is the limit for each sort or hash operation, so one complex query can use several multiples of it, and every concurrent connection gets the same allowance. A global increase large enough for this one function could push the server into memory pressure under peak load, which would simply replace one incident with another.

We needed the extra memory for one function only.

The fix: ALTER FUNCTION … SET work_mem#

PostgreSQL lets you attach configuration parameters to a function. The value takes effect when the function starts and is restored to its previous value when the function exits. Every query inside the function uses the new setting, and the rest of the session, and every other session, is unaffected.

ALTER FUNCTION fetch_all_products() SET work_mem TO '128MB';

The setting is stored in the function’s catalogue entry, so you can confirm it is attached:

SELECT proname, proconfig
FROM   pg_proc
WHERE  proname = 'fetch_all_products';

--       proname       |    proconfig
-- --------------------+------------------
--  fetch_all_products | {work_mem=128MB}

Proving the setting stays inside the function#

It is worth seeing the scoping for yourself. This small demo function returns the work_mem value it runs with (tested on PostgreSQL 17):

CREATE FUNCTION show_work_mem() RETURNS text
LANGUAGE sql AS $$ SELECT current_setting('work_mem') $$;

ALTER FUNCTION show_work_mem() SET work_mem TO '128MB';

SELECT current_setting('work_mem') AS session_work_mem,
       show_work_mem()             AS inside_function;

--  session_work_mem | inside_function
-- ------------------+-----------------
--  4MB              | 128MB

In the same statement, the session reports 4 MB and the function reports 128 MB. As soon as the function returns, the session is back at 4 MB.

To remove the setting later:

ALTER FUNCTION fetch_all_products() RESET work_mem;

The result: 150 GB a day to zero#

We compared temporary-file writes for the function over 24 hours before and after the change:

Stage	Function	Temp written (24 h)	Calls
Before	fetch_all_products	150 GB	1,135
After	fetch_all_products	0 GB	1,135

The function was called the same number of times, and disk spills went from 150 GB a day to none. API latency came back within KPI limits, the application returned to normal, and orders per second recovered.

If one function accounts for most of the temporary-file volume, ALTER FUNCTION ... SET work_mem fixes it where the problem is, without putting the rest of the server at risk.