Materialized Views GSoC 2010

From PostgreSQL wiki
Jump to navigationJump to search


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


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:

  1. Create MV
    • change grammar
    • ensure that backend will create table, make record in pg_class, etc.
  1. 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)
  1. Create Refresh command
    • again change gramar
    • make changes to command


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

Related pgsql-hackers posts