avoid WAL for refresh of materialized view

From: Remund Alain <alain(dot)remund(at)bertschi(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: avoid WAL for refresh of materialized view
Date: 2020-03-24 15:59:46
Message-ID: e6b0ccfe181f45868304015db4029746@bertschi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guillaume Lelarge 2020-03-24 16:03:42 Re: avoid WAL for refresh of materialized view
Previous Message Peter J. Holzer 2020-03-24 09:27:34 Re: Loading 500m json files to database