public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios - cloud <[email protected]>
To: Adrian Klaver <[email protected]>
To: [email protected] <[email protected]>
Cc: Achilleas Mantzios <[email protected]>
Subject: Re: Ideas about presenting data coming from sensors
Date: Thu, 13 Feb 2025 11:53:43 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
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!
>>
>>
>>
>>
>
view thread (11+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Ideas about presenting data coming from sensors
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox