X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id EC8229DC862 for ; Mon, 16 Jan 2006 11:40:00 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77116-01 for ; Mon, 16 Jan 2006 11:40:02 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from frank.wiles.org (frank.wiles.org [24.124.39.75]) by postgresql.org (Postfix) with ESMTP id BC9FA9DC85E for ; Mon, 16 Jan 2006 11:39:55 -0400 (AST) Received: from kungfu (frank.wiles.org [127.0.0.1]) by frank.wiles.org (8.13.1/8.13.1) with SMTP id k0GFduwf011920; Mon, 16 Jan 2006 09:39:56 -0600 Date: Mon, 16 Jan 2006 09:40:36 -0600 From: Frank Wiles To: Michael Riess Cc: pgsql-performance@postgresql.org Subject: Re: Materialized Views Message-Id: <20060116094036.12a05a8c.frank@wiles.org> In-Reply-To: References: X-Mailer: Sylpheed version 2.0.1 (GTK+ 2.6.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.053 required=5 tests=[AWL=0.053] X-Spam-Score: 0.053 X-Spam-Level: X-Archive-Number: 200601/199 X-Sequence-Number: 16677 On Mon, 16 Jan 2006 15:36:53 +0100 Michael Riess wrote: > Hi, > > I've been reading an interesting article which compared different > database systems, focusing on materialized views. I was wondering how > the postgresql developers feel about this feature ... is it planned > to implement materialized views any time soon? They would greatly > improve both performance and readability (and thus maintainability) > of my code. > > In particular I'm interested in a view which materializes whenever > queried, and is invalidated as soon as underlying data is changed. You can already build materialized views in PostgreSQL, but you end up doing the "heavy lifting" yourself with triggers. You put insert/update/delete triggers on the underlying tables of your view that "do the right thing" in your materialized view table. I wrote a blog entry about this recently, http://revsys.com/blog/archive/9, where I used a very simple materialized view to achieve the performance I needed. It has links to the relevant documentation you'll need however to build triggers for a more complex situation. Hope this helps! --------------------------------- Frank Wiles http://www.wiles.org ---------------------------------