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

Re: GSoC - proposal - Materialized Views in PostgreSQL

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pavelbaros <baros(dot)p(at)seznam(dot)cz>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: GSoC - proposal - Materialized Views in PostgreSQL
Date: 2010-04-11 03:40:48
Message-ID: 4BC144C0.3020909@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Robert Haas wrote:
> It's not obvious to me
> that a brief full-table lock wouldn't be acceptable for an initial
> implementation.  Obviously it wouldn't be suitable for every use case
> but since we're talking about manually refreshed views that was bound
> to be true anyway.
>   

There already is an initial implementation of sorts.  There are a couple 
of ways you can build these right now, so any new development has to 
look like it will end with good odds of being an improvement over what's 
already available before it's worth putting development resources into.

As a rough idea of what people want these for in the field, based on 
what I've seen requests for, imagine that someone has a 1TB table 
they're materializing a view on in order to get at least a 10:1, and 
hopefully close to a 100:1, speedup on viewing summary data.  Now, 
picture what happens if you have someone doing a sequential scan on the 
MV, which is still quite big, the updater process lines up to grab an 
exclusive lock when it's done, and now a second user wanting to read a 
single row quickly comes along behind it.  Given a full-table lock 
implementation, that scenario is unlikely to play out with the second 
user getting a fast response.  They'll likely sit in a lock queue for 
some potentially long period of time instead, waiting for the active seq 
scan to finish then the update to happen.  You have to build it that way 
or a steady stream of people reading could block out updates forever.

To be frank, that makes for a materalized view implementation of little 
value over what you can currently do as far as I'm concerned.  It might 
be interesting as a prototype, but that's not necessarily going to look 
like what's needed to do this for real at all.  I'm not a big fan of 
dumping work into projects when you can see exactly how it's going to 
fail before you even get started.  As I see if, if you know where it's 
going to fall down, you don't need to build a prototype as an exercise 
to show you how to build it--you should work on that part first instead.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com   www.2ndQuadrant.us


In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-04-11 05:08:45
Subject: Re: GSoC - proposal - Materialized Views in PostgreSQL
Previous:From: Greg SmithDate: 2010-04-11 03:03:31
Subject: Re: GSoC - proposal - Materialized Views in PostgreSQL

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