⟵ Posts

Production Postgres: set work_mem for each user

26 Apr 2022

Did you know you can set a different value for work_mem for each database user in postgres? So, what’s the upshot of this? Lots of savings on memory!

Queries from the front facing “web” fleet are short with simple lookups and joins where a lower work_mem is ideal. Admin-portals issue queries that are more complex with more sorts and joins, and benefit from a higher work_mem setting.

Using the same setting for both fleets leads to wastage (over allocation of memory). The queries from the web fleet don’t need the extra memory granted to them.

You can stop over allocating memory by using different database username for each fleet, and assigning a more appropriate value for work_mem to each user. A low one for the web fleet, and a higher one for admin-portals.

work_mem setting persists between restarts

Hope this helps!


Is your database getting crunchy? Need an extra set of eyes on a bottleneck? Reach out, say hi 👋🏽! I’m available for hire.