Materialized Views

Introduction

A materialized view stores the results of a query physically, unlike regular views which are virtual. Queries can read pre-computed results instead of re-executing the underlying query against base tables.

Materialized views improve performance for expensive queries by calculating results once during refresh rather than on every query. Common use cases include aggregations, joins, and filtered subsets of large tables.

Warning

Materialized views are experimental. The SPI and behavior may change in future releases. Use at your own risk in production environments.

To enable materialized views, set experimental.legacy-materialized-views = false in your configuration properties.

Alternatively, you can use SET SESSION legacy_materialized_views = false to enable them for a session, but only if experimental.allow-legacy-materialized-views-toggle = true is set in the server configuration. The toggle option should only be used in non-production environments for testing and migration purposes.

Security Modes

When legacy_materialized_views=false, materialized views support SQL standard security modes that control whose permissions are used when querying the view:

SECURITY DEFINER

The materialized view executes with the permissions of the user who created it. This is the default mode and matches the behavior of most SQL systems. When using DEFINER mode, column masks and row filters on base tables are permitted.

SECURITY INVOKER

The materialized view executes with the permissions of the user querying it. Each user must have appropriate permissions on the underlying base tables. When using INVOKER mode and there are column masks or row filters on the base tables, the materialized view is treated as stale, since the data would vary by user.

The default security mode can be configured using the default_view_security_mode session property. When the SECURITY clause is not specified in CREATE MATERIALIZED VIEW, this default is used.

Note

The REFRESH operation always uses DEFINER rights regardless of the view’s security mode.

Required Permissions

The following permissions are required for materialized view operations when legacy_materialized_views=false:

CREATE MATERIALIZED VIEW

  • CREATE TABLE permission

  • CREATE VIEW permission

REFRESH MATERIALIZED VIEW

  • INSERT permission (to write new data)

  • DELETE permission (to remove old data)

DROP MATERIALIZED VIEW

  • DROP TABLE permission

  • DROP VIEW permission

Querying a materialized view

  • For DEFINER mode: User needs SELECT permission on the view itself

  • For INVOKER mode: User needs SELECT permission on all underlying base tables

See Also

CREATE MATERIALIZED VIEW, DROP MATERIALIZED VIEW, REFRESH MATERIALIZED VIEW, SHOW CREATE MATERIALIZED VIEW