PSQLException: ERROR: could not write to hash-join temporary file: No space left on device

Hi team,



I have an issue with Postgres error: *PSQLException: ERROR: could not write
to hash-join temporary file: No space left on device*

After investigation, I could see that both *work_mem* and *temp_tablespace*
is NOT specified in postgresql.conf file,

but when I queried database, it showed as 15728kB.



$ cat postgresql.conf | grep temp_tablespaces

#temp_tablespaces = ''                  # a list of tablespace names, ''
uses



$ cat postgresql.conf | grep work_mem

#work_mem = 4MB                         # min 64kB

#maintenance_work_mem = 64MB            # min 1MB

#autovacuum_work_mem = -1               # min 1MB, or -1 to use
maintenance_work_mem

--Recommend is 64MB



postgres=# show work_mem;

work_mem

----------

15728kB

(1 row)



I can see that large number of temp files have been created.



postgres=# SELECT datname, temp_files,temp_bytes from
pg_catalog.pg_stat_database;

  datname   | temp_files |  temp_bytes

------------+------------+---------------

postgres   |         24 |      25013533

user_db    |     200797 | 2774922398171

template1  |          0 |             0

template0  |          0 |             0

(4 rows)



$ free -h

             total       used       free     shared    buffers     cached

Mem:           31G        18G        12G       5.4G       408M        17G

-/+ buffers/cache:       1.4G        29G

Swap:         4.0G       226M       3.8G



In my opinion, *work_mem* needs to be set since temp files created seem
quite large.

Can someone please advise the recommended value for work_mem (is 64MB
recommended?)

And if setting up temp_tablespace is also recommended in this case?



Thanks,

Calvin