Postgresql Materialized Views
I don't believe Chris is working on this anymore. -- Bjorn 070806
What's the idea?
Oracle has "materialized views", Microsoft SQL Server has "indexed views", Postgresql users are missing out. Basically, I want the ability to pre-compute (and maintain up-to-date) the results of certain queries. This usually comes up when you want a summary or aggregrate view of existing data, in a mostly-read, rarely-write environment, but has a wide variety of applications.
What I hope to accomplish
I want a user to be able to write a normal SQL view, and pass the view's name to a procedure, which will automatically do the following:
- Tell the user if the view could not be materialized, and why. (sometimes it's just not possible?)
- Create appropriate table indices.
- Materialize the view (dump its output to a table)
- One of the following (as specified by the user):
- DEFAULT: Create triggers for maintaining the matview with live data at all times.
- Create a job that bulk-updates the matview, and schedule it to run at a specified time and interval.
- Create some query rewrite rules so that queries against the normal view now make use of the matview (if and only if it's up-to-date).
- Tell the user what actions it just took, and how much space it used up in the process.
- Perhaps support a "dry-run" option?
How I plan to accomplish this
- Mostly research at the moment.
- Jonathan Gardner has made a first pass at it.
- I'm pretty sure I'll need to get my hands dirty with Postgresql Rules.
- Is this mostly done already? Need to check out Pg::Snapshot.
Who's working on this?
- chris
- Coordinate with the The matview Project @ GBorg if possible.
