Can't create 1 minute time bucket on historic data

TimescaleDB and PostgreSQL
1

Hello,
I have bulk loaded 100GB of history of financial ticks in TimescaleDb and I want to create minute, hour and day candle time buckets. I executed the commands from this tutorial

CREATE MATERIALIZED VIEW one_min_candle
WITH (timescaledb.continuous) AS
    SELECT
        time_bucket('1 min', time) AS bucket,
        symbol,
        FIRST(bid, time) AS "open",
        MAX(bid) AS high,
        MIN(bid) AS low,
        LAST(bid, time) AS "close",
        LAST(volume, time) AS day_volume
    FROM ticks
    GROUP BY bucket, symbol

but it freezes. Can someone give me recommendations on how to create the buckets? I will have to bulk insert data for the last 1-2 years, which will be terrabytes of data.

2

Hello and welcome to our community!

Firstly what is your current setting for work_mem (show work_mem from psql)?

Secondly can you elaborate on what you mean by freezes? How long have you left the statement for? If you look at the output of top can you see the session performing work?

Hopefully we can make some progress on this one soon and get you working again.

3

Hi James,
Currently work_mem shows 9440kB. We are running timescaledb-single in Kubernetes. We also set these parameters:

service:
  primary:
    type: LoadBalancer
persistentVolumes:
  wal:
    size: 50Gi
  data:
    size: 150Gi

By freezing I meant “hangs”. I deleted the data and inserted only 30GB and now it returns
SQL Error [53400]: ERROR: temporary file size exceeds temp_file_limit (1048576kB)

4

Hi Miroslav, please try to create your materialized view WITH NO DATA option, and then call refresh_continuous_aggregate with a period that works with your machine configuration.

5

I created the view “WITH NO DATA”. refresh_continuous_aggregate works for a 2-3 day interval. But with 5 days interval or more it throws temporary file size exceeds temp_file_limit. Is there a way to increase the temp_file_limit?

6

Yes! I guess you can change it here in the postgres > parameters session. You can also test the best one setting it in the session directly like this post explains.

7

Hi, my one_day_candle is not refreshing. I call the refresh_continuous_aggregate with interval of 2 days, the query hangs and I get SQL Error [08006]: An I/O error occurred while sending to the backend. after 5min. This is our configuration:

wal size: 50GB
temp_file_limit: 5GB
work_mem: 32MB
shared_buffers: 4GB (1/4 of 16GBRAM).
chunk_time_interval: 1hour - we get ~1GB new live data per hour.*

*Following the recommendation for chunk_time_interval “latest chunks should fit in the size of shared_buffers”

8

Can you share the query you’re using for this one_day_candle? is it built from the raw data?

Can you build it from the hour candlestick or some other pre-aggregated data to avoid fetching/reprocessing all the data?

9 
CREATE MATERIALIZED VIEW one_day_candle
WITH (timescaledb.continuous) AS
    SELECT
        time_bucket('1 day', time) AS bucket,
        symbol,
        FIRST(bid, time) AS "open_bid",
        FIRST(ask, time) AS "open_ask",
        LAST(bid, time) AS "close_bid",
        LAST(ask, time) AS "close_ask",
        MAX(bid) AS high_bid,
        MAX(ask) AS high_ask,
        MIN(bid) AS low_bid,
        MIN(ask) AS low_ask,
        LAST(volume, time) AS day_volume
    FROM ticks
    GROUP BY bucket, symbol
WITH NO DATA;

Yes, the query is built from raw data.

10

I tried creating it from one_hour_candle, but it throws SQL Error [0A000]: ERROR: invalid continuous aggregate view. Query:

CREATE MATERIALIZED VIEW one_day_candle
WITH (timescaledb.continuous) AS
    SELECT
        time_bucket('1 day', bucket) AS bucket,
        symbol,
        FIRST(open_bid, bucket) AS "open_bid",
        FIRST(open_ask, bucket) AS "open_ask",
        LAST(close_bid, bucket) AS "close_bid",
        LAST(close_ask, bucket) AS "close_ask",
        MAX(high_bid) AS "high_bid",
        MAX(high_ask) AS "high_ask",
        MIN(low_bid) AS "low_bid",
        MIN(low_ask) AS "low_ask",
        LAST(day_volume, bucket) AS "day_volume"
    FROM one_hour_candle
    GROUP BY bucket, symbol
WITH NO DATA;
11

