Re: GSoC - proposal - Materialized Views in PostgreSQL

Heikki Linnakangas wrote:
> Your proposal basically describes
> doing 1, in a limited fashion where the view is not updated
> automatically, but only when the DBA runs a command to refresh it. I'm
> not sure if that's useful enough on its own, writing "CREATE
> MATERIALIZED VIEW ... SELECT ..." doesn't seem any easier than just
> writing "CREATE TABLA AS ...". But if you can do something about 2, or
> even a very limited part of 1, keeping the view up-to-date
> automatically, it becomes much more useful.
>

You've hit upon the core issue here. You can build materialized views
right now using "CREATE TABLE AS". You can even update them by creating
a new table the same way, with a new name, and doing the
LOCK/RENAME/DROP shuffle--what people used to do for rewriting bloated
tables before there was CLUSTER. The first step in the proposal here is
essentially syntax to give an easier UI for that. That's an interesting
step, but recognize that it doesn't actually provide anything you can't
do already.

If you then note that doing any sort of incremental update to the view
is a hard problem, and that a lot of the useful cases for materialized
views involve tables where it's impractical to recreate the whole thing
anyway, you'll inevitably find yourself deeply lost in the minutia of
how to handle the updates. It's really the core problem in building
what people expect from a materialized view implementation in a serious
database. Chipping away at the other pieces around it doesn't move the
feature that far forward, even if you get every single one of them
except incremental updates finished, because everything else combined is
still not that much work in comparison to the issues around updates.

There certainly are a fair number of subproblems you can break out of
here. I just think it's important to recognize that the path that leads
to a useful GSoC project and the one that gives a production quality
materialized view implementation may not have that much in common, and
to manage expectations on both sides accordingly. If Pavel thinks he's
going to end up being able to say "I added materialized views to
PostgreSQL" at the end of the summer, that's going to end in
disappointment. And if people think this project plan will lead to
being able to claim PostgreSQL now has this feature, that's also not
going to go well. If the scope is "add initial grammar and rewriting
moving toward a future materialized view feature", which the underlying
implementation noted as a stub prototype, that might work out OK. This
is why I likened it to the work on "Syntax for partitioning", which has
a similarly focused subgoal structure.

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