PolarDB for PostgreSQL clusters support bulk writes for creating or refreshing materialized views.
Prerequisites
The supported versions of PolarDB for PostgreSQL are as follows:
-
PostgreSQL 14 (revision versions 2.0.14.9.15.0 to 2.0.14.13.27.0)
-
PostgreSQL 11 (revision version 2.0.11.15.37.0 or later)
-
You can view the revision version in the console, or check it by running the
SHOW polardb_version;statement. -
If the revision version requirement is not met, use one of the following solutions depending on the database engine:
-
PostgreSQL 11: Upgrade the revision version.
-
PostgreSQL 14 or later: Starting with PostgreSQL 14 revision version 2.0.14.13.28.0 and later (including higher database engine versions), you can use the more efficient Bulk import infrastructure feature to implement the current functionality.
-
Background
A materialized view is a database object that contains the results of a query. Unlike a regular view, a materialized view stores not only the view definition but also a copy of the data at the time when the materialized view was created. When the data in the materialized view is inconsistent with the data in the view definition, you can perform a materialized view refresh to keep the data in the materialized view consistent with the view definition. A materialized view is essentially a precomputation of the query defined in the view definition, so that the results can be reused at query time.
The CREATE TABLE AS syntax is used to build the data corresponding to a query into a new table whose structure is identical to the output columns of the query.
The SELECT INTO syntax is used to create a new table and write the data corresponding to a query into the table, instead of returning the query results to the client. Its table structure is identical to the output columns of the query.
How it works
Creating a materialized view (CREATE MATERIALIZED VIEW), refreshing a materialized view (REFRESH MATERIALIZED VIEW), and the CREATE TABLE AS/SELECT INTO syntax share very similar work steps at the database level, so the PostgreSQL kernel uses the same code logic to handle these statements. The kernel execution process mainly includes the following two steps:
-
Data scan: Run the query defined in the view definition or the
CREATE TABLE AS/SELECT INTOsyntax, and scan the rows that meet the query conditions. -
Data write: Write the rows scanned in the previous step to a new materialized view or table.
PolarDB for PostgreSQL introduces a bulk write optimization for the data write step. When the amount of data to be written is large, instead of writing each scanned tuple to the table or materialized view immediately, tuples are accumulated in memory to a certain quantity and then written to the table or materialized view in a single batch. This reduces the overhead of writing WAL logs and querying the free space map, lowers the frequency of page locks, and significantly shortens the execution time of the DDL statements described above.
Usage notes
To enable this feature, set the polar_enable_create_table_as_bulk_insert parameter to ON. The default value is ON.
SET polar_enable_create_table_as_bulk_insert TO ON;