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.

• *-0 plans: 32 MiB
• *-2 plans: 64 MiB
• *-3 plans: 100 MiB
• *-4 plans: 110 MiB
• *-5 plans: 120 MiB
• *-6 plans: 160 MiB
• *-7 plans: 430 MiB
• *-8 plans: 520 MiB
• *-9 plans: 520 MiB

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)