On Fri, Sep 23, 2011 at 3:03 AM, Mike Christensen <mike@xxxxxxxxxxxxx> wrote: > 1) Though I might have given the impression that a “manual complete > refresh” is not useful, I definitely see value in this especially for > data warehousing scenarios. However, I’d almost want to call this a > “snapshot” and not a “materialized view” – the two things are so > different, I think it warrants different syntax. [snip] I like the terminology employed by this page: http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views which clarifies between "snapshot", "eager", "lazy", and "very lazy" materialized views. [snip] > One could argue that if you’re going this far, you’ve basically turned > the whole trigger mechanism inside out and one could already implement > this whole thing on Postgres 9 using real tables and triggers when the > appropriate data changes. This is something I’m struggling with as > well. Materialized views seem to be great for quick database > snapshots and very simple selects, but anything too complicated and > it’s either not an option, or you’d have to explain so much logic that > you’re better off using a series of triggers and writing a custom > solution anyway. > > This makes me really question the fundamental use of materialized > views. In other words, what is a real solid scenario that you would > use one for that simply cannot be done currently using triggers? Or, > is it simply the “ease of creation” people are after? There must be > things I’m just not seeing, as this is the number one feature request > on postgresql.uservoice.com by a massive landslide, and I fail to > believe all those people are just “lazy.” As a preface, I think materialized views are awesome when used correctly. I don't have first-hand experience with Oracle's implementation of them. As far as I know, the maintenance of the matview itself can always be done just as well using triggers as it can by a database engine (like Oracle). In fact, I'd bet that for any reasonably complicated matview, you're going to be able to wring out quite a bit more performance from the hand-written one, as you can employ optimizations that the database engine just doesn't know about. For example, a while back I wrote a heavily-queried ~50M row matview which was derived from ~12 or so base relations, some of which were heavily updated. I was able to make the load imposed by the matview a small part of the bulk-loading of the base tables by keeping track of which changes to the base tables were actually "interesting" to the matview, where "interesting" meant "could possibly cause a significant change to the corresponding row in the matview". Plus little tricks like knowing when it'd be cheaper to perform a full refresh of the matview vs. slogging through millions of rows of UPDATEs. I imagine a database-engine maintained matview in Oracle would have imposed more system load and been slower to keep up-to-date. Of course, one cool thing a database-engine-supported matview can do is automatic query-rewriting, if it can prove that the materialized view is both up to date, and capable of satisfying the user's query. But then, if you are using materialized views, is it really that hard to change your performance-intensive queries to select from the view instead of the base table? As for materialized views being the #1 most-requested feature on uservoice... well, perhaps the voters don't have a good idea of how much effort it would be to implement (vs. other compelling features we could add), and perhaps also don't understand how powerful and flexible trigger-maintained matviews can be. The fact that the suggestion claims it would "... boost performance for every web application" makes me pretty skeptical right off the bat. I'm not saying it wouldn't be cool to have, just that there is still a lot more low-hanging fruit that the data-warehousing crowd would benefit from. Josh -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
