Materialized Views

From PostgreSQL wiki

Revision as of 04:00, 8 June 2013 by Barwick (Talk | contribs)

Jump to: navigation, search

Materialized Views are currently the #1 requested feature in a user survey for addition to PostgreSQL.

It is currently possible to build materialized views using the trigger capabilities of the database. Some implementations currently available include:

PostgresSQL 9.3 will provide materialized view support.

Contents

Adding built-in Materialized Views

Having MV support built into the database has been discussed actively since at least 2003. The main components required fall into three pieces:

1. Creation of materalized view

  • Current state: using "CREATE TABLE AS" or similar mechanism, maintain manually
  • Optimal: "CREATE MATERIALIZED VIEW" grammar, metadata to store MV data, dump/reload support

2. Updating materialized views

  • Current state: periodically create new snapshots, or maintain using triggers
  • Optimal: Built-in refresh via multiple strategies, with minimal locking as to improve concurrent access

3. Using materialized views in the planner

  • Current state: specify the manually created MV in queries that can use it
  • Optimal: Automatically accelerate queries that could be satisfied by substituting available MVs

Prior Work

Implementation

FlexViews and Oracle both seem to use a change-tracking / replication system as the basis for updating the views incrementally. Once such a system is in place you can inspect changes to determine whether they affect a materialized view and, if so, which rows of the view need to be updated. Then the view can be sub-selected against to insert new rows into the table backing the view.

For each view therefore you need to be able to transform the original select into an algorithm that takes a change (or list of changes) and outputs ranges of rows in the view to update. Updates are probably best done by issuing a DELETE and SELECT INTO or lower-level equivalent. This then triggers updates to any attached indices.

Examples / Use Cases

CREATE TABLE vec (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER)
CREATE MATERIALIZED VIEW hypot AS SELECT sqrt(a*a + b*b) as c

=> Add primary key id:INTEGER to hypot
=> INSERT INTO hypot (c) SELECT sqrt(a*a + b*b) FROM vec
=> ON UPDATE/INSERT/DELETE vec:
     DELETE FROM hypot WHERE hypot.id = ROW.id
     INSERT INTO hypot (c) SELECT sqrt(a*a + b*b) FROM vec WHERE vec.id = row.id
Personal tools