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)