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`	Gives the creator's permissions to another user, group, or service account (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
Passthrough columns fixed at creation	A passthrough view stores `SELECT *` expanded into the base table's current columns. A column added to the base table later does not reach the view; recreate the view to include it. See Columns are fixed at creation
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 and window function restrictions

Any part of the query that reads the base table, including nested queries, cannot use:

LIMIT. A LIMIT in a subquery over a different table is still allowed.
A window function, wherever it appears in a column. This includes a window function inside an operator, a cast, a function call, or a CASE, such as row_number() OVER (ORDER BY timestamp) + 1.

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, and a row_number() would start over on each batch.

Rejected: window function inside an expression
CREATE MATERIALIZED VIEW trades_numbered AS (
  SELECT timestamp, symbol,
    row_number() OVER (ORDER BY timestamp) + 1 AS rn
  FROM trades
);

Error

window function on base table is not supported for materialized views: trades

A LIMIT fails the same way, with LIMIT on base table is not supported for materialized views: trades.

Breaking change in QuestDB 10.2.0 and QuestDB Enterprise 4.2.0

Before QuestDB 10.2.0 and QuestDB Enterprise 4.2.0, the base-table query could contain a LIMIT, or a window function nested inside an expression. A window function that makes up a whole column was rejected in earlier versions too.

Materialized views that already contain either one keep refreshing after the upgrade. But if you recreate one, you have to rework the query first. The same applies when you replay SHOW CREATE MATERIALIZED VIEW output during an upgrade, 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. Moving the limit changes which rows the view stores, so it does not behave exactly like the original.
Move a window function out of the view and into the query you run against the view, where it sees all of the view's rows.

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 GROUPING SETS, ROLLUP, or CUBE
Not use non-deterministic functions (now(), rnd_*)
Not read catalogue functions through a regular view, and in QuestDB Enterprise call them only with SYSTEM ADMIN
Not use Enterprise administrative functions, such as permissions() or backups()

See query constraints for the full list.

Catalogue functions

Catalogue functions and statements list database objects or read their metadata:

tables(), all_tables(), views(), materialized_views(), live_views(), wal_tables(), and table_storage()
table_columns(), table_partitions(), wal_transactions(), and, in QuestDB Enterprise, table_cold_partitions()
information_schema.tables(), information_schema.columns(), information_schema.questdb_columns(), pg_class(), pg_attribute(), and pg_attrdef()
reader_pool(), writer_pool(), query_activity(), export_activity(), files(), and glob()
SHOW TABLES, SHOW COLUMNS, SHOW PARTITIONS, SHOW CREATE TABLE, SHOW CREATE VIEW, SHOW CREATE MATERIALIZED VIEW, SHOW CREATE LIVE VIEW, and SHOW CREATE DATABASE

A materialized view refreshes under a context that sees every object, and every reader of the view reads what the refresh stored. To keep a view from publishing objects its readers cannot see, these functions and statements are restricted:

In the view's own query, CREATE MATERIALIZED VIEW accepts them only from the built-in admin, unless it has assumed a service account, or from a principal holding SYSTEM ADMIN, which DATABASE ADMIN includes. This check applies to QuestDB Enterprise only.
Reached through a regular view that the query reads, they are rejected at CREATE and at every refresh, for every principal, because anyone who can alter that view could change what the refresh stores. A system view, such as storage_policies, counts as part of the view's own query.

After an upgrade to QuestDB 10.1.0 or QuestDB Enterprise 4.1.0, a materialized view that reads such a function through a regular view is invalidated at its first refresh. One that calls it in its own query keeps refreshing.

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

A materialized view over an audited view is not audited: its refreshes and its reads record nothing. A principal who can read an audited view and create materialized views can copy its data out of the audit trail's reach, so grant CREATE MATERIALIZED VIEW with that in mind.

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

The creator of a materialized view receives all permissions on it, with the GRANT option. OWNED BY gives these permissions to a user, group, or service account instead. QuestDB stores no owner for the view: afterwards they are ordinary grants that can be revoked like any other.

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.)
`catalogue function from view <view> cannot be used in materialized view: <function>`	A regular view the query reads calls a catalogue function. A `SHOW` statement reports `catalogue statement from view`
`catalogue function cannot be used in materialized view without SYSTEM ADMIN: <function>`	The query calls a catalogue function and the creator lacks `SYSTEM ADMIN` (Enterprise). A `SHOW` statement reports `catalogue statement`
`administrative function cannot be used in materialized view: <function>`	The query calls an Enterprise administrative function, such as `active_permissions()` or `backups()`
`permission denied`	Missing required permission (Enterprise)

Syntax​

Parameters​

Rules and defaults​

LIMIT and window function restrictions​

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​

Catalogue functions​

Permissions (Enterprise)​

Required permissions​

Owner permissions​

OWNED BY clause​

Permission examples​

Errors​

See also​