Stack Overflow
Stack Overflow

Questions tagged [continuous-aggregates]

17 questions
6
votes
1 answer

Real-Time aggregation not up to date

I am experiencing real time aggregation not to be up to date in real time. Is there something I am missing? A reproducible example on version 2.4.2 using the current docker image timescale/timescaledb:latest-pg12: CREATE TABLE data ( time …
Pasukaru
  • 1,050
  • 1
  • 10
  • 22
5
votes
1 answer

Very slow continuous aggregate on large hypertable

I am using the latest docker version of Postgres 14.3 and Timescale 2.7.0. I am running some benchmarks to make sure timescaledb is the right solution for my client. I have a hypertable with 50 million rows. These were inserted in (approximately)…
jbx
  • 21,365
  • 18
  • 90
  • 144
4
votes
1 answer

Does timescaledb support window functions?

I am trying to use the TimescaleDB extension to compute some continuous aggregates. I have this query which works fine: SELECT distinct time_bucket('1 hour', entry_ts) as date_hour, type_id, entry_id, …
jbx
  • 21,365
  • 18
  • 90
  • 144
2
votes
0 answers

TimescaleDB Continuous Aggregates WITH NO DATA not working

I use this query to create a continuous aggregate on one of my hypertables: CREATE MATERIALIZED VIEW logschema."aggregateValveMovementDaily" WITH (timescaledb.continuous) AS SELECT "ID", time_bucket('1 day', "hour") as "day", …
Paul Müller
  • 103
  • 8
2
votes
2 answers

Finding out the size of a continuous aggregate

Have hypertable table with a couple million rows. I'm able to select the size of this just fine using the following: SELECT pg_size_pretty( pg_total_relation_size('towns') ); I also have a continuous aggregate for that hypertable: WITH…
Nicole Staline
  • 557
  • 4
  • 15
2
votes
1 answer

Materialized View With No Data still loading data

My understanding of creating a materialized view WITH NO DATA was that no records would be loaded until I or a policy I've set refresh the view. However, when using timescaledb and providing this option, I can immediately query against the table and…
Nicole Staline
  • 557
  • 4
  • 15
2
votes
1 answer

What could be the problem with a Continuous Aggregate from multiple TimescaleDB Hypertables?

I've got a couple time series data tables in my Postgres database, which I recently converted to TimescaleDB hypertables. I've got a massive materialized view, but refreshing that takes ages. For that reason I wanted to use TimescaleDB Continuous…
kramer65
  • 50,427
  • 120
  • 308
  • 488
1
vote
1 answer

TimescaleDB Continuous Aggregate lagging behind

In TimescaleDB I have a continuous aggregate which contains daily averages, so bucket size is 1 day: CREATE MATERIALIZED VIEW sensors_daily WITH (timescaledb.continuous, timescaledb.materialized_only=true) AS SELECT time_bucket_gapfill('1d', time)…
Wintermute
  • 394
  • 4
  • 19
1
vote
0 answers

TimescaleDB continuous aggregate view does not refresh on old buckets

I'm trying to create a continuous aggregate view on a hypertable with the policy to refresh the view once a day. The first time it runs when I create the view was smooth, everything looked fine on Grafana. However, few days later, I found that the…
Sam Vo
  • 113
  • 10
1
vote
0 answers

Calculate and update a timescaleDB table with multiple timebased variables

I have the following Query for my timescaleDB that I want to optimize SELECT sensor_reading.day as day, sensor_delta_daily, pos1_daily, (sensor_delta_daily * 1 * calorific_value_daily) AS calcval1_daily FROM ( WITH…
flumingo
  • 513
  • 2
  • 7
  • 24
1
vote
1 answer

TimescaleDB time_bucket() function giving unexpected results for arbitrary time interval

I have created a hypertable water_meter to store the sensor data It contains following data ordered by timestamp in ascending order select * from water_meter order by time_stamp; As can be seen I have data starting from 01 May 2020 if I use…
Swapnil
  • 15
  • 4
0
votes
1 answer

Timescaledb - Materialized view very slow

Here is my hypertable - CREATE TABLE prices_daily ( datetime TIMESTAMP WITH TIME ZONE NOT NULL, security_id BIGINT NULL, open DOUBLE PRECISION NOT NULL, high DOUBLE PRECISION NOT NULL, low DOUBLE PRECISION NOT NULL, close DOUBLE PRECISION NOT…
Maddy
  • 55
  • 1
  • 8
0
votes
1 answer

Timescaledb continuous aggregate policy not updating back-dated data in range (Error: cannot execute SQL without an outer snapshot or portal)

I have a table data that looks something like this (simplified for the post): |insert_ts | actual_ts | group_id| amount| |2022-02-22 00:01:01| 2022-02-06 05:05:01 | 1 | 100| And a hypertable created as follows: CREATE…
jbx
  • 21,365
  • 18
  • 90
  • 144
0
votes
0 answers

Grant select on all tables does not work on continuous aggregates in Timescaledb

I have a read-only user which has been granted select on all tables including default privileges for any future schema additions. I have a Continuous Aggregate on a TimescaleDB Hypertable. The read-only user can query the hypertable fine, but when…
jbx
  • 21,365
  • 18
  • 90
  • 144
0
votes
0 answers

Best way to implement multiple continuous aggregates in postgres

Imagine you have to display information about rainfall based on cities over time. You have tables the provides the details on how much it rains in a specific city for every hour. There is an endpoint that returns the average amount of rainfall for…
Nicole Staline
  • 557
  • 4
  • 15
1
2