PolarDB:Real-time materialized views

Supported versions

To check your current revision version, run SHOW polardb_version; or view it in the PolarDB console. To upgrade, see Version management.

To install the polar_ivm extension:

CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;

Key concepts

How it works

Creating a real-time materialized view:

1. PolarDB rewrites the view query and calculates the hidden columns needed to maintain incremental state.

2. A trigger is created on the base table to fire on each DML statement.

3. Where applicable, unique indexes are created on the view to accelerate delta application.

Refreshing a real-time materialized view:

1. A DML statement on the base table fires the trigger.

2. The trigger captures the incremental data (delta) from the base table.

3. PolarDB calculates the updated view rows based on the view definition and the delta.

4. The calculated delta is applied to the view, keeping it in sync with the base table.

Dropping a real-time materialized view:

1. The delta refresh trigger is removed from the base table.

2. The materialized view is dropped.

Limitations

Real-time materialized views support a subset of SQL features:

• Base table type: The base table must be a standard table. Partitioned tables, inherited tables, and columnar tables are not supported.

• Join types: Only INNER JOIN is supported.

• Functions: Only immutable functions are supported.

• View definition complexity: Supported constructs include simple queries, projections, DISTINCT, and aggregate functions listed below. The following are not supported: subqueries, [NOT] EXISTS, [NOT] IN, LIMIT, HAVING, DISTINCT ON, CTEs (WITH), ORDER BY, window functions, GROUPING SETS, CUBE, ROLLUP, UNION, INTERSECT, and EXCEPT.

• `GROUP BY` and projection: When a GROUP BY clause is used, all grouped columns must appear in the projection (SELECT list).

• Aggregate functions: Only MIN, MAX, SUM, AVG, and COUNT are supported.

Create a real-time materialized view

CREATE MATERIALIZED VIEW table_name[ (column_name [, ...] ) ]
[ BUILD DEFERRED | IMMEDIATE ]
REFRESH FAST
ON COMMIT
AS query

Refresh a real-time materialized view

Views created with BUILD IMMEDIATE refresh automatically — no action required.

For views created with BUILD DEFERRED, run the following statement once to populate the view:

REFRESH MATERIALIZED VIEW table_name

After the first refresh, PolarDB applies all subsequent base table changes to the view in real time.

Drop a real-time materialized view

DROP MATERIALIZED VIEW [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ]

Performance considerations

Real-time materialized views improve read performance by maintaining precomputed results. The cost is write overhead: every DML statement on a base table triggers an incremental view update. In write-heavy workloads, this overhead can be significant.

Use real-time materialized views when reads outnumber writes. Before deploying to production, benchmark write performance in a test environment with a workload representative of your production traffic.

To reduce write overhead:

• Limit view count per base table. Each real-time materialized view on a base table adds a separate trigger and update path. Every additional view multiplies the per-statement cost, so keep the number of views per table small.

• Use batch writes. Batch write data to the base table — for example, using the COPY or INSERT INTO SELECT statement to batch import data — to reduce the frequency of incremental view updates.

• Add primary keys and include them in the view projection. Create primary keys for all base tables, and include those keys in the projected columns of the view definition to accelerate delta application.

Examples

The following example shows the complete lifecycle of a real-time materialized view: installation, creation, DML operations, and cleanup.

1. Install the `polar_ivm` extension.

CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;

2. Create a base table and insert initial data.

CREATE TABLE t (a INT, b VARCHAR);

INSERT INTO t VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e');

3. Create a real-time materialized view.

The view groups rows by b and computes MAX(a) and MIN(a) per group.

CREATE MATERIALIZED VIEW mv
REFRESH FAST
ON COMMIT
AS
SELECT max(a), min(a), b FROM t GROUP BY b;

4. Query the view and verify initial data.

SELECT * FROM mv ORDER BY b;

Expected output:

 max | min | b
-----+-----+---
   1 |   1 | a
   2 |   2 | b
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
(5 rows)

5. Insert a new row and verify the view updates automatically.

INSERT INTO t VALUES (6, 'f');

SELECT * FROM mv ORDER BY b;

Expected output:

 max | min | b
-----+-----+---
   1 |   1 | a
   2 |   2 | b
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
   6 |   6 | f
(6 rows)

6. Delete a row and verify the view reflects the change.

DELETE FROM t WHERE a = 2;

SELECT * FROM mv ORDER BY b;

Expected output:

 max | min | b
-----+-----+---
   1 |   1 | a
   3 |   3 | c
   4 |   4 | d
   5 |   5 | e
   6 |   6 | f
(5 rows)

7. Update rows and verify the view reflects the change.

UPDATE t SET a = a + 1;

SELECT * FROM mv ORDER BY b;

Expected output:

 max | min | b
-----+-----+---
   2 |   2 | a
   4 |   4 | c
   5 |   5 | d
   6 |   6 | e
   7 |   7 | f
(5 rows)

8. Drop the view when no longer needed.

DROP MATERIALIZED VIEW mv;