CREATE MATERIALIZED VIEW

Creates a materialized view that stores pre-computed query results and refreshes incrementally as new data arrives. For conceptual overview, see Materialized Views.

Syntax

CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] viewName
[ WITH BASE baseTableName ]
[ REFRESH ( IMMEDIATE | MANUAL | EVERY interval ) [ DEFERRED ]
           [ START timestamp ] [ TIME ZONE timezone ]
           [ PERIOD ( LENGTH length [ TIME ZONE tz ] [ DELAY delay ] ) ]
           [ PERIOD ( SAMPLE BY INTERVAL ) ] ]
AS [ ( ] query [ ) ]
[ TIMESTAMP ( columnRef ) ]
[ PARTITION BY ( YEAR | MONTH | WEEK | DAY | HOUR ) [ TTL n timeUnit ] ]
[ OWNED BY ownerName ]

Where:

• interval: Duration like 1m, 10m, 1h, 1d
• timeUnit: HOURS | DAYS | WEEKS | MONTHS | YEARS
• query: Must contain SAMPLE BY or time-based GROUP BY

Parameters

Rules and defaults

Valid clause combinations

Basic example

CREATE TABLE trades (
  timestamp TIMESTAMP,
  symbol SYMBOL,
  price DOUBLE,
  amount DOUBLE
) TIMESTAMP(timestamp) PARTITION BY DAY;

CREATE MATERIALIZED VIEW trades_hourly AS
SELECT
  timestamp,
  symbol,
  avg(price) AS avg_price
FROM trades
SAMPLE BY 1h;

The view refreshes incrementally each time trades receives new data.

Refresh strategies

IMMEDIATE (default)

Refreshes incrementally after each base table transaction:

CREATE MATERIALIZED VIEW trades_hourly
REFRESH IMMEDIATE AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h;

Best for: Real-time dashboards where data freshness matters.

EVERY interval

Checks for new data and refreshes on a timer schedule:

CREATE MATERIALIZED VIEW trades_hourly
REFRESH EVERY 10m AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h;

Every 10 minutes, QuestDB checks if the base table has new data and performs an incremental refresh if needed.

With start time and timezone:

CREATE MATERIALIZED VIEW trades_hourly
REFRESH EVERY 1h START '2025-01-01T00:00:00Z' TIME ZONE 'Europe/Berlin' AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h;

Best for: Reducing refresh overhead when real-time accuracy isn't required.

:::note Minimum interval is 1m. For faster refresh, use IMMEDIATE. :::

MANUAL

Refreshes only when explicitly triggered:

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

Trigger refresh with REFRESH MATERIALIZED VIEW.

Best for: Full control over refresh timing, batch processing workflows.

DEFERRED

Skips the initial full refresh on creation. Applies to any strategy:

CREATE MATERIALIZED VIEW trades_hourly
REFRESH IMMEDIATE DEFERRED AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h;

The view remains empty until:

• IMMEDIATE: Next base table transaction
• EVERY: Next scheduled refresh time
• MANUAL: Explicit REFRESH command

PERIOD clause

For data arriving at fixed intervals (e.g., end-of-day prices), use PERIOD to define an in-flight time window that won't refresh until complete.

Full PERIOD syntax

CREATE MATERIALIZED VIEW trades_daily
REFRESH PERIOD (LENGTH 1d TIME ZONE 'Europe/London' DELAY 2h) AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1d;

In this example, each day's data refreshes at 2AM London time.

Compact PERIOD syntax

Matches period to the SAMPLE BY interval:

CREATE MATERIALIZED VIEW trades_hourly
REFRESH PERIOD (SAMPLE BY INTERVAL) AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades
SAMPLE BY 1h ALIGN TO CALENDAR TIME ZONE 'Europe/London';

Ignores the latest incomplete interval, reducing refresh transactions during high-velocity ingestion.

PERIOD with other strategies

Combine PERIOD with EVERY or MANUAL:

CREATE MATERIALIZED VIEW hourly_stats
REFRESH EVERY 15m PERIOD (LENGTH 1h DELAY 5m) AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h;

This configuration:

