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 1tPmFS-00FDJk-ND for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:29:43 +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 1tPmFQ-00DICZ-8t for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:29:40 +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 1tPmFP-00DICN-SV for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:29:39 +0000 Received: from mail-oo1-xc2a.google.com ([2607:f8b0:4864:20::c2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tPmFM-001Fpt-Sh for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:29:39 +0000 Received: by mail-oo1-xc2a.google.com with SMTP id 006d021491bc7-5f2e370bb3aso1032896eaf.0 for ; Mon, 23 Dec 2024 09:29:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734974975; x=1735579775; 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=Pq6b6PYhizLEixicEIKJX8sQwxNVZQxwTenR9Wg3t9E=; b=DyUzKu083zW+U5Gjpk98CowetyX4scXq6VJPPTppvf+QbIrpDS3G2jGRB0JaLvXt8i x9rMfH/SK1S7LOfj+yjYvhzIFSvwgDRsySAvPgVh/PQrkNs4tr2xkx6iSEpz67md3ukM oDtep2J4GDdoTKqjUyc5cp79zfXFIUgT7Q/pgw7k2YefVg7h88spFd8Ler9SqjqVWE96 GL6bMbajcZAg/EAHTPO4PI2nzkwi6xONhEtTahVWhNKpTm8BmV9ugB2834VsIlj1ON4K oLnlzlyAklZ23TBJSyXFVFSPzU4a3Gd7K6gCkjqg4S2FXxrmYDZwIZD3psD+nXmj67Rr 9OHg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734974975; x=1735579775; 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=Pq6b6PYhizLEixicEIKJX8sQwxNVZQxwTenR9Wg3t9E=; b=gX33qau9dYW3l2bwGZD43fvqv8yW5hiry8yUK+6AnA+ZADCrsFYV2aE1BgFnQNk9bX tNozTnleXqdpqDIui96j3cHSClug9dt8hC3TnBYUsAciAw/+qyj78WfCZXxz0CoJath0 fYALnJVY4BaHDYRDj3v66DMusVkkA6OCoSLxaAqhsFTMMGWCBLntOmFJcFkTJSUNa5IZ 22Rzsh8htdhae3WrVtm53ZPYe6KvVOPmDE6xfM0y3VxCTGiqKPFr2rASzpDQpXyoMR0p bRfS0BULjrHjVvjOrHV/EeNUi3ieKKGT+FbalanCPAseFn/6zGvyGjYMYNmiNUDiLB7f PRWw== X-Forwarded-Encrypted: i=1; AJvYcCXcYI8T4vMqcuZeFBdUgBOAVie+IUs+q0aT8E6IAr5Bg8IUBU7ERiDLaT9LFJ8uHr/iItujQMIHoT32ot3S@lists.postgresql.org X-Gm-Message-State: AOJu0Yx/ja3kBkcKqdCW8NNVm6RRaw0YvuLj5KqomRvF4dn4z7amVcWW phEoXPI1PB2OWdMWXcK4WM5Nn5z33J87pjr9CYujZK0TCgl7Ftjtd3Wks7qdnv4Yt3ozTj0y+bl 4W1m4XLdEvuj36tH4d7w/aICPWv/v+EoVUX6vgA== X-Gm-Gg: ASbGnct6v882S8MPkExvqB5HiYTtXI7TcrmfJCUCKfMuacrh1UWM7C9Ln2aUeZECDAB 2rc0WSp6g30a2SMbqwDOEpwiPpneFGTDyA6HDB+lrfXk9ToJC1sn0damts+IUvmBFj1GoDjrz X-Google-Smtp-Source: AGHT+IHLhezxGfqH3P0yLaACBr0T5CT8dDo8Ndr2Uo7DxP0xPHQqB+uQUS8gxPTlDoC4/rS5LWcdcYIdVVe0EUpyMP8= X-Received: by 2002:a05:6820:2908:b0:5f6:4b3e:fd45 with SMTP id 006d021491bc7-5f64b3efdd0mr3896135eaf.3.1734974975300; Mon, 23 Dec 2024 09:29:35 -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:59:24 +0530 Message-ID: Subject: Re: Need help in database design To: "David G. Johnston" Cc: Adrian Klaver , pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000018db610629f357eb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000018db610629f357eb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Values can be updated based on customer actions All rows won't have all 50 key value pairs always if I make those keys into columns the rows might have null value on the other hand if it is JSONB then the key value pair will not be there Yes in UI customers can search for the key value pairs During data population the key value pair will be empty array in case of JSONB column or NULL in case of table columns, later when customer performs some actions that time the key value pairs will populate and update, based on what action customer performs. On Mon, 23 Dec 2024, 10:51=E2=80=AFpm Divyansh Gupta JNsThMAudy, < ag1567827@gmail.com> wrote: > Let's make it more understandable, here is the table schema with 50 > columns in it > > CREATE TABLE dbo.googledocs_tbl ( > gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 > MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, > userid int8 NOT NULL, > t1 int4 NULL, > t2 int4 NULL, > t3 int4 NULL, > t4 int4 NULL, > t5 int4 NULL, > t6 int4 NULL, > t7 int4 NULL, > t8 int4 NULL, > t9 int4 NULL, > t10 int4 NULL, > t11 int4 NULL, > t12 int4 NULL, > t13 int4 NULL, > t14 int4 NULL, > t15 int4 NULL, > t16 int4 NULL, > t17 int4 NULL, > t18 int4 NULL, > t19 int4 NULL, > t20 int4 NULL, > t21 int4 NULL, > t22 int4 NULL, > t23 int4 NULL, > t24 int4 NULL, > t25 int4 NULL, > t26 int4 NULL, > t27 int4 NULL, > t28 int4 NULL, > t29 int4 NULL, > t30 int4 NULL, > t31 int4 NULL, > t32 int4 NULL, > t33 int4 NULL, > t34 int4 NULL, > t35 int4 NULL, > t36 int4 NULL, > t37 int4 NULL, > t38 int4 NULL, > t39 int4 NULL, > t40 int4 NULL, > t41 int4 NULL, > t42 int4 NULL, > t43 int4 NULL, > t44 int4 NULL, > t45 int4 NULL, > t46 int4 NULL, > t47 int4 NULL, > t48 int4 NULL, > t49 int4 NULL, > t50 int4 NULL, > CONSTRAINT googledocs_tbl_pkey PRIMARY KEY (gdid), > ); > > Every time when i query I will query it along with userid > Ex : where userid =3D 12345678 and t1 in (1,2,3) and t2 in (0,1,2) > more key filters if customer applies > > On the other hand if I create a single jsonb column the schema will look > like : > > CREATE TABLE dbo.googledocs_tbl ( > gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 > MAXVALUE 9223372036854775807 START 1 CACHE 1 NO CYCLE) NOT NULL, > userid int8 NOT NULL, > addons_json jsonb default '{}'::jsonb > CONSTRAINT googledocs_tbl_pkey PRIMARY KEY (gdid), > ); > > and the query would be like > where userid =3D 12345678 and ((addons_json @> {t1:1}) or (addons_json @= > > {t1:2}) or (addons_json @> {t1:3}) > more key filters if customer applies > > > > On Mon, Dec 23, 2024 at 10:38=E2=80=AFPM David G. Johnston < > david.g.johnston@gmail.com> wrote: > >> >> >> On Mon, Dec 23, 2024, 10:01 Divyansh Gupta JNsThMAudy < >> ag1567827@gmail.com> wrote: >> >>> >>> So here my question is considering one JSONB column is perfect or >>> considering 50 columns will be more optimised. >>> >> The relational database engine is designed around the column-based >> approach. Especially if the columns are generally unchanging, combined >> with using fixed-width data types. >> >> David J. >> >> --00000000000018db610629f357eb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Values can be updated based on customer actions

All rows won't have all 50 key value pairs always if I m= ake those keys into columns the rows might have null value on the other han= d if it is JSONB then the key value pair will not be there

Yes in UI customers can search for the key value pairs

During data population the key value pair will be empty arra= y in case of JSONB column or NULL in case of table columns, later when cust= omer performs some actions that time the key value pairs will populate and = update, based on what action customer performs.


On Mon, 23 Dec 2024, 10:51=E2=80=AFpm Divyansh Gupta JNsThM= Audy, <ag1567827@gmail.com>= ; wrote:
Let's= make it more understandable, here is the table schema with 50 columns in i= t=C2=A0

CREATE TABLE dbo.googledocs_tbl (
gdid int8 GENERATED BY= DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 922337203685477580= 7 START 1 CACHE 1 NO CYCLE) NOT NULL,
userid int8 NOT NULL,
t1 int4= NULL,
t2 int4 NULL,
t3 int4 NULL,
t4 int4 NULL,
t5 int4 NU= LL,
t6 int4 NULL,
t7 int4 NULL,
t8 int4 NULL,
t9 int4 NULL,=
t10 int4 NULL,
t11 int4 NULL,
t12 int4 NULL,
t13 int4 NULL= ,
t14 int4 NULL,
t15 int4 NULL,
t16 int4 NULL,
t17 int4 NUL= L,
t18 int4 NULL,
t19 int4 NULL,
t20 int4 NULL,
t21 int4 NU= LL,
t22 int4 NULL,
t23 int4 NULL,
t24 int4 NULL,
t25 int4 N= ULL,
t26 int4 NULL,
t27 int4 NULL,
t28 int4 NULL,
t29 int4 = NULL,
t30 int4 NULL,
t31 int4 NULL,
t32 int4 NULL,
t33 int4= NULL,
t34 int4 NULL,
t35 int4 NULL,
t36 int4 NULL,
t37 int= 4 NULL,
t38 int4 NULL,
t39 int4 NULL,
t40 int4 NULL,
t41 in= t4 NULL,
t42 int4 NULL,
t43 int4 NULL,
t44 int4 NULL,
t45 i= nt4 NULL,
t46 int4 NULL,
t47 int4 NULL,
t48 int4 NULL,
t49 = int4 NULL,
t50 int4 NULL,
CONSTRAINT googledocs_tbl_pkey PRIMARY KE= Y (gdid),
);

Every time when i query I will query it along with u= serid=C2=A0
Ex : where userid =3D 12345678 and t1 in (1,2,3) and t2 in (= 0,1,2)
more key filters if customer applies=C2=A0

On the other ha= nd if I create a single jsonb column the schema will look like :

CRE= ATE TABLE dbo.googledocs_tbl (
gdid int8 GENERATED BY DEFAULT AS IDENTI= TY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 = NO CYCLE) NOT NULL,
userid int8 NOT NULL,
addons_json jsonb default= '{}'::jsonb
CONSTRAINT googledocs_tbl_pkey PRIMARY KEY (gdid),=
);

and the query would be like=C2=A0
where userid =3D 1234567= 8 and ((addons_json=C2=A0@> {t1:1}) or=C2=A0 (addons_json=C2=A0@> {t1:2}) or=C2=A0 (addons_json=C2=A0@> {t1:3})
more key filters= if customer applies=C2=A0



On Mon, Dec 23, 2024 at 10:38=E2= =80=AFPM David G. Johnston <david.g.johnston@gmail.com> w= rote:


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


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

The relational database engine is designed around the column-base= d approach.=C2=A0 Especially if the columns are generally unchanging, combi= ned with using fixed-width data types.

David J.

--00000000000018db610629f357eb--