Re: Materialized views WIP patch

> And explicitly not support
>
> 1) Automatically rewriting queries to select from matching views
> 2) Incrementally refreshing materialized views
> 3) Manual DML against data in materialized views (except truncate
> which is kind of DDL)
> 4) Keeping track of whether the data in the materialized view is up to date

The idea is to add the above features over the next few versions of
Postgres.

> I have to say I find this model a bit odd. It seems the UI you're
> presenting is that they're basically read-only tables that the
> database will fill in the data for automatically.

This is what matviews are in other DBMSes.

> My mental model of
> materialized views is that they're basically views that the database
> guarantees a different performance characteristic for.

How would we do that, exactly? That would be lovely, but unless you
have a way to accomplish it ...

> I would expect a materialized view to be up to date all the time.

Actually, there's a huge use case for asynchronously updated matviews,
so we would not want an implementation which ruled them out. Also
there's the argument that synchronously updated matviews have little
actual performance advantage over regular dynamic views.

Or to put it another way: I could use this feature, as it is, in about 8
different projects I'm currently supporting. I personally can't think
of a single project where I need synchronously updated matviews,
currently. I have in the past, but it's a LOT less frequent that the
desire for async, just as the desire for async replication is more
common than the desire for syncrep.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com