Re: Postgresql Materialized views

Merlin Moncure wrote:
> On Jan 12, 2008 4:19 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Sat, 2008-01-12 at 13:27 +0100, Jean-Michel Pouré wrote:
>>
>>> Please pick-up this important issue for developpers. There is no need to
>>> concentrate on complex issues, when handling materialized views could
>>> boost somme web apps. by a factor of 10 or more.
>> It's more complex than you think, but the main reason was that HOT was a
>> prerequisite for making summary tables work efficiently, which is only
>> now just about to go live into 8.3
>
> +1

I don't quite agree with that. HOT certainly speeds up UPDATEs on small
tables, like you a summary table, but there's a lot of use cases like
data warehousing, where the summary tables are not updated that often
for the updates to become a bottleneck.

> If you know how to write triggers, materialization techniques aren't
> all that difficult. The real technical limitation was not lack of
> materialization techniques (write triggers), but was dealing with the
> mvcc penalty. Previously to HOT, for summary tables I would redirect
> the trigger to insert to a 'roll up' table and move the data to the
> summary on cron or from an application event.
>
> Materialized views are syntax sugar (but still very sweet).

There's two things involved in materialized views:

1. Automatically updating the materialized view, when the tables change.
This can be done with triggers, right now, but requires quite a bit of
manual work to set up, especially with more complex views.

2. Using the materialized views to speed up existing queries. For
example, if you have a materialized view on "SELECT COUNT(*) FROM foo",
and someone issues the query "SELECT COUNT(*) FROM foo", the planner
should automatically use the view to satisfy that.

1 is syntactic sugar, but 2 isn't.

These are orthogonal features. Implementing just 1 without 2 would still
be very useful, and in fact that seems to be what most people mean by
materialized views.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com