Re: [Postgres-xc-developers] Fwd: [HACKERS] materialized views and FDWs

[Michael P. <mic...@gm...>]

On Tue, Mar 12, 2013 at 7:56 PM, Ashutosh Bapat <
ash...@en...> wrote:

> Hi All,
> Support for materialised view in XC is going to be a problem when we will
> pull this feature in XC. The problem is where should be storage of the
> materialized view. There are two ways, we can store a materialised view -
> a. make it coordinator only and store the materialized view result at the
> coordinator. OR b. store it like any other table, replicated or
> distributed.
>

> I am assuming that materialized views will be created for frequently
> occurring queries, such that a single materialized view is capable of
> serving whole query. In that case, having coordinator local storage would
> improve the performance, since the query doesn't need any fetches from
> datanode. We will need to create the infrastructure to have coordinator
> local storage for user data.
>
> The second option doesn't look that attractive unless a materialized view
> is being mis-used so that higher percentage of queries need joins with
> other views or tables.

I assume that materialized views should be replicated at each Coordinator
with storage on Coordinator. Only a thought though... This would really
improve performance on some query joins.
Then, what about refresh? After that, doing a refresh on all the
Coordinators within the same transaction could be problematic as each
Coordinator would need to connect to each remote node, finishing with a
dangerous state where multiple connections would be open on remote nodes
for the same session. A refresh that runs only locally for each Coordinator
is enough I think.
-- 
Michael