I’ll need to invoke REFRESH MATERIALIZED VIEW on each change to the tables involved, right? Yes, PostgreSQL by itself will never call it automatically, you need to do it some way. How should I go about doing this? Many ways to achieve this. Before giving some examples, keep in mind that REFRESH MATERIALIZED VIEW command … Read more
Materialized views are disk based and are updated periodically based upon the query definition. Views are virtual only and run the query definition each time they are accessed.
I don’t think MySQL supports materialized views which is what you would need, but it wouldn’t help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would … Read more
You should refresh the view in triggers after insert/update/delete/truncate for each statement on table1 and table2. create or replace function refresh_mat_view() returns trigger language plpgsql as $$ begin refresh materialized view mat_view; return null; end $$; create trigger refresh_mat_view after insert or update or delete or truncate on table1 for each statement execute procedure refresh_mat_view(); … Read more