Continuous aggregate view rebuilt at every query

TimescaleDB and PostgreSQL
1

I created this table:

-- enable timescale plugin
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-- create schema
CREATE SCHEMA IF NOT EXISTS {schemaName};

-- create table
CREATE TABLE IF NOT EXISTS {schemaName}.{tableName}
(
    ts           TIMESTAMP    NOT NULL,
    ticker       VARCHAR(16)  NOT NULL,
    m1           FLOAT4       NOT NULL,
    m5           FLOAT4       NOT NULL,
    m15          FLOAT4       NOT NULL,
    m30          FLOAT4       NOT NULL,
    h1           FLOAT4       NOT NULL,
    h2           FLOAT4       NOT NULL,
    h4           FLOAT4       NOT NULL,
    d1           FLOAT4       NOT NULL,
    high         FLOAT4       NOT NULL,
    vwap         FLOAT4       NOT NULL,
    low          FLOAT4       NOT NULL
);

-- create hypertable
SELECT create_hypertable('{schemaName}.{tableName}', 'ts', create_default_indexes => false, chunk_time_interval => INTERVAL '1 day', if_not_exists => TRUE);

-- set autovacum
ALTER TABLE {schemaName}.{tableName} SET (autovacuum_enabled = on);

-- create index
CREATE INDEX IF NOT EXISTS idx_kvwap_ticker_ts ON {schemaName}.{tableName}(ticker, ts DESC);

Then I create the views:

stringBuffer {
        for i in intervals do
            yield
                $"
                \n
                CREATE MATERIALIZED VIEW IF NOT EXISTS {schemaName}.kvwap_{i.ShortString.ToLower()}
                WITH (timescaledb.continuous) AS
                (
                    SELECT
                        time_bucket(INTERVAL '{i.TotalSeconds} second', ts) AS ts_bucket,
                        min(ticker)                  AS ticker,
                        round(avg(m1)::decimal,  8)  AS m1,
                        round(avg(m5)::decimal,  8)  AS m5,
                        round(avg(m15)::decimal, 8)  AS m15,
                        round(avg(m30)::decimal, 8)  AS m30,
                        round(avg(h1)::decimal,  8)  AS h1,
                        round(avg(h2)::decimal,  8)  AS h2,
                        round(avg(h4)::decimal,  8)  AS h4,
                        round(avg(d1)::decimal,  8)  AS d1,
                        min(low)                     AS low,
                        round(avg(vwap)::decimal, 8) AS vwap,
                        max(high)                    AS high
                    FROM
                        {schemaName}.kvwap
                    GROUP BY ticker, ts_bucket
                    ORDER BY ts_bucket
                );

                CREATE INDEX IF NOT EXISTS idx_kvwap_ticker_ts_{i.ShortString.ToLower()}
                ON {schemaName}.kvwap_{i.ShortString.ToLower()} (ticker, ts_bucket);
                "

    }

It will get new entries every minute.

When I do a simple query like:

SELECT * FROM table LIMIT 500;

it will always take 2 minutes to reply, while reading in the source table is ‘instant’.

1 Like
2

Hello!

What version of TimescaleDB are you on?

Aside from that you shouldn’t use min(ticker) and then group by ticker, you should just have the ticker and group by it, otherwise it’s going to mean that some of your filters are going to be quite inefficient for no reason. And could cause at least some of these problems.

It is possible that some of this is being redone at query time, even on newer versions of Timescale, on older versions it was definitely slower. If you want it extremely fast, you can try a materialized only continuous aggregate.

2 min is a long time though, it shouldn’t take that long, but with the old version of continuous aggregates that might have happened at least sometimes.

3

We’re using the latest version all the time.

How can I find out what causes the rebuild every time?

We’re capturing new data every minute, so my goal is to have this data aggregated into time bins (hourly, daily, etc.). What is a materialized continuous aggregate?

thanks for the help!

4

Have you made a continuous aggregate policy? Timescale Docs And if you set it to materialized only it will set it to only use the data that has been materialized rather than getting data from the raw hypertable that hasn’t yet been materialized.

5

Thanks David, I hadn’t made a policy