Live views

A live view is a QuestDB table that stores the incrementally maintained result of a window-function query over a single base table. As new rows arrive in the base table, the window functions run once per new row and the output is appended to the view. Querying the live view then scans precomputed rows instead of reprocessing the base table on every read.

Live views target workloads where the same window aggregate is read frequently against high-rate ingestion: rolling VWAP, cumulative volume, running ranks, or day-over-day comparisons that would otherwise recompute a window over millions of rows on each query.

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.

Live views vs materialized views​

Both feature types pre-compute a query and refresh it incrementally, but they serve different query shapes:

AspectLive viewMaterialized view
Query shapeWindow functions (OVER)SAMPLE BY / time-based GROUP BY
Output cardinalityOne row per base rowOne row per time bucket
Typical useRunning totals, moving averages, rankingsOHLC bars, downsampled summaries
Base tablesA single WAL-backed tableOne or more tables (JOINs allowed)
Freshness / durabilityFLUSH EVERY, IN MEMORYREFRESH strategy

Use a materialized view when you want to aggregate rows into time buckets. Use a live view when you want to keep a row-per-input result of a window computation.

Quick example​

Given a trades table of incoming trades:

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

Create a live view that keeps a 300-row moving average of price per symbol:

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

Query it like any table:

Query the live view
SELECT * FROM trades_ma;

The view updates incrementally as new rows arrive in trades. Each new trade produces one output row carrying its moving average. A direct SELECT of the full output rows sees data as soon as it is refreshed. Filtering a read to a timestamp interval (for example WHERE timestamp IN '$today') is served from the disk tier and can trail by up to one FLUSH EVERY interval; see Freshness.

How live views work​

A live view is its own WAL-backed table maintained by a background refresh worker. The worker reads new committed rows from the base table and runs the view's window functions over them, appending the output. Two independent cadences govern how that output becomes visible and durable:

  • Refresh runs continuously. As the worker computes output rows, it appends them to an in-memory tier. This is what keeps the view fresh.
  • Flush runs on the FLUSH EVERY cadence. It persists the in-memory rows to the live view's own WAL-backed disk tier and advances a durability checkpoint.

Reads combine both tiers. Recent rows are served from the in-memory tier and the older prefix from disk, so a query sees the freshest computed rows without waiting for a flush.

Show the live view definition
SHOW CREATE LIVE VIEW trades_ma;

Freshness​

Because refresh publishes to the in-memory tier ahead of flush, a direct SELECT that reads the full output rows sees data as soon as it is refreshed. This is independent of FLUSH EVERY, which is a durability and write-amplification control, not a freshness control.

Some read shapes are served from the disk tier only and therefore trail by up to one FLUSH EVERY interval:

  • Reads that project or aggregate the view's columns rather than reading full output rows
  • Reads filtered to a timestamp interval
  • A live view used as the right-hand side of an ASOF JOIN

Keep FLUSH EVERY small (for example 1s) so this lag stays negligible.

tip

A live view falling behind sustained ingestion stays correct but grows stale. There is no automatic throttle. See Monitoring for how to interpret lag and detect a view that cannot keep up.

Supported window functions​

Live views maintain window functions whose result can be computed incrementally in a single forward pass and whose state can be checkpointed:

  • Anchored ranking: row_number, rank, dense_rank
  • Bounded or anchored aggregates: sum, avg, count, min, max, ksum, first_value, last_value, nth_value
  • Offset: lag
  • Statistics: variance, stddev, covariance, correlation, EMA, and VWEMA

Stateful window functions must have a PARTITION BY clause. Both bounded ROWS and bounded RANGE frames are supported. Ranking functions must use an anchored window because an out-of-order row would otherwise change every later rank.

String, VARCHAR, BINARY, ARRAY, and SYMBOL columns can appear as pass-through output columns and as count arguments, but there are no string- or array-valued window functions.

