Skip site navigation (1) Skip section navigation (2)

Re: GSoC - proposal - Materialized Views in PostgreSQL

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Greg Smith <greg(at)2ndquadrant(dot)com>
Cc: Josh Berkus <josh(at)agliodbs(dot)com>, pavelbaros <baros(dot)p(at)seznam(dot)cz>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: GSoC - proposal - Materialized Views in PostgreSQL
Date: 2010-04-12 20:03:09
Message-ID: n2z603c8f071004121303if6fa85ddg9217d2c62603ce2f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, Apr 12, 2010 at 3:43 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Josh Berkus wrote:
>>
>> There are basically 2 major parts for materialized views:
>> A) Planner: Getting the query planner to swap in the MatView for part of
>> a query automatically for query plan portions which the MatView supports;
>> B) Maintenance: maintaining the MatView data according to the programmed
>> scheme (synch, asynch, periodic).
>>
>
> I'm run more into problems where it's perfectly fine to specify using the
> materialized view directly in the query, but keeping that view up to date
> usefully was the real problem.  The whole idea of getting a MV used
> automatically is valuable, but far down the roadmap as I see it.
>
> Not everyone would agree of course, and your description does suggest a
> better way to organize a high-level summary though; here's a first cut:
>
> 1) Creation of materalized view
> Current state:  using "CREATE TABLE AS" or similar mechanism, maintain
> manually
> Optimal:  "CREATE MATERIALIZED VIEW" grammar, metadata to store MV data,
> dump/reload support
>
> 2) Updating materialized views
> Current state:  periodically create new snapshots, or maintain using
> triggers
> Optimal:  Built-in refresh via multiple strategies, with minimal locking as
> to improve concurrent access
>
> 3) Using materialized views in the planner
> Current state:  specify the manually created MV in queries that can use it
> Optimal:  Automatically accelerate queries that could be satisfied by
> substituting available MVs
>
> With (1) being what I think is the only GSoC sized subset here.
>
> I'm not saying someone can't jump right into (3), using the current
> implementations for (1) and (2) that are floating around out there.  I just
> think it would end up wasting a fair amount of work on prototypes that don't
> work quite the same way as the eventual fully integrated version.  You
> certainly can start working on (3) without a fully fleshed out
> implementation of (2), I don't know that it makes sense to work on before
> (1) though.

Good summary.

...Robert

In response to

pgsql-hackers by date

Next:From: Josh BerkusDate: 2010-04-12 21:46:20
Subject: Re: GSoC - proposal - Materialized Views in PostgreSQL
Previous:From: Greg SmithDate: 2010-04-12 19:43:51
Subject: Re: GSoC - proposal - Materialized Views in PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group