Postgresql: Refreshing materialized view fails with "No space left on device" and a huge spike in disk usage

Question

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.

• Vérace: Please provide the definitions of your tables and your materialized view. p.s. welcome to the forum!

Answer 1

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.

• underachiever: Thanks for the response. Does the temp file is really this large? I mean it has almost 930GB of free space and it's requiring even more space? By efficient, do you mean a query with better execution plan? Or a better way to insert the data into the mat view? (which I'm not aware of).
• Itzblend: Better execution plan exactly. How does your query look like?
• underachiever: I figured it out! There was an outer join that was generating millions of rows. This was causing the spike in disk usage.
• Itzblend: Glad you found it out. Please mark the right answer so others can find this post useful.