Ordinary materialized views in PolarDB for PostgreSQL-PolarDB(PolarDB)-阿里云帮助中心

Background

Unlike ordinary views, materialized views directly store query results. For complex queries, using a materialized view to hold the result can dramatically improve query performance. However, the data in a materialized view does not automatically change with its base tables, so you must run REFRESH MATERIALIZED VIEW to refresh it.

In PostgreSQL community, refreshing an ordinary materialized view has two main pain points:

Slow refresh: REFRESH MATERIALIZED VIEW re-executes the defining query of the materialized view and replaces the old data with the result. When the base tables are large, executing the defining query (typically an analytical query involving aggregation or multi-table joins) is expensive.
Refresh blocks reads: A non-CONCURRENTLY refresh blocks all queries against the materialized view for the entire duration. A large materialized view with a long refresh time becomes unreadable for that entire window. REFRESH MATERIALIZED VIEW CONCURRENTLY does not block reads, but it requires a unique index on the view that has no WHERE clause and applies changes through a row-by-row compare-and-merge process, which is noticeably slower when the change volume is large.

PolarDB for PostgreSQL addresses these two pain points and provides the following enhancements:

Columnar-accelerated refresh: The defining query during refresh can be executed by the in-memory column index (IMCI) with the vectorized engine, significantly speeding up analytical workloads such as aggregation and multi-table joins.
Non-blocking refresh: When enabled, read-only queries against the materialized view are no longer blocked while a refresh is in progress.
Scheduled refresh: With the pg_cron extension, refresh can be configured as a background scheduled job without any external scheduler.

Scope of application

PostgreSQL 14: Requires kernel minor version 2.0.14.20.43.0 or later.

Note

You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If the minor engine version does not meet the requirements, upgrade the minor engine version。

Columnar-accelerated refresh

How it works

REFRESH MATERIALIZED VIEW essentially re-executes the defining query of the materialized view and replaces the old data with the result. Defining queries are typically analytical workloads such as aggregation and multi-table joins—exactly the workloads the columnar engine is optimized for.

After columnar query is enabled, the defining query during refresh participates in columnar routing just like any other query: when the base table has a columnar store index and the estimated cost exceeds the threshold, the query is executed by the in-memory column index (IMCI) executor (based on the columnar replica and the vectorized engine), and the result is written back to the materialized view. Both REFRESH MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW CONCURRENTLY are supported.

Scope of application

Note

New cluster versions require no additional setup. For earlier cluster versions, you must first install the polar_csi extension (CREATE EXTENSION polar_csi;). For more information, see Enable the IMCI feature.

Version details:

PostgreSQL 16 (2.0.16.9.8.0 and later) or PostgreSQL 14 (2.0.14.17.35.0 and later): No additional setup required.
PostgreSQL 16 (2.0.16.8.3.0 to 2.0.16.9.8.0) or PostgreSQL 14 (2.0.14.10.20.0 to 2.0.14.17.35.0): Extension installation required.

Usage example

Setting polar_csi.enable_query to on lets the defining query during refresh follow the columnar routing rules. You can enable it at the session level or through a hint:

-- Enable at the session level
SET polar_csi.enable_query = on;
SET polar_csi.cost_threshold = 0;
REFRESH MATERIALIZED VIEW mv_name;

-- Enable through a hint
/*+Set(polar_csi.enable_query on) Set(polar_csi.cost_threshold 0) */REFRESH MATERIALIZED VIEW mv_name;

View the refresh execution plan

After you enable polar_enable_explain_refresh_matview, EXPLAIN can directly explain a REFRESH MATERIALIZED VIEW statement and show the execution plan the refresh will use. EXPLAIN ANALYZE is also supported for actual execution:

SET polar_enable_explain_refresh_matview = on;
EXPLAIN REFRESH MATERIALIZED VIEW mv_name;
EXPLAIN ANALYZE REFRESH MATERIALIZED VIEW mv_name;

Non-blocking refresh

Background

In PostgreSQL community, REFRESH MATERIALIZED VIEW (non-CONCURRENTLY) blocks all queries against the materialized view for the entire refresh: reads stay blocked as long as the refresh takes. For a large materialized view with a long refresh, that means a long window of unavailable reads.

REFRESH MATERIALIZED VIEW CONCURRENTLY does not block reads, but it requires a unique index on the view without a WHERE clause and applies changes through row-by-row compare and merge, which is noticeably slower when the change volume is large.

Usage

Enabling polar_enable_reduce_refresh_matview_lockmode allows non-CONCURRENTLY refresh to stop blocking read-only queries against the materialized view:

SET polar_enable_reduce_refresh_matview_lockmode = on;
REFRESH MATERIALIZED VIEW mv_name;

Once enabled, read-only queries against the materialized view are no longer blocked during a non-CONCURRENTLY refresh (including the entire process of executing the defining query, writing the new data, and rebuilding indexes); they read the old data as it was before the refresh. A short mutual exclusion still occurs during the finalization phase (to ensure correct replay on read-only nodes); if any read transactions are not yet committed at that point, the refresh waits for them to finish before committing.

Parallel index build on materialized view indexes likewise does not block reads, and you do not need to configure anything.

Comparison of the three refresh methods

Refresh method	Reads during refresh	Unique index requirement	Refresh cost
`REFRESH` (default)	Blocked throughout	None	Full rebuild, fast
`REFRESH` (with `polar_enable_reduce_refresh_matview_lockmode` enabled)	Not blocked (reads return old data)	None	Full rebuild, fast
`REFRESH CONCURRENTLY`	Not blocked (reads return old data)	Requires a unique index without a `WHERE` clause	Row-by-row compare and merge; slow when change volume is large

Notes

Multiple refreshes on the same materialized view (including CONCURRENTLY) are still mutually exclusive and run serially.
Long-running uncommitted read transactions delay the commit phase of the refresh. Avoid continuously accessing a materialized view that is being refreshed inside a long transaction.
The parameter has no effect on REFRESH MATERIALIZED VIEW CONCURRENTLY, which already does not block reads.

Scheduled refresh

An ordinary materialized view is not automatically refreshed when its base tables change. You must explicitly run REFRESH MATERIALIZED VIEW. With the pg_cron extension, you can configure refresh as a background scheduled job without any external scheduler.

pg_cron schedules jobs in the postgres database by default. Connect to the postgres database, create the extension, and configure the scheduled job:

-- Run in the postgres database
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Refresh the materialized view every 10 minutes
SELECT cron.schedule(
  'refresh-mv-name',
  '*/10 * * * *',
  $$REFRESH MATERIALIZED VIEW your_schema.your_mv;$$
);

Parameter reference

Parameter	Default value	Level	Description
`polar_enable_explain_refresh_matview`	`off`	Session-level	Allows `EXPLAIN [ANALYZE] REFRESH MATERIALIZED VIEW` to show the refresh execution plan.
`polar_enable_reduce_refresh_matview_lockmode`	`off`	Session-level	When enabled, non-`CONCURRENTLY` refresh no longer blocks read-only queries against the materialized view.
`polar_csi.enable_query`	`off`	Session-level	Allows queries (including the defining query used by materialized view refresh) to be routed to columnar execution.
`polar_csi.cost_threshold`	`50000`	Session-level	The cost threshold for routing to columnar. Setting it to `0` always attempts columnar.