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 1tPlu5-00FArd-A4 for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:07:37 +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 1tPlu4-00Cupp-M9 for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:07:36 +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 1tPlu4-00Cupc-9y for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:07:36 +0000 Received: from mail-oi1-x234.google.com ([2607:f8b0:4864:20::234]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tPlu1-001FWl-8A for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:07:35 +0000 Received: by mail-oi1-x234.google.com with SMTP id 5614622812f47-3eba50d6da7so1076028b6e.2 for ; Mon, 23 Dec 2024 09:07:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734973651; x=1735578451; 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=jZulPr6iQ2/fyhsSbPZL7kuGRX+FDI2CuHcGvTU/Te0=; b=j8P6xGiYyaeiXY1zNNRydfpA6zkB6COXJ1GNHW4ScomlWNv0ZApqZV9ICKMR4CZMDy I0wNtGA5c0549P5dodog6KiBsPoxpWZWTlanNQ9426JpR250P1gxtXYzJMvzZfQRQwfc jpk6PamIFk9MXNCpMW8PU3cLNotYmt4oTRvmlTMlKVtFH0Q1+uHRY1Vp3yKUDC+/ImgH X4VNtY3GHs7GcgzxxBU81ZD2FO9fy6fdba4rC4mmOEEGgI6CaUQhT8aXr3U0EnGCwVmG f1CqSeN2kAJl0yVQQWbdwLPr46Uk8V0HJ+Scjoe7PyLSt31O/JMX5XHcpZtit9KtDbOh je3w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734973651; x=1735578451; 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=jZulPr6iQ2/fyhsSbPZL7kuGRX+FDI2CuHcGvTU/Te0=; b=gn3hRkefM1Dp3ZhHGSbJ0J25+ryw7goxl8SbxYnUN0FCu4+0MVJDrnil1NpkCRQxQq cX/WQ+H97DVEMFcM7341RG5PX0okG6NydT4XZyrQPiZEPKLkNTWaOfw+iewHZzK52XFO UvHmFFKWoSoeX+YZFABg8uSuW/HDZqXekRkdr6ZY6OMs1AoCuRlnKl8Cpbm6rwqKBVPf Q5WMYGKRTPCedjK0TFTfkgwH6dy63ZiSpkJxcFGzIHucIwgNRSvm628x8iONRJk9cafR wzmQpe64/ogg95fRzAromc9DHosobJ5CpqorXt3yPaR3+PHwxfKi/SP0nw6Bh/bI5K0i Bo4A== X-Gm-Message-State: AOJu0Yz08mTKz9octJ4oSazBjjCLIQbLeQd2jrOZoIk6xZc4cAh85Qxz 0KmmZ4ysYl9Wq90k0v4b+JE/+iWbLMKMo8RHUF5NevSc1rwjSL+pJn0HG3G4SYcnWGvIThngn8g JX3LpmbtPj89jz33crCUXSaZROSPX5usJ X-Gm-Gg: ASbGncuS5U9TAx+PPWR2j+Cg/HTSKIrnf8a3mpq+fvSZ/qa0fbrT4ePI7NEDgykMhFy wanF2HSi61WhEMw2nGMUrtK+U5YBNgbVInIPLZ+Fk0k+81KDeKvWJBmPYSDrnw5fe1ppdbEdv X-Google-Smtp-Source: AGHT+IE2dLCpmhirU7HRBChGxdW/65aACR6YzeHlBMSyr4Hmje009V+joEJTCuxG0y0O1xE21caPTl5mpEj5ajAxWRU= X-Received: by 2002:a05:6808:191a:b0:3ea:4e7c:a91a with SMTP id 5614622812f47-3ed89066f23mr7775077b6e.34.1734973651614; Mon, 23 Dec 2024 09:07:31 -0800 (PST) MIME-Version: 1.0 References: <06e1f1ee-74b2-43a2-9a63-da20ae455ae2@aklaver.com> In-Reply-To: From: Divyansh Gupta JNsThMAudy Date: Mon, 23 Dec 2024 22:37:20 +0530 Message-ID: Subject: Re: Need help in database design To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000003303020629f30806" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003303020629f30806 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 w= e >>> > store 50 values for keys in an individual column that will provid= e >>> > 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 >>> 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 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 ke= y >>> > 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 firs= t >>> > 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 tha= t >>> > 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 >>> approach >>> > to create >>> > > 50 new columns and some states that creating one JSONB wou= ld >>> > be best >>> > > that's why I need your help to move forward, also I am rea= dy >>> > 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 >>> >>> > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --0000000000003303020629f30806 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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, <ronljohnsonjr@gmail.com> wrot= e:
How do you uniquely reference each set of 50 k/v pairs?


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!
--0000000000003303020629f30806--