pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMaterialized Views
4+ messages / 3 participants
[nested] [flat]
* Materialized Views
@ 2006-01-16 14:36 Michael Riess <mlriess@gmx.de>
2006-01-16 15:40 ` Re: Materialized Views Frank Wiles <frank@wiles.org>
2006-01-16 16:17 ` Re: Materialized Views me@alternize.com
0 siblings, 2 replies; 4+ messages in thread
From: Michael Riess @ 2006-01-16 14:36 UTC (permalink / raw)
To: pgsql-performance
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.
Mike
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Materialized Views
2006-01-16 14:36 Materialized Views Michael Riess <mlriess@gmx.de>
@ 2006-01-16 15:40 ` Frank Wiles <frank@wiles.org>
2006-01-16 16:26 ` Re: Materialized Views Michael Riess <mlriess@gmx.de>
1 sibling, 1 reply; 4+ messages in thread
From: Frank Wiles @ 2006-01-16 15:40 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On Mon, 16 Jan 2006 15:36:53 +0100
Michael Riess <mlriess@gmx.de> 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 <frank@wiles.org>
http://www.wiles.org
---------------------------------
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Materialized Views
2006-01-16 14:36 Materialized Views Michael Riess <mlriess@gmx.de>
2006-01-16 15:40 ` Re: Materialized Views Frank Wiles <frank@wiles.org>
@ 2006-01-16 16:26 ` Michael Riess <mlriess@gmx.de>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Riess @ 2006-01-16 16:26 UTC (permalink / raw)
To: pgsql-performance
Thanks!
Of course I know that I can build materialized views with triggers, but
so far I've avoided using triggers altogether ... I would really
appreciate something like "create view foo (select * from b) materialize
on query".
But I'll look into your blog entry, thanks again!
Mike
> On Mon, 16 Jan 2006 15:36:53 +0100
> Michael Riess <mlriess@gmx.de> 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 <frank@wiles.org>
> http://www.wiles.org
> ---------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Materialized Views
2006-01-16 14:36 Materialized Views Michael Riess <mlriess@gmx.de>
@ 2006-01-16 16:17 ` me@alternize.com
1 sibling, 0 replies; 4+ messages in thread
From: me@alternize.com @ 2006-01-16 16:17 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; pgsql-performance
hi mike
> In particular I'm interested in a view which materializes whenever
> queried, and is invalidated as soon as underlying data is changed.
from the german pgsql list earlier last week:
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
this seems to be pretty much what you want (except you'll have to update
everything yourself). would be really nice if pgsql supports this "in-house"
cheers,
thomas
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-01-16 16:26 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-16 14:36 Materialized Views Michael Riess <mlriess@gmx.de>
2006-01-16 15:40 ` Frank Wiles <frank@wiles.org>
2006-01-16 16:26 ` Michael Riess <mlriess@gmx.de>
2006-01-16 16:17 ` me@alternize.com
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox