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 "REFRESH MATERIALIZED VIEW CONCURRENTLY tableName", 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.