By Jingfeng (Yaojing)
In an earlier post, "Goodbye On-the-Fly Processing: How PolarDB Materialized Views Deliver 100x Query Performance," we walked through the core capabilities of full materialized views in PolarDB for MySQL — precomputing and persisting query results and, by offloading computation to In-Memory Column Index (IMCI) nodes with a hybrid row-column storage architecture, delivering up to 100x acceleration for complex analytical queries. Since its release, the feature has been widely adopted across many customers' HTAP workloads.
But when a table grows to hundreds of millions of rows and each change touches only a handful of records, the "recompute the entire view" approach of a full refresh becomes far too expensive — refreshes take a long time, data freshness suffers, and near-real-time analytics is out of reach. That is why PolarDB for MySQL now introduces the Incremental Materialized View (IMV): it processes only the data that has changed since the last refresh. In the TPC-H 100G benchmark, it delivers several-fold to tens-of-fold speedups over a full refresh — 32x on a four-table JOIN query and nearly 11x on a single-table aggregation query.
An IMV processes only the INSERT/UPDATE/DELETE changes to the base tables (the original business tables a materialized view depends on) since the last refresh, cutting refresh overhead by more than 90%. A four-table JOIN query drops from 74 seconds to 2.3 seconds. With auto-refresh intervals as short as one second, your data stays "fresh by the second."
Changes are captured automatically as the redo log is replayed on the IMCI nodes — no triggers, no binlog parsing. Nothing is added to the business tables, and the base tables are never locked during a refresh, so online transactions are completely unaffected.
The system automatically derives and creates the columnar delta tables from the view definition. They come and go with the base tables and are cleaned up automatically — the entire lifecycle is transparent to the user, with no DBA needed to maintain log tables by hand.
Built on a decoupled storage-and-compute architecture, IMCI compute nodes are stateless and delta data lives on shared storage. Incremental refreshes can be load-balanced across nodes, with second-level scale-up and horizontal scale-out.
An e-commerce platform's order table grows by thousands of rows per second, and the operations team needs live statistics by user, category, and region. An IMV "layers" each second's new and changed orders onto the existing result, without rescanning hundreds of millions of historical orders. With acceleration like the 10.8x seen in Q1, a full refresh that once took several seconds compresses to sub-second, and the dashboard always shows the latest data.
A manufacturer's supply-chain system spans several core business tables — suppliers, orders, materials, regions — and management reviews cross-table reports multiple times a day. An IMV pre-joins these tables into a "wide table" and joins only the changed rows against the others, avoiding a full multi-table recomputation. With acceleration like the 31.8x seen in Q10, a refresh that once took more than a minute compresses to 2 seconds, raising report freshness from hourly to every minute.
A layered data warehouse often computes from the detail layer (ODS) to the summary layer (DWS) to the application layer (ADS), one layer at a time. Nesting IMVs builds a lightweight ETL pipeline in which each layer processes only the incremental changes passed down from upstream, dramatically lowering the compute cost at every layer. Combined with refresh intervals as short as one second, you can build a near-real-time data-processing pipeline entirely inside the database, reducing reliance on external ETL tools.
Architecturally, the IMV keeps the "offload computation to IMCI column-store nodes" design and builds an end-to-end path around the "incremental" idea that is completely transparent to the business.
First, change capture is driven by the database kernel's redo log stream: base-table changes are identified in real time as the log is replayed on the IMCI nodes — no triggers, no binlog parsing, with sync latency held to the millisecond level.
Second, the system automatically derives and implicitly creates the columnar delta tables from the view definition, and reclaims expired data automatically based on the global transaction position — the whole lifecycle is transparent to the DBA and maintenance-free.
Third, incremental operators such as filtering, aggregation, and JOIN are all pushed down to the column-store nodes and executed with SIMD vectorization, with the optimal incremental strategy chosen automatically by query pattern — aggregations "layer on" directly, JOINs use only the changed rows — eliminating full-table recomputation entirely.
Fourth, during the refresh phase, Parallel DML (PDML) has the IMCI and read-write (RW) nodes cooperate in a pipeline, with multi-threaded, concurrent Insert/Update/Delete write-back so that write throughput scales linearly with concurrency. These four capabilities work together to deliver a refresh experience that is "fresh by the second, with zero business impact."
Figure: Overall architecture of the IMV
PolarDB's IMV already covers a range of mainstream analytical scenarios, and coverage keeps expanding:
| Scenario type | Description | Typical application | Status |
|---|---|---|---|
| Single-table filtering | WHERE-clause filtering | Data extraction / partition caching | ✅ Supported |
| Single-table aggregation | COUNT/SUM/AVG grouped statistics | Real-time reports / metric dashboards | ✅ Supported |
| Multi-table joins | 2–6 table inner joins | Wide-table precomputation | ✅ Supported |
| Multi-table left joins | Left outer joins | Dimension enrichment / detail completion | ✅ Supported |
| Nested materialized views | Base table is itself a materialized view | Multi-level nested analytics | ✅ Supported |
| Multi-table aggregation | JOIN + GROUP BY | Multi-dimensional roll-up analytics | Coming soon |
| ON COMMIT real-time refresh | Real-time refresh | Real-time monitoring / risk control | Coming soon |
Testing was done on a PolarDB for MySQL 8.0.2 cluster (32 vCPUs / 256 GB memory) with the TPC-H 100G dataset (the lineitem table has about 600 million rows), covering typical scenarios such as single-table aggregation, multi-table JOIN, and LEFT JOIN. Each scenario had about 1,000 changed rows per round, averaged over five rounds.
Note: Q1 is the native TPC-H SQL. The other queries marked with are based on the original TPC-H SQL, adapted for the operator scenarios that incremental refresh currently supports (for example, removing subqueries, CTEs, and ORDER BY/LIMIT, which are not yet supported), while preserving the core join and filter logic.
Full-refresh time is determined by the total data volume of the base tables, whereas incremental-refresh time depends only on the number of changed rows — the larger the data and the smaller the change ratio, the greater the incremental advantage. In the most representative four-table mixed JOIN scenario (Q10), a full refresh scans and joins four large tables in 74 seconds, while an incremental refresh processes just 1,000 changed rows in 2.3 seconds — a 31.8x speedup. In the native TPC-H Q1 single-table aggregation, grouping and aggregating 600 million lineitem rows drops from 3.45 seconds to 0.32 seconds. Even in the most complex six-table INNER JOIN (Q5), the incremental refresh still holds a speedup of more than 2.3x, and its time rises only slightly across different change granularities (100–10,000 rows), making it far more stable than a full refresh.
Full refresh and incremental refresh are not mutually exclusive — they are two complementary strategies that can work together in the same system:
▶︎ Choose incremental refresh when: the base tables are large and change frequently (each change touches only a small fraction of the total), data freshness requirements are high (minutes or even seconds), and the query logic falls within incremental support (filtering, aggregation, JOIN, LEFT JOIN, and so on).
▶︎ Choose full refresh when: the query logic includes window functions, HAVING, ORDER BY, or other syntax not yet supported by incremental refresh; data changes infrequently (a daily refresh is enough); or you need to rebuild the view data from scratch.
Building on what's already supported, we will keep expanding to broader SQL operators and capabilities such as multi-table aggregation (JOIN + GROUP BY), UNION ALL, and MIN/MAX. We will also soon introduce ON COMMIT real-time refresh and transparent query rewrite, evolving materialized views from "on-demand refresh by the second" to "transaction-level real-time sync," with the optimizer automatically matching views to accelerate business queries — lowering the barrier to entry even further.
The IMV is a key step in PolarDB's move from "query acceleration" to "data freshness": its intelligent, redo-log-based incremental maintenance turns a materialized view into a "living" data asset that beats in sync with your business data.
🙋 See the official documentation for details. The feature is available for trial in the latest version of PolarDB for MySQL.
PolarDB-X Storage Engine Internals | Native Multi-Version Unique Key Index — Panda Index
Goodbye Spark Scripts: Fully Automated Table Optimization in AnalyticDB for MySQL
Alibaba Cloud Native Community - January 27, 2026
Apache Flink Community - April 25, 2025
ApsaraDB - May 12, 2026
ApsaraDB - November 18, 2025
ApsaraDB - September 30, 2021
Apache Flink Community - May 30, 2025
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
PolarDB for Xscale
Alibaba Cloud PolarDB for Xscale (PolarDB-X) is a cloud-native high-performance distributed database service independently developed by Alibaba Cloud.
Learn More
PolarDB for MySQL
Alibaba Cloud PolarDB for MySQL is a cloud-native relational database service 100% compatible with MySQL.
Learn More
Tablestore
A fully managed NoSQL cloud database service that enables storage of massive amount of structured and semi-structured data
Learn MoreMore Posts by ApsaraDB
