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

performance postgresql space tablespace
Yousuf Sultan picture Yousuf Sultan · Apr 13, 2015 · Viewed 16k times · Source

I am executing PostgreSQL functions to update a table which has a huge amount of data and the update happens for about 100000 records everyday. During the update I get an error saying:

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

I have not really been able to get something useful to overcome this error. I got something where it says to SET a temporary table spaces. But I was not able to find how do I create a temporary table space where the data will be stored during the executing of the update procedure.

Answer

Vao Tsun picture Vao Tsun · Apr 13, 2015
  1. SQL> create tablespace temp_tbs location '/some/big/disk';
  2. change temp_tablespaces = 'temp_tbs' in postgresql.conf.
  3. select pg_reload_conf();
  4. enjoy

Related questions

Best way to select random rows PostgreSQL

I want a random selection of rows in PostgreSQL, I tried this: select * from table where random() < 0.01; But some other recommend this: select * from table order by random() limit 1000; I have a very large table with 500 Million rows, I …

Read More
postgresql COUNT(DISTINCT ...) very slow

I have a very simple SQL query: SELECT COUNT(DISTINCT x) FROM table; My table has about 1.5 million rows. This query is running pretty slowly; it takes about 7.5s, compared to SELECT COUNT(x) FROM table; which takes about 435ms. …

Read More
Is it possible to get a history of queries made in postgres

Is it possible to get a history of queries made in postgres? and is it be possible to get the time it took for each query? I'm currently trying to identify slow queries in the application I'm working on. I'm …

Read More