Received-SPF: pass (ha01s018.org-dns.com: connection is authenticated)
Date: Thu, 13 Feb 2025 19:44:26 +0100 (GMT+01:00)
From: Thiemo Kellner <thiemo@gelassene-pferde.biz>
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Message-ID: <f80fdc40-3179-4686-b8bf-4f45bb930b4d@gelassene-pferde.biz>
In-Reply-To: <d8892101-4cab-4f42-8c8b-9a84cb8ff767@cloud.gatewaynet.com>
References: <8d2dd92a-da16-435b-a38e-fe72191fc9d1@cloud.gatewaynet.com>
 <ea77fa23-6361-42f0-a511-8e0997dada26@aklaver.com>
 <d8892101-4cab-4f42-8c8b-9a84cb8ff767@cloud.gatewaynet.com>
Subject: Re: Ideas about presenting data coming from sensors
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/f80fdc40-3179-4686-b8bf-4f45bb930b4d%40gelassene-pferde.biz>
Precedence: bulk

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 addition=
ally 11 tables each tag of type c) . And we are not guaranteed that the sam=
e 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) int=
o 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 h=
andle 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 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
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 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,...).

What is the downside of having each a table for the special tags? More effo=
rt in setting up.
The upside is less effort in retrieval, and a much more understandable mode=
l.

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 https://www.anchormodeling.com/
.