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 1tPlo8-00FAAB-JO for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:01:29 +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 1tPlo7-00Ck6k-W3 for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:01:27 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tPlo7-00Ck6c-KE for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:01:27 +0000 Received: from mail-oi1-x231.google.com ([2607:f8b0:4864:20::231]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tPlo4-001FT5-Iu for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:01:26 +0000 Received: by mail-oi1-x231.google.com with SMTP id 5614622812f47-3eb9de518e2so2144805b6e.2 for ; Mon, 23 Dec 2024 09:01:24 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734973283; x=1735578083; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=lQlOhej9+nr5CmsAm8ikI8nGwqdgncx93w7XHt4ZM4I=; b=AG6lYOHS1ke+Tq3SCxNsN8bi8hXgjnf9YjB2553+bYYXFv3UXM1IJBZ8cGQrnKxebj yu2xWIYFVIroM3VIf1sXbjTmUaIGHNb3VBI/XmvKOvGfhehc8oi3ZUmn/zYYT1Hr+A4j i4pIMp5p7iy38BiBtNrgoWSmS8hpHpqzSX2UHP62Od18K7iZMnmWmP8gOgU/QRKt0Lrj BUedW6EEJ8KEghPR/wvd8RouOLYHAXZ1kODGvbDlSAEYM4knpQWb9hbuz2g/RxuXUi09 41DB2I+1BZPnK2L8OMk6b1fvllUHSFCto2nVlgIEuXCGqroQLdi8QchfmGeoMpRT3mkf NY5w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734973283; x=1735578083; h=cc: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=lQlOhej9+nr5CmsAm8ikI8nGwqdgncx93w7XHt4ZM4I=; b=ceB8ErIi77uEvmamUqiya/k8cl+IAqxbaEyffjgso7abPdHDStGSqUCyPy2zQ8KFPg BC2KB2DZuxZfLpZUFqwqdFk05QfzMrDowNfkMb/gB/pWkKgtwpN1P7+Gid6gFlQVTd7s UnXJCwU8YyFVZ1QCmCxDmhXZl+5Sgfv9p0rNAeQ6wpbJuRiGgBPUdBasBCuPTadnXnAA CecO4hv2pgPY0MMxUf+hVxae6Zm45s+sSsgWOu6EuMCPjH+YIYZZXL3m3GB2drGeTqxp fZM//fNlYRSzDbvQe3AuxGoUuy8G2jNUIucvCCsAeoDzkg6vpfXtVHL/eyGbtOuSXu+K 1adQ== X-Gm-Message-State: AOJu0YymBGEyOwl3wbHSRaDFs8XueqqJeu4X7oWd2o+SUTj3BTAnyQrt WVOGiVbFKFhm2cHQ6GwC3NoLLqV3vbt/kggmU/u4IVdBJ4fI1WS9co48vR3RDJ8CsqIiSe6HfJY z7+68SjdF11a4Skl9RZKok13qI/PHElbr X-Gm-Gg: ASbGncsyA6/B9+ofH1du1jiFP6mYZ3cS8zfsKBmQ4p7hbFoqZVgp6LimeL7DBteRUKU JQ7bRmp526pGpvd+adsbABB1TICPS2H3MOQb5s9eew1avn4OtrpKXbR38986F3HVRZiLDj+H7 X-Google-Smtp-Source: AGHT+IGbsnGmCSWvdbjYr6yiawiDiAL+GZ/qCj2mRPOnRbVpfepCkVeYn/FV4Ffn/1DCCwNS8DRjL0gh/DO4ZP1ZpAc= X-Received: by 2002:a05:6808:152c:b0:3ea:f809:44d0 with SMTP id 5614622812f47-3ed890de420mr7802429b6e.35.1734973282890; Mon, 23 Dec 2024 09:01:22 -0800 (PST) MIME-Version: 1.0 References: <06e1f1ee-74b2-43a2-9a63-da20ae455ae2@aklaver.com> In-Reply-To: <06e1f1ee-74b2-43a2-9a63-da20ae455ae2@aklaver.com> From: Divyansh Gupta JNsThMAudy Date: Mon, 23 Dec 2024 22:31:11 +0530 Message-ID: Subject: Re: Need help in database design To: Adrian Klaver Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000038bc680629f2f2d4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000038bc680629f2f2d4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 will b= e > > 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, > > > 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 store all the key value pair in a single JSONB column th= e > > 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 in a > > dilemma, > > > weather I create 50 new columns of int2 data type each colum= n > > 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 through > > > multiple articles some of them mentioned it's a good approac= h > > 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 performance. > > > 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 > > --00000000000038bc680629f2f2d4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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

Now each key could have value= s from 0 to 3=C2=A0

So let suppose we have JSONB like tha= t 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


S= o here my question is considering one JSONB column is perfect or considerin= g 50 columns will be more optimised.



On Mon= , 23 Dec 2024, 10:24=E2=80=AFpm Adrian Klaver, <adrian.klaver@akl= aver.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:ag15678= 27@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@aklaver.com= <mailto:adrian.klaver@aklaver.com>> wrote:<= br> >
>=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.klaver@aklav= er.com <mailto:adrian.klaver@aklaver.com> >

--
Adrian Klaver
adrian.klaver@aklaver.com

--00000000000038bc680629f2f2d4--