Batch writes of statements for creating or refreshing materialized views - PolarDB

PolarDB for PostgreSQL allows you to batch write statements to create or refresh materialized views.

Prerequisites

The feature is supported on the PolarDB for PostgreSQL clusters that run the following engine:

PostgreSQL 14 (revision version 14.9.15.0 or later)

Note

You can run the following statement to view the minor version of PolarDB for PostgreSQL clusters:

PostgreSQL 14

select version();

Background information

Materialized views are database objects that contain query results. Unlike normal views, materialized views store both view definitions and data copies when they are created. For more information, see CREATE MATERIALIZED VIEW. If the data in the materialized view is inconsistent with that in the view definition, you can refresh the materialized view to keep the data consistent. For more information, see REFRESH MATERIALIZED VIEW. Materialized views essentially pre-compute queries in view definitions so that they can be reused during queries.

The CREATE TABLE AS statement defines a new table from the results of a query. The table has the same structure as the output columns of the query.

The SELECT INTO statement creates a new table and populates it with data computed by a query. The data is not returned to the client. The new table has exactly the same structure as the output columns of the query.

How it works

The PostgreSQL kernel uses the same set of code logics to handle the statements for creating a materialized view (CREATE MATERIALIZED VIEW) and refreshing a materialized view (REFRESH MATERIALIZED VIEW) and the CREATE TABLE AS and SELECT INTO statements, because these statements perform similar steps at the database level. The following steps are usually included:

Data scans: Execute the query specified in the view definition or in the CREATE TABLE AS or SELECT INTO statement to scan the data that meets the conditions.
Data writes: Write the data scanned in the preceding step to a new materialized view or a new table.

To optimize the data write steps, PolarDB for PostgreSQL introduces batch writes. When a large amount of data is to be written, a certain number of tuples that are accumulated in the memory are written to the table or materialized view at a time. This reduces the overheads for recording WAL logs and the frequency of locking pages, and significantly shortens the DDL execution time.

Usage

Set the polar_enable_create_table_as_bulk_insert parameter to ON to enable this feature. The default value is ON.

SET polar_enable_create_table_as_bulk_insert TO ON;