3

I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it seems that triggers cannot be set on postgres materialized views. I'm running out of options. Can you recommend a way to do this without reaching up into the application layer?

Many thanks in advance. Max.

3
  • You can add a column now() AS last_refresh to your materialized view in your query definition. This way it will hold the time when the view has been refreshed.
    – thibautg
    May 31, 2016 at 9:47
  • 2
    Hello thibautg. If performance were not an issue, that is what I would have done. If I were to add such a column then (a) the view would become 50% larger, and it is already big, and (b) when doing refresh materialized view concurrently every single row would be changed, so instead of updating just a handful of rows every time it would have to update all of them. At the moment it looks as if my best choice is to look at the last modified time of the file on disk, although that is far from accurate. I'm looking at commit_timestamps now.
    – Max Murphy
    May 31, 2016 at 13:49
  • 1
    Here is a related post: dba.stackexchange.com/questions/58214/…
    – Max Murphy
    May 31, 2016 at 13:50

1 Answer 1

1

Possible solution in 2021:

There is an extension mv_stats that captures MV (Materialized View) statistics. It adds triggers for "CREATE MATERIALIZED VIEW", "ALTER MATERIALIZED VIEW", "REFRESH MATERIALIZED VIEW" and then records timestamps when they are called for a MV.

Tracking the refresh Performance of Materialized Views with mv_stat in PostgreSQL

Gitlab: https://gitlab.com/ongresinc/extensions/mv_stats/

1
  • Thank you. I won't get a chance to play with it any time soon but hopefully someone to whom it is relevant now will find this useful!
    – Max Murphy
    Sep 2, 2021 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.