Remund Alain <alain.remund@xxxxxxxxxxxx> 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@xxxxxxxxxxx
