Re: GSoC - proposal - Materialized Views in PostgreSQL

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.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us