I have a database that's ~130 GB and one table makes up for most of this (85 GB). Because this table holds a lot of duplicates, we have a materialized view which basically uses a window function to only select the newest rows.
The original table holds 85 mio. records and the materialized view is expected to have approximately 15 mio. records. So I would expect about 15 GB of disk usage. There are no additional attributes in the view, that are not already in the table.
However, when I invoke
REFRESH MATERIALIZED VIEW it fails with "not enough disk space", even though there are 160 GB free. I checked and it really writes that much (and then rolls back).
Why would I need that much space? 160 GB is almost twice of the original table, although it should contain only 1/5 of the data.
I am running Postgresql 10.6.