• Checks for updates every 15 minutes (EVERY 15m)
• Processes data in 1-hour chunks (LENGTH 1h)
• Waits 5 minutes after each hour ends before refreshing it (DELAY 5m)

The DELAY allows late-arriving data to be included before the period closes.

CREATE MATERIALIZED VIEW trades_daily
REFRESH MANUAL PERIOD (LENGTH 1d TIME ZONE 'UTC' DELAY 1h) AS
SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1d;

With MANUAL, refresh only occurs when you run REFRESH MATERIALIZED VIEW explicitly.

WITH BASE (for JOINs)

When querying multiple tables, specify which table triggers refresh:

CREATE MATERIALIZED VIEW trades_with_metadata
WITH BASE trades AS
SELECT
  t.timestamp,
  t.symbol,
  m.description,
  avg(t.price) AS avg_price
FROM trades t
JOIN instruments m ON t.symbol = m.symbol
SAMPLE BY 1h;

Only changes to trades trigger refresh. Changes to instruments do not.

Partitioning

Specify storage partitioning with PARTITION BY:

CREATE MATERIALIZED VIEW trades_hourly AS (
  SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h
) PARTITION BY DAY;

Options: YEAR, MONTH, WEEK, DAY, HOUR

If omitted, partitioning is inferred from SAMPLE BY.

:::warning Partitioning cannot be changed after creation. :::

TTL (Time-To-Live)

Limit data retention with TTL:

CREATE MATERIALIZED VIEW trades_hourly AS (
  SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h
) PARTITION BY DAY TTL 7 DAYS;

Time units: HOURS, DAYS, WEEKS, MONTHS, YEARS

The view's TTL is independent of the base table's TTL. See TTL documentation for details.

Complete example

Putting it all together:

CREATE MATERIALIZED VIEW IF NOT EXISTS trades_hourly_stats
WITH BASE trades
REFRESH EVERY 15m
  START '2025-01-01T00:00:00Z'
  TIME ZONE 'UTC'
  PERIOD (LENGTH 1h DELAY 5m)
AS (
  SELECT
    timestamp,
    symbol,
    avg(price) AS avg_price,
    sum(amount) AS total_volume
  FROM trades
  SAMPLE BY 1h
)
PARTITION BY DAY TTL 30 DAYS;

This creates a view that:

• Checks for updates every 15 minutes (EVERY 15m)
• Processes data in 1-hour chunks, waiting 5 minutes for late data (PERIOD)
• Aggregates from trades table (WITH BASE trades)
• Stores hourly averages and volumes (SAMPLE BY 1h)
• Keeps 30 days of data (TTL 30 DAYS)

Metadata

Query view metadata with materialized_views():

SELECT view_name, base_table_name, view_status, last_refresh_finish_timestamp
FROM materialized_views();

See meta functions for all available columns.

Query constraints

Materialized view queries must:

• Use SAMPLE BY or GROUP BY with designated timestamp
• Not use FROM-TO, FILL, or ALIGN TO FIRST OBSERVATION
• Not use non-deterministic functions (now(), rnd_*)

See query constraints for the full list.

Permissions (Enterprise)

Creating and managing materialized views requires specific permissions.

Required permissions

Owner permissions

When you create a materialized view, you automatically receive all permissions on it (including DROP MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW) with the GRANT option.

OWNED BY clause

Assign ownership to a user, group, or service account:

CREATE GROUP analysts;
CREATE MATERIALIZED VIEW trades_hourly AS (
  SELECT timestamp, symbol, avg(price) AS avg_price FROM trades SAMPLE BY 1h
) OWNED BY analysts;

:::note External users (authenticated via external identity providers) must specify the OWNED BY clause when creating materialized views. :::

Permission examples

GRANT CREATE MATERIALIZED VIEW TO user1;

GRANT SELECT ON trades TO user1;

GRANT REFRESH MATERIALIZED VIEW ON trades_hourly TO user1;

GRANT DROP MATERIALIZED VIEW ON trades_hourly TO user1;

Errors

See also

• Materialized views concept
• REFRESH MATERIALIZED VIEW
• DROP MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW SET REFRESH
• ALTER MATERIALIZED VIEW SET TTL