Hello all, I am working on a plan to implement incrementally refreshed materialized "views" with the existing functionality in Postgres.
Below is the plan for doing that: Trigger based eagerly updated materialized tables for Postgres 9.5 > > > > High level plan: > > Have a view definition stored in the database which we can use for > reference. Create functions which will read that view definition, and > create a materialized table with all the same columns as the reference > view, create triggers on all tables the view depends on to keep the > materialized table fresh within a transaction. All queries would hit the > materialized table, the view is just there so we know what dependencies to > track, and have an easy way to update the materialized table. > > > > How do we actually do the refresh? > > 1. A refresh key is defined for the materialized table. > > 2. Every dependent table must roll up to that refresh key so we > know what rows to refresh. > > 3. That key should be able to be referenced in the views where > clause performantly so we can refresh just the rows that match the refresh > key using the view. > > 4. The refresh will be done by deleting any existing rows with the > key, and inserting new ones with the key from the view. > > How do we know what to refresh? > > 1. A before statement trigger to create a temp table to log all > changes. > > 2. A for each row trigger to log the rows modified by DML. > > a. This should be done at the refresh key level. > > i. We > need to figure out a way to generate queries to roll up things multiple > levels on the dependency chain until we get to the refresh key. Not sure > at all how to do that. > > 3. An after statement trigger to run a refresh on the materialized > table, looking at only the rows touched by the DML. > I am however stuck on: How do we know what to refresh? -> Step 2 Pretty much, I need to figure out how to follow the joins in the view back to whatever key was defined as the "refresh key" for each dependent table. I know about the information_schema.view_column_usage, but I don't think that'll get me everything I need. I'd really appreciate any help with this, as i'd love a better way to get eagerly refreshed materialized views in Postgres rather than doing everything manually as I have to now. If I can provide any more info please let me know. Thanks, -Adam
