Production Postgres: set work_mem for each user26 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
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.
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.