Re: Ideas about presenting data coming from sensors

On 1/30/25 18:45, Adrian Klaver wrote:
> On 1/30/25 06:18, Achilleas Mantzios - cloud wrote:
>> Dear PostgreSQL people
>>
>> We have a project having to do with capturing sensor data and alarms
>> from various machinery using various protocols/standards (e.g. NMEA ,
>> MODBUS). We have about 150 sites (vessels of various types) and each
>> site will generate about 500 rows per minute. We have solved most of
>> the design + issues regarding DB, data transfer to the central DB,
>> etc and now we have started to think about presenting them.
>>
>> Ideally and eventually each one of those will be connected to some
>> machinery item defined in our inventory / live system, so the final
>> goal is to have smth like "mimics" and interactive UI that will let
>> the user have an overall view of  the fleet (all vessels), spot the
>> red/orange ones, then click on a vessel, spot the red/orange areas,
>> then keep on zooming in until he/she finds the exact item that is in
>> trouble.
>>
>> This is the rough idea.
>>
>> But for now we are more interested in just viewing the incoming data
>> that will be basically tag/value pairs.
>>
>> The types of those data will be divided in normal tag with a normal
>> float value, or alarm data which have about 2 discrete boolean
>> variables (valid and acknowledged). So we want to cover those two
>> cases as far as viewing is concerned.
>>
>> The data will have minutely resolution. So if a vessel e.g. has 700
>> discrete tags then the system will generate and transfer to the
>> central DB 700 rows for every minute.
>>
>> Any ideas? Graphs time series? We have graphs (Java) that show time
>> series with daily resolution. What would you guys recommend for
>> showing such data, coming from data acquisition systems ?
>
> It seems there are two uses in play here:
>
> 1) Minute by minute data acquisition to track sensor and alarm output
> in general.
>
> 2) Troubleshooting emerging problems.
>
> For 2) you seem to already have the idea of drilling down on values
> that have moved into the orange --> red area. That does not require
> displaying all the data, just the data that enters the zone of interest.
>
>
> For 1) the question is do you think folks are going to look at 700
> values recorded on a minute by minute basis on 150 ships? Where that
> information could help is supplying context for the problems arising
> in 2), especially  intermittent issues. I could see picking a problem
> defined in 2) and allowing for the user to select both a time period
> and related values over that period to help determine how the problem
> developed.
>
Thank you Adrian!

Yes the ultimate goal would be to have a graphical overview where the
user will finally zoom in until he/she spots the exact single problem at
hand. Then view trends or other historical data that might have had
contributed or caused this specific event.

We took a look in timescaledb, and this seems to fit completely what we
need :

- postgresql

- time series data

- efficient storage, efficient querying

- out of the box partitioning

- etc

Now my problem is on the design . We have :

a) tags that have primitive values, float4 lets say - this is the
majority, e.g. 60% of all tags

b) tags that contain alarms data also with defined structure, which have
additional data such as time of the initial alarm set, acknowledgement
of this alarm , validity of this alarm. Those represent smth like 35% fo
all tags

c) tags that are basically polymorphic (about 11 of them all in all),
each one has different structure, and their fields/cols range a few (1)
up to many (25)

We have a table for a) and a table for b).

If we followed a strict normalized approach then we would create
additionally 11 tables each tag of type c) . And we are not guaranteed
that the same tags would have the same structure over the whole
fleet/manufacturers. So we are thinking of putting all semi-structured
data of tags of type c) into one table with a single col of type jsonb .
From what I read timescaledb plays nice with jsonb (or at least not bad).

Do you ppl see any gotcha with this approach ?

For starters we will not convert yet to timescaledb, but store them and
handle them like normal tables. At least until we grasp the ins and outs
of this.

>
>>
>> Thank you!
>>
>>
>>
>>
>