I have 5 tables in my database with respectively a size of 70Gb, 500Mb, 400 Mb, 110Mb and 20 Mb.
I want to create a new table that contains all columns of all tables, so I tried 2 queries, the first one is :
select into new_table as select .. from t1 join t2 join t3 join t4 ...
and the second one is :
insert into new_table select .. from t1 join t2 join t3 join t4 ...
Before executing these two queries on my big data tables, I tried both on a total 1G database, the first on took only 7s and the second one approximately 10 mn.
Now, executing the first one on my huge database, made my disk full even though I had 250Gb free space before running the query, and without finishing the query so I got the follow error :
ERROR: could not write to temporary file: No space left on device
The second one, is taking a lot of time and consuming my free disk space slowly and, as the first one, not returning the result.
What are the difference between these two queries ? Is there a way to make the insert into non transactional so as I can follow my insert steps. And I guess Postgres uses logs (journalization) so is there a way to deactivate that in order to speed up the insertion ? or I should follow another method in order to get a desired result without filling up all disk