24 questions
1
vote
1
answer
58
views
Creating a merged dataset from a subset of variables from multiple hypertables with TimescaleDB - continuous aggregate
I wanted to see if anyone can suggest ways around this before I give up on it entirely as an option.
We have a Postgres (14) TimescaleDB database. It has several hypertables, each of which is ...
- 199
1
vote
1
answer
163
views
How should I automate refreshing of my continuous aggregates every time I insert historical data
I have created multiple continous aggregates on my hypertable using timescaledb 2.15.3 extension in postgreSQL 15.
When inserting historical data to this hypertable, I have to manually refresh all ...
- 91
1
vote
0
answers
159
views
Missing rows in a continuous aggregate timescaledb table
I have a tick table in timescaledb as follows from which I create a materialized view for 1-minute OHLC bars with its corresponding refresh policy. However, my tick_1min table does not end up having ...
- 2,388
0
votes
1
answer
186
views
Slow performance when querying continuous aggregate with time_bucket_gapfill despite relatively small data set
CREATE TABLE sku_usage (
-- create a regular table
time timestamptz NOT NULL,
device_id uuid NOT NULL,
loyalty_program_id text NOT NULL,
sku text NOT NULL,
quantity double ...
- 3,171
1
vote
1
answer
917
views
Timescaledb Hierarchical continuous aggregates generate duplicated time bucket
I'm new in Timescaledb and I'm exploring the continuous aggregates. I use the docker image timescale/timescaledb-ha:pg14 (PostgreSQL 14 with Timescaledb v2.11.1)
I have historical data which are half-...
- 41
0
votes
2
answers
251
views
timescaledb or postgresql aggregation by getting time difference between two events
I collect device ON/OFF events in a timeseries table. Following is a sample data.
time_stamp
state
2023-10-04 10:05:53
0
2023-10-04 10:15:58
1
2023-10-04 10:30:59
0
2023-10-04 10:40:00
1
2023-10-04 ...
- 407
0
votes
1
answer
118
views
How to use average() in a query without adding it to groupby - postgresql
I have a hierarchal aggregate that uses below statement
CREATE MATERIALIZED VIEW IF NOT EXISTS public.values_summary_five_minutes
WITH (timescaledb.continuous,timescaledb.materialized_only = true) AS
...
- 645
0
votes
1
answer
726
views
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 ...
- 65
5
votes
1
answer
938
views
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 ...
- 22.3k
0
votes
1
answer
451
views
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|...
- 22.3k
1
vote
1
answer
824
views
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=...
- 393
2
votes
0
answers
514
views
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-...
- 22.3k
3
votes
0
answers
1k
views
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 "...
- 133
6
votes
1
answer
1k
views
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:...
- 1,105
1
vote
0
answers
692
views
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 ...
- 113
4
votes
3
answers
1k
views
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 ...
- 711
2
votes
1
answer
5k
views
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 ...
- 711
0
votes
0
answers
160
views
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 ...
- 711
1
vote
0
answers
241
views
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)...
- 513
1
vote
1
answer
2k
views
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 ...
- 25
1
vote
2
answers
2k
views
TimeScaleDB continuous aggregate refresh policy end offset is not working as expected
I am new to TimeScaleDB, I have created a continuous aggregate view as
CREATE MATERIALIZED VIEW minute_data
WITH (timescaledb.continuous)
AS
SELECT
time_bucket('1 min', time_stamp) as bucket,
...
- 25
3
votes
1
answer
2k
views
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 ...
- 54.5k
0
votes
1
answer
1k
views
TimescaleDB - Continuous Aggregation Refresh takes long time for even small volume of data
Continuous Aggregation Refresh takes long time for even small volume of data
This is regarding the continuous aggregation and refreshing it.
We ran the following queries and noted the observations.
...
- 16
4
votes
1
answer
3k
views
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,
...
- 22.3k