This is the time of year when the Wisconsin Courts formalize their
annual plan for where people will be spending the bulk of their time
in the coming year. Two years ago at this time, managers decided
that serializable transactions were a big enough issue to justify
assigning about half of my 2011 time to working on PostgreSQL
enhancements for that. This year our big database issue is
materialized views.
As we strive to create our next generation of software we find
ourselves wanting to provide "dashboard" type windows with graphs of
statistics which are insanely expensive to calculate on the fly.
We've been creating ad hoc materialized views to deal with the
performance issues, but that is labor intensive. I'm considering
submitting a proposal to management that I be assigned to work on
a declarative implementation in PostgreSQL to allow speedier
application development of software needing materialized views.
I'm posting to make sure that nobody else is already in the midst of
working on this, and to check regarding something on the Wiki page
for this topic:
http://wiki.postgresql.org/wiki/Materialized_Views
That page describes three components: creating MVs, updating MVs, and
having the planner automatically detect when an MV matches some
portion of a regular query and using the MV instead of the specified
tables in such cases. I have high confidence that if time is
approved I could do the first two for the 9.3, but that last one
seems insanely complicated and not necessarily a good idea. (That's
particularly true with some of the lazier strategies for maintaining
the data in the materialized view.) I don't think we want to use
that 3rd component in our shop, anyway. So the question is, would a
patch which does the first two without the third be accepted by the
community?
I'm not at the point of proposing specifics yet; the first phase
would be a close review of prior threads and work on the topic
(including the GSoC work). Then I would discuss implementation
details here before coding.
The hope on our end, of course, is that the time spent on
implementing this would be more than compensated by application
programmer time savings as we work on our next generation of
application software, which seems like a pretty safe bet to me.
-Kevin