Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tPm5K-00FC8D-3p for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:19:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tPm5J-00D8u7-Ep for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:19:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tPm5I-00D8t5-Qp for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:19:12 +0000 Received: from mail-oo1-xc2e.google.com ([2607:f8b0:4864:20::c2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tPm5H-000D7S-0Q for pgsql-general@postgresql.org; Mon, 23 Dec 2024 17:19:11 +0000 Received: by mail-oo1-xc2e.google.com with SMTP id 006d021491bc7-5f6497fbccbso737973eaf.0 for ; Mon, 23 Dec 2024 09:19:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734974350; x=1735579150; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=AN8JBWTFY4b0Vde/CR5ut1sfT6IR08wQ2k7uq8sf0Fk=; b=WdtLHwhETIq1JmCPybWEbv6OKFcka9fHEJZcGOvLLiYMQVSwHNWe+cd2VCMfouGRud ydPSq82/ktqT2Ecb+KyHoVDm1gnchfPz/O2iXwkeXPzFFmJ3FHyOIvRHTSOieo5O5GrL CNDc7f7KkfvPoD17C8i7lpmu8i5SO2GzRUqahEBd94EMVomdTz0oUNkr38186CH6VHV+ p9kHveNcjr5P/BOsQQTq+fWpuUcOauk72f1XvqbS8d6HovV+4LBCVET70GYDKSzCFVXy nW5PFebl/hu72YJ3PqEKXKYiM4xGnrUpXgJTrZhz03b1U4h3Odzo/6Yo0/twy0KKengA Usew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734974350; x=1735579150; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=AN8JBWTFY4b0Vde/CR5ut1sfT6IR08wQ2k7uq8sf0Fk=; b=k9GwzyZAtSplbJPK7TvJwrExV3/uRJ19jgfkTiGGisMrUlhvF7/KuM8Zfj2x2nZJXr ln9+eeNqJMXSyk4FjvnlIBZpQFtleA8PudZAZ5vTWULTg0aIr8b9DEnZpty1hALy7IhN /xt3x/WHvm3RpqLBvEi7CcMWSarOaIbhO6BzsFQtA2Le5/1hBzWVcdOwBawhASYZbWco 8ghQ8Y6PDDi5FDvRnva/cq5nlyZNnuzTxc2zJXMwZPH9Jnzy76cRI1hN6/rq21/GatsR q7ipcWmEmwFnIDNEpSymbjNPfZ7QVL6+b9xliZDVZPu9jyPwdxdOGU2nca+AOm7oPnUx tn9g== X-Gm-Message-State: AOJu0YzVpedt74yjz/bF+KAbtzqkzniBtXcG6ENm8Oh4apgop1zFg38r kdZJZyLTo5XkFQ6n6+IlkRNicSXB9QiT5mSsAM+wSljbnZZccNiBCRosu6jiTkBYAukSpnZOsd3 gSk0PW/TuBxgZsYq0S5P4N4AQAJR77g== X-Gm-Gg: ASbGncuLUk9Y09mV3vcWMnPALVtyaJ+v/qOeMHtvV1Ny4AsAZM/QJcg8ZEFrt2UBukB YYWz7STSLeSSs1iNjTH/w2Eae3C2FV5rwFDzNZJkfJg2k9F/XZMFjgW5cqcag2iNXPlfY58U= X-Google-Smtp-Source: AGHT+IGd5PybmjaSzKg2u54NpYGISsVXcr1B1erGYnIByyvGgQB8BjYvFk30DeVpEA7YZRc6YH1pjI9MUI+OJny2hDs= X-Received: by 2002:a05:6870:9106:b0:29f:96b3:df91 with SMTP id 586e51a60fabf-2a7d10b0c85mr10737377fac.1.1734974350180; Mon, 23 Dec 2024 09:19:10 -0800 (PST) MIME-Version: 1.0 References: <06e1f1ee-74b2-43a2-9a63-da20ae455ae2@aklaver.com> In-Reply-To: From: Ron Johnson Date: Mon, 23 Dec 2024 12:18:59 -0500 Message-ID: Subject: Re: Need help in database design To: pgsql-general Content-Type: multipart/alternative; boundary="000000000000d6485a0629f3313a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d6485a0629f3313a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable As I expected. Next: 1) Will all 50 of those k/v pairs be populated when you insert the record? 2) Will there be updates? 3) In each row, will some of those 50 pairs stay empty? 4) When querying a row, will the app care about all 50 k/v pairs at the same time, or just one -- or even some -- of them? On Mon, Dec 23, 2024 at 12:07=E2=80=AFPM Divyansh Gupta JNsThMAudy < ag1567827@gmail.com> wrote: > Each row have a PK (gdid) that will uniquely refrence 50 k/v pair set > > On Mon, 23 Dec 2024, 10:35=E2=80=AFpm Ron Johnson, > wrote: > >> How do you uniquely reference each set of 50 k/v pairs? >> >> On Mon, Dec 23, 2024 at 12:01=E2=80=AFPM Divyansh Gupta JNsThMAudy < >> ag1567827@gmail.com> wrote: >> >>> Sure , for example, I have 50 key with name as t1 , t2 ,t3 .......t50 >>> >>> Now each key could have values from 0 to 3 >>> >>> So let suppose we have JSONB like that for row r1 >>> >>> { >>> >>> t1: 1 >>> >>> t2: 2 >>> >>> t3 : 3 >>> >>> } >>> >>> >>> As if I convert it into columns so r1 will have >>> >>> t1 column will contain 1 >>> >>> t2 column will contain 2 >>> >>> ...... So on >>> >>> >>> So here my question is considering one JSONB column is perfect or >>> considering 50 columns will be more optimised. >>> >>> >>> >>> On Mon, 23 Dec 2024, 10:24=E2=80=AFpm Adrian Klaver, >>> wrote: >>> >>>> On 12/23/24 08:46, Divyansh Gupta JNsThMAudy wrote: >>>> > Also as you ask how 50 pairs turns into 50 column so each column wil= l >>>> be >>>> > a key and the value of that key will store inside their respective >>>> > column for each row >>>> >>>> My problem with understanding this is 50 pairs =3D 100 values, I don't >>>> understand how that can fit in 50 columns that can only handle 1 value >>>> each. You need to provide some example data showing what you want to >>>> achieve. It does not need to be the full 50, just something to show th= e >>>> process. >>>> >>>> >>>> > >>>> > >>>> > On Mon, 23 Dec 2024, 10:14=E2=80=AFpm Divyansh Gupta JNsThMAudy, >>>> > > wrote: >>>> > >>>> > As per the discussion with other team members they suggested if = we >>>> > store 50 values for keys in an individual column that will provi= de >>>> > better performance as the data type is native (INT2) on the othe= r >>>> > hand if we store 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, >>>> > > >>>> 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 i= n >>>> 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 k= ey >>>> > 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 fir= st >>>> > 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 th= at >>>> > all contains >>>> > > 1 Billion rows of data and have 84 hash partitions, I hav= e >>>> > gone through >>>> > > multiple articles some of them mentioned it's a good >>>> approach >>>> > to create >>>> > > 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 performanc= e. >>>> > > Please help me to comes out from that dilemma. >>>> > > >>>> > > Regards, >>>> > > Divyansh Gupta, >>>> > > Database Administrator >>>> > >>>> > -- >>>> > Adrian Klaver >>>> > adrian.klaver@aklaver.com >>>> > >>>> >>>> -- >>>> Adrian Klaver >>>> adrian.klaver@aklaver.com >>>> >>>> >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000d6485a0629f3313a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
As I expected.

Next:
1) Will all 50 of those k/v pairs be populated when you insert the recor= d?
2) Will there be updates?
3) In each row, will some = of those 50 pairs stay empty?
4) When querying a row, will the ap= p care about all 50 k/v pairs at the same time, or just one -- or even some= -- of them?


