MIME-Version: 1.0
In-Reply-To: <CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>
References: <CAKna9VbQ=o2Yhjo1EitaxzYpWAXe9vw6QtamzCsNNp2-rQOFSA@mail.gmail.com>
 <CAKFQuwYpai5snt5mEgyXK4m6nR5T6sP-YTYH-GQ0Hs2rXS7uLA@mail.gmail.com> <CAKna9VZdihb=n9mNGaCZLKYbWAoS0+knfi_PAfLC5w=Mj5Egig@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Thu, 4 Jul 2024 22:36:03 -0700
Message-ID: <CAKFQuwZHHHF7A-5AhC-p-H4cquZSZq-JTGWg0Q+51Drfwc8VCQ@mail.gmail.com>
Subject: Re: Design strategy for table with many attributes
To: Lok P <loknath.73@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000007351e3061c79701f"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZHHHF7A-5AhC-p-H4cquZSZq-JTGWg0Q%2B51Drfwc8VCQ%40mail.gmail.com>
Precedence: bulk

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

On Thursday, July 4, 2024, Lok P <loknath.73@gmail.com> wrote:

>
> But do you also suggest keeping those table pieces related to each other
> through the same primary key ?
>
>
Yes, everyone row produced from the input data =E2=80=9Crow=E2=80=9D should=
 get the same ID
associated with it - either as an entire PK or a component of a
multi-column PK/unique index.

>
>
>  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 t=
o
> multiple tables it will happen to all the individual pieces, won't that
> cause additional burden to the data load?
>

Yes, doing this requires additional CPU time to perform the work.  I=E2=80=
=99d say
IO is hopefully a wash.


>
> 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.
>

In a row-oriented system wider is worser.  I start evaluation of table
design with that in mind at the fourth column (including the surrogate key
that is usually present, and the natural key - ignoring auditing columns.)


>
>  And if we should maintain any specific order in the columns from start t=
o
> end column in the specific table?
>

There is material out there on micro-optimizing column ordering to match
with alignment boundaries.  I=E2=80=99d the benefit is meaningful but there=
 is a
non-trivial cost to actually setup the testing to verify that what you=E2=
=80=99ve
figured out is working.  Never actually done it myself.  Though it actually
seems like something someone could/may have written an algorithm for
(though I do not recall ever seeing mention of one.)

David J.

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

On Thursday, July 4, 2024, Lok P &lt;<a href=3D"mailto:loknath.73@gmail.com=
">loknath.73@gmail.com</a>&gt; wrote:<br><blockquote class=3D"gmail_quote" =
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><di=
v dir=3D"ltr"><div dir=3D"ltr"><br></div><div class=3D"gmail_quote"><div>Bu=
t do you also suggest keeping those table pieces related to each other thro=
ugh the same primary key ?<br></div><div><br></div></div></div></blockquote=
><div><br></div><div>Yes, everyone row produced from the input data =E2=80=
=9Crow=E2=80=9D should get the same ID associated with it - either as an en=
tire PK or a component of a multi-column PK/unique index.=C2=A0</div><block=
quote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc=
 solid;padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div><=
br></div><div><br></div><div>=C2=A0Won&#39;t there be a problem when we loa=
d the data like say for example , in normal scenario=C2=A0the data load wil=
l be to one table but when we break it to multiple tables it will happen to=
 all the individual pieces, won&#39;t that cause additional burden to the d=
ata load?</div></div></div></blockquote><div><br></div><div>Yes, doing this=
 requires additional CPU time to perform the work.=C2=A0 I=E2=80=99d say IO=
 is hopefully a wash.</div><div>=C2=A0</div><blockquote class=3D"gmail_quot=
e" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">=
<div dir=3D"ltr"><div class=3D"gmail_quote"><div><br></div><div>Also I unde=
rstand the technical limitation of the max number of columns=C2=A0per table=
 is ~1600. But should you advise to restrict/stop us to some low number lon=
g before reaching that limit , such that we will not face any anomalies whe=
n we grow in future.</div></div></div></blockquote><div><br></div><div>In a=
 row-oriented system wider is worser.=C2=A0 I start evaluation of table des=
ign with that in mind at the fourth column (including the surrogate key tha=
t is usually present, and the natural key - ignoring auditing columns.)</di=
v><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 =
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr"><div cla=
ss=3D"gmail_quote"><div><br></div><div>=C2=A0And if we should maintain any =
specific order in the columns from start to end column in the specific tabl=
e?</div></div></div></blockquote><div><br></div><div>There is material out =
there on micro-optimizing column ordering to match with alignment boundarie=
s.=C2=A0 I=E2=80=99d the benefit is meaningful but there is a non-trivial c=
ost to actually setup the testing to verify that what you=E2=80=99ve figure=
d out is working.=C2=A0 Never actually done it myself.=C2=A0 Though it actu=
ally seems like something someone could/may have written an algorithm for (=
though I do not recall ever seeing mention of one.)</div><div><br></div><di=
v>David J.</div><div><br></div>

--0000000000007351e3061c79701f--