When you create or refresh a materialized view — or run CREATE TABLE AS or SELECT INTO — on large datasets, each row is written individually, generating a separate write-ahead log (WAL) record and a page lock per write. This makes DDL execution slow on tables with millions of rows.
PolarDB for PostgreSQL's batch write optimization buffers multiple rows in memory and flushes them together. This reduces WAL overhead and page-locking frequency, significantly shortening DDL execution time for large datasets. The feature is enabled by default.
Supported versions
Batch writes are supported on PolarDB for PostgreSQL clusters running:
| Engine version | Supported revision versions |
|---|---|
| PostgreSQL 14 | 2.0.14.9.15.0 to 2.0.14.13.27.0 |
| PostgreSQL 11 | 2.0.11.15.37.0 or later |
To check your cluster's revision version, run SHOW polardb_version; or view it in the PolarDB console.
If your version does not meet these requirements:
PostgreSQL 11: upgrade the revision version.
PostgreSQL 14 (revision version 2.0.14.13.28.0 or later): use the bulk import infrastructure feature instead.
How it works
The following statements share the same execution path in the PostgreSQL kernel and all benefit from batch writes:
| Statement | What it does |
|---|---|
CREATE MATERIALIZED VIEW | Creates a materialized view and populates it with query results |
REFRESH MATERIALIZED VIEW | Repopulates a materialized view with current data |
CREATE TABLE AS | Creates a table with the same structure and data as a query's output |
SELECT INTO | Creates a table and populates it with query results; data is not returned to the client |
Each statement runs two steps:
Data scan: Run the query to scan rows that meet the conditions.
Data write: Write the scanned rows to the new materialized view or table.
Without batch writes, step 2 writes one row at a time — one WAL record and one page lock per row. With batch writes enabled, PolarDB for PostgreSQL accumulates rows in memory and flushes them in a single operation, reducing WAL records written and page locks acquired.
Enable batch writes
Batch writes are controlled by the polar_enable_create_table_as_bulk_insert parameter. The default value is ON.
To enable batch writes for the current session:
SET polar_enable_create_table_as_bulk_insert TO ON;SET applies the change to the current session only. To apply it at the database or cluster level permanently, configure the parameter through the PolarDB console or contact your DBA.Example: create and refresh a materialized view with batch writes
The following example creates a materialized view over a large sales table, queries it, and then refreshes it — all with batch writes active.
-- Enable batch writes for this session (already ON by default)
SET polar_enable_create_table_as_bulk_insert TO ON;
-- Create a materialized view summarizing total sales per region
CREATE MATERIALIZED VIEW regional_sales_summary AS
SELECT
region,
SUM(amount) AS total_sales,
COUNT(*) AS order_count
FROM orders
GROUP BY region;
-- Query the materialized view directly (no query re-execution)
SELECT * FROM regional_sales_summary ORDER BY total_sales DESC;
-- When the underlying data changes, refresh the view
REFRESH MATERIALIZED VIEW regional_sales_summary;For CREATE TABLE AS, the pattern is the same:
SET polar_enable_create_table_as_bulk_insert TO ON;
-- Create a snapshot table from a query result
CREATE TABLE high_value_orders AS
SELECT * FROM orders WHERE amount > 10000;