AnalyticDB:Real-time materialized views

How it works

Real-time materialized views support two refresh modes. Choose based on your consistency and throughput requirements.

To change the default mode, submit a ticketsubmit a ticket.

Synchronous mode

In synchronous mode, the MV update runs as part of the same transaction as the base table write. When an SQL statement, such as INSERT, COPY, UPDATE, or DELETE, is executed on a base table, the database updates the base table first, then the MV.

• If the base table write fails, the MV is not changed.

• If the MV update fails, the base table write is also rolled back, and the write statement returns an error.

When you use explicit transactions (BEGIN/COMMIT):

• Under the READ COMMITTED isolation level, MV updates are invisible to other transactions until the transaction commits.

• If you roll back the transaction, both the base table and the MV are rolled back.

Supported versions

Synchronous mode is the default for:

• AnalyticDB for PostgreSQL V7.0 instances earlier than V7.0.6.9

• AnalyticDB for PostgreSQL V6.0 instances earlier than V6.6.2.5

Asynchronous mode

In asynchronous mode, write transactions commit immediately. The database then schedules MV updates in the background. When multiple writes arrive concurrently, the system batch-processes them together—so the MV may reflect several writes at once rather than each write individually.

The result is eventual consistency: MV data matches the base table, typically within seconds under normal system load. This makes asynchronous mode suitable for most real-time data warehouse scenarios.

Supported versions

Asynchronous mode is the default for new real-time materialized views in:

• AnalyticDB for PostgreSQL V7.0 instances of V7.0.6.9 or later

• AnalyticDB for PostgreSQL V6.0 instances of V6.6.2.5 or later

Create or delete a real-time materialized view

Create

CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM base WHERE id > 40;

Delete

DROP MATERIALIZED VIEW mv;

Example

The following example creates a base table, creates a real-time materialized view with a filter, inserts data, and queries both to show incremental refresh in action.

-- Step 1: Create the base table
CREATE TABLE test (a int, b int) USING HEAP DISTRIBUTED BY (a);

-- Step 2: Create a real-time materialized view (only rows where b > 40)
CREATE INCREMENTAL MATERIALIZED VIEW mv AS SELECT * FROM TEST WHERE b > 40 DISTRIBUTED BY (a);

-- Step 3: Insert rows into the base table
INSERT INTO test VALUES (1, 30), (2, 40), (3, 50), (4, 60);

-- Step 4: Query the base table
SELECT * FROM test;

Result:

 a | b
---+----
 1 | 30
 2 | 40
 3 | 50
 4 | 60
(4 rows)

-- Step 5: Query the materialized view
SELECT * FROM mv;

The MV reflects only the rows matching b > 40:

 a | b
---+----
 3 | 50
 4 | 60
(2 rows)

When to use real-time materialized views

Real-time materialized views work well when all of the following are true:

• The result set is small relative to the base table—filter conditions narrow down rows significantly, aggregations group values, or semi-structured data analysis produces compact results.

• Queries are expensive to run repeatedly—aggregate operations take a long time, and precomputing the result saves query time.

• Incremental data volume is much smaller than total data volume—most rows in the base table remain unchanged between queries.

Real-time materialized views are also a good fit when you need a real-time ETL pipeline without an external scheduler. Chain real-time materialized views: create one view on the base table, then create another view on top of it. When the base table changes, both views update automatically in sequence—producing real-time wide tables, aggregations, or other transformations.

Unlike standard materialized views, real-time materialized views maintain high data consistency with minimal performance cost through incremental updates. Standard materialized views require a manual full refresh every time the base table changes, which is expensive. Real-time materialized views perform far better in scenarios that involve significant data changes or streaming updates.

Limitations

Performance considerations

Real-time materialized views function like indexes maintained in real time: they speed up reads but add overhead to writes. The write overhead depends on:

• Query complexity and nesting depth. A single-layer view over one table or a simple JOIN can sustain tens to hundreds of thousands of row writes per second, depending on instance size. Deeply nested views with complex joins consume significantly more compute resources.

• JOIN operations and write amplification. When a view joins a large fact table (e.g., 1 billion rows) with a small dimension table (e.g., 10,000 rows), writes to the dimension table can trigger large-scale recomputation—degrading write throughput substantially.

• Available instance resources. Incremental computation shares resources with other workloads. If write performance does not meet requirements, increase compute resources to improve throughput.

What's next

• Manage materialized views

• Manage transactions