Skip site navigation (1) Skip section navigation (2)

Re: [HACKERS] [SQL] Materialized View Summary

From: Jonathan Gardner <jonagard(at)amazon(dot)com>
To: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
Cc: Richard Huxton <dev(at)archonet(dot)com>, pgsql-hackers(at)postgresql(dot)org,pgsql-sql(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org
Subject: Re: [HACKERS] [SQL] Materialized View Summary
Date: 2004-02-24 22:19:39
Message-ID: 200402241419.41973.jonagard@amazon.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performancepgsql-sqlpgsql-www
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tuesday 24 February 2004 01:48 pm, Robert Treat wrote:
> On Tue, 2004-02-24 at 12:11, Richard Huxton wrote:
> > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote:
> > > I've written a summary of my findings on implementing and using
> > > materialized views in PostgreSQL. I've already deployed eagerly
> > > updating materialized views on several views in a production
> > > environment for a company called RedWeek: http://redweek.com/. As a
> > > result, some queries that were taking longer than 30 seconds to run
> > > now run in a fraction of a millisecond.
> > >
> > > You can view my summary at
> > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.htm
> > >l
>
> have you done much concurrency testing on your snapshot views? I
> implemented a similar scheme in one of my databases but found problems
> when I had concurrent "refresh attempts".  I ended up serializing the
> calls view LOCKing, which was ok for my needs, but I thought potentially
> problematic in other cases.
>

I don't actually use snapshot views in production. I would imagine that if 
you had two seperate processes trying to update the views simultaneously, 
that would be a problem. All I can say is "don't do that". I think you'd 
want to lock the table before we go and start messing with it on that 
scale.

We are running into some deadlock issues and some other problems with eager 
mvs, but they are very rare and hard to reproduce. I think we are going to 
start locking the row before updating it and see if that solves it. We also 
just discovered the "debug_deadlock" feature.

I'll post my findings and summaries of the information I am getting here 
soon.

I'm interested in whatever you've been working on WRT materialized views. 
What cases do you think will be problematic? Do you have ideas on how to 
work around them? Are there issues that I'm not addressing but should be?

> > Interesting (and well written) summary. Even if not a "built in"
> > feature, I'm sure that plenty of people will find this useful. Make
> > sure it gets linked to from techdocs.
>
> Done. :-)
>

*blush*

> > If you could identify candidate keys on a view, you could conceivably
> > automate the process even more. That's got to be possible in some
> > cases, but I'm not sure how difficult it is to do in all cases.
>
> it seems somewhere between Joe Conways work work arrays and polymorphic
> functions in 7.4 this should be feasible.
>

I'll have to look at what he is doing in more detail.

- -- 
Jonathan M. Gardner
Web Developer, Amazon.com
jonagard(at)amazon(dot)com - (206) 266-2906
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAO837BFeYcclU5Q0RAhonAKDBY7Svz9/vxmerS+y/h2mLgV1ZZQCdFlnd
7aMPFvRx4O8qg+sJfWkaBh8=
=zdhL
-----END PGP SIGNATURE-----

In response to

pgsql-performance by date

Next:From: Robert CreagerDate: 2004-02-25 05:10:16
Subject: Speed up a function?CREATE TABLE readings ( "when" TIMESTAMP DEFAULT timeofday()::timestamp NOT NULL PRIMARY KEY, "barometer" FLOAT DEFAULT NULL,
Previous:From: Tom LaneDate: 2004-02-24 22:16:08
Subject: Re: Column correlation drifts, index ignored again

pgsql-www by date

Next:From: World Wide Web OwnerDate: 2004-02-25 02:01:34
Subject: New News Entry
Previous:From: Robert TreatDate: 2004-02-24 21:48:49
Subject: Re: [HACKERS] [SQL] Materialized View Summary

pgsql-hackers by date

Next:From: Dann CorbitDate: 2004-02-24 23:03:05
Subject: Is indexing broken for bigint columns?
Previous:From: Robert TreatDate: 2004-02-24 21:48:49
Subject: Re: [HACKERS] [SQL] Materialized View Summary

pgsql-sql by date

Next:From: Humble GeekDate: 2004-02-25 03:56:22
Subject: PLSQL Question regarding multiple inserts
Previous:From: Robert TreatDate: 2004-02-24 21:48:49
Subject: Re: [HACKERS] [SQL] Materialized View Summary

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group