From: Jonathan Gardner <jonagard@amazon.com>
Organization: Amazon
To: Robert Treat <xzilla@users.sourceforge.net>
Subject: Re: [HACKERS] [SQL] Materialized View Summary
Date: Tue, 24 Feb 2004 14:19:39 -0800
User-Agent: KMail/1.6
Cc: Richard Huxton <dev@archonet.com>, pgsql-hackers@postgresql.org,
	pgsql-sql@postgresql.org, pgsql-performance@postgresql.org
References: <200402240811.13013.jgardner@jonathangardner.net>
	<200402241711.20947.dev@archonet.com>
	<1077659329.15366.6061.camel@camel>
In-Reply-To: <1077659329.15366.6061.camel@camel>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200402241419.41973.jonagard@amazon.com>

-----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=
=20
you had two seperate processes trying to update the views simultaneously,=
=20
that would be a problem. All I can say is "don't do that". I think you'd=20
want to lock the table before we go and start messing with it on that=20
scale.

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

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

I'm interested in whatever you've been working on WRT materialized views.=
=20
What cases do you think will be problematic? Do you have ideas on how to=20
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.

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

iD8DBQFAO837BFeYcclU5Q0RAhonAKDBY7Svz9/vxmerS+y/h2mLgV1ZZQCdFlnd
7aMPFvRx4O8qg+sJfWkaBh8=3D
=3DzdhL
-----END PGP SIGNATURE-----