MIME-Version: 1.0
References: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
 <CAKFQuwYpai5snt5mEgyXK4m6nR5T6sP-YTYH-GQ0Hs2rXS7uLA@mail.gmail.com>
In-Reply-To: <CAKFQuwYpai5snt5mEgyXK4m6nR5T6sP-YTYH-GQ0Hs2rXS7uLA@mail.gmail.com>
From: Lok P <loknath.73@gmail.com>
Date: Fri, 5 Jul 2024 10:37:16 +0530
Message-ID: <CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>
Subject: Re: Design strategy for table with many attributes
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000040414d061c790a4c"
Archived-At: <https://www.postgresql.org/message-id/CAKna9VZdihb%3Dn9mNGaCZLKYbWAoS0%2Bknfi_PAfLC5w%3DMj5Egig%40mail.gmail.com>
Precedence: bulk

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

On Fri, Jul 5, 2024 at 1:26=E2=80=AFAM David G. Johnston <david.g.johnston@=
gmail.com>
wrote:

> On Thu, Jul 4, 2024 at 12:38=E2=80=AFPM Lok P <loknath.73@gmail.com> wrot=
e:
>
>>
>> Should we break the single transaction into multiple tables like one mai=
n
>> table and other addenda tables with the same primary key to join and fet=
ch
>> the results wherever necessary?
>>
>>
> I would say yes.  Find a way to logically group sets of columns together
> and place those groups into separate tables.  I'd also be looking for cas=
es
> where multiple columns really should be multiple rows.  This is not
> uncommon.
>
> David J.
>
>
Thank you David.

As you said, to logically break this into multiple tables so i believe it
means it should be such that there will be no need to query multiple tables
and join them most of the time for fetching the results. It should just
fetch the results from one table at any point in time.

But do you also suggest keeping those table pieces related to each other
through the same primary key ? Won't there be a problem when we load the
data like say for example , in normal scenario the data load will be to one
table but when we break it to multiple tables it will happen to all the
individual pieces, won't that cause additional burden to the data load?

Also I understand the technical limitation of the max number of columns per
table is ~1600. But should you advise to restrict/stop us to some low
number long before reaching that limit , such that we will not face any
anomalies when we grow in future. And if we should maintain any specific
order in the columns from start to end column in the specific table?

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jul 5, 2024 at 1:26=E2=80=AFAM Da=
vid G. Johnston &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.j=
ohnston@gmail.com</a>&gt; wrote:<br></div><div class=3D"gmail_quote"><block=
quote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1=
px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"lt=
r"><div style=3D"font-family:arial,helvetica,sans-serif"><span style=3D"fon=
t-family:Arial,Helvetica,sans-serif">On Thu, Jul 4, 2024 at 12:38=E2=80=AFP=
M Lok P &lt;<a href=3D"mailto:loknath.73@gmail.com" target=3D"_blank">lokna=
th.73@gmail.com</a>&gt; wrote:</span><br></div></div><div class=3D"gmail_qu=
ote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><br=
></div></blockquote><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr">Should we break the single transaction into multiple tables lik=
e one main table and other addenda tables with the same primary key to join=
 and fetch the results wherever necessary?<br><div><br></div></div></blockq=
uote><div><br></div><div style=3D"font-family:arial,helvetica,sans-serif">I=
 would say yes.=C2=A0 Find a way to logically group sets of columns togethe=
r and place those groups into separate tables.=C2=A0 I&#39;d also be lookin=
g for cases where multiple columns really should be multiple rows.=C2=A0 Th=
is is not uncommon.</div><div style=3D"font-family:arial,helvetica,sans-ser=
if"><br></div><div style=3D"font-family:arial,helvetica,sans-serif">David J=
.</div><div style=3D"font-family:arial,helvetica,sans-serif"><br></div></di=
v></div></blockquote><div><br></div><div>Thank you David.</div><div><br></d=
iv><div>As you said, to logically break this into multiple tables so i beli=
eve it means it should be such that there will be no need to query multiple=
=C2=A0tables and join them most of the time for fetching the results. It sh=
ould just fetch the results from one table at any point in time.=C2=A0=C2=
=A0</div><div><br></div><div>But do you also suggest keeping those table pi=
eces related to each other through the same primary key ? Won&#39;t there b=
e a problem when we load the data like say for example , in normal scenario=
=C2=A0the data load will be to one table but when we break it to multiple t=
ables it will happen to all the individual pieces, won&#39;t that cause add=
itional burden to the data load?</div><div><br></div><div>Also I understand=
 the technical limitation of the max number of columns=C2=A0per table is ~1=
600. But should you advise to restrict/stop us to some low number long befo=
re reaching that limit , such that we will not face any anomalies when we g=
row in future. And if we should maintain any specific order in the columns =
from start to end column in the specific table?</div></div></div>

--00000000000040414d061c790a4c--