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.

  • 1
    Did you check where it is writing that much? The pg_wal, the temp tablespace, the final location? What is the EXPLAIN (or better EXPLAIN ANALYZE) for the query the materialized view is based on? – jjanes Aug 15 '19 at 19:08
  • I did check now. It writes to /var/lib/postgresql/10/main/base/pgsql_tmp. The explain for the query is: explain.depesz.com/s/q4Nw – user1068464 Aug 20 '19 at 11:21
  • You are sorting the entire table to disk (temp files) before any filtering is done, so that explains where at least half of the space goes. – jjanes Aug 20 '19 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.