thanks for your write-up on mviews!
first, it seems the easiest implementation of mview is a simple RULE around
the INSERT, UPDATE, DELETE for a table.
this will not work in all instances and, performance-wise, is pretty
expensive.
on the other hand it is quite easy, requires no structural overhead, and no
triggers.
second, regarding what you call Lazy Materialized Views. have done this
before by putting relevant info in a session-scoped temporary table. using
a post transaction trigger, queried this for the rows of the data needed to
be melded with the mview.
not overly pretty, but quite workable. as a caution, did this years ago in
Oracle and have not tried this particular trick in pg. Oracle then and now
had autonomous functions, allowing dml in an independent session -
frequently a life savior - but do not remember if they were necessary.
third, to the best of my poor (romantic?) recollection, some of the things
that made mviews very cool were that they could be refreshed when you do a
select from them, and then if you haven't used them in a while the updates
discontinue automatically. could specify refresh interval and schedule
automatic refreshes at night. for me it was a combination of these
features that made them really special.
where I was using these, for example, information up to the last hour or so
was quite sufficient, so refreshes occurred no more than once an hour.
this was very positive to overall performance. refreshed at night
automatically. a very nasty view with sums by group etc.
