Give a “super” user more work_mem

One common pattern I’ve noticed over the years is that people tend to keep increasing working memory as their data size increases, so that more complex queries can still take full advantage of memory speed, but for some extreme tasks (e.g., reporting).

It could be advantageous to increase that number drastically only for some outlier queries that are seldom run. For instance, I might want to keep work_mem at eg. 8 MB for normal operations but then run a specific query with a specific client/user having something like 250 MB of working memory so that I can do drastic things entirely in memory during a single session, quickly.

Is this possible? Am I completely off base for thinking of this as a solution?

You have a few possibilities.

To change work_mem for a user is easy:

ALTER ROLE alice SET work_mem TO '5GB';

If you can arrange things like alice is the user running those heavy queries, this might be enough. If this number is huge (a significant portion of the RAM you have on your server), it makes sense to limit the user to have only a small number of connections (even 1 might make sense) at any time:

ALTER ROLE alice WITH CONNECTION LIMIT 2;

If this is not feasible, you can store your queries as functions. The advantage of this is that you can set chosen parameters when defining the function, or after it:

ALTER FUNCTION heavy(integer) SET work_mem TO '2GB';
ALTER FUNCTION very_heavy(integer) SET work_mem TO '13GB';

StackExchange Database Administrators Q#202306, answer score: 6