PSQLException: ERROR: could not write to hash-join temporary file: Nospace left on device - Mailing list pgsql-novice

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