Unlogged materialized views

Hi Zsolt,

I like to bring up supporting unlogged materialized views again.

+1 from me. Most of the MVs I've built over the years were:
denormalized lookup tables behind a search screen, pre-aggregated
rollups for reporting, cached joins that were too slow to run per
request.
Losing some of those to a crash would have been fine as refresh
time for them wasn't a huge issue upon recovery.

* automatically refreshing unlogged views after crash recovery in a
background worker

Something still has to notice and rebuild, and right now that would be on
every user to write themselves. It would be nice to have this built-in IMO.

Thanks,
Adam

Hey Zsolt,

+1

Will take a look at the patch

Thanks,
Dharin

On Mon, Aug 10, 2026 at 9:13 PM Adam Brusselback <adambrusselback@gmail.com>
wrote:

Thanks for the feedback!

Something still has to notice and rebuild, and right now that would be on
every user to write themselves. It would be nice to have this built-in IMO.

I agree that this would be more useful in core or in contrib, and I can implement it as part of this patch or as a follow up, but before that, I'd like some feedback if people agree with the current approach or not, as with a different approach we might need a completely different solution for that.

Hello!

I attached a rebased version, otherwise there are no changes.

On Tue, Aug 11, 2026 at 10:08 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:

Hi Zsolt

On 19/08/2026 14:17, Zsolt Parragi wrote:

I attached a rebased version, otherwise there are no changes.

While doing some research for a new project (REFRESH ALL MATERIALIZED
VIEWS) I found this patch. 0002 and 0003 were not applying due to the
latest changes in meson.build and catversion.h. In order to test the
feature I had to rebase them -- PFA the rebased 0002 and 0003.

A few initial comments:

== The docs do not compile ==

Apparently the new ids in varlistentry, which are currently not used,
are breaking the file.

generating docs ...
ERROR: id attribute missing on <varlistentry> element under /book[@id =
'postgres']/part[@id = 'reference']/reference[@id =
'sql-commands']/refentry[@id =
'sql-altermaterializedview']/refsect1/variablelist
no result for postgres-full.xml
make[3]: *** [Makefile:130: html-stamp] Error 10
make[2]: *** [Makefile:8: html] Error 2
make[1]: *** [Makefile:16: html] Error 2
make: *** [GNUmakefile:27: html] Error 2

== tab completion ==

The tab completion does not suggest the new flag:

CREATE UNLOGGED <TAB>
SEQUENCE TABLE

== \d output ==

\d shows an unlogged matview as a normal one

postgres=# CREATE UNLOGGED MATERIALIZED VIEW umv1 AS SELECT 1;
SELECT 1
postgres=# \d umv1
Materialized view "public.umv1"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
?column? | integer | | |

A unlogged table is shown as unlogged, so I believe this should also
apply to matviews:

postgres=# CREATE UNLOGGED TABLE ut1 AS SELECT 1;
SELECT 1
postgres=# \d ut1
Unlogged table "public.ut1"
Column | Type | Collation | Nullable | Default
----------+---------+-----------+----------+---------
?column? | integer | | |

PFA 004 with a few suggestions for the above mentioned issues. I'll take
a closer look at the code in the coming week.

Best, Jim

Thank you for the feedback and suggestions!

I rebased again; the patch no longer increments the
controlfile/catalog version to make conflicts less likely.

I included the changes from 0004, and added a few other minor changes/fixes.

I also found another issue while testing replication scenarios. To fix
that, the counter is now based on the LSN, not the timeline, this is
the most significant change compared to v3.

Another open question I want to point out is that I found a pg_dump
failure with --no-unlogged-table-data. For now I chose the option
where this flag doesn't affect materialized views, as they are not
tables, but I am not sure if this is the proper solution.

v5 is a trivial rebase changing tap test numbers.

On 09/10/2026 13:40, Zsolt Parragi wrote:

v5 is a trivial rebase changing tap test numbers.

Today I finally found some time revisit this patch.

== no relispopulated in pg_matviews ==

Client tools like pgAdmin rely on this column to display matview info:

diff --git a/src/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index ddf801307eb..724a23d559f 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -150,7 +150,7 @@ CREATE VIEW pg_matviews AS
         pg_get_userbyid(C.relowner) AS matviewowner,
         T.spcname AS tablespace,
         C.relhasindex AS hasindexes,
-        C.relispopulated AS ispopulated,
+        pg_matview_is_populated(C.oid) AS ispopulated,
         pg_get_viewdef(C.oid) AS definition
     FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
          LEFT JOIN pg_tablespace T ON (T.oid = C.reltablespace)

With the patch applied, pgAdmin can no longer display the properties of
a matview:

column c.relispopulated does not exist
LINE 6: c.relispopulated AS with_data,
^
HINT: Perhaps you meant to reference the column "c.relpopulated".

I will take a closer look at the code in the next few days.

Best, Jim

== no relispopulated in pg_matviews ==

Client tools like pgAdmin rely on this column to display matview info:

There is (or rather: there wasn't even before), the column is called pg_matview.ispopulated and it is still there

column c.relispopulated does not exist
LINE 6: c.relispopulated AS with_data,
^
HINT: Perhaps you meant to reference the column "c.relpopulated".

What is missing is the relispopulated boolean from pg_class, and I don't see how that could stay with this approach.

pg_class.relispopulated could only stay if we could update it after recovery, but before accepting connections (as otherwise it could become stale for unlogged matviews), and I don't see an easy way to do that, that's why I implemented this patch in a different way.
Otherwise, it could become stale, that's why the patch removes it.

But looking at pgadmin, it would be easy to patch it in a way that works with both all earlier versions and this patch.