1

I am currently trying to create a (materialized) view which is based on a TSDB (timescale DB) continuous aggregation. Some context: I have a table which takes consumption data of meters through a dongle which is attached to that meter, i.e.:

CREATE TABLE data (
   dongle_id              UUID,
   timestamp              timestamp,
   meter_id               varchar,
   current_consumption    integer
   total_consumption      integer)

This data is recorded every 10 seconds and used to display the current consumption.

To create graphs we also do aggregations on the total_consumption to create minute- and hourly-charts. We use the total_consumption field for this since there may be entries lost if one of the dongles loses connection or something else happens. With the total_consumption we can still do accurate calculations between data points even when one or more are missing.

At first it was done with a timescaleDB view and time_bucket() of appropriate size. Where we calculated the difference of the minimum and maximum total_consumption for the requested chart, i.e.

CREATE VIEW data_aggregation_minute WITH (
    timescaledb.continuous
    timescaledb.ignore_invalidation_older_than = '6 days',
    timescaledb.refresh_lag = '2 minutes',
    timescaledb.refresh_interval = '1 minute')
AS 
SELECT dongle_id
       meter_id
       time_bucket(INTERVAL '1 minutes', timestamp) + '1 minutes' AS timestamp,
       max(total_consumption) - min(total_consumption) AS total_consumption
FROM data
GROUP BY dongle_id, meter_id, time_bucket(INTERVAL '1 minutes', timestamp);

However, this will lead to 1/6th of the information to be lost, or as in this example 1/3 with a resolution of 20 seconds:

timestamp |  total_consumption
00:00        0
00:20        100
00:40        200
01:00        300
01:20        400
01:40        500
02:00        600
02:20        700
02:40        800
03:00        900

Total according to live data: 900

Aggregation with max()-min() in time_bucket:

timestamp |  total_consumption_diff
00:00        0
01:00        200                  (200-0)
02:00        200                  (500-300)
03:00        200                  (800-600)

Total according to aggregations: 600

To resolve this issue I wanted to apply a different strategy, which just documents the max() of each minute in a table:

CREATE VIEW data_aggregation_minute_max WITH (
    timescaledb.continuous
    timescaledb.ignore_invalidation_older_than = '6 days',
    timescaledb.refresh_lag = '2 minutes',
    timescaledb.refresh_interval = '1 minute')
AS 
SELECT dongle_id
       meter_id
       time_bucket(INTERVAL '1 minutes', timestamp) + '1 minutes' AS timestamp,
       max(total_consumption) AS total_consumption
FROM data
GROUP BY dongle_id, meter_id, time_bucket(INTERVAL '1 minutes', timestamp);

And on this aggregation table I wanted to create another view which calculates the difference of a data-point with the data-point that came before it with the lag() function:

CREATE VIEW data_aggregation_minute
AS
    SELECT dongle_id
           meter_id
           timestamp
           CASE
               WHEN (NULLIF(meter_id, lag(meter_id, 1) OVER (PARTITION BY dongle_id ORDER BY timestamp))) IS NULL AND
                    lag(total_consumption, 1) OVER (PARTITION BY dongle_id, meter_id ORDER BY timestamp) IS NOT NULL
               THEN (greatest(0, total_consumption - lag(total_consumption, 1) OVER (PARTITION BY dongle_id, meter_id ORDER BY timestamp))) END AS total_consumption
    FROM data_aggregation_minute_max
    ORDER BY dongle_id, meter_id, timestamp

Now if we do the calculation again:

timestamp |  total_consumption
00:00        0
00:20        100
00:40        200
01:00        300
01:20        400
01:40        500
02:00        600
02:20        700
02:40        800
03:00        900

Total according to live data: 900

Aggregation with max() in time_bucket:

timestamp |  total_consumption_max
00:00        0
01:00        200                  
02:00        500                  
03:00        800
04:00        900

And the resulting calculation in the view table:

timestamp |  total_consumption_diff
00:00        null
01:00        200              (200-0)            
02:00        300              (500-200)    
03:00        300              (800-500)
04:00        100              (900-800)

Total according to aggregations in the view table: 900, which is now correct

However, this is just a simple example and the full tables contain a lot more different values than just total_consumption

Since we receive data every 10 seconds from hundreds of dongles and keep data for 7 days, the data table becomes accordingly heavy which trickles down to the timescaleDB table and also the view which calculates the correct aggregations.

For now I have only managed to create this as a non-materialized view. Creating this view each time its data is accessed would be fairly heavy on our database since it leads to potentially millions of rows having to be calculated each time the view is accessed by a user.

According to the timescaleDB documentation, it is not possible to create a TSDB view with window functions inside of it, and I am using lag() to get the previous entry to calculate the correct total.

Also creating a materialized view wouldn't improve performance even with the concurrent option since this would only allow the view to be accessed even when it's updated. The full table would still be re-queried each time the underlying table is updated.

Are there any performance gains to be made in the database in this scenario?

Or should I give up on trying to do it as a view and just transform it into a parametrized query in Java to limit it to a single dongle and timestamp window, which would heavily cut down on the scope of the data I want to gather. But it will have to be calculated for each query which comes from the backend.

2
  • You can create a query or view using postgres window functions on top of the continuous aggregate but I sense this might not give you the answer that you need. I'll try to get some attention after the holiday if you don't get a community response before. Great post by the way! (Timescale's Community Manager)...
    – greenweeds
    Nov 25 at 15:35
  • 1
    @greenweeds yes, I am currently using a postgres view with a window function (lag() in this case) on top of the continuous aggregate. Thanks for your reply and I am glad to know it's a good post (my first in this community) since I wasn't sure about that. Hope to hear back from you after the holidays :) for now I will convert it into a query, but would love to have it in the database as a table since it would be more transparent for our backend.
    – Streamline
    Nov 26 at 7:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.