postgres work_mem at user level

Question

If work_mem is set at user level by using below statement, assume this gets applied to each query run by this user?

alter user user1 set work_mem='64MB';

If a function is executed with security definer - will it run with user1's work_mem or the work_mem of function owner?
If a function is executed with security invoker and there is a work_mem set to say 1GB - will it run with user1's work_mem or the work_mem set in the function?
If user1 is executing multiple sessions, does each session get its own 64MB memory or use one chunk of 64MB set at user level?

The idea here is to give certain users more memory.

Laurenz Albe · Accepted Answer · 2021-01-27 04:38:43Z

If you change a parameter with ALTER ROLE, that parameter is set when the role connects to the database.

If you later change your user context, for example with SET ROLE or by executing a SECURITY DEFINER function, the parameter is not changed.

If you set a parameter with ALTER FUNCTION ... SET, the parameter is set to that value for the duration of the execution. Such a setting temporarily overrides the value set when the database session started.

work_mem does not limit the amount of memory for all sessions of a user, and it does not limit how much memory a single backend can use. It limits the amount of memory available for a single step in an execution plan (sort, hash or bitmap). If a single query execution plan contains more than one memory-hungry execution steps, this query can use several times work_mem.

My rule of thumb for configuring memory is:

work_mem * max_connections + shared_buffers ≤ RAM

Collectives™ on Stack Overflow

postgres work_mem at user level

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related