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.