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
| ( query )
  [ TIMESTAMP ( columnRef ) ]
  [ PARTITION BY ( YEAR | MONTH | WEEK | DAY | HOUR )
        [ TTL n timeUnit ] ]
  [ EXPIRE ROWS expirePolicy [ CLEANUP EVERY duration ] ]
  [ OWNED BY ownerName ]
}

You can leave out the parentheses around query only when the query is the last thing in the statement. They are required if any clause comes after the query, such as TIMESTAMP, PARTITION BY, TTL, EXPIRE ROWS, or OWNED BY.

Where:

interval: Duration like 1m, 10m, 1h, 1d
timeUnit: HOURS | DAYS | WEEKS | MONTHS | YEARS
query: Either an aggregating query (with SAMPLE BY or a time-based GROUP BY), or a passthrough query that copies rows from a single table
expirePolicy: WHEN predicate | KEEP LATEST [ON timestamp] PARTITION BY cols | KEEP [N] (HIGHEST|LOWEST) ON col [PARTITION BY cols]. This policy is meant for passthrough views (see below).

Parameters

Parameter	Description
`viewName`	Name for the materialized view
`IF NOT EXISTS`	Create only if view doesn't already exist
`WITH BASE`	Specify base table (required for JOINs)
`REFRESH`	Refresh strategy (default: `IMMEDIATE`)
`DEFERRED`	Skip initial refresh on creation
`query`	An aggregating (`SAMPLE BY` / time-based `GROUP BY`) or passthrough query
`TIMESTAMP`	Designate timestamp column for the view
`PARTITION BY`	Partitioning unit for view storage
`TTL`	Retention period for view data
`EXPIRE ROWS`	Row-level retention for passthrough views (see below)
`OWNED BY`	Assign ownership (Enterprise)

Rules and defaults

Rule	Description
Query must aggregate or be passthrough	Either `SAMPLE BY` / `GROUP BY` with a designated timestamp, or a passthrough query that copies rows one-for-one from a single table
Default refresh	`IMMEDIATE` (refreshes after each base table transaction)
WITH BASE required	Must specify when query contains JOINs
PARTITION BY sizing	Should be larger than or equal to `SAMPLE BY` interval
PERIOD requires SAMPLE BY	The `PERIOD` clause only works with `SAMPLE BY` queries
EVERY minimum	Minimum timer interval is `1m`

LIMIT restrictions and existing definitions

You cannot use LIMIT in any part of the query that reads the base table, including nested queries. This applies to both aggregating and passthrough views. Each refresh runs the query over the rows that just changed, so a LIMIT 100 would cap each batch rather than the view as a whole. A LIMIT in a subquery over a different table is still allowed.

Upgrading existing definitions

Materialized views that already contain such a LIMIT keep refreshing. But if you recreate one after upgrading from a version that allowed it, you have to remove or rework the limit first. The same applies when you replay SHOW CREATE MATERIALIZED VIEW output during a migration or restore.

If the limit was only there to cap how many rows your application receives, remove it from the view and instead use ORDER BY ... LIMIT ... when you query the view. Note that moving the limit changes which rows the view stores, so it does not behave exactly like the original.

Valid clause combinations

Refresh	DEFERRED	PERIOD	Valid
IMMEDIATE	✓	✓	✓
MANUAL	✓	✓	✓
EVERY interval	✓	✓	✓
(none specified)	✗	✗	✓ (defaults to IMMEDIATE)

Basic example

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

Materialized view with hourly aggregation
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;

Option	Description
`EVERY interval`	How often to check for updates (e.g., `10m`, `1h`)
`START timestamp`	When to begin the schedule
`TIME ZONE`	Timezone for schedule alignment

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;

Option	Description
`LENGTH`	Period duration (e.g., `1d`)
`TIME ZONE`	Timezone for period boundaries
`DELAY`	Grace period before period closes (e.g., `2h` for late data)

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:

Period with timer refresh
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.

Period with manual refresh
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.

EXPIRE ROWS

