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

Start with the implementation referenced as questdb/questdb#7263 and compare its scope with related issue #114. Review superseding issue #137 before choosing work, then verify whether projection, latest-by, join, retention, permissions, and replication requirements are covered.

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

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 (#111) attach to and what the ticking grid (#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.