Materialized views without SAMPLE BY: latest state, filtered feeds and enrichment

[bluestreak01]

Summary

Materialized views that keep rows rather than time buckets: projection views that hold a filtered, column-selected mirror of a base table, latest-by views that hold the current row per key, and join views that enrich base rows with other tables. They use today's refresh model, reading from the base table once its WAL transactions have been applied, and refresh incrementally like SAMPLE BY views. Row-level retention keeps them bounded.

Current Limitations

• Materialized views require SAMPLE BY or a time-based GROUP BY; a view cannot simply keep rows
• The current state per key needs a LATEST ON scan over the base table on every read
• Enrichment without aggregation, such as an ASOF JOIN of trades with quotes, cannot be materialized
• There is no way to keep a bounded, continuously pruned copy of a table

Features

Feature	Description
Projection views	A filtered, column-selected mirror of the base table that refreshes incrementally as rows arrive
Latest-by views	One row per key holding the most recent state, maintained as data arrives instead of a LATEST ON scan per query
Join views	Base rows enriched through JOIN, including ASOF JOIN, with no SAMPLE BY required; the base table triggers refresh, and refresh from joined tables is tracked in #114
Row-level retention	EXPIRE ROWS on a view: expire by predicate, keep the latest row per key, or keep the N highest or lowest per group, with background reclamation where the predicate allows
Same tooling	Incremental refresh, TTL, SHOW CREATE MATERIALIZED VIEW, materialized_views() and replication behave as for existing views

How it works

1. The view declares its query; no SAMPLE BY is needed.
2. As WAL transactions are applied to the base table, the view refreshes incrementally over the new rows.
3. Reads over a view with a retention policy are filtered so expired rows disappear immediately, and disk is reclaimed in the background.

Example

Illustrative syntax:

-- projection: a filtered mirror of trades
CREATE MATERIALIZED VIEW aapl_trades AS (
  SELECT timestamp, price, amount FROM trades WHERE symbol = 'AAPL'
);

-- latest-by: current state per key, bounded by retention
CREATE MATERIALIZED VIEW positions AS (
  SELECT * FROM fills
) EXPIRE ROWS KEEP LATEST ON timestamp PARTITION BY account, symbol;

-- join: trades enriched with the prevailing quote
CREATE MATERIALIZED VIEW enriched_trades WITH BASE trades AS (
  SELECT t.timestamp, t.symbol, t.price, q.bid, q.ask
  FROM trades t ASOF JOIN quotes q ON (symbol)
);

Benefits

• Current state in one read — positions, last quotes and device status come from a view instead of a LATEST ON scan
• Bounded working sets — retention keeps a view to the rows that matter without touching the base table
• Enrichment materialized once — joins run as data arrives, not on every dashboard query
• The shapes push needs — these views are what subscriptions (Real-time push: subscribe to tables and views over QWP #111) attach to and what the ticking grid (Ticking grid for live blotters in Web Console notebooks #132) reads

Scope

• Projection views
• Latest-by views with KEEP LATEST retention
• Join views, including ASOF JOIN
• EXPIRE ROWS retention policies with background reclamation
• Permissions and replication support in QuestDB Enterprise

Implementation: questdb/questdb#7263. Related: #114 (refresh triggered by joined tables). Followed by #137, which moves all views onto the WAL-fed live view model.