Parallel refresh

How it works

1. PolarDB selects a remote node (a column-store index read-only node or a row-store read-only node) based on the offload policy and establishes a connection.

2. The primary (RW) node sends the materialized view's definition query to the remote node for execution.

3. The remote node streams the result set back. Compressed data transmission is supported.

4. The primary (RW) node creates multiple worker threads. Each worker thread fetches rows in parallel and writes them to different partitions of the container table.

Features

• Query computation runs on the remote node and does not consume CPU resources on the primary (RW) node.

• Write operations run locally on the primary (RW) node to ensure transactional consistency.

• In wide-table scenarios, parallel efficiency is not affected by row length.

• Supports multi-primary cluster (Limitless).

Usage

After configuring the parameters, run the following command to refresh the materialized view:

-- Refresh the materialized view
REFRESH MATERIALIZED VIEW <schema_name>.<mview_name>;

How it works (MPP offloading to a remote column-store index read-only node when the offload policy is FORCE_IMCI)

1. The system schedules PQ Workers to a remote column-store index read-only node to perform a SELECT scan.

2. The PQ Workers send the scan results back to the primary (RW) node over the internal network, which supports compression.

3. Multiple PQ Workers scan the source table data in parallel on the primary (RW) node.

4. The PQ Workers write the scan results in parallel to the partitions of the container table.

5. Finally, the primary (RW) node completes the data write locally.

Features

• End-to-end parallelism: A single parallel execution plan handles both scans and writes.

• Near-linear scaling for narrow tables.

• Avoids the single-port network transfer limitation of ETL.

• Currently not supported in Limitless clusters. The operation automatically falls back to serial execution.

Network compression (MPP offload scenario)

Procedure

-- Perform the refresh
REFRESH MATERIALIZED VIEW <schema_name>.<mview_name>;

-- 1. On the primary (RW) node, disable its ability to act as a PQ Worker resource.
-- In the console, set pq_mpp_allow_imci_node = OFF;

-- 2. On the primary (RW) node, allow scheduling to an IMCI node.
-- In the console, set pq_mpp_allow_dispatch_to_imci_node = ON;

-- 3. Connect to the IMCI node that should handle the offloaded query, and enable the scheduling entry point only on that node.
-- In the console, set pq_mpp_allow_dispatch_to_imci_node = ON;

-- 4. Return to the primary (RW) node and refresh with the IMCI offload policy.
SET SESSION materialized_view_complete_refresh_offload_policy = FORCE_IMCI;
REFRESH MATERIALIZED VIEW <schema_name>.<mview_name>;

Policy determines node type

Decision logic for the COST policy

When materialized_view_complete_refresh_offload_policy = COST, the system determines the target node by comparing the query's optimizer cost to a threshold:

• If the query cost ≥ cost_threshold_for_imci: Offload to an IMCI read-only node. IMCI is suitable for high-cost scan and aggregation queries.

• If the query cost < cost_threshold_for_imci: Offload to a row-store read-only node. Row-store read-only nodes are sufficient for lightweight queries.

-- Set the cost threshold (default is 0, which means all queries tend to be offloaded to IMCI).
SET SESSION cost_threshold_for_imci = 100000;

-- Use the COST policy.
SET SESSION materialized_view_complete_refresh_offload_policy = COST;

Multi-node load balancing

If multiple nodes of the same type are available, the system uses a Round-Robin approach to select a node:

• Each refresh task selects the next node based on a global counter.

• If the connection to the selected node fails, the system automatically tries the next node in the list until all nodes have been attempted.

• The system distributes refresh tasks for different materialized views evenly across the nodes.

To offload ePQ + MPP tasks to an IMCI node, use one of the following two methods:

1. Enable pq_mpp_allow_imci_node to support offloading to all IMCI read-only nodes.

2. First, set pq_mpp_allow_imci_node = OFF, then use pq_mpp_allow_dispatch_to_imci_node to narrow down the candidate IMCI read-only nodes:

   • On the master (RW) node, set pq_mpp_allow_dispatch_to_imci_node = ON to allow the master (RW) node to accept the dispatch entry-point status from remote IMCI read-only nodes.

   • On each IMCI read-only node that you want to handle offloaded queries, set pq_mpp_allow_dispatch_to_imci_node = ON.

   • On each IMCI read-only node that you do not want to handle offloaded queries, keep pq_mpp_allow_dispatch_to_imci_node = OFF.

Recommended policies

In a production environment, use FORCE_IMCI or FORCE_TPRO. Avoid PREFER_* policies.

Reason: If the target node is unavailable, a PREFER_* policy silently falls back to local execution on the master (RW) node. This fallback causes the INSERT SELECT statement to perform a full scan and write on the master (RW) node. This creates a large transaction and can impact the performance of online workloads on the master (RW) node.

Configuration examples

-- Recommended: Force the use of an IMCI node. If unavailable, the operation fails instead of falling back.
SET SESSION materialized_view_complete_refresh_offload_policy = FORCE_IMCI;

-- Use only row-store read-only nodes. If unavailable, the operation fails.
SET SESSION materialized_view_complete_refresh_offload_policy = FORCE_TPRO;

-- Automatically choose between IMCI and TPRO based on query cost.
SET SESSION materialized_view_complete_refresh_offload_policy = COST;
SET SESSION cost_threshold_for_imci = 50000;

Basic rules

• GLOBAL parameters: are set only in the console and apply to all sessions and automatic refresh threads.

• SESSION parameters: You can set them for the current session by using SET SESSION. You can also modify their default values in the console. Automatic refresh threads use the GLOBAL default values.

Parameter scopes for ETL parallel write mode

Parameter scopes for ePQ parallel write mode

Notes

• If you use only manual refreshes, you can adjust SESSION parameters for each session.

• If you rely on automatic refreshes, you must set appropriate default values for all SESSION parameters in the console.

• GLOBAL parameters must be set in the console, regardless of the refresh method. Changes take effect immediately for all subsequent tasks.

• SET SESSION settings do not persist after a cluster restart. In contrast, GLOBAL parameters modified in the console are persistent.

Core parameters

Compute offloading parameters

Network compression parameters

How compression works:

The materialized_view_etl_compression_level parameter takes effect only when materialized_view_etl_compression_algorithm = ZSTD. If set to 0, the ZSTD default level of 3 is used. If set to a value from 1 to 22, the specified level is used. A higher level provides a better compression ratio but increases CPU overhead.

Offload policy (materialized_view_complete_refresh_offload_policy parameter)