The following shapes cannot be maintained by an append-only incremental refresh and are rejected at creation time:

  • Multi-pass or look-ahead functions: percent_rank, cume_dist, ntile, lead
  • Stateful window functions without PARTITION BY
  • Unanchored ranking functions
  • Frames that start at UNBOUNDED PRECEDING, except stateless last_value shapes

Anchored windows​

An anchored window resets its cumulative aggregate on a boundary, which is useful for running totals that restart each day or on a period boundary. Declare it in a named window with either ANCHOR DAILY 'HH:MM' ['timezone'] or an ANCHOR EXPRESSION clause:

Cumulative daily volume per symbol
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'
);

ANCHOR DAILY resets at the specified wall-clock time. Without a time zone, the time is interpreted in UTC; add an IANA time zone such as 'America/New_York' when the boundary follows local civil time.

An anchored window must be a named window, must partition by base-table columns, must order by the designated timestamp ascending, and cannot use a bounded frame. A live view supports at most one anchored window. An anchor expression must be deterministic and return TIMESTAMP, LONG, or INT.

Start boundary and historical data​

Every live view has a mandatory START FROM clause that defines an event-time lower bound for its rows:

  • START FROM NOW resolves to the creation time.
  • START FROM BEGINNING includes all base-table history.
  • START FROM 'timestamp' includes rows at or after the specified timestamp.

The boundary is inclusive and is evaluated against the base table's designated timestamp, not commit time. If qualifying rows already exist when the view is created, QuestDB seeds them before switching to continuous refresh.

Include all existing history
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;

The seed sweep is resumable: it checkpoints its progress and continues after a restart. START FROM NOW can still seed existing future-dated rows whose designated timestamps are at or above the resolved creation-time boundary.

Base table lifecycle​

A live view is tied to a single WAL-backed base table and tracks the exact set of base columns its query references:

  • Changes to columns the view does not reference pass through transparently and the view keeps refreshing.
  • Dropping, renaming, or changing the type of a referenced column invalidates the view.
  • Renaming or dropping the base table invalidates the view.
  • DROP PARTITION, TRUNCATE, and base TTL eviction freeze the already-emitted rows and the view continues forward from where it was.

An invalidated view keeps serving its existing data and reports the reason in live_views(). It stops refreshing. Invalidation is permanent: reversing the schema change does not automatically revalidate the view, and ALTER LIVE VIEW ... RESUME WAL only recovers a suspended WAL writer.

To recover, inspect invalidation_reason, repair the base-table schema, and save the definition before dropping the view:

SHOW CREATE LIVE VIEW trades_ma;

Then drop and recreate the live view. Use START FROM BEGINNING or an explicit timestamp if it must include history that is still present in the base table. Dropping the invalid view removes its materialized rows, including rows whose source history is no longer retained by the base table.

Live views over deduplicated base tables are supported. A keep-last UPSERT replacement at an earlier timestamp is reflected in the view. A view over a deduplicated base is one FLUSH EVERY cycle behind rather than sub-cycle fresh, because its refresh is coupled to base apply.

Monitoring​

The live_views() function exposes the state, refresh lag, in-memory footprint, and seed progress of every live view:

List all live views
SELECT view_name, base_table_name, view_status, lag_seqtxn, lag_micros
FROM live_views();

lag_seqtxn is the number of committed base-table WAL transactions beyond the view's durable processed watermark. It counts transactions, not rows: one transaction may contain one row or millions. A value of 0 means that the durable tier is caught up. A temporary non-zero value is expected between FLUSH EVERY cycles, especially when ingestion produces many small commits.

There is no universal acceptable non-zero value. Sample the metric over time and compare it with the view's normal flush-cycle baseline. A bounded sawtooth that returns to zero around flushes is normal. A value that stays elevated for multiple flush intervals or keeps increasing indicates that the view cannot keep up.

lag_micros reports the elapsed time since the last successful flush. It is a flush-activity indicator, not the timestamp difference between base and view rows, and can continue growing while an idle view has lag_seqtxn = 0.

