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 1tPm7x-00FCPX-Oy for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:21:58 +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 1tPm7w-00DCsV-Rb for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 17:21:56 +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 1tPm7w-00DCsN-Fa for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:21:56 +0000 Received: from mail-oa1-x35.google.com ([2001:4860:4864:20::35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tPm7p-001FmU-IM for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 17:21:55 +0000 Received: by mail-oa1-x35.google.com with SMTP id 586e51a60fabf-29fe7ff65e6so1542217fac.0 for ; Mon, 23 Dec 2024 09:21:49 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734974508; x=1735579308; 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=K/m3ATvkqnKQ7JRZ9dCOtEd/aYYlsyICwJEPHHFy1sg=; b=eRK91/Wz0cP3vv2Ak+7/61NW58mudptLIIg9Im0P1Ow2fTOnFEV29yc6YzJiAMOMFU /z+ankjRoPycWycYp4QgWvB32U7UzOHvoMZNgD8o6su5GD4fvHa1PsIYbs24YyAeu7YK ddlzFOH0an4nXZjuTUWDz7PKfwTHxr2aVOnGRO5YQ59ARRaAQscVeouTpkIfIRQ9sFyF ViRg6KJkzuNbkXjb6L/kXyAMJcozFm2Nz2ABtkahxZZVCJGrJTPKE2QM4UtD8XHGH0Z8 hZE7FAj4edNMLckuC6Ibjs0vQ5PoTjmM0NGq4nFI9LljV9TTcNbrMQS/QWFz2cWHw3KP BBow== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734974508; x=1735579308; 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=K/m3ATvkqnKQ7JRZ9dCOtEd/aYYlsyICwJEPHHFy1sg=; b=OvEdN0gKWYOmcW2LP+zRBwEPTYMEMZPzIr0/1rlAIeWNd8dZBnVmipkhutIyUAh56A Xz/iGCiagACiCA2VE/0ffHdWSTRjGKode2JoOAw9W3ARFsQ/ykvmjiuMSj51HqFDX05Q TprIr0FVhAAnumSCJM/Xau8gi9ZzxuH/4iSei2wslnymZ3wV/A0Bs3uU44vQvcJUfdLB ga2WQCIai81CezWNBLs9P4HXWd9U/pJgrWg803sZpThZADOSveEhRq30YBi2L8Aq8Sux I3rT/xvkKkOyBBPYXUvD8Xd1b/aOdopdbV4NtCjcf+mZz5mUH0MFrJ9c/qo4zpSaf4bU bAQg== X-Forwarded-Encrypted: i=1; AJvYcCXNAf5KmcjipopaJP1A8c+2O3TQlYthQDuI8AyDqHYidzD2s2zvv7Cdgx27Q+o7d5Yk2eVO3qqIdiLhvnKF@lists.postgresql.org X-Gm-Message-State: AOJu0YwV+FyMlDQ3/txRE2ARc12wvxYnpWgBK+52Mp7/hJ514zuwpTCR k3OHRhGt7lkiPNvYxeHnQjdfvsYPWcCjs2EKGCQKyR/XnAQxkEzJyk14U2oRY/7IF7TiJgItHlj hjZQlpjJXas0w621PZJptxNZz3IM= X-Gm-Gg: ASbGncuX2gLZTQJNZ4bAIEniiX0Ob+SbB4qt3TvS4hhQZMFq0GrVIUSRmvyufP3ebXF tG+JM5eDGfVMw4l7RiK4+29/7HiZfXbgADX7Xw2Ozw2lSnbBclEvXckxMF6GdTfPoxKN5Jps= X-Google-Smtp-Source: AGHT+IFF7DF0o9k4UbWFNJ9sfkuZVM1DDkAxD3ScLCiuScsDwGV7Yt1FIdYab6pLH3wI3edZqCN2+A71ifFBOM8TJMg= X-Received: by 2002:a05:6870:dc49:b0:29e:3c14:2bb0 with SMTP id 586e51a60fabf-2a7fb346697mr8871772fac.30.1734974508044; Mon, 23 Dec 2024 09:21:48 -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:51:36 +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="0000000000003f1c320629f33bdd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003f1c320629f33bdd Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 > 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. > > --0000000000003f1c320629f33bdd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Let's make it more understandable, here is the table s= chema with 50 columns in it=C2=A0

CREATE TABLE dbo.googledocs_tbl (<= br> gdid int8 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 M= AXVALUE 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 in= t4 NULL,
t9 int4 NULL,
t10 int4 NULL,
t11 int4 NULL,
t12 in= t4 NULL,
t13 int4 NULL,
t14 int4 NULL,
t15 int4 NULL,
t16 i= nt4 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,
t2= 8 int4 NULL,
t29 int4 NULL,
t30 int4 NULL,
t31 int4 NULL,
t= 32 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 goog= ledocs_tbl_pkey PRIMARY KEY (gdid),
);

Every time when i query I = will query it along with userid=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 hand if I create a single jsonb column the schema w= ill look like :

CREATE TABLE dbo.googledocs_tbl (
gdid int8 GENE= RATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036= 854775807 START 1 CACHE 1 NO CYCLE) NOT NULL,
userid int8 NOT NULL,
= addons_json jsonb default '{}'::jsonb
CONSTRAINT googledocs_tb= l_pkey PRIMARY KEY (gdid),
);

and the query would be like=C2=A0where userid =3D 12345678 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. Jo= hnston <david.g.johnston@g= mail.com> wrote:


<= div dir=3D"ltr" class=3D"gmail_attr">On Mon, Dec 23, 2024, 10:01 Divyansh G= upta 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 a= pproach.=C2=A0 Especially if the columns are generally unchanging, combined= with using fixed-width data types.

David J.

--0000000000003f1c320629f33bdd--