Incremental materialized views

Create a DIMV and its mlog

When you create a DIMV with CREATE MATERIALIZED VIEW ... REFRESH FAST ON DEMAND ... WITH NO DATA, the system automatically creates an mlog for each involved base table and adds the columns required by the query. Always use WITH NO DATA so that the system creates and augments the mlog for you.

CREATE MATERIALIZED VIEW dimv_name
REFRESH FAST ON DEMAND
AS query
WITH NO DATA;

Each base table needs only one mlog, which can be shared by multiple DIMVs. If an existing mlog is missing columns that a new DIMV depends on, creating the new DIMV with WITH NO DATA lets the system fill in the missing columns automatically. Without WITH NO DATA, you must add the columns beforehand; otherwise, DIMV creation fails.

Build the baseline with the first full refresh

After you create a DIMV with WITH NO DATA, the DIMV has no data and no "incremental refresh baseline" (that is, no trusted starting transaction ID has been recorded). If you call polar_matview.incremental_refresh_mv() directly for an incremental refresh at this point, the following error is returned:

ERROR:  No refresh snapshot found for incremental materialized view mv_name
ERROR:  materialized view "mv_name" has not been populated
HINT:  Use the REFRESH MATERIALIZED VIEW command.

The correct startup sequence is to first run a full REFRESH MATERIALIZED VIEW to build the incremental baseline. After that, you can call polar_matview.incremental_refresh_mv() for on-demand incremental refreshes.

-- 1. Create the base table and insert initial data
CREATE TABLE t_dimv(id INT PRIMARY KEY, cat TEXT, amount NUMERIC);
INSERT INTO t_dimv VALUES(1,'a',10),(2,'a',20),(3,'b',30);

-- 2. Create the DIMV with WITH NO DATA (the system auto-creates mlog on t_dimv)
CREATE MATERIALIZED VIEW mv_dimv
REFRESH FAST ON DEMAND
AS SELECT cat, SUM(amount) FROM t_dimv GROUP BY cat
WITH NO DATA;

-- 3. First full refresh — build the incremental baseline
REFRESH MATERIALIZED VIEW mv_dimv;

SELECT * FROM mv_dimv ORDER BY cat;
-- Output:
-- cat | sum
-- ----+-----
-- a   |  30
-- b   |  30

-- 4. Append more data to the base table
INSERT INTO t_dimv VALUES(4,'b',40),(5,'a',50);

-- 5. On-demand incremental refresh (applies delta only, not a full recompute)
SELECT polar_matview.incremental_refresh_mv('mv_dimv'::regclass::oid);
-- Returns t on success

SELECT * FROM mv_dimv ORDER BY cat;
-- Output:
-- cat | sum
-- ----+-----
-- a   |  80
-- b   |  70

Configure automatic refresh and purge

DIMVs are not refreshed automatically by default. Use the pg_cron extension to launch one or more long-running DIMV workers when the database starts. Then configure a refresh interval on the DIMVs that need automatic refresh — the workers will perform incremental refresh and mlog purge in the background.

-- 1. Install pg_cron if it is not already installed
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 2. Use pg_cron to bring up DIMV refresh workers in the background when the cluster starts
-- Each cron.schedule call corresponds to one worker. Scale the worker count with the refresh workload.
SELECT cron.schedule(
    'dimv-auto-refresh-worker-1',
    '@restart',
    $$CALL polar_matview.auto_refresh_worker_main()$$
);

SELECT cron.schedule(
    'dimv-auto-refresh-worker-2',
    '@restart',
    $$CALL polar_matview.auto_refresh_worker_main()$$
);

-- Create a dedicated role and set both timeout parameters to 0
CREATE ROLE dimv_worker LOGIN;
ALTER ROLE dimv_worker SET statement_timeout = 0;
ALTER ROLE dimv_worker SET polar_transaction_timeout = 0;

-- Schedule workers under that role (via pg_cron background execution)
SELECT cron.schedule_in_database(
    'dimv-auto-refresh-worker-1',
    '@restart',
    $$CALL polar_matview.auto_refresh_worker_main()$$,
    'postgres',       -- Target database
    'dimv_worker'     -- Execute as this dedicated role
);

-- Set the automatic refresh interval of a DIMV to 1 second
SELECT polar_matview.set_refresh_interval('dimv_name'::regclass::oid, '1 seconds'::interval);

-- Disable automatic refresh for a DIMV
SELECT polar_matview.reset_refresh_interval('dimv_name'::regclass::oid);

End-to-end example

The following end-to-end example covers the entire DIMV lifecycle, from installing the extension to configuring automatic refresh.

-- 1. Install extensions
CREATE EXTENSION IF NOT EXISTS polar_ivm;
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 2. Launch background workers (auto-started after each database restart)
SELECT cron.schedule(
    'dimv-auto-refresh-worker-1',
    '@restart',
    $$CALL polar_matview.auto_refresh_worker_main()$$
);

-- 3. Create base tables and insert initial data
CREATE TABLE products (
    product_id  INT PRIMARY KEY,
    name        TEXT,
    category    TEXT,
    price       NUMERIC
);

CREATE TABLE order_items (
    item_id     BIGSERIAL PRIMARY KEY,
    product_id  INT,
    order_date  DATE,
    quantity    INT
);

INSERT INTO products VALUES
    (1, 'Widget A',  'Electronics', 100),
    (2, 'Widget B',  'Electronics', 150),
    (3, 'Gadget C',  'Home',         80);

INSERT INTO order_items (product_id, order_date, quantity) VALUES
    (1, '2024-01-01', 10),
    (2, '2024-01-02',  5),
    (1, '2024-01-03',  8);

-- 4. Create the DIMV with WITH NO DATA; the system auto-creates and augments the mlog
CREATE MATERIALIZED VIEW mv_product_sales
REFRESH FAST ON DEMAND
AS
SELECT p.product_id,
       p.category,
       SUM(oi.quantity) AS total_qty,
       COUNT(*)         AS order_count,
       MAX(oi.order_date) AS latest_order
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.category
WITH NO DATA;

-- 5. First full refresh — build the incremental baseline
REFRESH MATERIALIZED VIEW mv_product_sales;

-- 6. Configure a 1-second automatic refresh; the system manages the corresponding mlog purge interval
SELECT polar_matview.set_refresh_interval(
    'mv_product_sales'::regclass::oid,
    '1 second'::interval
);

-- 7. Subsequent base-table changes are incrementally refreshed into the materialized view by the background worker, and mlog is purged automatically
INSERT INTO order_items (product_id, order_date, quantity) VALUES (3, '2024-01-04', 20);
UPDATE order_items SET quantity = quantity + 1 WHERE item_id = 1;
DELETE FROM order_items WHERE item_id = 2;

-- Wait one refresh cycle and query to see the latest results
SELECT * FROM mv_product_sales ORDER BY product_id;

Manually create and adjust mlog

The recommended approach is to create DIMVs with WITH NO DATA and let the system manage mlog. If you need to create mlog before creating the DIMV, or add and drop columns on an existing mlog, use the following functions provided by the polar_ivm schema.

SELECT polar_ivm.create_matview_log('base_table'::regclass);

-- Add a column to mlog
SELECT polar_ivm.matview_log_add_column('base_table'::regclass, 'new_col');

-- Drop a column from mlog
SELECT polar_ivm.matview_log_drop_column('base_table'::regclass, 'old_col');

SELECT polar_ivm.drop_matview_log('base_table'::regclass);

Manual refresh

After changes are made to base tables, call polar_matview.incremental_refresh_mv() to perform an on-demand incremental refresh (the argument is the materialized view OID):

-- DML on base tables
INSERT INTO users VALUES (1, 'Alice');
UPDATE users SET name = 'Alice A.' WHERE userid = 1;
INSERT INTO sales VALUES (100, 1, '2023-01-01', 500);

-- Incrementally refresh the DIMV that depends on both base tables
SELECT polar_matview.incremental_refresh_mv('mv_sales_summary'::regclass::oid);

Incremental refresh has the following transaction constraints:

• Must run at the READ COMMITTED isolation level.

• The finest constraint unit is the transaction. Within a single transaction, if a base table has already executed DML that writes to mlog, you cannot then refresh a DIMV that depends on that base table, and vice versa.

• The constraint is evaluated per base table. Modifying unrelated tables or refreshing DIMVs that do not depend on that base table is not affected.

• You can refresh the same DIMV or different DIMVs multiple times, and modify the same base table multiple times.

You can also run a full refresh. A full refresh updates the incremental baseline; subsequent incremental refreshes are computed from the new baseline:

REFRESH MATERIALIZED VIEW mv_sales_summary;

Manual purge

Use polar_matview.matview_log_purge() to manually purge the mlog of a specific base table:

SELECT polar_matview.matview_log_purge('base_table'::regclass);

DIMV refresh statistics

Use the monitoring view polar_matview_monitor.dimv_stat_refresh to inspect DIMV refresh statistics:

SELECT * FROM polar_matview_monitor.dimv_stat_refresh;

The columns are:

mlog purge statistics

Use the monitoring view polar_matview_monitor.mlog_stat_purge to inspect mlog purge statistics:

SELECT * FROM polar_matview_monitor.mlog_stat_purge;

The columns are:

mlog invalidation and recovery

An mlog is marked invalid when either of the following conditions is met. Dependent DIMVs receive a warning and get false as the return value from incremental refresh (polar_matview.incremental_refresh_mv()):

• The mlog's transaction ID span exceeds polar_mlog_xid_max_limit (default 2 billion). As transaction IDs approach wraparound, the relative order of log entries can no longer be determined reliably.

• The time since the last purge exceeds polar_mlog_purge_max_interval (default 18,000 seconds). After long periods without purge, even the timestamp-based fallback cannot guarantee that log entries remain identifiable.

These two conditions are not triggered under normal operation. As long as the mlog is continuously purged by workers, its transaction ID span keeps shrinking and never approaches 2 billion. They are only a safety net — typically only hit when you have disabled automatic purge, switched to manual purge, and let the mlog go without purge for a long time (long enough for over 2 billion transaction IDs to be consumed).

Detect mlog state through the valid, age, and valid_lifecycle columns of the polar_matview_monitor.mlog_stat_purge monitoring view.

To recover, truncate and revalidate the mlog, and then rebuild the baseline on the dependent DIMVs:

SELECT polar_ivm.matview_log_truncate_and_validate('base_table_name'::regclass);

DIMV invalidation and recovery

A DIMV becomes invalid when the materialized view itself can no longer be incrementally refreshed (the metadata field polar_ivm.imatview_metadata.is_incrementally_updatable is set to false). This is triggered by the following operations:

• TRUNCATE on a base table: all populated DIMVs that depend on that base table become invalid.

• A non-concurrent full REFRESH MATERIALIZED VIEW on a nested DIMV that serves as a base table for other DIMVs: it resets that DIMV's mlog and cascades invalidation to the downstream DIMVs.

• The mlog of a base table is reset (for example, by polar_ivm.matview_log_truncate_and_validate()): DIMVs dependent on that base table are cascaded to invalid.

Other incompatible DDL that would break the incremental baseline (for example, dropping or altering a depended-on column) is blocked by default, rather than silently invalidating the DIMV. See Impact of DDL on base tables.

Supported aggregate functions

In addition to the common aggregates (SUM, AVG, COUNT, MIN, MAX), DIMV also supports the following aggregates:

• String and array aggregates: string_agg, array_agg.

• Ordered-set aggregates: such as percentile_disc and percentile_cont.

• User-defined aggregates: aggregate functions that you define with CREATE AGGREGATE.

Example using string_agg and array_agg:

CREATE MATERIALIZED VIEW mv_agg
REFRESH FAST ON DEMAND
AS
SELECT cat,
       string_agg(name, ',') AS names,
       array_agg(id ORDER BY id) AS ids
FROM t_dimv
GROUP BY cat
WITH NO DATA;

Constraints on arbitrary aggregates

• Requires GROUP BY. Whole-table arbitrary aggregates without GROUP BY are not supported.

• NULL grouping keys and multi-column grouping keys are supported.

• Groups that change are recomputed in full; refresh cost is approximately O(delta group).

• Common aggregates can be maintained at the base-table change granularity and are usually cheaper than arbitrary aggregates.

Constraints on window functions

PolarDB for PostgreSQL currently supports the following window functions: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE(), and window aggregates. Constraints:

• Every window function must specify PARTITION BY. Whole-table windows are not supported.

• Multiple window functions must have PARTITION BY keys that form a compatible subset chain.

• PARTITION BY can use columns or immutable function expressions.

• Window functions cannot be combined with GROUP BY or aggregate functions.

• Window functions cannot be combined with outer joins or EXISTS sublinks.

• Window functions cannot appear inside a subquery.