CREATE LIVE VIEW

Creates a live view that incrementally maintains the result of a window-function query over a single base table and can be queried like a regular table. For a conceptual overview, see Live views.

note

Live views are currently released as beta. The supported SQL surface is deliberately narrow in this first version. See Limitations for the shapes that are rejected at creation time.

Syntax

CREATE LIVE VIEW
CREATE LIVE VIEW [ IF NOT EXISTS ] viewName
FLUSH EVERY duration
[ IN MEMORY duration ]
[ PARTITION BY ( YEAR | MONTH | WEEK | DAY | HOUR ) ]
START FROM ( NOW | BEGINNING | 'timestamp' )
AS [ ( ] query [ ) ]
[ OWNED BY ownerName ]

Where:

duration: a single token with a unit of ms, s, m, h, or d, for example 100ms, 5s, or 30m.
query: a SELECT over one WAL-backed base table whose projection contains window functions.

FLUSH EVERY is required and must come first. START FROM is also required and may appear in any order with the optional IN MEMORY and PARTITION BY clauses. These clauses all precede AS; the optional OWNED BY clause follows the query.

Parameters

Parameter	Description
`viewName`	Name for the live view
`IF NOT EXISTS`	Create only if a view with this name does not already exist
`FLUSH EVERY`	How often computed rows are persisted to disk. Required
`IN MEMORY`	Window of recent rows kept in RAM for fresh reads. Defaults to `FLUSH EVERY`
`PARTITION BY`	Partitioning unit for the view's disk tier. Defaults to the base table's scheme
`START FROM`	Inclusive event-time boundary: `NOW`, `BEGINNING`, or a timestamp literal. Required
`query`	A window-function `SELECT` over a single WAL-backed base table
`OWNED BY`	Assign ownership (Enterprise)

Clauses

FLUSH EVERY

FLUSH EVERY sets how often the view's computed rows are persisted from the in-memory tier to the view's own WAL-backed disk tier. It controls durability and write amplification, not read freshness: a direct SELECT reads the freshest computed rows regardless of the flush cadence.

A smaller interval persists more often, shortening crash recovery at the cost of more write volume. A larger interval reduces write volume but lengthens recovery and increases the staleness of the read shapes that are served from disk only (see Freshness).

The minimum is 100ms. The maximum is cairo.live.view.in.memory.max (60 minutes by default), because IN MEMORY defaults to FLUSH EVERY.

CREATE LIVE VIEW trades_ma
FLUSH EVERY 1s
START FROM NOW
AS
SELECT timestamp, symbol,
  avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS 300 PRECEDING)
    AS moving_avg
FROM trades;

IN MEMORY

IN MEMORY sets how long a window of recent output rows is retained in RAM to serve fast, fresh reads. Reads of recent data are served from the in-memory tier and older data from disk. It defaults to FLUSH EVERY.

IN MEMORY must be at least FLUSH EVERY and at most cairo.live.view.in.memory.max.

CREATE LIVE VIEW trades_ma
FLUSH EVERY 1s
IN MEMORY 5s
START FROM NOW
AS
SELECT timestamp, symbol,
  avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS 300 PRECEDING)
    AS moving_avg
FROM trades;

PARTITION BY

PARTITION BY sets the partitioning of the view's disk tier. If omitted, the view inherits the base table's partitioning scheme.

CREATE LIVE VIEW trades_ma
FLUSH EVERY 1s
PARTITION BY HOUR
START FROM NOW
AS
SELECT timestamp, symbol,
  avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS 300 PRECEDING)
    AS moving_avg
FROM trades;

START FROM

START FROM defines the inclusive event-time boundary for rows in the live view. It is mandatory and accepts:

NOW: resolve the engine clock once when the view is created.
BEGINNING: include all base-table history.
A quoted timestamp literal: include rows whose designated timestamp is equal to or later than that value.

The boundary applies to the base table's designated timestamp, not to commit time. QuestDB performs a resumable initial seed for qualifying rows already present at creation, then continues refreshing from new base commits.

CREATE LIVE VIEW trades_ma
FLUSH EVERY 1s
START FROM BEGINNING
AS
SELECT timestamp, symbol,
  avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS 300 PRECEDING)
    AS moving_avg
FROM trades;

Start from an explicit timestamp
CREATE LIVE VIEW trades_ma_from_april
FLUSH EVERY 1s
START FROM '2026-04-01T00:00:00.000000Z'
AS
SELECT timestamp, symbol,
  avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS 300 PRECEDING)
    AS moving_avg
FROM trades;

Anchored windows

An anchored window resets its functions on a boundary. Declare it in a named WINDOW with one of these forms:

ANCHOR DAILY 'HH:MM' [ 'timezone' ]
ANCHOR EXPRESSION expression

ANCHOR DAILY requires a quoted 24-hour time. An optional IANA time zone makes the reset follow local civil time; without one, the boundary is in UTC.

Cumulative daily volume, reset each day
CREATE LIVE VIEW trades_daily_volume
FLUSH EVERY 1s
START FROM NOW
AS
SELECT timestamp, symbol,
  sum(amount) OVER w AS cumulative_volume
