Heroku Postgres: Out of memory error with work_mem settings on max-CodePudding

My plan on Heroku Postgres was 8GB RAM.

I try to run query with "order by" and get an error out of memory !!

I try setting work_mem to 2GB and after this to 4GB and get the same error.

ALTER DATABASE xxxx  SET work_mem = '4096MB';

Until recently all the questions worked great and suddenly everything got stuck.

After upgrading the plan to 15RAM and setting work_mem to 8GB the I get the same error. Heroku Support recommended setting "temp_tablespaces" to "pg_default" but I again get the same error

ALTER DATABASE xxxx SET work_mem = '8192MB';
ALTER DATABASE xxxx SET temp_tablespaces = 'pg_default';

I do not understand what happened to the server and Heroku support does not really help. What could be a problem?

CodePudding user response：

work_mem can be allocated several times by a single query, and there can be several concurrent queries. Moreover, the memory allocated for shared_buffers has to be taken into account.

Use a much smaller setting for work_mem.

CodePudding user response：

You are going in the wrong direction. You are giving it your permission to use more memory, but then when it tries to use it, the system bonks it on the head, as the memory isn't there to be used. You need to tell your session to use less memory, not more. Then it will switch to a disk sort instead of trying to do it all in RAM.