Re: Postgresql Materialized views

Added to TODO:

* Add the ability to automatically create materialized views

Right now materialized views require the user to create triggers on the
main table to keep the summary table current. SQL syntax should be able
to manager the triggers and summary table automatically. A more
sophisticated implementation would automatically retrieve from the
summary table when the main table is referenced, if possible.

I also thought this was on the TODO list.

---------------------------------------------------------------------------

Zeugswetter Andreas ADI SD wrote:
>
> > > Traditionally materialized views exist, so that you do not need to code differently.
> > > Your queries still run on the detail table, but are silently answered
> > > by a suitable MV. The MV might have count + other aggregated columns
> > > grouped by some columns, and thus be able e.g. shortcircuit a
> > > "select count(*) from atab". The MV should be MVCC aware (have different
> > > values for different snapshots) and not substantially reduce possible
> > > concurrency of updates to the base table.
> >
> > Note that you just raised the minimum bar for implementation of the
> > feature by a couple orders of magnitude. We cannot automatically
> > substitute an MV into queries unless this is guaranteed not to change
> > the results. No lazy updates, MVCC transparency required, etc.
>
> Yes, unfortunately. But don't you also think that this is what makes it
> a worthwhile feature ?
>
> I mean, we do have the doityourself triggered summary table approach,
> which is not overly difficult to set up. It needs some thought and possibly design
> by the user to solve the most obvious concurrency issues, but it is doable.
>
> Imho MV could be separated in 2 parts:
> 1: materialized and MVCC aware views (only used explicitly)
> 2: add the smarts to rewrite sql
>
> Part 1 is already useful by itself since it provides a generic and easy
> solution to concurrency for the user. (probably nice and mindboggling, how to
> best implement that, though :-)
>
> The "lazy update" and non MVCC approach imho sounds too much like your
> "you can make it arbitrarily fast if it does not need to be correct" :-)
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +