Updated on 2025-09-22 GMT+08:00

Materialized Views

Overview

A materialized view is a special physical table. Unlike a view, a materialized view stores query results instead of query definitions. Compared with views, materialized views have a higher query speed. However, materialized views cannot be updated in real time and are not flexible for services that require real-time response. Materialized views are classified into fast-refresh materialized view and complete-refresh materialized view.

  • Complete-refresh materialized view: When a complete-refresh materialized view needs to be refreshed, the view is fully updated by re-executing the query and replacing the result in the current view. The complete-refresh materialized views are suitable for scenarios with low data update frequency and high query costs, as the high cost of refreshing such views makes them unsuitable for frequent updates.
  • Fast-refresh materialized view: allows incremental updates to a view, that is, only the data that has changed since the last refresh is updated. It applies to scenarios with high update frequency. Although the fast-refresh materialized view is cost-effective, it supports only a few scenarios (for example, aggregate functions are not supported). Currently, only simple filter query and base table UNION ALL are supported.

Examples

  • Complete-refresh materialized view
    -- Create a table and insert data (complete-refresh materialized view supports only the Astore base table).
    gaussdb=# CREATE TABLE tb_test(pid varchar(5),snum varchar(5)) WITH (storage_type = astore);
    CREATE TABLE
    gaussdb=# INSERT INTO tb_test VALUES ('00001','aaaaa');
    INSERT 0 1
    gaussdb=# INSERT INTO tb_test VALUES ('00001','aacba');
    INSERT 0 1
    gaussdb=# INSERT INTO tb_test VALUES ('00002','badbc');
    INSERT 0 1
    
    -- Create a complete-refresh materialized view.
    gaussdb=# CREATE MATERIALIZED VIEW mv_test AS SELECT pid,count(snum) cnt FROM tb_test GROUP BY pid;
    CREATE MATERIALIZED VIEW
    
    -- Query the complete-refresh materialized view.
    gaussdb=# SELECT * FROM mv_test;
      pid  | cnt 
    -------+-----
     00002 |   1
     00001 |   2
    (2 rows)
    
    -- Insert data, and then continue to query complete-refresh materialized view.
    gaussdb=# INSERT INTO tb_test VALUES ('00002','bdacc');
    INSERT 0 1
    -- The query result does not change. Manually refresh the materialized view.
    gaussdb=# SELECT * FROM mv_test;
      pid  | cnt 
    -------+-----
     00002 |   1
     00001 |   2
    (2 rows)
    
    
    -- Refresh the complete-refresh materialized view.
    gaussdb=# REFRESH MATERIALIZED VIEW mv_test;
    REFRESH MATERIALIZED VIEW
    
    -- Query the complete-refresh materialized view.
    gaussdb=# SELECT * FROM mv_test;
      pid  | cnt 
    -------+-----
     00002 |   2
     00001 |   2
    (2 rows)
    
    
    -- Drop the table and materialized view.
    gaussdb=# DROP MATERIALIZED VIEW mv_test;
    DROP MATERIALIZED VIEW
    gaussdb=# DROP TABLE tb_test;
    DROP TABLE
  • Fast-refresh materialized view
    -- Create a table.
    gaussdb=# CREATE TABLE tb_test(c1 int) WITH (storage_type = astore);
    CREATE TABLE
    
    -- An error is reported when the fast-refresh materialized view contains an aggregate function.
    gaussdb=# CREATE INCREMENTAL MATERIALIZED VIEW imv_test AS SELECT count(*) FROM tb_test;
    ERROR:  Feature not supported
    DETAIL:  aggregates on incremental materialized view creation
    
    -- Create a fast-refresh materialized view.
    gaussdb=# CREATE INCREMENTAL MATERIALIZED VIEW imv_test AS SELECT * FROM tb_test;
    CREATE MATERIALIZED VIEW
    
    -- Fastly refresh the fast-refresh materialized view.
    gaussdb=# REFRESH INCREMENTAL MATERIALIZED VIEW imv_test;
    REFRESH MATERIALIZED VIEW
    
    -- Completely refresh the fast-refresh materialized view.
    gaussdb=# REFRESH MATERIALIZED VIEW imv_test;
    REFRESH MATERIALIZED VIEW
    
    -- Drop the table and materialized view.
    gaussdb=# DROP MATERIALIZED VIEW imv_test;
    DROP MATERIALIZED VIEW
    gaussdb=# DROP TABLE tb_test;
    DROP TABLE