FROM trades
WINDOW w AS (
  PARTITION BY symbol
  ORDER BY timestamp
  ANCHOR DAILY '00:00'
);

For example, ANCHOR DAILY '09:30' 'America/New_York' resets at the New York market open and follows daylight-saving transitions.

Anchor on an arbitrary expression
CREATE LIVE VIEW trades_hourly_volume
FLUSH EVERY 1s
START FROM NOW
AS
SELECT timestamp, symbol,
  sum(amount) OVER w AS bucket_volume
FROM trades
WINDOW w AS (
  PARTITION BY symbol
  ORDER BY timestamp
  ANCHOR EXPRESSION timestamp_floor('1h', timestamp)
);

An anchored window must:

Be a named window; ANCHOR is not supported in an inline OVER (...).
Use PARTITION BY with base-table columns directly.
ORDER BY the designated timestamp ascending.
Use the default unbounded frame; ANCHOR cannot be combined with a bounded ROWS or RANGE frame.

A live view supports at most one anchored window. An ANCHOR EXPRESSION must be deterministic, non-constant, and return TIMESTAMP, LONG, or INT.

Query constraints

The view query is validated at creation time and must:

Read a single WAL-backed base table that has a designated timestamp. No JOINs, subqueries, or CTEs.
Contain window functions that can be maintained incrementally (see supported functions).
Give every stateful window function a PARTITION BY clause.
Use a bounded ROWS or RANGE frame, or a named anchored window. Ranking functions (row_number, rank, and dense_rank) must be anchored. Frames starting at UNBOUNDED PRECEDING are rejected except for stateless last_value shapes.
Not use SAMPLE BY, GROUP BY, a top-level ORDER BY, or LIMIT in the view query. The ORDER BY inside a window's OVER (...) is required and allowed.
List output columns explicitly; wildcard projections such as SELECT * are not allowed.
Not filter on the base table's designated timestamp. Other deterministic WHERE predicates are supported.
Not use non-deterministic functions such as now(), sysdate(), systimestamp(), or rnd_*().
Not read another live view.

Complete example

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

Fully specified live view
CREATE LIVE VIEW IF NOT EXISTS trades_ma
FLUSH EVERY 1s
IN MEMORY 5s
PARTITION BY HOUR
START FROM BEGINNING
AS
SELECT
  timestamp,
  symbol,
  price,
  avg(price) OVER (
    PARTITION BY symbol
    ORDER BY timestamp
    ROWS 300 PRECEDING
  ) AS moving_avg
FROM trades;

This creates a view that:

Persists computed rows to disk every second (FLUSH EVERY 1s)
Keeps 5 seconds of recent rows in RAM for fresh reads (IN MEMORY 5s)
Partitions its disk tier by hour (PARTITION BY HOUR)
Includes all existing history in trades (START FROM BEGINNING)
Keeps a 300-row moving average of price per symbol

Metadata

Query view metadata with live_views():

SELECT view_name, base_table_name, view_status, lag_seqtxn
FROM live_views();

Permissions (Enterprise)

Creating a live view requires the database-level CREATE LIVE VIEW permission and SELECT on the base table:

Grant permission to create live views

GRANT CREATE LIVE VIEW TO user1;

Grant SELECT on the base table

GRANT SELECT ON trades TO user1;

When you create a live view you automatically receive all permissions on it, including DROP LIVE VIEW, with the GRANT option.

OWNED BY clause

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

CREATE GROUP analysts;
CREATE LIVE VIEW trades_ma
FLUSH EVERY 1s
START FROM NOW
AS
SELECT timestamp, symbol,
  avg(price) OVER (PARTITION BY symbol ORDER BY timestamp ROWS 300 PRECEDING)
    AS moving_avg
FROM trades
OWNED BY analysts;

Errors

Error	Cause
`live views are disabled`	Live-view support is turned off (`cairo.live.view.enabled=false`)
`live view already exists`	A live view of this name exists and `IF NOT EXISTS` was not specified
`table or view with the requested name already exists`	The name is taken by a table, view, or materialized view
`live view FLUSH EVERY must be at least 100ms`	The `FLUSH EVERY` interval is below the minimum
`live view select must be a simple scan of a single WAL base table; joins, subqueries, GROUP BY, ORDER BY and LIMIT are not supported yet`	The view query is not a simple scan of one base table
`base table must be a WAL table`	The base object is a non-WAL table or a regular view
`live views are not allowed as base tables in V1`	The base object is another live view
`live view base table must have a designated timestamp`	The base table has no designated timestamp
`wildcard column select is not allowed in live view queries`	The top-level projection contains `*`
`live view unbounded window must have an ANCHOR clause`	A stateful partitioned window uses the default unbounded frame without an anchor
`non-deterministic function cannot be used in live view`	The query uses `now()`, `rnd_*()`, or a similar non-deterministic function
`permission denied`	Missing required permission (Enterprise)

Syntax​

Parameters​

Clauses​

FLUSH EVERY​

IN MEMORY​

PARTITION BY​

START FROM​

Anchored windows​

Query constraints​

Complete example​

Metadata​

Permissions (Enterprise)​

OWNED BY clause​

Errors​

See also​