Re: avoid WAL for refresh of materialized view

Remund Alain <alain(dot)remund(at)bertschi(dot)com> writes:

> Hi all
>
>
>
> We have PostgreSql 9.6 running and started to work with materialized
> views. To refresh the materialized views, we set up a cron job that
> refreshes the materialized views on a fix schedule.
>
> Since our materialized views cache quite some data, we noticed a
> considerable increase in WAL files. It seems, that every refresh of a
> materialized view is logged in the WAL.
>
>
>
> We tried to figure out how we can alter the materialized view to set
> it to "UNLOGGED" but this does not seem possible.
>
> --> "alter materialized view xyz set UNLOGGED;" leads to "ERROR:
> "xyz" is not a table, SQL state: 42809"
>
>
>
> Is there another way to avoid logging a refresh of a materialized
> view in the WAL?
>

The workaround for this is to not use mat view at all but instead
materialize the output into an unlogged table that you trunc before
every refresh.

HTH

>
>
> Kind regards,
>
> Alain Remund
>
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net