Postgresql: Refreshing materialized view fails with "No space left on device" and a huge spike in disk usage I have a materialized view that refreshes every five minutes. The SQL aggregates the data among many tables with over 800k rows in each.
However, the when using the &quot;REFRESH MATERIALIZED VIEW CONCURRENTLY tableName&quot;, the query runs for about an hour and then complains:
ERROR:  could not write block 39760692 of temporary file: No space left on device
It should be noted that this 39760692 changes every time I execute the query.
The disk size is about 960 GB and the database size is about 30 GB. So the disk has a free space of about 930 GB.
I noticed that when running the refresh query, there is a huge spike in the disk usage of about 12GB per minute and then finally the query errors out with no space error when it hits the 960 GB mark. Immediately, the disk usage is back to 30GB from the abnormal growth.
I even tried the REFRESH MATERIALIZED VIEW tableName (without concurrently) and seeing the same behavior.
I'm not sure what can be done here to diagnose the problem.
Find where postgres writes temp files for you and follow it during the refresh. As you are processing alot of data inside the database, postgres has to write a huge temp file and as this fills your disk, you get an error.
You probably have to find a more efficent way to write your query.