[rhaas]

The work that has been done on transition tables is intended to enable future work on automatically updated materialized views; the idea is that the system will automatically derive a query to update the view based on the deltas between the set of old rows and the set of new rows. That will take more work, though. I do agree it would be valuable. It's possible to set up similar things by writing your own triggers, and having transition tables available in PL/pgsql will make it easier, but it's not necessarily easy to figure it all out by hand for a complex view involving joins and aggregates.

[pgaddict]

I wonder why not to go for a changelog-based implementation. Instead of modifying the materialized view directly, write the changes into a changelog, and then update the matview in the background. More efficient, less locking issues, etc.

[foota]

How do you know when the change will affect the matview? I don't know the syntax for creating it off the top of my head, but imagine a materialized view limited to the top ten records of a table.

[pgaddict]

I assume there will be some limitations what matviews can be updated automatically/ incrementally. E.g. updatable views (regular, not materialized) have a range of such restrictions, see: https://www.postgresql.org/docs/devel/static/sql-createview....

Hopefully, some of those restrictions will disappear, because matviews are often used to store pre-aggregted data, so the restriction on GROUP BY would be unfortunate.

[fiatjaf]

Thank you.