Views

Views

Views are persistent "fake" tables that have rows from other tables. These can be exact rows as they exists on another table, or a computed value.

They can be referenced in any place where you would normally reference a table.

Views don't create new tables or move any data around.

Views don't improve performance on heavy queries.

Since the View data is not "saved", every access to a View will under the hood run the heavy query every time.

Creating

CREATE VIEW view_name AS (
    -- In here, any kind of SELECT query
    SELECT * FROM somewhere
);

Updating

CREATE OR REPLACE VIEW view_name AS (
    -- In here, any kind of SELECT query
    SELECT * FROM somewhere
);

Deleting

DROP VIEW view_name;

Materialized Views

With Materialized Views, the query gets executed only at specific times, but the results are saved and can be referenced without rerunning the query.

The ideia is that it will not garantee data integrity, since it can hold old values, until its data is refreshed.

Materialized Views work as regular tables, so you can add indexes, primary keys, and etc.

Running SELECT on an empty materialized view will generate an error of "Unpopulated materialized view" instead of an empty result.

Creating

CREATE MATERIALIZED VIEW [IF NOT EXISTS] view_name (
    -- In here, the heavy query
    SELECT * FROM somewhere
) WITH [NO] DATA;

WITH DATA will load data into the materialized view at the creation time. (Will lock the source tables for as long as it takes to fully populate the view)

Refreshing data

Data must be manually refreshed.

Postgres doesn't automatically update the data on them.

By default a materialized view is locked while it is refreshing.

REFRESH MATERIALIZED VIEW [CONCURRENTLY] view_name;

concurrently

CONCURRENTLY allows for the update of the materialized view without locking it. (Postgres creates a temporary updated version of the materialized view, compares the two versions, and performs INSERT and UPDATE only on the differences)

A requirement for using CONCURRENTLY is that the materialized view must have a UNIQUE INDEX.

Deleting

DROP MATERIALIZED VIEW view_name;