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 1tPlrx-00FAbB-PC for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:05:26 +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 1tPlrx-00CoQJ-2D for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:05:24 +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 1tPlrw-00CoPX-LV for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:05:24 +0000 Received: from mail-oa1-x2f.google.com ([2001:4860:4864:20::2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tPlrp-001FVF-S3 for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:05:23 +0000 Received: by mail-oa1-x2f.google.com with SMTP id 586e51a60fabf-29fcbf3d709so1478759fac.2 for ; Mon, 23 Dec 2024 09:05:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734973516; x=1735578316; darn=lists.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=ofOr6uIitscG2bCBoGBvc8/glGdI5KcTKZyNmKc86OU=; b=ctIxxiYtwZSBn2dV3ZwmjH8rL7RNiD9iM8NIDHzObjTG3+17vzloOve3HYvD9u5MKv NgN11ebnoSCeDFSh0uCPa/o6VuowW1bMQEIdl1MvrBUb4fm1Xo+iim+Z0Je3sOGBOCX1 MXenRvTd6VOo6Yiniuc0QMB4RbMhIlFAD624wIaAABmL6LgRC01HeKyvrIqGFsaZHx3v r4ZURhKTA3fY/7YobZ2x7/U25CBG9OHAFUGggYuuFJ56auEkVl/jkJQuQsg8l2dF3bgp 2sELb+g8gUehVgIPn93IY5kRk46oZQ47ho0GPjTRkPpAsaplPtFSFk5+ops4IaEtn9js 3P+Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734973516; x=1735578316; 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=ofOr6uIitscG2bCBoGBvc8/glGdI5KcTKZyNmKc86OU=; b=O6dODmkzuPehWRTLPFq/TYLHo7HU4WZjHjGLITptadHAUQCEjObVoadui9BP2TOmN7 dVIjDzpkZG8nyMaqwhurvY2Rjwy6/AMpJPU7GbNVcsaHJG0TJi0H4+WldnE5qAu4bEmO xZxTwsCBKQitSB3bRa1peFjIEqzNCOncws/zdoz2WzqQenUHSzwMcDRKgLOS8RzKrF54 SBepFH2AM8n36IdS40Rqhcx7Ra9tly+fKJoHtzbxeKqNuyyhiNsFozIBF/jD9r326Obt 7WA4wzRrut8FsVDwsF2crJPFJWE4rtX390W7ILThcThUMh46jE4nAdwIIQXHpkeK67Sz wKKw== X-Gm-Message-State: AOJu0YxwEAWn0/1d3HnaVXwaEuO+tRy0J6FgdQRLrLq4g1XjDQBG9sXB lbL1cKR2grdOnmMZ/V1iuxDwtMwlDDDI79yRYwoWmuotWuX5qWDsKufcxbZ49PEAYM8PIZnoNIR fBCTcNBN/wIwoStyBO3ZOKAfc0phlPg== X-Gm-Gg: ASbGncsRhTtA9TkLuaLqihqT1WD9Xn8bMkQvgA8vC/dMBQptUaGzVXKCehBCHI+1vMW tzOlgqHe23NPy5uTc61MQfRWzv2UPN0ElSznspLwKbqxCLFqG5j5kgPOFyB6qGJn4CkMQfvY= X-Google-Smtp-Source: AGHT+IFo80DjS7WunGR5ATK4JaHFoB6Li+pWuoGgBGIooZcFDWZIDzgqpu5LF5CVMmBy82QP8Yu7V/hzTCc8JcEryB4= X-Received: by 2002:a05:6870:6b0d:b0:29f:a0b8:6f7f with SMTP id 586e51a60fabf-2a7fb0bfc7fmr7084728fac.12.1734973515855; Mon, 23 Dec 2024 09:05:15 -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:05:05 -0500 Message-ID: Subject: Re: Need help in database design To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000001b7d000629f30035" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001b7d000629f30035 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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, >> > > 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 t= he >> > 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 colu= mn >> > 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 approa= ch >> > to create >> > > 50 new columns and some states that creating one JSONB woul= d >> > be best >> > > that's why I need your help to move forward, also I am read= y >> > 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 >> >> --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000001b7d000629f30035 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
How do you uniquely reference each set of= 50 k/v pairs?

On Mon, Dec 23, 2024 at 12:01=E2=80=AFP= M Divyansh Gupta JNsThMAudy <ag15= 67827@gmail.com> wrote:

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

Now each key c= ould have values from 0 to 3=C2=A0

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



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