Re: Postgresql Materialized views

> > 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