Re: Add statistics refresh materialized view

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(at)neurorx(dot)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(at)oss(dot)nttdata(dot)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.

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.

> 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.

> 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.

> 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.

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?

Michael