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