Real-time materialized views

Choose the right view type

Key concepts

How it works

Creating a real-time materialized view:

1. PolarDB rewrites the view query and computes hidden columns needed for incremental maintenance.

2. A trigger is created on each base table to capture DML changes.

3. When the required conditions are met, 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.

3. PolarDB calculates the delta based on the view definition and the captured data.

4. The delta is applied to the view, keeping it consistent with the base table.

Dropping a real-time materialized view:

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

2. The view is dropped.

Limitations

• The base table must be a regular table. Partitioned tables, inherited tables, and column store tables are not supported.

• Only INNER JOIN is supported. Other join types are not supported.

• Only IMMUTABLE functions are supported.

• Supported view definition elements: simple queries, projections, DISTINCT, and the following aggregate functions: MIN, MAX, SUM, AVG, and COUNT.

• The following query constructs are not supported: subqueries, [NOT] EXISTS, [NOT] IN, LIMIT, HAVING, DISTINCT ON, WITH (CTE), ORDER BY, window functions, GROUPING SETS, CUBE, ROLLUP, UNION, INTERSECT, and EXCEPT.

• When GROUP BY is used, all group-by columns must appear in the projection.

Performance considerations

Real-time materialized views improve read performance at the cost of write throughput. Every DML statement on a base table fires a trigger and updates the view, adding latency to write operations. The actual overhead depends on the view definition, write load, and the base table's structure and indexes.

To reduce write overhead:

• Limit the number of views per base table. Each additional real-time materialized view adds another trigger execution per DML.

• Use batch writes. Running COPY or INSERT INTO SELECT processes a large dataset in fewer trigger invocations than row-by-row inserts.

• Define primary keys on all base tables and include those primary key columns in the view's projection. This allows PolarDB to use unique indexes on the view for faster delta application.

Before deploying real-time materialized views to production, test write performance in a staging environment to confirm the overhead is acceptable for your workload.

Prerequisites

Check your cluster's minor engine version before using real-time materialized views:

SHOW polar_version;

• V1.1.27 (released September 2022) or later: Real-time materialized views are available by default. No additional setup is needed.

• Earlier than V1.1.27: Update the minor engine version to V1.1.27 or later, then install the polar_ivm extension:

  CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;

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 are updated automatically on every base table DML — no manual refresh is needed.

For views created with BUILD DEFERRED, run the following statement to populate the view for the first time. After this initial refresh, subsequent base table changes are synchronized automatically.

REFRESH MATERIALIZED VIEW table_name

Drop a real-time materialized view

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

End-to-end example

This example creates a real-time materialized view, runs INSERT, DELETE, and UPDATE operations on the base table, and verifies that the view stays synchronized automatically.

Step 1: Install the extension (if needed)

CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;

Step 2: Create a base table and load sample data

CREATE TABLE t (a INT, b VARCHAR);

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

Step 3: Create the real-time materialized view

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

Step 4: Query the view and verify synchronization

Initial state — matches the base table:

SELECT * FROM mv ORDER BY b;

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

After inserting a new row:

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

SELECT * FROM mv ORDER BY b;

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

After deleting a row:

DELETE FROM t WHERE a = 2;

SELECT * FROM mv ORDER BY b;

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

After updating all rows:

UPDATE t SET a = a + 1;

SELECT * FROM mv ORDER BY b;

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

The view reflects each change immediately — no manual refresh needed.

Step 5: Drop the view

DROP MATERIALIZED VIEW mv;