Re: GSoC - proposal - Materialized Views in PostgreSQL

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