On Mon, De= c 23, 2024 at 12:07=E2=80=AFPM Divyansh Gupta JNsThMAudy <ag1567827@gmail.com> wrote:

Each row have a PK (gdid) that will uniquely r= efrence 50 k/v pair set


On Mon= , 23 Dec 2024, 10:35=E2=80=AFpm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
=
How do you uniquely reference each set of 50 k/v pairs?
On = Mon, Dec 23, 2024 at 12:01=E2=80=AFPM Divyansh Gupta JNsThMAudy <ag1567= 827@gmail.com> wrote:

Sure , for example, I have 50= key with name as t1 , t2 ,t3 .......t50

Now each key cou= ld have values from 0 to 3=C2=A0

So let suppose we have J= SONB like that for row r1

{

t1: 1

t2: 2

t3 : 3

}


As if I convert it into columns so r1 will = have

t1 column will contain 1

t2 column= will contain 2

...... So on


So here my question is considering one JSONB column is perfect= or considering 50 columns will be more optimised.



On Mon= , 23 Dec 2024, 10:24=E2=80=AFpm Adrian Klaver, <adrian= .klaver@aklaver.com> wrote:
On 12/23/24 08:46, Divyansh Gupta JNsThMAudy wrote:
> 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

My problem with understanding this is 50 pairs =3D 100 values, I don't =
understand how that can fit in 50 columns that can only handle 1 value
each. You need to provide some example data showing what you want to
achieve. It does not need to be the full 50, just something to show the process.


