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


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


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)

Ask on Stack Overflow

Engage with a community of passionate experts to get the answers you need

Ask on Stack Overflow

Heroku Support

Create a support ticket and our support experts will get back to you

Contact Heroku Support