How can I check and configure `work_mem` on my Heroku Postgres database?

Issue

I want to check what's the current value for work_mem in my database and learn how to change it.

Resolution

The work_mem setting in PostgreSQL sets the base maximum amount of memory to be used by a query operation (such as a sort or hash table) before writing to temporary disk files.

While this value can be altered and configuted by customers depending on their needs, the default configuration for work_mem for Heroku Postgres databases depends on the database plan and you can check the default work_mem values for each of those in the "Understanding Data Caching" Dev Center article.

Checking work_mem

In order to check the current work_mem value for your database, open a heroku pg:psql session and run:

SHOW work_mem;

Modifying the work_mem value

A different work_mem value can be set:

• Per transaction: SET LOCAL work_mem = '100 MB';
• Per session: SET work_mem = '100 MB';
• Per role: ALTER ROLE username SET work_mem = '100 MB'; (will apply only to new connections)
• At the database level: ALTER DATABASE database_name SET work_mem = '100 MB'; (will apply only to new connections)