Creating continuous aggregates

Now that you've been introduced to continuous aggregates, create your own continuous aggregate from your data.

Create an aggregate query to use in your continuous aggregate

The data we are using for this tutorial is second-by-second (tick) data for stock trades. A popular aggregate pattern used for analyzing stock data is called a candlestick. Generally, candlestick charts use 4 different aggregations over a specific interval of time (for example, 1-minute, 5-minute, or 1-day aggregates):

• high: highest stock price per interval
• open: opening stock price per interval
• close: closing stock price per interval
• low: lowest stock price per interval

For this example query, the time_bucket() interval is 1 day. The high and low values can be found by using the PostgreSQL MAX() and MIN() functions. Finally, the open and close values can be found by using the first() and last() functions.

Create continuous aggregate from aggregate query

Now that you have the aggregation query, you can use it to create a continuous aggregate.

The CREATE MATERIALIZED VIEW command triggers the database to create a materialized view with the given name, in this case stock_candlestick_daily. In the next line, WITH (timescaledb.continuous) instructs TimescaleDB to create a continuous aggregate and not just a generic materialized view. Finally, the query from above is added after the AS keyword.

Notice that the SELECT statement is the same query you wrote earlier without the ORDER BY clause. By default, this code both creates the aggregate and materializes the aggregated data. That means the view is created and populated with the aggregate calculations from your existing hypertable data.

The query may take some time to run because it needs to perform these calculations across all of your stock trade data the first time. After the calculation results are stored, querying the data from the continuous aggregate is much faster.

Run the following query to get all the data in your continuous aggregate, and note how much faster this is than running the aggregate SELECT query on the raw hypertable data.

SELECT * FROM stock_candlestick_daily
ORDER BY day DESC, symbol;

Real-time continuous aggregates

By default, all continuous aggregates are created as real-time aggregates. This means that TimescaleDB always appends (or UNION) recent data that has not yet been materialized through a refresh policy to the output of the continuous aggregate. In the diagram above that corresponds to the last three points of raw data, which belong to an incomplete bucket.

SELECT * FROM timescaledb_information.continuous_aggregates;

hypertable_schema|hypertable_name |view_schema|view_name     |view_owner|materialized_only|compression_enabled|materialization_hypertable_schema|materialization_hypertable_name|view_definition                                                                                                                                                                                                                                                |
-----------------+----------------+-----------+--------------+----------+-----------------+-------------------+---------------------------------+-------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
public           |stocks_real_time|public     |candle_one_min|tsdbadmin |false            |false              |_timescaledb_internal            |_materialized_hypertable_3     | SELECT time_bucket('00:01:00'::interval, stocks_real_time."time") AS bucket,¶    stocks_real_time.symbol,¶    first(stocks_real_time.price, stocks_real_time."time") AS open,¶    max(stocks_real_time.price) AS high,¶    min(stocks_real_time.price) AS low,|

Next Steps

Now that your continuous aggregate is created, the next step is to create a continuous aggregate refresh policy.

Without an automatic refresh policy, your continuous aggregate won't materialize new data as it is inserted into the stocks_real_time hypertable. As mentioned before, when you query your continuous aggregate, TimescaleDB performs real-time aggregation to include any unmaterialized data. As the amount of unmaterialized data grows, this can slow down your queries.

With a continuous aggregate policy, your new data automatically materializes into your continuous aggregate, keeping the need for real-time computations low and your continuous aggregate queries efficient.

Learn more about continuous aggregates

See how real TimescaleDB users leverage continuous aggregates in the blog posts How FlightAware fuels flight prediction models for global travelers with TimescaleDB and Grafana and How I power a (successful) crypto trading bot with TimescaleDB.

Detailed information on continuous aggregates and real-time aggregation can be found in the continuous aggregates docs.