One exciting new feature coming in PostgreSQL 9.3 is materialized views. Materialized views is really a mechanism for caching data of a query. It is especially useful if you have long running queries where the answers change infreqently. There are many things unfortunately that materialized views won't do where you are still better off with regular views.
They don't refresh themselves automatically. Thus requiring a cron job/pgagent job or a trigger on something to refresh.
They can't be user dependent or time dependent. For example if you have a view that does something like WHERE user=current_user(), then a materialized
view is out of the question.
For PostGIS work we need some views that take more than 10 seconds to compute. For example for one project we have a base parcel table and tables broken out by fiscal year that inherit from the base parcel table. We also have housing and neighborhood revitalization projects whose parcels are keyed based on parcel id. Housing and revitalization projects by their very nature change the landscape by creating and destroying parcels. So the parcel set you started out with may not be what you end up with. In order to reference an older project that has changed the parcel divide landscape, we sometimes need to reference old no longer existent parcels. What we do to achieve this is to have a table, We'll call it parcelmax that contains 3 columns -- the fiscal year a parcel was last seen, the parcel id, and the geometry of the parcel. Quering our parcel hierarchy takes too long so as part of the process of adding a new table
we always rebuild this table from the parcel hierarchy. We, now in PostgreSQL 9.3, have the option to use a materialized view.
Here is structures of our tables just for last 2 fiscal years.
The materialized view to replace our old parcelmax looks like this:
DROP MATERIALIZED VIEWIFEXISTS parcelmax;
CREATE MATERIALIZED VIEW parcelmax ASSELECTDISTINCTON(pid) pid, fy, geom
ORDERBY pid, fy DESC;
CREATEUNIQUEINDEX iudx_parcelmax_pid ON parcelmax (pid);
CREATEINDEX idx_parcelmax_geom ON parcelmax USINGgist(geom);
So now whenever we update our parcel hierarchy or add a new table to the hierarchy, we need to run:
REFRESH MATERIALIZED VIEW parcelmax;
Triggering automagical rebuild
This is a smidge better than what we used to do, but frankly not that much better. Most of the time where we forget to refresh our table happens
when we have to correct geometries in a table or delete an invalid record. It's these times we wish something automagical would happen.
Luckily PostgreSQL has something called statement triggers. We've never had much need for statement level triggers except for logging.
However the marriage between materialized views and statement level triggers seems like a good one especially for tables you update infrequently.
Here is the code to cause the view to rebuild in event any dependency tables change:
CREATEORREPLACEFUNCTIONtrig_refresh_parcelmax() RETURNS trigger AS
BEGINREFRESH MATERIALIZED VIEW parcelmax;
LANGUAGE plpgsql ;
CREATE TRIGGER trig_01_refresh_parcelmax AFTER TRUNCATE ORINSERTORUPDATEORDELETEON parcel_2013 FOR EACH STATEMENT
CREATE TRIGGER trig_01_refresh_parcelmax AFTER TRUNCATE ORINSERTORUPDATEORDELETEON parcel_2012 FOR EACH STATEMENT
Test out our statement makeup
To test this architecture out, we'll add some records to both tables.