Re: Materialized views

On 8 November 2011 21:23, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> 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.

+1

I was pleased to see the subject of this thread. I definitely think
it's worth it, especially if you're able to make it also work for
foreign tables (saving expense of seeking external data so can also
act as a local cache, but that's me getting carried away). And I
agree regarding the planner detection. If that ever were desired, it
certainly would't need implementing in the first phase.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company