Create and Manage Materialized Views in Cloudberry Database

In Cloudberry Database, materialized views are similar to views. A materialized view enables you to save a frequently used or complex query, then access the query results in a SELECT statement as if they were a table. Materialized views persist the query results in a table-like form.

Although accessing the data stored in a materialized view can be much faster than accessing the underlying tables directly or through a regular view, the data is not always current. The materialized view data cannot be directly updated. To refresh the materialized view data, use the REFRESH MATERIALIZED VIEW command.

The query used to create the materialized view is stored in exactly the same way that a view's query is stored. For example, you can create a materialized view that quickly displays a summary of historical sales data for situations where having incomplete data for the current date is acceptable.

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT seller_no, invoice_date, sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY seller_no, invoice_date

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);

The materialized view might be useful for displaying a graph in the dashboard created for sales people. You can schedule a job to update the summary information each night using the following command:

REFRESH MATERIALIZED VIEW sales_summary;

The information about a materialized view in the Cloudberry Database system catalogs is exactly the same as it is for a table or view. A materialized view is a relation, just like a table or a view. When a materialized view is referenced in a query, the data is returned directly from the materialized view, just like from a table. The query in the materialized view definition is only used for populating the materialized view.

If you can tolerate periodically updating the materialized view data, you can get great performance benefit from the view.

One use of a materialized view is to allow faster access to data brought in from an external data source such as external table or a foreign data wrapper. Also, you can define indexes on a materialized view, whereas foreign data wrappers do not support indexes; this advantage might not apply for other types of external data access.

If a subquery is associated with a single query, consider using the WITH clause of the SELECT command instead of creating a seldom-used materialized view.

Create materialized views

The CREATE MATERIALIZED VIEW command defines a materialized view based on a query.

CREATE MATERIALIZED VIEW us_users AS SELECT u.id, u.name, a.zone FROM users u, address a WHERE a.country = 'USA';

tip

When a materialized view is created with an ORDER BY or SORT clause, this sorting is applied only at the time of the view's initial creation. Subsequent refreshes of the materialized view do not maintain this order, because the view is essentially a static snapshot of data and does not dynamically update or preserve the sorting with new data insertions.

Refresh or deactivate materialized views

The REFRESH MATERIALIZED VIEW command updates the materialized view data.

REFRESH MATERIALIZED VIEW us_users;

With the WITH NO DATA clause, the current data is removed, no new data is generated, and the materialized view is left in an unscannable state. An error is returned if a query attempts to access an unscannable materialized view.

REFRESH MATERIALIZED VIEW us_users WITH NO DATA;

Drop materialized views

The DROP MATERIALIZED VIEW command removes a materialized view definition and data. For example:

DROP MATERIALIZED VIEW us_users;

The DROP MATERIALIZED VIEW ... CASCADE command also removes all dependent objects. For example, if another materialized view depends on the materialized view which is about to be dropped, the other materialized view will be dropped as well. Without the CASCADE option, the DROP MATERIALIZED VIEW command fails.