Clear materialized view in PostgreSQL

Question

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

Looks like an issue with auto vacuum. Does it run and is aggressive enough? — Frank Heikens, Dec 14 '22 at 08:33

score 3 · Accepted Answer · answered Dec 14 '22 at 09:14

3

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.

answered Dec 14 '22 at 09:14

Laurenz Albe

209,280
17
206
263

By the way, what the difference between this 2 ways? Is one is faster or need much more memory? – yuoggy Dec 15 '22 at 04:23
`VACUUM (FULL)` will be faster if the query that defines the view takes a long time to run. But `VACUUM (FULL)` won't sctualze the data in the materialized view. – Laurenz Albe Dec 15 '22 at 07:05
Where I can read about it? – yuoggy Dec 16 '22 at 06:53
The PostgreSQL documentation. – Laurenz Albe Dec 16 '22 at 07:02

Clear materialized view in PostgreSQL

1 Answers1