Hi Miroslav, thanks for sharing the queries.

I missed saying that you cannot materialize a continuous aggregates on top of another continuous aggregate until Timescale 2.9 (which will be released soon).

For now, I’d try to create views with no continuous aggregates nor materialized based on one hour. Probably they’ll be fast as they’ll need to fall into just a smaller subset of the records.

Soon it will be released and you can also materialize it.

1 Like
12

Thanks for the response.
We deleted the materialized views, but the read queries are still taking 5-6 seconds to execute

SELECT bucket, open_bid, close_bid, high_bid, low_bid FROM {bucketName} WHERE symbol = $1 AND bucket >= $2 AND bucket <= $3 ORDER BY bucket ASC

We also updated to TimescaleDb 2.9.0. I tried with hierarchical caggs, but they are still slow. What can I do to optimize ?

Regards

13

Hi Miroslav, can you share a few metadata about your scenario?

  • What is your chunk_time_interval for the hypertable?
  • What is the data cardinality? How many symbols do you have?
  • How many records do you have per second?
  • What hardware are you running it on?
  • Can you share an EXPLAIN ANALYSE from your query?

I’d strongly suggest you to try to isolate a minimal reproducible case using the new hierarchical caggs system and create an issue in the Timescaledb github repository to allow the developers to take a deep look at the scenario.

14 
image:
  tag: pg14.6-ts2.9.0-p0
service:
  primary:
    type: LoadBalancer
persistentVolumes:
  wal:
    size: 50Gi
  data:
    size: 1Ti
nodeSelector:
  role: workers-prod
patroni:
  bootstrap:
    dcs:
      postgresql:
        parameters:
          temp_file_limit: 5GB
          work_mem: 32MB

