Re: Incrementally refreshed materialized view

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