When lag grows persistently, check view_status and writer_stall_micros for a failed or blocked flush. Also verify CPU and I/O capacity, reduce the number or cost of maintained views, or increase the shared mat.view.refresh.worker.count setting.

For out-of-order repair cost, compare o3_replay_scan_rows, o3_resume_replay_rows, and o3_boundary_replay_rows. The first counts base rows scanned; the other two split rows emitted by the resume-from-checkpoint and rebuild paths. checkpoint_repair_plan reports whether the view has a finite range, rows, or anchor repair plan. The last disposition and denial columns show which path actually ran and why a local rebuild was not selected. These counters reset on restart.

The checkpoint_timeline_* columns describe the persistent timeline used for restart and out-of-order repair. In particular, checkpoint_timeline_sharing_ratio shows how effectively checkpoint state is shared, while checkpoint_gc_lag_generations and checkpoint_obsolete_segment_bytes can expose delayed cleanup. See live_views() for the complete catalog.

Live views also appear in tables() with table_type = 'L', and are recognized by SHOW CREATE LIVE VIEW, EXPLAIN, pg_class, and information_schema.tables.

Limitations​

Live views have a deliberately narrow surface in this first version. Statements outside it are rejected at creation time with a specific error:

Base objectDerived objectSupported
Live viewRegular viewYes
Live viewMaterialized viewNo
Live viewLive viewNo
Regular viewLive viewNo; a regular view is not a WAL table
Materialized viewLive viewYes

A full rebuild of a materialized view invalidates a live view that uses it as its base. Recreate the live view after the rebuild, following the recovery steps in Base table lifecycle.

  • Single base table only. No JOINs, subqueries, or CTEs in the view query.
  • Explicit output columns only. SELECT * and other wildcard projections are rejected because the output schema is fixed when the view is created.
  • No pre-aggregation. SAMPLE BY and GROUP BY are not allowed between the base table and the window functions. A view like "5-minute candles with a rolling VWAP" must pre-aggregate upstream.
  • No designated-timestamp filter. A WHERE clause may filter other columns, but cannot filter the base table's designated timestamp yet.
  • Deterministic queries only. Non-deterministic functions such as now(), sysdate(), systimestamp(), and rnd_*() are rejected in the projection, the WHERE filter, and window-function arguments.
  • No TTL on the view. Live-view disk growth is unbounded in this version. Size retention on the base table instead.

Tradeoffs​

  • Storage grows with output. The computed rows are stored on the live view's disk tier in addition to the base table's rows. For wide projections or long retention the view's footprint can exceed the base table.
  • No admission control. A view that cannot keep up with ingestion stays correct but stale, with no automatic throttle or drop.
  • Per-partition state for partitioned windows grows with distinct partition cardinality. A base table with high-cardinality partition keys (UUIDs, session ids) holds state per key. Use cairo.live.view.refresh.memory.limit.bytes to bound the per-view refresh footprint. The in_mem_bytes column in live_views() separately reports the peak-sticky capacity of the recent-row tier.

Enterprise features​

QuestDB Enterprise adds access control, replication, and backup support for live views.

Permissions​

Two dedicated permissions govern live-view DDL, modelled on the materialized-view permissions:

  • CREATE LIVE VIEW is a database-level permission.
  • DROP LIVE VIEW is checked against the target view.

Querying a live view uses the standard table-level SELECT permission, since a live view is a regular table token. See Role-based access control for the full permission model.

Replication​

Live-view definitions replicate, but their derived rows do not. Every node refreshes and flushes its own node-local live-view table:

  • The primary refreshes directly from the base table's WAL.
  • A read-only replica refreshes from its locally applied copy of the base table.
  • Live-view WAL is not uploaded or transferred between nodes.

A role switch continues the local refresh state; it does not reconstruct or transfer the former primary's live-view rows. Replica freshness therefore also depends on base-table replication and apply lag.

Backup and restore​

A live view is captured by the object-store backup like a materialized view: its table data rides the standard table path and its definition sidecars are carried in the backup manifest. After restore, unflushed rows are re-derived from the base table.