- Hot standby
- Materialized views
- Synchronous replication
- Parallel queries
- Better administration and monitoring tools
I remember many years ago, materialized views were a somewhat hot topic, but things have cooled down significantly now. In a broader sense, materialized views are queries caches, and I did hear something about that at PGCon. In the meantime, Jonathan Gardner's article Materialized Views in PostgreSQL appears to remain the most frequently cited treatment on the subject. (The original link is down, unfortunately.) The PGCon 2008 tutorial Materialized Views that Really Work by Dan Chak is also worth looking at.
So, who's up for implementing built-in materialized views/query caching?
Also, if you haven't done so, submit your suggestions and votes at the UserVoice forum.
Posts
I'm proud that i originally posted and wrote the description about materialized vies :-)
ReplyDeleteIt's true that many of us develop very complex applications, and the need for speed, in most of them, is a prerequisite.
So, the materialized views are unavoidable. I have spent most of my time creating and maintaining the triggers and functions to handle these views. I don't say that someone cannot make it right, but the whole process is very risky for critical errors.
An automated process would be much better, and would allow developers to use them more often, and still keeping their database infrastructure clean and fast, by materializing the necessary views.
So if someone could implement this for the community it will be a great gift, for a great need :-)
Thx,
Tasos
Unfortunatelly, materialized views could became somewhat heavy to mantain than actual CTE. Most of my performance view needs were solved with CTE.
ReplyDeleteSure, we can't always use CTE, because there are legacies which just won't work with new features, but, I'm not a PostgreSQL developer, just the DBA. :)
Jonathan Gardner here...
ReplyDeleteI fixed the URL. Sorry about that, all.
Seeing how certain other DBs handle Materialized Views, I'm not convinced that we should have a general solution as part of PostgreSQL. It almost seems like it is easier to roll your own solution with triggers and functions than to rely on something built-in to the database.
Perhaps we should focus more on education and less on implementation for the time being. Most people who want Materialized Views don't really understand that they're just trading one thing for another. Without that understanding, you can really dig yourself into a hole that you won't know how to get out of.
so I ran across a need for materialized views just recently, in the spatial database realm.. PostGIS. If I have "big data" on the world, the US or even "just" my state of California, and I want to do some detailed investigation of a reasonably sized area, like my county or city, then I write a few SELECTs that take subsets (from points) or create new data (subsets of linestrings or polygons). I was thinking that a materialized view might be just the thing there.. the cost of the SELECT is high, and the result is subsequently referred to often....
ReplyDeleteIs there any progress in materialized views?
ReplyDeleteAre they going to be implemented? Is it under discussion and consideration or the idea is rejected?
Can someone from the "inside" inform us :-)
PS. A big "thank you" to all postgresql committers.
I think it's safe to say that there is no one working on this currently.
ReplyDeleteI will be working on it this year, with the intention of getting it accepted for the 9.3 release.
ReplyDelete