avoid WAL for refresh of materialized view

Le mar. 24 mars 2020 à 17:00, Remund Alain <alain.remund@bertschi.com> 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.

Remund Alain <alain.remund@bertschi.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.consulting@comcast.net