Use the power of TimescaleDB to avoid triggers

[DanielBertocci]

I am facing some challenges with performance with FROST and I have identified the bottleneck in triggers on Observation insertions.
I am using a setup where Observations table is a hypertable indexed on PHENOMENON_TIME_START and partitioned on DATASTREAM_ID.
With the performance of timescale, get the max and min of PHENOMENON_TIME_START for the Observations in a datastream is really a cheap query.

Is there a way, playing with FROST configurations or plugin loader, to avoid to store phenomenonTime for datastreams and retrieve it with a query?

[hylkevds]

Do you, by any chance, have moving sensors?
In my performance tests the update of the phenomenonTime did not have much impact, but the update of the observedArea did. For static sensors this has been optimised a lot, but for moving sensors there is not much that can be done.

[DanielBertocci]

We don't have moving sensors, if we exclude the noise on gps. So for us is not a problem and anyway we can accept slower updates on the observedArea, for example scheduled.

We have use-cases where we have to move observations from a datastream to another. In a vanilla FROST setup, this would be simply an update on the datastream_id for the observations to move.
We have billions of observations and for optimization reasons, they are in a hypertable on phenomenon_time_start and partitioned on datastream_id. This means that we cannot anymore update the datastream_id, because it is a partition key, but create a copy to the target datastream and then delete the old ones in a transaction.

And here the trigger heavily impact on the performances. We are considering per statement triggers for that.

One of the advantages of using Timescale though, is that a query like the following is really performant:

SELECT MIN("PHENOMENON_TIME_START")
FROM "OBSERVATIONS"
WHERE "DATASTREAM_ID" = 'datastream_id'

So there is no need to compute it every insert.

[hylkevds]

Changing FROST to use a subquery to get calculate the phenomenonTime live is theoretically possible, but not trivial.
Since you are doing essentially a batch update, a much simpler solution is to make sure the phenomenonTime does not need to be updated for every Observation that is inserted. Since the trigger only needs to write a new phenomenonTime when the new Observation is outside of the range of the current phenomenonTime, there are two main ways to do that:

1. First insert the last Observation (and the first, for an empty target Datastream) and then the rest.
2. Manually update the Datastream/phenomenonTime before the batch. By setting plugins.coreModel.editableDsProperties to true one can directly edit the normally calculated properties.

[DanielBertocci]

It is an interesting solution indeed, thank you. We investigated also on per-statement triggers but timescale does not support transient table on hypertables, sadly.