Documentation PR: questdb/documentation#202
Ent PR: https://github.com/questdb/questdb-enterprise/pull/634

Includes the following new features and enhancements for materialized views:

• Period materialized views
• Manual refresh for materialized views (disabled by default, can be enabled with mat.view.debug.enabled=true config property)
• Adds optional TIME ZONE clause for materialized views with timer refresh and period materialized views
• Updates the CREATE MATERIALIZED VIEW syntax: REFRESH INCREMENTAL is replaced with REFRESH IMMEDIATE, but older syntax is also accepted
• Adds optional DEFERRED clause to CREATE MATERIALIZED VIEW syntax
• REFRESH LIMIT is now respected by full refresh

Breaking change 0: previously set REFRESH LIMIT and REFRESH EVERY (timer refresh) settings will be reset by this patch. Materialized views with timer refresh will be treated as manual refresh ones. To restore a timer view, it has to be dropped and recreated. To restore the REFRESH LIMIT, an alter statement must be executed after the upgrade. Example:

-- restore REFRESH LIMIT
ALTER MATERIALIZED VIEW trades_latest_1d SET REFRESH LIMIT 1 WEEK;

Breaking change 1: updated syntax for timer refresh. Instead of REFRESH START <timestamp> EVERY <interval> in CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW we now expect REFRESH EVERY <interval> START <timestamp> syntax.

Period materialized views

Adds special period type of materialized views. These materialized views never aggregate data on incomplete period interval, e.g. current day, and only refresh older periods. Once the current period is over, it's refreshed automatically (unless the materialized view is configured for manual refresh). Inserts into complete periods are reflected in the view data just like with non-period materialized views.

CREATE MATERIALIZED VIEW trades_latest_1d
REFRESH PERIOD (LENGTH 1d TIME ZONE 'Europe/Berlin' DELAY 2h) AS
SELECT 
    timestamp, 
    symbol, 
    side, 
    last(price) AS price, 
    last(amount) AS amount, 
    last(timestamp) AS latest
FROM trades
SAMPLE BY 1d;

The above materialized view will start refreshing one-day periods on the next day after it was created, at 02:00 in Berlin time zone (CEST). The time is 2 hours past the end of the period due to the 2h delay value. Since this view has the default IMMEDIATE refresh type, all writes to the base table that are older than the current period will be asynchronously reflected in the trades_latest_1d view.

Full REFRESH PERIOD syntax:

REFRESH [IMMEDIATE | MANUAL | EVERY <interval>] PERIOD
    LENGTH <interval>
    [TIME ZONE '<timezone>']
    [DELAY <interval>]

For now, the maximum period length is limited with 24 hours.

Internally, a new lastPeriodHi persisted field is added to materialized view state to track the last refreshed period interval.

Manual refresh for materialized views

CREATE MATERIALIZED VIEW price_1h REFRESH MANUAL AS
SELECT ts, symbol, avg(price) avg_price
FROM price
SAMPLE BY 1h;

Manual refresh materialized views can be refreshed via the REFRESH MATERIALIZED VIEW price_1h INCREMENTAL; syntax or, if the view became invalid via the REFRESH MATERIALIZED VIEW price_1h FULL; syntax. The incremental refresh steps are the same as for IMMEDIATE and timer (EVERY) materialized views except for the only way to trigger a refresh on a manual view is via REFRESH SQL.

TIME ZONE clause

The optional TIME ZONE clause is available for materialized views with timer refresh and period materialized views. When specified, the time zone is applied to both start timestamp and timer intervals.

DDL example with the new TIME ZONE clause:

CREATE MATERIALIZED VIEW view_5m
REFRESH EVERY 30m START '2025-06-10T01:01:00.000000' TIME ZONE 'Europe/Berlin' AS
SELECT ts, k, avg(v)
FROM base_table
SAMPLE BY 5m;

DEFERRED clause

The default behavior now is to refresh the view once it's created, but when the DEFERRED clause is specified, the view will only refresh on the corresponding event, e.g. explicit REFRESH SQL in case of manual refresh.

CREATE MATERIALIZED VIEW trades_latest_1d
REFRESH IMMEDIATE DEFERRED AS
-- ...

CREATE MATERIALIZED VIEW trades_latest_1d
REFRESH MANUAL DEFERRED AS
-- ...

CREATE MATERIALIZED VIEW trades_latest_1d
REFRESH EVERY 1d DEFERRED START --...
-- ...