Don’t Waste Memory — Set `work_mem` per (db) User in PostgreSQL

PostgreSQL lets you set work_mem per user, which is useful when you have varying workloads hitting the database.

Most user-facing queries don’t need as much memory as overnight analytics jobs. They typically return fewer rows and involve fewer joins, so keeping work_mem low helps avoid exhausting memory on the database server.

Admin portals and analytics jobs have a different profile — they tend to involve multiple joins and large result sets. Those benefit from a much higher work_mem.

To make the most of available memory, it’s important to set a lower value for the app’s DB user, and a much higher one for the admin user and any roles running reports.

How to do it

You can give any user their own work_mem setting using ALTER ROLE:

ALTER ROLE admin_portal SET work_mem TO '64MB';

To confirm it worked, connect as that user and run:

SHOW work_mem;

Here’s what it looks like in practice — different users, different work_mem values:

Hope this helps!