Message-ID: <d8892101-4cab-4f42-8c8b-9a84cb8ff767@cloud.gatewaynet.com>
Date: Thu, 13 Feb 2025 11:53:43 +0200
MIME-Version: 1.0
Subject: Re: Ideas about presenting data coming from sensors
To: Adrian Klaver <adrian.klaver@aklaver.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <8d2dd92a-da16-435b-a38e-fe72191fc9d1@cloud.gatewaynet.com>
 <ea77fa23-6361-42f0-a511-8e0997dada26@aklaver.com>
Content-Language: en-US
From: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
Cc: Achilleas Mantzios <itdev@gatewaynet.com>
In-Reply-To: <ea77fa23-6361-42f0-a511-8e0997dada26@aklaver.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/d8892101-4cab-4f42-8c8b-9a84cb8ff767%40cloud.gatewaynet.com>
Precedence: bulk


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!
>>
>>
>>
>>
>