Materialized Views
Materialized Views have been implemented in PostgreSQL 9.3 after they had been the #1 requested feature in a user survey.
Versions before Postgres 9.3
In earlier versions it was possible to build materialized views using the trigger capabilities of the database. Some implementations available include:
- PostgreSQL Materialized Views by Jonathan Gardner.
- Materialized Views that Really Work by Dan Chak.
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
