Add statistics refresh materialized view

On 2021/07/09 1:39, Seino Yuki wrote:

Hi.

This is a proposal for a new feature in statistics collector.
I think we need to add statistics about refresh matview to pg_stat_all_tables view.

Why do you want to treat only REFRESH MATERIALIZED VIEW command special?
What about other utility commands like TRUNCATE, CLUSTER, etc?

It's not good design to add new columns per utility command into
pg_stat_all_tables. Otherwise pg_stat_all_tables will have to have lots of
columns to expose the stats of many utility commands at last. Which is
ugly and very user-unfriendly.

Most entries in pg_stat_all_tables are basically for tables. So the columns
about REFRESH MATERIALIZED VIEW are useless for those most entries.
This is another reason why I think the design is not good.

When the "REFRESH MATERIALIZED VIEW" was executed, the number of times it was executed
and date it took were not recorded anywhere.

pg_stat_statements and log_statement would help?

"pg_stat_statements" can be used to get the number of executions and the date and time of execution,
but this information is statement-based, not view-based.

pg_stat_statements reports different records for REFRESH MATERIALIZED VIEW
commands on different views. So ISTM that we can aggregate the information
per view, from pg_stat_statements. No?

Regards,

--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

On 2021-09-01 23:15, Fujii Masao wrote:

Why do you want to treat only REFRESH MATERIALIZED VIEW command
special?
What about other utility commands like TRUNCATE, CLUSTER, etc?

First of all, knowing the update date and time of the MATVIEW is
essential for actual operation.
Without that information, users will not be able to trust the MATVIEW.

In terms of the reliability of the information in the table,
I think the priority of the REFRESHED MATVIEW is higher than that of
TRUNCATE and CLUSTER.

It's not good design to add new columns per utility command into
pg_stat_all_tables. Otherwise pg_stat_all_tables will have to have lots
of
columns to expose the stats of many utility commands at last. Which is
ugly and very user-unfriendly.

Most entries in pg_stat_all_tables are basically for tables. So the
columns
about REFRESH MATERIALIZED VIEW are useless for those most entries.
This is another reason why I think the design is not good.

I agree with this opinion.
Initially, I thought about storing this information in pg_matviews,
but decided against it because of the overhead of adding it to the
system catalog.

pg_stat_statements reports different records for REFRESH MATERIALIZED
VIEW
commands on different views. So ISTM that we can aggregate the
information
per view, from pg_stat_statements. No?

I made this suggestion based on the premise that the last update date
and time of the Mateview should always be retained.
I think the same concept applies to Oracle Database.
https://docs.oracle.com/cd/F19136_01/refrn/ALL_MVIEWS.html#GUID-8B9432B5-6B66-411A-936E-590D9D7671E9
I thought it would be useless to enable pg_stat_statements and
log_statement to see this information.

However, as you said, for most use cases, pg_stat_statements and
log_statement may be sufficient.
I would like to withdraw this proposal.

Regards,

On Tue, Sep 07, 2021 at 06:11:14PM +0900, Seino Yuki wrote:

I would like to withdraw this proposal.

This was registered in the CF, so marked as RwF.
--
Michael

Hi,

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.

On Thu, 4 Jul 2024 at 21:13, Said Assemlal <sassemlal@neurorx.com> wrote:

Hi,

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.

Hi all,

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.

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,
/messages/by-id/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.

That's the second option: a dedicated catalog, e.g. pg_matview_meta(mvrelid
oid, mvlastrefresh timestamptz), one tuple per materialized view. 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 - but it leaves
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.

Looking forward to your inputs, particularly:
- whether the dedicated-catalog approach addresses the 2021 concerns, or
there's still a preference for the pg_class route
- naming (pg_matview_meta / mvrelid / mvlastrefresh)
- handling of REFRESH ... WITH NO DATA should it clear the recorded
timestamp or leave the last real refresh time visible

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

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,
/messages/by-id/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.

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

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,

/messages/by-id/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

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.pghackers@gmail.com> wrote:

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,

/messages/by-id/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

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH