MIME-Version: 1.0
References: <CAHesJ5LES3aTDf=xp7NOwrADQ_HWC-Spsv7yLu9ZY+zxzZO53A@mail.gmail.com>
 <b450927c-49da-46e5-ad74-bf38ceff166b@aklaver.com> <CAHesJ5+ASNoSNMiC5Ms0Ts=gw7v2_UeBpUT=phujO4yE_XCbEw@mail.gmail.com>
In-Reply-To: <CAHesJ5+ASNoSNMiC5Ms0Ts=gw7v2_UeBpUT=phujO4yE_XCbEw@mail.gmail.com>
From: Divyansh Gupta JNsThMAudy <ag1567827@gmail.com>
Date: Mon, 23 Dec 2024 22:16:17 +0530
Message-ID: <CAHesJ5JbkCBZ2f_AvUr8+KWnGPAsobu4zyfnWm8bEeb7X9oqDQ@mail.gmail.com>
Subject: Re: Need help in database design
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000e6c2470629f2bc57"
Archived-At: <https://www.postgresql.org/message-id/CAHesJ5JbkCBZ2f_AvUr8%2BKWnGPAsobu4zyfnWm8bEeb7X9oqDQ%40mail.gmail.com>
Precedence: bulk

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

Also as you ask how 50 pairs turns into 50 column so each column will be a
key and the value of that key will store inside their respective column for
each row

On Mon, 23 Dec 2024, 10:14=E2=80=AFpm Divyansh Gupta JNsThMAudy, <
ag1567827@gmail.com> wrote:

> As per the discussion with other team members they suggested if we store
> 50 values for keys in an individual column that will provide better
> performance as the data type is native (INT2) on the other hand if we sto=
re
> all the key value pair in a single JSONB column the performance will
> degrade even after applying a GIN index on that however the statement
> sounds funny but I want to take everyone openion?
>
> On Mon, 23 Dec 2024, 10:05=E2=80=AFpm Adrian Klaver, <adrian.klaver@aklav=
er.com>
> wrote:
>
>> On 12/23/24 07:53, Divyansh Gupta JNsThMAudy wrote:
>> > Hii Community,
>> >
>> > I need to provide a support for some functionality for my application
>> > for that I need to store 50 key value pair set, so I am in a dilemma,
>> > weather I create 50 new columns of int2 data type each column will
>>
>> This is unclear, I am trying to figure out you go from '50 key value
>> pair set' to '50 new columns of int2'.
>>
>> In other words how 50 pairs turn into 50 columns?
>>
>> Then there is the question of why 50 keys per row in the first place?
>>
>>
>> > contain value of a specific key or should I go with JSONB data type
>> with
>> > 50 key value pair, the table on which I am going to do that all
>> contains
>> > 1 Billion rows of data and have 84 hash partitions, I have gone throug=
h
>> > multiple articles some of them mentioned it's a good approach to creat=
e
>> > 50 new columns and some states that creating one JSONB would be best
>> > that's why I need your help to move forward, also I am ready to make
>> > H-Store instead of JSONB if it provides better performance.
>> > Please help me to comes out from that dilemma.
>> >
>> > Regards,
>> > Divyansh Gupta,
>> > Database Administrator
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com
>>
>>

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

<p dir=3D"ltr">Also as you ask how 50 pairs turns into 50 column so each co=
lumn will be a key and the value of that key will store inside their respec=
tive column for each row</p>
<br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=
=3D"gmail_attr">On Mon, 23 Dec 2024, 10:14=E2=80=AFpm Divyansh Gupta JNsThM=
Audy, &lt;<a href=3D"mailto:ag1567827@gmail.com">ag1567827@gmail.com</a>&gt=
; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .=
8ex;border-left:1px #ccc solid;padding-left:1ex"><p dir=3D"ltr">As per the =
discussion with other team members they suggested if we store 50 values for=
 keys in an individual column that will provide better performance as the d=
ata type is native (INT2) on the other hand if we store all the key value p=
air in a single JSONB column the performance will degrade even after applyi=
ng a GIN index on that however the statement sounds funny but I want to tak=
e everyone openion? </p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Mon=
, 23 Dec 2024, 10:05=E2=80=AFpm Adrian Klaver, &lt;<a href=3D"mailto:adrian=
.klaver@aklaver.com" target=3D"_blank" rel=3D"noreferrer">adrian.klaver@akl=
aver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 12/23/24=
 07:53, Divyansh Gupta JNsThMAudy wrote:<br>
&gt; Hii Community,<br>
&gt; <br>
&gt; I need to provide a support for some functionality for my application =
<br>
&gt; for that I need to store 50 key value pair set, so I am in a dilemma, =
<br>
&gt; weather I create 50 new columns of int2 data type each column will <br=
>
<br>
This is unclear, I am trying to figure out you go from &#39;50 key value <b=
r>
pair set&#39; to &#39;50 new columns of int2&#39;.<br>
<br>
In other words how 50 pairs turn into 50 columns?<br>
<br>
Then there is the question of why 50 keys per row in the first place?<br>
<br>
<br>
&gt; contain value of a specific key or should I go with JSONB data type wi=
th <br>
&gt; 50 key value pair, the table on which I am going to do that all contai=
ns <br>
&gt; 1 Billion rows of data and have 84 hash partitions, I have gone throug=
h <br>
&gt; multiple articles some of them mentioned it&#39;s a good approach to c=
reate <br>
&gt; 50 new columns and some states that creating one JSONB would be best <=
br>
&gt; that&#39;s why I need your help to move forward, also I am ready to ma=
ke <br>
&gt; H-Store instead of JSONB if it provides better performance.<br>
&gt; Please help me to comes out from that dilemma.<br>
&gt; <br>
&gt; Regards,<br>
&gt; Divyansh Gupta,<br>
&gt; Database Administrator<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" rel=3D"noreferrer noreferrer" =
target=3D"_blank">adrian.klaver@aklaver.com</a><br>
<br>
</blockquote></div>
</blockquote></div>

--000000000000e6c2470629f2bc57--