MIME-Version: 1.0
References: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
In-Reply-To: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Thu, 4 Jul 2024 16:50:23 -0400
Message-ID: <CANzqJaB49Rp0buF3DwPjzivSbTM+3p+COvr9n6Athk+v35nFiQ@mail.gmail.com>
Subject: Re: Design strategy for table with many attributes
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000026388a061c72191c"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaB49Rp0buF3DwPjzivSbTM%2B3p%2BCOvr9n6Athk%2Bv35nFiQ%40mail.gmail.com>
Precedence: bulk

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

On Thu, Jul 4, 2024 at 3:38=E2=80=AFPM Lok P <loknath.73@gmail.com> wrote:

> Hello,
> In one of the applications we are getting transactions in messages/events
> format and also in files and then they are getting parsed and stored into
> the relational database. The number of attributes/columns each transactio=
n
> has is ~900+. Logically they are part of one single transaction
>

Nothing out of the ordinary.


> and should be stored in one table as one single row.
>

Says who?


> There will be ~500million such transactions each day coming into the
> system. And there will be approx ~10K peak write TPS and 5K read TPS in
> target state. This system has a postgres database as a "source of truth" =
or
> OLTP store. And then data moves to snowflakes for the olap store.
>
> Initially when the system was designed the number of attributes per
> transaction was <100 but slowly the business wants to keep/persist other
> attributes too in the current system and the number of columns keep growi=
ng.
>
> However, as worked with some database systems , we get few suggestions
> from DBA's to not have many columns in a single table. For example in
> oracle they say not to go beyond ~255 columns as then row chaining and ro=
w
> migration type of things are going to hunt us. Also we are afraid
> concurrent DMLS on the table may cause this as a contention point. So I
> wanted to understand , in such a situation what would be the best design
> approach we should use irrespective of databases? Or say, what is the
> maximum number of columns per table we should restrict? Should we break t=
he
> single transaction into multiple tables like one main table and other
> addenda tables with the same primary key to join and fetch the results
> wherever necessary?
>

You need database normalization.  It's a big topic.  Here's a good simple
explanation:
https://popsql.com/blog/normalization-in-sql

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Jul 4, 2024 at 3:38=E2=80=AFPM Lo=
k P &lt;<a href=3D"mailto:loknath.73@gmail.com">loknath.73@gmail.com</a>&gt=
; wrote:<br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_quo=
te" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204=
);padding-left:1ex"><div dir=3D"ltr">Hello,<br>In one of the applications w=
e are getting transactions in messages/events format and also in files and =
then they are getting parsed and stored into the relational database. The n=
umber of attributes/columns each transaction has is ~900+. Logically they a=
re part of one single transaction </div></blockquote><div><br></div><div>No=
thing out of the ordinary.</div><div>=C2=A0</div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex"><div dir=3D"ltr">and should be stored in one table =
as one single row.</div></blockquote><div><br></div><div>Says who?</div><di=
v>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D=
"ltr"> There will be ~500million such transactions each day coming into the=
 system. And there will be approx ~10K peak write TPS and 5K read TPS in ta=
rget state. This system has a postgres database as a &quot;source of truth&=
quot; or OLTP store. And then data moves to snowflakes for the olap store.<=
br><br>Initially when the system was designed the number of attributes per =
transaction was &lt;100 but slowly the business wants to keep/persist other=
 attributes too in the current system and the number of columns keep growin=
g.<br><br>However, as worked with some database systems , we get few sugges=
tions from DBA&#39;s to not have many columns in a single table. For exampl=
e in oracle they say not to go beyond ~255 columns as then row chaining and=
 row migration type of things are going to hunt us. Also we are afraid conc=
urrent DMLS on the table may cause this as a contention point. So I wanted =
to understand , in such a situation what would be the best design approach =
we should use irrespective of databases? Or say, what is the maximum number=
 of columns per table we should restrict? Should we break the single transa=
ction into multiple tables like one main table and other addenda tables wit=
h the same primary key to join and fetch the results wherever necessary?<br=
></div></blockquote><div><br></div><div>You need database normalization.=C2=
=A0 It&#39;s a big topic.=C2=A0 Here&#39;s a good simple explanation:</div>=
<div><a href=3D"https://popsql.com/blog/normalization-in-sql">https://popsq=
l.com/blog/normalization-in-sql</a>=C2=A0<br></div></div></div>

--00000000000026388a061c72191c--