Re: Add statistics refresh materialized view - Mailing list pgsql-hackers

On Wed, 29 Jul 2026 at 14:17, Michael Banck <mbanck@gmx.net> wrote:

Hi,

On Wed, Jul 29, 2026 at 10:21:38AM +0200, Rafia Sabih wrote:
> On Thu, 4 Jul 2024 at 21:13, Said Assemlal <sassemlal@neurorx.com> wrote:
> > > However, as you said, for most use cases, pg_stat_statements and
> > > log_statement may be sufficient.  I would like to withdraw this
> > > proposal.
> >
> > Well, they either require extensions or parameters to be set properly.
> > One advantage I see to store those kind of information is that it can be
> > queried by application developers (users are reporting old data for
> > example).
> >
> > We currently have to rely on other ways to figure out if materialized
> > views were properly refreshed.
>
> I agree that there is no easy way currently to figure out the last time a
> materialized view was refreshed. I want to work on this, but before
> implementing anything I'd like to discuss the right approach.

Yeah.

> Looking at the code, REFRESH MATERIALIZED VIEW and the populate step of
> CREATE MATERIALIZED VIEW both go through RefreshMatViewByOid() in
> matview.c, which already updates relispopulated on pg_class transactionally
> once the refresh completes. Recording a timestamp would hook in right
> there. Unlike VACUUM, though, REFRESH can run inside a transaction block
> and get rolled back, so the timestamp needs to be a normal transactional
> catalog update, not something written directly into shared stats memory
> like last_vacuum_time. That part seems straightforward. The real question
> is where this information should live.
>
> One option is a column on pg_class, say last_refresh timestamptz, set the
> same way relispopulated is. Less implementation work, but it would stay
> null for every non-matview row, and pg_class already carries every table,
> index, sequence, etc., so this adds width with no benefit for the rest.
>
> This was actually discussed back in 2021 (Seino Yuki,
> https://www.postgresql.org/message-id/flat/6fe02a8ab3b2b0801933b9cabfab62cf@oss.nttdata.com),
> proposing count and last-refresh-time columns on pg_stat_all_tables. I
> gather, Fujii Masao objected on two grounds: it singles out REFRESH
> MATERIALIZED VIEW when other utility commands (TRUNCATE, CLUSTER, etc.)
> could make the same claim, and the columns would be dead weight for the
> vast majority of pg_stat_all_tables entries, which are regular tables. I
> think that objection is right, and it points at a way to avoid it entirely:
> don't touch pg_stat_all_tables or pg_class at all.

pg_class does not have any timestamps so far, so putting it there looks
out-of-place to me. While being important information, it is also not
essential information, so another strike against pg_class in my opinion.

Thank you Michael for your input and giving another good reason to not go this route. 

Why not circle back to pg_stat_all_tables and discuss two new columns
there: last_rewrite and rewrite_count? Those would have to be maintained
for regular table rewrites during DDL as well, but I think that would be
(relatively) valuable information anyway. One could argue that "rewrite"
is wrong terminology for a matview refresh but I guess most users would
figure it out.

I like the idea, so as I understand it would also be populated by other commands like VACUUM, CLUSTER, ALTER TABLE, so maybe this could be a good solution in that way. But in regards to materialised view I am unsure how to handle REFRESH MATERIALISED VIEW CONCURRENTLY, since in that path there is no real rewrite happening, rather it makes a new heap. Particularly, in all the other cases we are calling finish_heap_swap so we can get the timestamp there for our purpose but not for REFRESH with CONCURRENTLY case.

> That's the second option: a dedicated catalog, e.g. pg_matview_meta(mvrelid
> oid, mvlastrefresh timestamptz), one tuple per materialized view.

As this is more-or-less performance data, I would suggest to go with the
usual pg_stat_* naming schema and non-cryptic column names, as well as
denormalization of schemaname/relation name similar to
pg_stat_all_tables. 

So something like pg_stat_matviews with oid, schemaname, relname,
last_refresh, refresh_count and what else. Regarding what else, when you
are adding a new system catalog anyway, it might make sense to maintain
the duration of the refresh as well, similar to pg_stat_statements. So
something like total_refresh_times, min_refresh_time, max_refresh_time,
mean_refresh_time, stddev_refresh_time. But as somebody mentioned
upstream, those should be available from pg_stat_statements today if one
enables it.

+1 

> More work - it needs its own catalog OID, a unique index on mvrelid,
> and exclusion from pg_dump / reset on upgrade, since this isn't user
> data

As an aside, why would it need special pg_dump work? I don't think we
ever dump system catalogs, at least for regular dumps.

I meant we need to ensure that it is not included in pg_dump, etc. But you are right there shouldn't be any special handling for this case. 

> pg_class and pg_stat_all_tables untouched, sidestepping both of Fujii's
> objections, and follows the same pattern as pg_partitioned_table or
> pg_statistic_ext_data. I'm leaning this way, partly because it leaves room
> to later add refresh duration, last error time, or refresh count.

Again, those are all stats.

> - handling of REFRESH ... WITH NO DATA should it clear the recorded
> timestamp or leave the last real refresh time visible

Good question.

Yes and an interesting one to know the answer to before starting implementation.

I think the other important question is: if we ever get incremental
materialized views (IVM), would that change anything here and possibly
make that new system catalog redundant? Would that be a problem in that
case?

I thought a little about it based on the extension pg_ivm, but it doesn't look like anything changes here. The ivm is working based on triggers so it doesn't conflict with the path which we are covering here. Also, looks like even when ivm is in core it is likely to have its own catalog table.

Michael

--

Regards,
Rafia Sabih

CYBERTEC PostgreSQL International GmbH