Hacktoberfest 2026: le issue che i maintainer hanno segnato per ottobre, aperte e adatte ai principianti. Sfoglia le issue Hacktoberfest

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

Aperta
  • Qualcosa non va?
#136 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
20/100
Tipo di issue
Funzionalità
Chiarezza
Da chiarire
Stato di attività
Ferma
Stack tecnologico
sql
Ambito
databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

open source SQL

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 (#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.

Lingua principale
Nessun dato sulla lingua
Stelle
7
Fork
0
Nessuna PR unita negli ultimi 30g

Preparare l'ambiente

Questo progetto non fornisce container di sviluppo, Dockerfile né guida per i contributori, quindi l'ambiente è a tuo carico: parti dal suo README e consulta la nostra guida al primo contributo per i passaggi generali.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di questdb/roadmap

Tutte le issue di questdb/roadmap

Issue simili

Altre issue su Databases

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.