QUERY PLAN
Sort  (cost=10587.70..10608.29 rows=8236 width=40) (actual time=37.827..38.170 rows=9252 loops=1)
  Sort Key: "*SELECT* 1".bucket
  Sort Method: quicksort  Memory: 1107kB
  ->  Append  (cost=3.18..10052.04 rows=8236 width=40) (actual time=0.058..35.778 rows=9252 loops=1)
        ->  Subquery Scan on "*SELECT* 1"  (cost=3.18..7935.96 rows=8036 width=40) (actual time=0.057..35.087 rows=9252 loops=1)
              ->  Custom Scan (ChunkAppend) on _materialized_hypertable_2  (cost=3.18..7855.60 rows=8036 width=112) (actual time=0.057..34.222 rows=9252 loops=1)
                    Chunks excluded during startup: 0
                    ->  Bitmap Heap Scan on _hyper_2_64_chunk  (cost=3.18..120.50 rows=117 width=49) (actual time=0.055..0.348 rows=117 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=117
                          ->  Bitmap Index Scan on _hyper_2_64_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..3.15 rows=117 width=0) (actual time=0.036..0.036 rows=234 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_65_chunk  (cost=15.15..630.97 rows=605 width=49) (actual time=0.233..2.136 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_65_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..15.00 rows=605 width=0) (actual time=0.156..0.156 rows=1200 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_66_chunk  (cost=15.52..675.78 rows=629 width=48) (actual time=0.233..2.469 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_66_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..15.36 rows=629 width=0) (actual time=0.154..0.154 rows=1200 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_67_chunk  (cost=15.09..627.94 rows=601 width=49) (actual time=0.210..2.065 rows=594 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=594
                          ->  Bitmap Index Scan on _hyper_2_67_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..14.94 rows=601 width=0) (actual time=0.147..0.147 rows=713 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Seq Scan on _hyper_2_70_chunk  (cost=0.00..4.90 rows=116 width=47) (actual time=0.014..0.034 rows=116 loops=1)
                          Filter: ((bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone) AND (symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)))
                    ->  Seq Scan on _hyper_2_71_chunk  (cost=0.00..24.00 rows=546 width=47) (actual time=0.007..0.420 rows=545 loops=1)
                          Filter: ((bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone) AND (symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)))
                          Rows Removed by Filter: 55
                    ->  Bitmap Heap Scan on _hyper_2_535_chunk  (cost=14.33..587.88 rows=551 width=48) (actual time=0.175..2.212 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_535_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..14.19 rows=551 width=0) (actual time=0.115..0.115 rows=600 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_536_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_536_chunk  (cost=0.43..390.89 rows=356 width=48) (actual time=0.044..1.263 rows=359 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_537_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_537_chunk  (cost=0.29..2.51 rows=1 width=47) (actual time=0.015..0.016 rows=0 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_538_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_538_chunk  (cost=0.29..2.51 rows=1 width=47) (actual time=0.010..0.010 rows=0 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_540_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_540_chunk  (cost=0.29..2.51 rows=1 width=47) (actual time=0.022..0.022 rows=0 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_541_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_541_chunk  (cost=0.29..2.51 rows=1 width=47) (actual time=0.013..0.013 rows=0 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_542_chunk  (cost=9.15..369.59 rows=356 width=49) (actual time=0.105..1.378 rows=354 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=354
                          ->  Bitmap Index Scan on _hyper_2_542_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..9.06 rows=356 width=0) (actual time=0.063..0.063 rows=354 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_543_chunk  (cost=14.89..617.24 rows=588 width=49) (actual time=0.195..2.668 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_543_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..14.75 rows=588 width=0) (actual time=0.134..0.134 rows=600 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_544_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_544_chunk  (cost=0.43..578.99 rows=529 width=48) (actual time=0.033..2.076 rows=591 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_545_chunk  (cost=15.43..647.34 rows=623 width=49) (actual time=0.160..2.217 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_545_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..15.27 rows=623 width=0) (actual time=0.100..0.100 rows=600 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_546_chunk  (cost=15.61..682.34 rows=635 width=48) (actual time=0.173..2.417 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_546_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..15.45 rows=635 width=0) (actual time=0.113..0.113 rows=600 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_547_chunk  (cost=15.09..627.93 rows=601 width=49) (actual time=0.187..2.378 rows=587 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=587
                          ->  Bitmap Index Scan on _hyper_2_547_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..14.94 rows=601 width=0) (actual time=0.123..0.123 rows=587 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_548_chunk  (cost=15.58..667.91 rows=633 width=48) (actual time=0.182..2.453 rows=600 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=600
                          ->  Bitmap Index Scan on _hyper_2_548_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..15.42 rows=633 width=0) (actual time=0.122..0.122 rows=600 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Bitmap Heap Scan on _hyper_2_549_chunk  (cost=12.94..572.55 rows=532 width=48) (actual time=0.171..2.143 rows=590 loops=1)
                          Recheck Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                          Heap Blocks: exact=590
                          ->  Bitmap Index Scan on _hyper_2_549_chunk__materialized_hypertable_2_symbol_bucket_idx  (cost=0.00..12.81 rows=532 width=0) (actual time=0.113..0.113 rows=590 loops=1)
                                Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_551_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_551_chunk  (cost=0.42..7.15 rows=5 width=72) (actual time=0.045..2.043 rows=600 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
                    ->  Index Scan using _hyper_2_552_chunk__materialized_hypertable_2_symbol_bucket_idx on _hyper_2_552_chunk  (cost=0.43..11.65 rows=9 width=72) (actual time=0.039..2.171 rows=599 loops=1)
                          Index Cond: ((symbol = 'EURUSD'::text) AND (bucket < COALESCE(_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(2)), '-infinity'::timestamp with time zone)) AND (bucket >= '2022-11-21 09:04:13.524+00'::timestamp with time zone) AND (bucket <= '2022-12-21 11:04:13.524+00'::timestamp with time zone))
        ->  Subquery Scan on "*SELECT* 2"  (cost=2069.41..2074.91 rows=200 width=40) (actual time=0.004..0.005 rows=0 loops=1)
              ->  HashAggregate  (cost=2069.41..2072.91 rows=200 width=112) (actual time=0.003..0.004 rows=0 loops=1)
                    Group Key: time_bucket('00:01:00'::interval, ticks."time"), ticks.symbol
                    Batches: 1  Memory Usage: 40kB
                    ->  Custom Scan (ChunkAppend) on ticks  (cost=0.30..2062.03 rows=492 width=32) (actual time=0.000..0.000 rows=0 loops=1)
                          Chunks excluded during startup: 492
Planning Time: 186.866 ms
Execution Time: 43.710 ms
15

Hi JĂ´natas,
We are still stuck with this issue.

16

Hi all!
There is a proposal to add a new parameter to the refresh_continuous_aggregate() and add_continuous_aggregate_policy() functions, which will regulate the execution of a commit when a limit is reached, for example, 10000 rows or temp_file_limit - 50%.