Re: [HACKERS] [SQL] Materialized View Summary

-----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-----