>
>
> On Mon, 23 Dec 2024, 10:14=E2=80=AFpm Divyansh Gupta JNsThMAudy,
> <ag1567827@gmail.com <mailto:ag1567827@gmail.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0As per the discussion with other team members they = suggested if we
>=C2=A0 =C2=A0 =C2=A0store 50 values for keys in an individual column th= at will provide
>=C2=A0 =C2=A0 =C2=A0better performance as the data type is native (INT2= ) on the other
>=C2=A0 =C2=A0 =C2=A0hand if we store all the key value pair in a single= JSONB column the
>=C2=A0 =C2=A0 =C2=A0performance will degrade even after applying a GIN = index on that
>=C2=A0 =C2=A0 =C2=A0however the statement sounds funny but I want to ta= ke everyone openion?
>
>
>=C2=A0 =C2=A0 =C2=A0On Mon, 23 Dec 2024, 10:05=E2=80=AFpm Adrian Klaver= ,
>=C2=A0 =C2=A0 =C2=A0<adrian.klaver@akla= ver.com <mailto:adrian.klaver@aklaver.c= om>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0On 12/23/24 07:53, Divyansh Gupta JNs= ThMAudy wrote:
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > Hii Community,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > I need to provide a support for= some functionality for my
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0application
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > for that I need to store 50 key= value pair set, so I am in a
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0dilemma,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > weather I create 50 new columns= of int2 data type each column
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0will
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0This is unclear, I am trying to figur= e out you go from '50 key
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0value
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0pair set' to '50 new columns = of int2'.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0In other words how 50 pairs turn into= 50 columns?
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Then there is the question of why 50 = keys per row in the first
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0place?
>
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > contain value of a specific key= or should I go with JSONB
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0data type with
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > 50 key value pair, the table on= which I am going to do that
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0all contains
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > 1 Billion rows of data and have= 84 hash partitions, I have
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0gone through
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > multiple articles some of them = mentioned it's a good approach
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0to create
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > 50 new columns and some states = that creating one JSONB would
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0be best
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > that's why I need your help= to move forward, also I am ready
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0to make
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > H-Store instead of JSONB if it = provides better performance.
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > Please help me to comes out fro= m that dilemma.
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > Regards,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > Divyansh Gupta,
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > Database Administrator
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0adrian.k= laver@aklaver.com <mailto:adrian.klaver= @aklaver.com>
>

--
Adrian Klaver
adrian.klaver@aklaver.com



--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--000000000000d6485a0629f3313a--