Reference: Materialized Views

A materialized view stores the result of a query as a table. Unlike a regular view, CedarDB does not rerun the query when you read from a materialized view. Instead, it returns the rows that it computed when the view was created or last refreshed. Changes to the underlying tables become visible only after you run REFRESH MATERIALIZED VIEW.

CREATE TABLE trees (
    id       integer PRIMARY KEY,
    species  text    NOT NULL,
    height_m numeric NOT NULL
);
INSERT INTO trees VALUES (1, 'Oak', 12.4), (2, 'Oak', 20.1), (3, 'Birch', 9.0);

CREATE MATERIALIZED VIEW species_stats AS
    SELECT species, count(*) AS tree_count, avg(height_m) AS avg_height_m
    FROM trees
    GROUP BY species;

SELECT * FROM species_stats ORDER BY species;

 species | tree_count | avg_height_m
---------+------------+--------------
 Birch   |          1 |     9.000000
 Oak     |          2 |    16.250000

Using Materialized Views

Materialized views are useful for expensive queries whose results you read much more often than the underlying data changes. Typical examples are aggregations for dashboards and reports, or pre-joined data for an application.

You can read from a materialized view like from any other table, join it with other tables and views, and create indexes on it. You cannot change its contents with INSERT, UPDATE, DELETE, TRUNCATE, or COPY FROM. The only way to change the contents is REFRESH MATERIALIZED VIEW.

After creating a materialized view, you can find it in the pg_matviews system view. In pg_class, materialized views have the relkind value m.

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW defines a new materialized view and, by default, populates it with the result of the query.

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] <name> [ ( <column_name> [, ...] ) ]
    [ WITH ( <storage_option> = <value> [, ...] ) ]
    AS <query>
    [ WITH [ NO ] DATA ]

Rename the output columns:

CREATE TABLE trees (id integer, species text, height_m numeric);

CREATE MATERIALIZED VIEW tallest (tree_species, max_height_m) AS
    SELECT species, max(height_m) FROM trees GROUP BY species;

Store the contents of a materialized view on remote storage, such as S3, instead of local disk. This works for materialized views just like for tables, and requires a server previously created with CREATE SERVER:

CREATE TABLE trees (id integer, species text, height_m numeric);

CREATE MATERIALIZED VIEW tree_heights WITH (server = remote_storage) AS
    SELECT id, height_m FROM trees;

A materialized view can read from tables, views, and other materialized views. It cannot read from temporary tables, neither directly nor through a view, because a materialized view outlives the session of a temporary table.

Unpopulated materialized views

With WITH NO DATA, CedarDB only checks the query for errors and derives the columns, but does not run it. Reading from an unpopulated materialized view is an error:

CREATE TABLE trees (id integer, species text);
INSERT INTO trees VALUES (1, 'Oak');

CREATE MATERIALIZED VIEW oaks AS
    SELECT id FROM trees WHERE species = 'Oak'
    WITH NO DATA;

SELECT * FROM oaks;
-- ERROR:  materialized view "oaks" has not been populated

REFRESH MATERIALIZED VIEW oaks;
SELECT * FROM oaks;
--  id
-- ----
--   1

The ispopulated column of pg_matviews and the relispopulated column of pg_class show whether a materialized view is populated.

Indexes

You can create regular and unique indexes on a materialized view. CedarDB maintains them on every refresh. If a refresh produces rows that violate a unique index, the refresh fails and the view keeps its previous contents:

CREATE TABLE trees (id integer, species text);
INSERT INTO trees VALUES (1, 'Oak'), (2, 'Birch');

CREATE MATERIALIZED VIEW tree_species AS SELECT id, species FROM trees;
CREATE UNIQUE INDEX ON tree_species (species);

INSERT INTO trees VALUES (3, 'Oak');
REFRESH MATERIALIZED VIEW tree_species;
-- ERROR:  duplicate key value violates unique constraint "tree_species_species_key"

Permissions

To create a materialized view, you need the CREATE privilege on the target schema and the SELECT privilege on all relations that the query reads. The creating role becomes the owner of the view.

Other roles only need the SELECT privilege on the materialized view itself to read it. They do not need any privileges on the underlying tables:

GRANT SELECT ON species_stats TO dashboard_reader;

REFRESH MATERIALIZED VIEW

REFRESH MATERIALIZED VIEW replaces the contents of a materialized view with the current result of its query.

REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] <name>
    [ WITH [ NO ] DATA ]

CREATE TABLE trees (id integer, species text);
INSERT INTO trees VALUES (1, 'Oak');

CREATE MATERIALIZED VIEW tree_count AS SELECT count(*) AS total FROM trees;

INSERT INTO trees VALUES (2, 'Birch');
SELECT total FROM tree_count;
-- 1

REFRESH MATERIALIZED VIEW tree_count;
SELECT total FROM tree_count;
-- 2

A refresh is transactional like any other write. Concurrent transactions keep reading the previous contents until the refresh commits. If you roll back the transaction, the view keeps its previous contents.

When a materialized view reads from another materialized view, refreshing the outer view uses the current contents of the inner one. Refresh the views in dependency order, starting with the ones that only read from tables.

CedarDB does not refresh materialized views automatically. To keep a view up to date, run REFRESH MATERIALIZED VIEW periodically or after loading new data.

Permissions

Only the owner of a materialized view can refresh it. The owner needs the SELECT privilege on all relations that the query reads.

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW changes the properties of an existing materialized view. Specify IF EXISTS to not throw an error if the materialized view does not exist.

Rename a materialized view:

ALTER MATERIALIZED VIEW species_stats RENAME TO species_summary;

Rename a column of a materialized view. This only renames the column of the view, not the column of the underlying table:

ALTER MATERIALIZED VIEW species_stats RENAME COLUMN tree_count TO number_of_trees;

Move a materialized view to another schema:

ALTER MATERIALIZED VIEW species_stats SET SCHEMA reporting;

Change the owner of a materialized view:

ALTER MATERIALIZED VIEW species_stats OWNER TO analyst;

ALTER TABLE can also rename a materialized view, move it to another schema, and change its owner. It rejects changes that only apply to tables, such as adding columns or constraints, since the columns of a materialized view are defined by its query.

Permissions

To alter a materialized view, you must be its owner. Superusers can alter any materialized view.

DROP MATERIALIZED VIEW

DROP MATERIALIZED VIEW removes a materialized view, its data, and its indexes.

DROP MATERIALIZED VIEW [ IF EXISTS ] <name> [, ...] [ CASCADE | RESTRICT ]

DROP MATERIALIZED VIEW species_stats;

Do not throw an error if the materialized view does not exist:

DROP MATERIALIZED VIEW IF EXISTS species_stats;

If other views or materialized views read from the materialized view, the drop fails. Use CASCADE to drop the dependent objects as well:

DROP MATERIALIZED VIEW species_stats CASCADE;

Use the right DROP statement for the object type: DROP TABLE and DROP VIEW do not drop materialized views.

Permissions

To drop a materialized view, you must own it, own its schema, or be a superuser.

Dependencies

A materialized view depends on all tables and views its query reads. While the materialized view exists, CedarDB refuses to drop these relations or the columns the view reads, unless you use CASCADE.

You also cannot rename these relations or move them to another schema while the materialized view exists.

PostgreSQL Differences

• REFRESH MATERIALIZED VIEW never blocks readers, as CedarDB uses snapshot isolation. CONCURRENTLY is accepted as a synonym for a regular refresh, does not require a unique index, and can be combined with WITH NO DATA.
• CedarDB refuses to rename or move a table, view, or materialized view to another schema while a materialized view reads from it.