PolarDB:Real-time materialized views

Terms

• Base table: a common table used in the definition of a materialized view.
• Delta: a collection of data that is added or deleted when the data in the base table changes, compared with the data in the materialized view.
• Refresh: maintains a materialized view so that the data in the materialized view is consistent with the data obtained by querying the current base table based on the view definition.
• Apply Delta: inserts or deletes calculated incremental data from a real-time materialized view to maintain data consistency between the real-time materialized view and the base table.

Limits

Performance degradation

• Real-time materialized views greatly improve the query performance but have a significant impact on the write performance of the base tables. We recommend that you use real-time materialized views only in scenarios with much higher reads than writes.
• The impact of real-time materialized views on the write performance of the base tables depends on factors such as the view definitions and the write loads, structures, and indexes of the base tables. Before you create real-time materialized views in the production environment, we recommend that you first test the impact of real-time materialized views on the write performance of the base tables in the test environment. You can use real-time materialized views in the production environment if the write performance meets the requirements.
• The following methods help reduce the costs of real-time materialized views:
  • Create a few real-time materialized views on the same base table.
  • Batch writes data to the base table. For example, you can execute the COPY or INSERT INTO SELECT statement to batch import data.
  • Create primary keys for all base tables, and include the primary keys for all base tables in the projected column for the definitions of real-time materialized views.

How it works

• Create a real-time materialized view
  • Rewrite the query of the materialized view and calculate the hidden columns required to maintain the real-time materialized view.
  • Create a trigger for the base table to refresh the real-time materialized view.
  • Create unique indexes for the real-time materialized view when specified conditions are met to accelerate delta refresh.
• Refresh a real-time materialized view
  • Data changes in the base table activate the trigger.
  • Obtain incremental data from the base table by using the trigger.
  • Calculate delta of the real-time materialized view based on the definitions and the incremental data of the current base table.
  • Apply the calculated incremental data to the real-time materialized view to implement delta refresh.
• Delete a real-time materialized view
  • Delete the delta refresh trigger from the base table of the real-time materialized view.
  • Delete the real-time materialized view.

Usage

• Prerequisites
  On clusters that use the minor version V1.1.27 released in September 2022, you can create real-time materialized views. For existing clusters, you must upgrade their minor version to V1.1.27 and create the polar_ivm plug-in in the clusters.

  CREATE EXTENSION polar_ivm WITH SCHEMA pg_catalog;

  Note You can execute the following statement to view the revision version of a PolarDB for Oracle cluster:

  show polar_version;

• Create a real-time materialized view

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

  Parameters

  Parameter	Description
  table_name	The name of the real-time materialized view, which can be schema-qualified.
  column_name	The name of a column in the real-time materialized view. If no column name is provided, it is obtained from the queried column names.
  BUILD DEFERRED	Only the structure of the real-time materialized view is created, but no data is populated. No real-time refresh is performed on the real-time materialized view. When you query the real-time materialized view, no error is reported but no data is found before the REFRESH MATERIALIZED VIEW statement is executed on the real-time materialized view.
  BUILD IMMEDIATE	The default value. A complete real-time materialized view is immediately created.
  query	The definition of the real-time materialized view, which is a SELECT, TABLE, or VALUES. The query runs in a secure operation.

• Refresh a real-time materialized view

  REFRESH MATERIALIZED VIEW table_name

  Note Note:

  • table_name: the name of the real-time materialized view.
  • For a real-time materialized view created with the BUILD IMMEDIATE and WITH DATA parameters, no manual refresh is required to ensure data consistency between the view and base table.
  • For a real-time materialized view created with the BUILD DEFERRED and WITH NO DATA parameters, manual refresh generates data based on the definition and enables real-time refresh for subsequent modifications of the base table.
• Delete a real-time materialized view

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

  Parameters

  Parameter	Description
  IF EXISTS	If the real-time materialized view does not exist, a prompt, instead of an error, is returned.
  table_name	The name of the real-time materialized view, which can be schema-qualified.
  CASCADE	The objects that depend on the real-time materialized view (such as other materialized views or regular views) are automatically deleted, and then all objects that depend on automatically deleted objects are deleted.
  RESTRICT	If any object depends on the real-time materialized view, the real-time materialized view is not deleted. This parameter is selected by default.

Performance test

1. Create the dependency plug-in for the real-time materialized view.

   CREATE EXTENSION IF NOT EXISTS polar_ivm WITH SCHEMA pg_catalog ;

2. Create the base table and populate it with data.

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

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;

4. Execute DML statement on the base table.
   • Query data of the real-time materialized view.

     SELECT * FROM mv ORDER BY b;

     Sample result:

      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 of the real-time materialized view is consistent with the data of the base table.
   • Insert new data into the base table and query data of the real-time materialized view.

     INSERT INTO t VALUES(6,'f');
     
     SELECT * FROM mv ORDER BY b;

     Sample result:

      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 of the real-time materialized view is consistent with the data of the base table.
   • Delete data of the base table and query data of the real-time materialized view.

     DELETE FROM t WHERE a = 2;
     
     SELECT * FROM mv ORDER BY b;

     Sample result:

      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 of the real-time materialized view is consistent with the data of the base table.
   • Update data of the base table and query data of the real-time materialized view.

     UPDATE t SET a = a + 1;
     
     SELECT * FROM mv ORDER BY b;

     Sample result:

      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 of the real-time materialized view is consistent with the data of the base table.
5. Delete the real-time materialized view.

   DROP MATERIALIZED VIEW mv;