Clear materialized view in PostgreSQL-CodePudding

I had a table with size of 10gb and materialized view on it (without join and etc.) of size 10gb too. I used pg_repack on table and its size decreased to 660mb. But after refresh materialized view concurrently and size of mat view still 10 gb. When I create the same new mat view on that table it size set 610 mb. Are there any another ways to clear materialized view except delete and recreate? Mat view should be available in every time

CodePudding user response：

You can clear a materialized view with

REFRESH MATERIALIZED VIEW mv WITH NO DATA;

But that does not make much sense. Your materialized view is bloated. This happens because REFRESH MATERIALIZED VIEW CONCURRENTLY does not actually replace, but update the existing data. There are two ways to get rid of the bloat:

```
REFRESH MATERIALIZED VIEW mv;
```
Refreshing the materialized view without CONCURRENTLY will discard the old, bloated table.
```
VACUUM (FULL) mv;
```

Both methods render the materialized view inaccessible while the statement is running, but there is no way to avoid that.