avoid WAL for refresh of materialized view

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

avoid WAL for refresh of materialized view

Remund Alain

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?

 

Kind regards,

Alain Remund

Reply | Threaded
Open this post in threaded view
|

Re: avoid WAL for refresh of materialized view

Guillaume Lelarge-3
Le mar. 24 mars 2020 à 17:00, Remund Alain <[hidden email]> a écrit :

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?

 


As you say, there is no unlogged materialized view. So, no, it will always log to the WAL during refresh.


--
Guillaume.
Reply | Threaded
Open this post in threaded view
|

Re: avoid WAL for refresh of materialized view

Jerry Sievers-3
In reply to this post by Remund Alain
Remund Alain <[hidden email]> 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: [hidden email]