Predict how much hard drive space a query might cost?
It is hard to make a prediction, because that depends on many factors like the chosen execution plan.

You should first find out where the files are that keep growing.


If they are in the base/12345 subdirectory of the PostgreSQL data directory (the number will be different), it is the materialized view that is being built. That would mean that your disk space is not sufficient to contain the resulting object.
If the growing files are in base/pgsql_tmp, they are temporary files that the query executor creates to cache a large intermediate result.

That might be an indication that you forgot a join condition somewhere - hard to tell, as we only see part of the query.

Since CTEs are materialized before v12, it could also be the intermediate result of a WITH expression. You can try them out one after the other and see if they make things go boom.


Sorry, but it is not possible to give you more than these guidelines.