Search code examples
databasepostgresqlheroku

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

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';

enter image description here

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

Solution

  • 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.