Re: Add statistics refresh materialized view

Based on this discussion, I worked on the first version of this patch.
Please find the attached file.
Looking forward to your feedback.

On Thu, 30 Jul 2026 at 08:32, Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com> wrote:

>
>
> On Wed, 29 Jul 2026 at 14:17, Michael Banck <mbanck(at)gmx(dot)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(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.
>>
>
> 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
>

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH