Обсуждение: avoid WAL for refresh of materialized view
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
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?
--
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