TimescaleDB working with continuous aggregate

Kien Nguyen kienngd@gmail.com included in category SQL

2023-05-25 2023-05-25 303 words 2 minutes

Contents

What is continuous aggregates

….

Working with continuous aggregates

Create a materialized view:

1
2
3
4
5
6
7


    CREATE MATERIALIZED VIEW daily_revenue_sum_by_reseller WITH (timescaledb.continuous) AS
    select time_bucket('1 day', "time") as timebucket,
        reseller_namespace, 
        sum(interval_amount) as daily_amount
    from billing_items bi
    group by timebucket, reseller_namespace
    WITH NO DATA;

Use WITH NO DATA; so you can create view instancely, don’t need to wait for refresh data. (With very large table, this will make problem with performace)

Manually refresh the view:

1

    CALL refresh_continuous_aggregate('daily_revenue_sum_by_reseller', '2021-07-17', '2021-07-20');

Add the policy:

1
2
3
4


    SELECT add_continuous_aggregate_policy('daily_revenue_sum_by_reseller',
        start_offset => INTERVAL '1 week',
        end_offset   => INTERVAL '1 hour',
        schedule_interval => INTERVAL '30 minutes');

List all table and views

1

    SELECT * FROM information_schema.tables WHERE table_schema = 'public';

List all jobs

1

    SELECT * FROM timescaledb_information.jobs;

Enable / disable job by id

1
2


    SELECT alter_job(<JOB_ID>, scheduled => false);
    SELECT alter_job(<JOB_ID>, scheduled => true);

Delete job
1

SELECT delete_job(1000);
Automatically created indexes if you define a continuous aggregate view with GROUP BY device, location, bucket, two composite indexes are created: one on {device, bucket} and one on {location, bucket}.

Turn off automatic index creation

1
2
3
4


    CREATE MATERIALIZED VIEW conditions_daily
    WITH (timescaledb.continuous, timescaledb.create_group_indexes=false)
    AS
    ...

Manual create index

1

    CREATE INDEX avg_temp_idx ON _timescaledb_internal._materialized_hypertable_2 (avg_temp);

With TimescaleDB < 2.7 you can’t create an index on an aggregated column.

How to find hypertable table name?

1
2
3


    SELECT view_name, format('%I.%I', materialization_hypertable_schema,
        materialization_hypertable_name) AS materialization_hypertable
    FROM timescaledb_information.continuous_aggregates;

Drop view:

1

    DROP MATERIALIZED VIEW daily_revenue_sum_by_reseller;

For an existing table, at the psql prompt, disable real time aggregation:

1

    ALTER MATERIALIZED VIEW table_name set (timescaledb.materialized_only = true);

Re-enable real time aggregation:

1

    ALTER MATERIALIZED VIEW table_name set (timescaledb.materialized_only = false);

Compress continuous aggregates

1
2


    ALTER MATERIALIZED VIEW cagg_name set (timescaledb.compress = true);
    ALTER MATERIALIZED VIEW cagg_name set (timescaledb.compress = false);