Materialized Views GSoC 2010
Overview
A materialized view (MV) is an optimization technique that behaves like a cache. Classic views are virtual tables while an MV is a real table based on view definition. By creating an MV you can pre-compute expensive queries to speed up certain queries.
Materialized views are a very useful feature implemented by leading database systems like Oracle, DB2, SQL Anywhere or SQL Server and it is the number one requested feature by users [1] in PostgreSQL.
Student name: Pavel Baroš
Mentor name: Robert Haas
Deliverables
The general idea is to implement so called Snapshot MV for this summer. Snapshot materialized views are refreshed by a special refresh command by rebuilding the MV definition.
This project could be divided to three main steps:
- Create MV
- change grammar
CREATE MATERIALIZED VIEW ..
- ensure that backend will create table, make record in pg_class, etc.
- Using in SELECT
- learn backend (rule system) to use MV in SELECT command (when used in SELECT, fetch row from MV and not rewrite with view definition)
- Create Refresh command
- again change gramar
ALTER MATERIALIZED VIEW mvname REFRESH
- make changes to command
Schedule
until May 31:
- discuss about implementation specifics with comunity
- reading code (especialy understanding new version 9.0)
June 1 - June 15:
- implement new relation kind
- change grammar create mv
- create mv (physical table), save tuple in pg_class
June 16 - June 30:
- change grammar for refresh command
- considering new system rule for MV
- change rule system - backend should behave to MV like table
July 1 - July 15:
- create refresh command - consider and implement the way it could work
- make other changes - psql, pg_dump, etc.
- make regression tests, test cases
July 16 - August 9:
- consider extensibility for better MV
- code rewiew
- test for psql, pg_dump, etc.
August 10 - August 16:
- testing and commiting
