Materialized views

info

Materialized View support is in beta. It may not be fit for production use.

Please let us know if you run into issues. Either:

  1. Email us at support@questdb.io
  2. Join our public Slack
  3. Post on our Discourse community

A materialized view is a special QuestDB table that stores the pre-computed results of a query. Unlike regular views, which compute their results at query time, materialized views persist their data to disk, making them particularly efficient for expensive aggregate queries that are run frequently.

What are materialized views for?​

Let's say that your application is ingesting vast amounts of time series data. Soon your QuestDB instance will grow from gigabytes to terabytes.

trades ddl
CREATE TABLE 'trades' ( 
	symbol SYMBOL,
	side SYMBOL,
	price DOUBLE,
	amount DOUBLE,
	timestamp TIMESTAMP
) TIMESTAMP(timestamp) PARTITION BY DAY;

Queries that rely on a specific subset of the data (say, the last hour) will continue to run fast, but anything that requires scanning large numbers of rows or the entire dataset will begin to slow down.

One of the most common queries for time series data is the SAMPLE BY query. This query is used to aggregate data into time-window buckets. Here's an example that can analyze trade volumes by the minute, broken down by symbol.

SAMPLE BY query
SELECT
  timestamp,
  symbol,
  side,
  sum(price * amount) AS notional
FROM trades
SAMPLE BY 1m;

Each time this query is run it will scan the entire dataset. This type of query will become slower as the dataset grows. Materialized views run the query only on small subset of rows of the base table each time when new rows are inserted. In other words, materialized views are designed to maintain the speed of your queries as you scale your data.

When you create a materialized view you register your time-based grouping query with the QuestDB database against a base table.

sampling into a materialized view

Conceptually a materialized view is an on-disk table tied to a query: As you add new data to the base table, the materialized view will efficiently update itself. You can then query the materialized view as a regular table without the impact of a full table scan of the base table.

Creating a materialized view​

To create a materialize view, surround your SAMPLE BY or time-based GROUP BY query with a CREATE MATERIALIZED VIEW statement.

trades_notional_1m ddl
CREATE MATERIALIZED VIEW 'trades_notional_1m' AS (
  SELECT
    timestamp,
    symbol,
    side,
    sum(price * amount) AS notional
  FROM trades
  SAMPLE BY 1m
) PARTITION BY DAY;

Querying a materialized view can be up to hundreds of times faster than executing the same query on the base table.

querying a materialized view
SELECT *
FROM trades_notional_1m;

Roadmap and limitations​

We aim to expand the scope materialized view over time. For now, the feature focuses on time-based aggregations. It currently supports JOIN operations, but does not yet support all query types.

Continue learning​