Add a row-level retention policy with EXPIRE ROWS. Where TTL drops whole partitions by age, EXPIRE ROWS keeps a chosen set of rows: the latest per key, the top-N per group, or rows that match a condition. QuestDB keeps that set up to date as the view refreshes.

EXPIRE ROWS is built for passthrough (non-aggregating) views: a query over a single table with no SAMPLE BY / GROUP BY, where each view row matches one base row. An aggregating view is allowed, but only with a warning in the log, because a later refresh can rebuild rows that were deleted. The query must not read another view that has a policy, whether as its base table or through a join:

Passthrough view that keeps the latest row per symbol
CREATE MATERIALIZED VIEW trades_latest AS (
  SELECT * FROM trades
) EXPIRE ROWS KEEP LATEST ON timestamp PARTITION BY symbol;

A WHEN condition is for rules that depend on the current time, such as a rolling timestamp < dateadd('d', -7, now()) window. The view's query cannot do this, because it is not allowed to call functions like now(). A rule that only looks at a row's own values belongs in the query's WHERE clause instead, which keeps those rows out of the view completely; see WHERE filter or EXPIRE ROWS?.

The clause goes after the query (and after PARTITION BY if present):

EXPIRE ROWS
  { WHEN predicate
  | KEEP LATEST [ ON timestampColumn ] PARTITION BY col [, col ...]
  | KEEP [ N ] ( HIGHEST | LOWEST ) ON col [ PARTITION BY col [, col ...] ] }
  [ CLEANUP EVERY duration ]

A WHEN threshold that is a fixed value at definition time and comes out as NULL is rejected, because it would expire nothing. This covers the obvious timestamp < CAST(NULL AS TIMESTAMP) and also math that overflows onto the reserved NULL value, such as timestamp < 2147483647 + 1. See A NULL threshold is rejected.

For how expired rows are filtered and freed from disk, see How EXPIRE ROWS works. Change or remove a policy with ALTER MATERIALIZED VIEW SET EXPIRE.

A view can have both TTL and EXPIRE ROWS. TTL comes first in the statement and first in effect: it removes rows from the view, and the EXPIRE ROWS policy then applies to what is left. See Combining with TTL.

See the Expiring rows concept page for all modes, examples, and details (NULLs, ties, and when rows are deleted from disk).

Larger example

Create a materialized 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)

Fully specified materialized view
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;

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

Permission	Level	Required for
`CREATE MATERIALIZED VIEW`	Database (global)	Creating a materialized view
`SELECT`	Table/Column (base table)	All columns referenced in the view query
`DROP MATERIALIZED VIEW`	Materialized view	Dropping the view
`REFRESH MATERIALIZED VIEW`	Materialized view	Manually refreshing the view

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 permission to create materialized views

GRANT CREATE MATERIALIZED VIEW TO user1;

Grant SELECT on base table (required to create view from it)

GRANT SELECT ON trades TO user1;

Grant permission to refresh a specific view

GRANT REFRESH MATERIALIZED VIEW ON trades_hourly TO user1;

Grant permission to drop a specific view

GRANT DROP MATERIALIZED VIEW ON trades_hourly TO user1;

Errors

Error	Cause
`materialized view already exists`	View exists and `IF NOT EXISTS` not specified
`base table does not exist`	Referenced table doesn't exist
`query is not supported`	Query doesn't meet constraints (missing SAMPLE BY, uses FILL, etc.)
`permission denied`	Missing required permission (Enterprise)

Syntax​

Parameters​

Rules and defaults​

LIMIT restrictions and existing definitions​

Valid clause combinations​

Basic example​

Refresh strategies​

IMMEDIATE (default)​

EVERY interval​

MANUAL​

DEFERRED​

PERIOD clause​

Full PERIOD syntax​

Compact PERIOD syntax​

PERIOD with other strategies​

WITH BASE (for JOINs)​

Partitioning​

TTL (Time-To-Live)​

EXPIRE ROWS​

Larger example​

Metadata​

Query constraints​

Permissions (Enterprise)​

Required permissions​

Owner permissions​

OWNED BY clause​

Permission examples​

Errors​

See also​