MIME-Version: 1.0
References: <8d2dd92a-da16-435b-a38e-fe72191fc9d1@cloud.gatewaynet.com>
 <ea77fa23-6361-42f0-a511-8e0997dada26@aklaver.com> <d8892101-4cab-4f42-8c8b-9a84cb8ff767@cloud.gatewaynet.com>
 <f80fdc40-3179-4686-b8bf-4f45bb930b4d@gelassene-pferde.biz>
In-Reply-To: <f80fdc40-3179-4686-b8bf-4f45bb930b4d@gelassene-pferde.biz>
From: Allan Kamau <kamauallan@gmail.com>
Date: Fri, 14 Feb 2025 12:34:00 +0300
Message-ID: <CAF3N6oR+8VvfrDEfp-VMD2FmTkPc4mLdqRixBgARCyuqjQHqpw@mail.gmail.com>
Subject: Re: Ideas about presenting data coming from sensors
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ec7c65062e16e018"
Archived-At: <https://www.postgresql.org/message-id/CAF3N6oR%2B8VvfrDEfp-VMD2FmTkPc4mLdqRixBgARCyuqjQHqpw%40mail.gmail.com>
Precedence: bulk

--000000000000ec7c65062e16e018
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Thu, Feb 13, 2025 at 9:44=E2=80=AFPM Thiemo Kellner <thiemo@gelassene-pf=
erde.biz>
wrote:

> 13.02.2025 10:54:05 Achilleas Mantzios - cloud <
> a.mantzios@cloud.gatewaynet.com>:
>
> > If we followed a strict normalized approach then we would create
> additionally 11 tables each tag of type c) . And we are not guaranteed th=
at
> the same tags would have the same structure over the whole
> fleet/manufacturers. So we are thinking of putting all semi-structured da=
ta
> 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.
>
> I have not come to see the real advantage of putting data into something
> like Jason or XML columns. Sue, you make life easier initially saving the
> time to put them into columns one by one, on the other end you have the
> hassle of dissecting the JSON, XML you name it when you retrieve/select t=
he
> data, every query. While the query might stay stable the  computational
> effort dissection will have to be done with every query execution.
>
> For c) you could go to 6th normal form, or what number that is, by a tabl=
e
> like TAG(id, tag_name, tag_value). You would have to convert the values t=
o
> string to enter them. It is a pain in the butt, imho, retrieving them
> again. You can alleviate by having a specific column/table per data type,
> e.g. TAG(id, tag_name,tag_value_varray,tag_value_date,...).
>
> What is the downside of having each a table for the special tags? More
> effort in setting up.
> The upside is less effort in retrieval, and a much more understandable
> model.
>
> If your tag structure is volatile, you might have generic column names on
> a table mapping them in a view to speaking names. Taking this further doe=
s
> anchor modelling https://www.anchormodeling.com/
> .
>
>
>
Though you haven't asked for comments on database design, I have a
suggestion, have you considered using table partitioning based on a well
defined date interval, maybe monthly?
Here your DB data population application will determine the name of the
table based on the current date then check for the existence of the table
and then construct the inherited table if one does not exist and proceed to
populate the child table accordingly.
This may prove useful in future.

-Allan.

--000000000000ec7c65062e16e018
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Feb 13, 2025=
 at 9:44=E2=80=AFPM Thiemo Kellner &lt;<a href=3D"mailto:thiemo@gelassene-p=
ferde.biz" target=3D"_blank">thiemo@gelassene-pferde.biz</a>&gt; wrote:<br>=
</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;b=
order-left:1px solid rgb(204,204,204);padding-left:1ex">13.02.2025 10:54:05=
 Achilleas Mantzios - cloud &lt;<a href=3D"mailto:a.mantzios@cloud.gatewayn=
et.com" target=3D"_blank">a.mantzios@cloud.gatewaynet.com</a>&gt;:<br>
<br>
&gt; If we followed a strict normalized approach then we would create addit=
ionally 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 timescale=
db plays nice with jsonb (or at least not bad).<br>
&gt;<br>
&gt; Do you ppl see any gotcha with this approach ?<br>
&gt;<br>
&gt; For starters we will not convert yet to timescaledb, but store them an=
d handle them like normal tables. At least until we grasp the ins and outs =
of this.<br>
<br>
I have not come to see the real advantage of putting data into something li=
ke Jason or XML columns. Sue, you make life easier initially saving the tim=
e to put them into columns one by one, on the other end you have the hassle=
 of dissecting the JSON, XML you name it when you retrieve/select the data,=
 every query. While the query might stay stable the=C2=A0 computational<br>
effort dissection will have to be done with every query execution.<br>
<br>
For c) you could go to 6th normal form, or what number that is, by a table =
like TAG(id, tag_name, tag_value). You would have to convert the values to =
string to enter them. It is a pain in the butt, imho, retrieving them again=
. You can alleviate by having a specific column/table per data type, e.g. T=
AG(id, tag_name,tag_value_varray,tag_value_date,...).<br>
<br>
What is the downside of having each a table for the special tags? More effo=
rt in setting up.<br>
The upside is less effort in retrieval, and a much more understandable mode=
l.<br>
<br>
If your tag structure is volatile, you might have generic column names on a=
 table mapping them in a view to speaking names. Taking this further does a=
nchor modelling <a href=3D"https://www.anchormodeling.com/" rel=3D"noreferr=
er" target=3D"_blank">https://www.anchormodeling.com/</a><br>
.<br>
<br>
<br></blockquote><div><br></div><div>Though you haven&#39;t asked for comme=
nts on database design, I have a suggestion, have you considered using tabl=
e partitioning based on a well defined date interval, maybe monthly?</div><=
div>Here your DB data population application will determine the name of the=
 table based on the current date then check for the existence of the table =
and then construct the inherited table if one does not exist and proceed to=
 populate the child table accordingly.</div><div>This may prove useful in f=
uture.</div><div><br></div><div>-Allan.</div><div><br></div></div></div>
</div>

--000000000000ec7c65062e16e018--