All Products
Search

This Product

    PolarDB:Real-time materialized views

    Document Center

    PolarDB:Real-time materialized views

    Last Updated:Jan 07, 2026

    This topic describes the real-time materialized view feature of PolarDB for PostgreSQL.

    Scope

    PolarDB for PostgreSQL supports the following versions:

    • PostgreSQL 14 (minor engine version 2.0.14.8.11.0 or later)

    • PostgreSQL 11 (minor engine version 2.0.11.9.27.0 or later)

    Note

    You can view the minor engine version in the console or run the SHOW polardb_version; statement. If the minor engine version of your cluster does not meet the requirements, you can upgrade the minor engine version.

    Background information

    Unlike standard views, materialized views store query results directly. In complex query scenarios, this can significantly improve query efficiency. However, the data in a materialized view is not automatically updated when the data in its base tables changes. This means that queries on the materialized view may not always return the most up-to-date data.

    To address this issue, PolarDB introduces real-time materialized views. Compared with standard materialized views, real-time materialized views offer the following benefits:

    • Real-time materialized views support statement-level updates. When you perform a DML operation, such as an insert, delete, or update, on a base table, the data in the real-time materialized view is automatically updated after the statement is complete. This ensures data consistency between the real-time materialized view and the base table.

    • Real-time materialized views leverage incremental data from the base tables. When you refresh a real-time materialized view, a full query of the view is not required. This provides better performance than the frequent full refreshes required by standard materialized views.

    • Real-time materialized views can significantly improve query performance while ensuring that query results are consistent with the data in the base tables.

    Glossary

    • Base tables: The standard tables used in the definition of a materialized view.

    • Delta: A collection of incremental changes (additions or deletions) that occur in a base table.

    • Refresh: A maintenance operation that updates a materialized view to ensure its data is consistent with the current data in its base tables.

    • Apply Delta: The process of applying calculated incremental data (the delta) to a real-time materialized view to maintain data consistency with its base table.

    Limits

    The following limitations apply to the view definitions of real-time materialized views:

    • The base table must be a standard table. It cannot be a partitioned table or an inherited table.

    • Only IMMUTABLE functions are supported.

    • Only view definitions that contain simple queries, projections, DISTINCT, and some aggregate functions are supported. View definitions that contain complex queries such as subqueries, [NOT] EXISTS, [NOT] IN, LIMIT, HAVING, DISTINCT ON, WITH (CTE), ORDER BY, window functions, GROUPING SETS, CUBE, ROLLUP, UNION, INTERSECT, or EXCEPT are not supported.

    • When you use a GROUP BY clause, the columns specified in the GROUP BY clause must also be included in the projection.

    • Only the following built-in aggregate functions are supported: MIN, MAX, SUM, AVG, and COUNT.

    • View definitions that contain INNER JOIN are supported.

    • View definitions that contain OUTER JOIN (including LEFT JOIN, RIGHT JOIN, and FULL JOIN) are supported. However, the following limits apply:

      Note

      This feature is supported only for minor engine versions 2.0.14.18.37.0 and later.

      • OUTER JOIN and DISTINCT cannot be used together.

      • OUTER JOIN and GROUP BY cannot be used together.

      • OUTER JOIN and aggregate functions cannot be used together.

      • The FROM clause cannot contain both an OUTER JOIN and a comma-separated list of tables.

      • An OUTER JOIN query only supports simple equi-join conditions. It does not support conditions with AND/OR operators or conditions where both sides of the equi-join refer to columns from the same table.

      • If a query contains an OUTER JOIN, the projection must include all columns used in the JOIN condition.

      After you create a real-time materialized view on a base table, the following DDL restrictions apply to the base table:

      • You can run the DROP TABLE command on the base table only if you specify the CASCADE option.

      • When you run the ALTER TABLE command on the base table, you cannot delete or modify columns that are referenced by the materialized view.

    Performance impact

    • Real-time materialized views significantly improve query performance but have a significant impact on the write performance of the base tables. Use real-time materialized views in scenarios where read operations are more frequent than write operations.

    • The impact of real-time materialized views on the write performance of the base tables depends on factors such as the view definitions, write payloads, structures, and indexes of the base tables. Before you create real-time materialized views in a production environment, test the write performance of the base tables in a staging environment. Use real-time materialized views in the production environment only if the write performance meets your requirements.

    • The following methods can help reduce the maintenance cost of real-time materialized views:

      • Avoid creating too many real-time materialized views on the same base table.

      • Write data to the base table in batches. For example, use the COPY or INSERT INTO SELECT command to import data in batches.

      • Each base table has a primary key, which must be included in the projection columns of the real-time materialized view.

    How it works

    • Create a real-time materialized view

      • The system rewrites the materialized view query to calculate the hidden columns required for maintenance.

      • A trigger is created on the base table to enable incremental refresh.

      • Unique indexes are created on the real-time materialized view when specific conditions are met to accelerate incremental refreshes.

    • Incrementally refreshing a real-time materialized view

      • Changes to the data in the base table activate the corresponding trigger.

      • The trigger retrieves incremental data from the base table.

      • A delta for the real-time materialized view is calculated based on its definition and the incremental data from the base table.

      • The calculated delta is applied to the real-time materialized view to complete the incremental refresh.

    • Delete a real-time materialized view

      • Delete the incremental refresh trigger for the real-time materialized view from the base table.

      • Delete the real-time materialized view itself.

    Usage guide

    • Preparations

      In the database where you want to use real-time materialized views, create 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 [, ...] ) ]
REFRESH FAST
ON COMMIT
AS query
[ WITH [ NO ] DATA ]

      Parameter description

      Parameter

      Description

      table_name

      The name of the real-time materialized view to create. The name can be schema-qualified.

      column_name

      The name of a column in the new materialized view. If you do not provide column names, the names are taken from the output column names of the query.

      WITH DATA

      The default option. Immediately creates the complete real-time materialized view.

      WITH NO DATA

      Creates only the structure of the real-time materialized view. The view contains no data and is not updated in real time.

      Querying this view returns an error until you run the REFRESH MATERIALIZED VIEW command on the view.

      query

      The view definition for the real-time materialized view. It can be a SELECT, TABLE, or VALUES command. This query runs in a security-restricted operation.

    • Incrementally refresh a real-time materialized view

      REFRESH MATERIALIZED VIEW table_name
      Note

      Note:

      • table_name: The name of the real-time materialized view to refresh. The name can be schema-qualified.

      • A manual refresh is not required for a real-time materialized view created with WITH DATA because data consistency is maintained automatically. Manual refresh operations on such views are skipped.

      • When you refresh a real-time materialized view created with WITH NO DATA, the view is populated with data based on its definition, and real-time refresh is enabled for subsequent changes to the base table.

    • Delete a real-time materialized view

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

      Parameter description:

      Parameter

      Description

      IF EXISTS

      Does not throw an error if the real-time materialized view does not exist. A notice is issued instead.

      table_name

      The name of the real-time materialized view to remove. The name can be schema-qualified.

      CASCADE

      Automatically drops objects that depend on the real-time materialized view, such as other materialized views or regular views, and then all objects that depend on those objects.

      RESTRICT

      Denies the deletion if any objects depend on the real-time materialized view. This is the default.

    Examples

    Real-time materialized view with aggregate functions

    1. Create the required extension for real-time materialized views.

      CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog ;

    2. Create a base table and import 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.

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

    4. Perform DML operations on the base table.

      • Query the data in the real-time materialized view.

        SELECT * FROM mv ORDER BY b;

        The following result is returned:

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

        The result shows that the data in the real-time materialized view is consistent with the data in the base table.

      • Insert new data into the base table and then query the data in the real-time materialized view.

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

SELECT * FROM mv ORDER BY b;

        The following result is returned:

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

        The result shows that the data in the real-time materialized view is consistent with the data in the base table.

      • Delete data from the base table and then query the data in the real-time materialized view.

        DELETE FROM t WHERE a = 2;

SELECT * FROM mv ORDER BY b;

        The following result is returned:

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

        The result shows that the data in the real-time materialized view is consistent with the data in the base table.

      • Update data in the base table and then query the data in the real-time materialized view.

        UPDATE t SET a = a + 1;

SELECT * FROM mv ORDER BY b;

        The following result is returned:

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

        The result shows that the data in the real-time materialized view is consistent with the data in the base table.

    5. Delete the real-time materialized view.

      DROP MATERIALIZED VIEW mv;

    Real-time materialized view with an OUTER JOIN

    Note

    This feature is supported only for minor engine versions 2.0.14.18.37.0 and later.

    1. Create the required extension for real-time materialized views.

      CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog;

    2. Create base tables and import initial data.

      CREATE TABLE t1(a INT, b VARCHAR);
INSERT INTO t1 VALUES
  (1,'a'),
  (2,'b'),
  (3,'c'),
  (4,'d'),
  (5,'e');

CREATE TABLE t2(a INT, c VARCHAR);
INSERT INTO t2 VALUES
  (1,'e'),
  (2,'f');

    3. Create a real-time materialized view.

      CREATE MATERIALIZED VIEW mv
REFRESH FAST
ON COMMIT AS
SELECT t1.a as t1a, t1.b,
       t2.a as t2a, t2.c
FROM t1 LEFT JOIN t2
ON t1.a = t2.a;

    4. Perform DML operations on the base tables.

      • Query the data in the real-time materialized view.

        SELECT * FROM mv ORDER BY b;

        The following result is returned:

         t1a | b | t2a | c
-----+---+-----+---
   1 | a |   1 | e
   2 | b |   2 | f
   3 | c |     |
   4 | d |     |
   5 | e |     |

        The result shows that the data in the real-time materialized view is consistent with the data in the base tables.

      • Insert new data into a base table and then query the data in the real-time materialized view.

        INSERT INTO t2 VALUES(3,'g');

SELECT * FROM mv ORDER BY b;

        The following result is returned:

         t1a | b | t2a | c 
-----+---+-----+---
   1 | a |   1 | e
   2 | b |   2 | f
   3 | c |   3 | g
   4 | d |     | 
   5 | e |     |

        The result shows that the data in the real-time materialized view is consistent with the data in the base tables.

      • Delete data from a base table and then query the data in the real-time materialized view.

        DELETE FROM t1 WHERE a in (1, 4);

SELECT * FROM mv ORDER BY b;

        The following result is returned:

         t1a | b | t2a | c
-----+---+-----+---
   2 | b |   2 | f
   3 | c |   3 | g
   5 | e |     |

        The result shows that the data in the real-time materialized view is consistent with the data in the base tables.

      • Update data in a base table and then query the data in the real-time materialized view.

        UPDATE t2 SET a = a + 1;

SELECT * FROM mv ORDER BY b;

        The following result is returned:

         t1a | b | t2a | c
-----+---+-----+---
   2 | b |   2 | e
   3 | c |   3 | f
   5 | e |     |

        The result shows that the data in the real-time materialized view is consistent with the data in the base tables.

    5. Delete the real-time materialized view.

      DROP MATERIALIZED VIEW mv;