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 1tPnqI-00FQI2-0M for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 19:11:50 +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 1tPnqF-00EqWo-TX for pgsql-general@arkaria.postgresql.org; Mon, 23 Dec 2024 19:11:47 +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 1tPnqF-00EqWf-E1 for pgsql-general@lists.postgresql.org; Mon, 23 Dec 2024 19:11:47 +0000 Received: from mail-oo1-xc2e.google.com ([2607:f8b0:4864:20::c2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tPnq8-001Gc8-1N for pgsql-general@postgresql.org; Mon, 23 Dec 2024 19:11:46 +0000 Received: by mail-oo1-xc2e.google.com with SMTP id 006d021491bc7-5f63c89e72bso581012eaf.1 for ; Mon, 23 Dec 2024 11:11:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1734981098; x=1735585898; darn=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=+4uq6cD8bDShWkoVeEKNEWTcZcalG1yrJVlX+0lDLRc=; b=QS8AAgmFzlxShODllj/nZY0u5NkJ00PfxlKIXox07a5uL0oK16NhZt7ySaqcbgoX7s FvMyqypxnF1uPsRHhmqshFMd8HHjCm9bLoqBchRBSoSD3IzcPnn6Us3+hzFZ1qfvSwnc 473yITWwD+o1D2VTOsBcMVWQ96mjtVNJwhQ9d3JNc7b9mzcGCdcKtrYjS3PZjLakdhKA ldXTfb+L4oAobbe2sn+QJZFVpqneLC24tjcjpe8HjmvA30CdcU4J6jYRt3NQbDVwaMmu yWGDdZORch7oSsmWyyLfoHYHjbIAa0dF76DwYDqhJ9XSWQ/aLyHgK1Bg67jKppTC1Wpm resg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1734981098; x=1735585898; 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=+4uq6cD8bDShWkoVeEKNEWTcZcalG1yrJVlX+0lDLRc=; b=e10wY5a5etu940m8FLc35F0tiZw1yGGrul9cn1qJfMNis+9AOQHqNJSnG2ZHD0Asyd Cv9kh4wZiLQF/lI2JLLaeSsmmyoKtQcolvrp4sJ+DptB7ZUznT4Dv5yTnVmBcb6l+IjQ KVElwgHvK/C/xyHtKiOmB0TNxPBl941Yl5A7IeHrZdmnZ9/ua56wcTKl2QpaVEEKEqo6 SgvgXf0ADQmazEdmtYorT9O818H9jmDOdkFPlF906SJ9DE8LytEk5tkWJoweEI4GNaTn EV/w9TYW1E1J5T06W4MNRl0pK0vr9hg44stRwsTOkTgf0p17zZ+qlFMDpRTByVjejDwz bIeA== X-Gm-Message-State: AOJu0YyYUoajUybrStNsbG6JN5bfxrih4e03BR43wZaWqmY3cZkCfnjd SSK0wydkfEhHzDWwaA1PIm7nZpHXGtc4L0hQNuiiJYBWWBghaxmDZ36wxTHp9YBsRDUG51QiJzW 6tjXxL1uQ++zJcLZh4vY6gjuXcbI= X-Gm-Gg: ASbGncsOKzz0iQdQOAjyhCLy2X26i6/Fz4OtAIAxWGNrYFztugg5vK930eVwme/YpX+ YGexWWTJbOtM0Atgu4mgm+KpxHkJj09kNhR1Tr4wpu4/Iqer9NVkGZto6NJnC8f0Cpp8Iac2S X-Google-Smtp-Source: AGHT+IEFrLTicKfdyQjgpiB/capSdQUNOzgx5mkQIPuFknbB7qDIWanD2LqX1e4BMArzAIe5JwU8XIwmZXtXsJchDTg= X-Received: by 2002:a05:6820:1c9a:b0:5f3:4175:1d7e with SMTP id 006d021491bc7-5f62e775ab4mr7431851eaf.4.1734981098409; Mon, 23 Dec 2024 11:11:38 -0800 (PST) MIME-Version: 1.0 References: <06e1f1ee-74b2-43a2-9a63-da20ae455ae2@aklaver.com> In-Reply-To: From: Divyansh Gupta JNsThMAudy Date: Tue, 24 Dec 2024 00:41:26 +0530 Message-ID: Subject: Re: Need help in database design To: Ron Johnson Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000001014370629f4c473" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001014370629f4c473 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thank you everyone for giving your valuable responses, I am glad that everyone understands my concern. I got some good ideas about the database design that I am following after going through some stress testing I will implement the same. Thank you so much Everyone On Tue, 24 Dec 2024, 12:09=E2=80=AFam Ron Johnson, wrote: > Are these columns really unique for all 20M rows that a userid can have i= n > the table? I'm dubious. > > Split a LOT of those columns out into a separate table named "user" with > PK userid. It'll save a huge amount of disk space, and speed up queries = by > not having to fetch it all every time. > > useremail varchar(600) NOT NULL, > title public.citext NULL, > authorname varchar(600) NULL, > authoremail varchar(600) NULL, > updated varchar(300) NOT NULL, > entryid varchar(2000) NOT NULL, > lastmodifiedby varchar(600) NULL, > lastmodifiedbyemail varchar(600) NULL, > "size" varchar(300) NULL, > contenttype varchar(250) NULL, > fileextension varchar(50) NULL, > docfoldername public.citext NULL, > folderresourceid public.citext NULL, > filesize int8 DEFAULT 0 NOT NULL, > retentionstatus int2 DEFAULT 0 NOT NULL, > docfileref int8 NULL, > usid int4 NULL, > archivepath varchar(500) NULL, > createddate timestamp(6) DEFAULT NULL::timestamp without time zone NULL, > zipfilename varchar(100) NULL, > oncreatedat timestamp(6) DEFAULT clock_timestamp() NOT NULL, > onupdateat timestamp(6) DEFAULT clock_timestamp() NOT NULL, > startsnapshot int4 DEFAULT 0 NOT NULL, > currentsnapshot int4 DEFAULT 0 NOT NULL, > dismiss int2 DEFAULT 0 NOT NULL, > checksum varchar NULL, > typeoffile int2 GENERATED ALWAYS AS ( > > > > On Mon, Dec 23, 2024 at 1:32=E2=80=AFPM Divyansh Gupta JNsThMAudy < > ag1567827@gmail.com> wrote: > >> Currently I haven't created those columns , I have created addons_json >> column which is a JSONB column yet in a discussion weather I should crea= te >> or consider only one JSONB column. >> >> On Tue, 24 Dec 2024, 12:00=E2=80=AFam Divyansh Gupta JNsThMAudy, < >> ag1567827@gmail.com> wrote: >> >>> Range partition can help when you applies filter for a specific range >>> but in my case I need to apply filter on userid always, however I have = date >>> columns but there is less variation in timestamp which I have that's wh= y >>> didn't go for range partition. >>> >>> On Mon, 23 Dec 2024, 11:57=E2=80=AFpm Ron Johnson, >>> wrote: >>> >>>> >>>> 1. I bet you'd get better performance using RANGE partitioning. >>>> 2. Twenty million rows per userid is a *LOT*. No subdivisions (like >>>> date range)? >>>> >>>> On Mon, Dec 23, 2024 at 1:23=E2=80=AFPM Divyansh Gupta JNsThMAudy < >>>> ag1567827@gmail.com> wrote: >>>> >>>>> Adrian, Please check this out; >>>>> >>>>> PARTITION BY HASH (userid); CREATE TABLE dbo. >>>>> googledocs_tbl_clone_part_0 PARTITION OF dbo.googledocs_tbl_clone FOR >>>>> VALUES WITH (modulus 84, remainder 0); ... CREATE TABLE dbo. >>>>> googledocs_tbl_clone_part_83 PARTITION OF dbo.googledocs_tbl_clone FO= R >>>>> VALUES WITH (modulus 84, remainder 83); >>>>> >>>>> On Mon, Dec 23, 2024 at 11:48=E2=80=AFPM Divyansh Gupta JNsThMAudy < >>>>> ag1567827@gmail.com> wrote: >>>>> >>>>>> Adrian, the partition is on userid using hash partition with 84 >>>>>> partitions >>>>>> >>>>>> Ron, there could be more than 20 Million records possible for a >>>>>> single userid in that case if I create index on userid only not on o= ther >>>>>> column the query is taking more than 30 seconds to return the result= s. >>>>>> >>>>>> On Mon, 23 Dec 2024, 11:40=E2=80=AFpm Ron Johnson, >>>>>> wrote: >>>>>> >>>>>>> If your queries all reference userid, then you only need indices on >>>>>>> gdid and userid. >>>>>>> >>>>>>> On Mon, Dec 23, 2024 at 12:49=E2=80=AFPM Divyansh Gupta JNsThMAudy = < >>>>>>> ag1567827@gmail.com> wrote: >>>>>>> >>>>>>>> I have one confusion with this design if I opt to create 50 column= s >>>>>>>> I need to create 50 index which will work with userid index in Bit= map on >>>>>>>> the other hand if I create a JSONB column I need to create a singl= e index ? >>>>>>>> >>>>>>>> On Mon, 23 Dec 2024, 11:10=E2=80=AFpm Ron Johnson, >>>>>>>> wrote: >>>>>>>> >>>>>>>>> Given what you just wrote, I'd stick with 50 separate t* columns. >>>>>>>>> Simplifies queries, simplifies updates, and eliminates JSONB conv= ersions. >>>>>>>>> >>>>>>>>> On Mon, Dec 23, 2024 at 12:29=E2=80=AFPM Divyansh Gupta JNsThMAud= y < >>>>>>>>> ag1567827@gmail.com> wrote: >>>>>>>>> >>>>>>>>>> Values can be updated based on customer actions >>>>>>>>>> >>>>>>>>>> All rows won't have all 50 key value pairs always if I make thos= e >>>>>>>>>> keys into columns the rows might have null value on the other ha= nd 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 whe= n customer >>>>>>>>>> performs some actions that time the key value pairs will populat= e 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 wit= h >>>>>>>>>>> 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 CYCL= E) 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 CYCL= E) 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 perfec= t >>>>>>>>>>>>> or considering 50 columns will be more optimised. >>>>>>>>>>>>> >>>>>>>>>>>> The relational database engine is designed around the >>>>>>>>>>>> column-based approach. Especially if the columns are generall= y unchanging, >>>>>>>>>>>> combined with using fixed-width data types. >>>>>>>>>>>> >>>>>>>>>>>> David J. >>>>>>>>>>>> >>>>>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> Death to , and butter sauce. >>>>>>>>> Don't boil me, I'm still alive. >>>>>>>>> lobster! >>>>>>>>> >>>>>>>> >>>>>>> >>>>>>> -- >>>>>>> Death to , and butter sauce. >>>>>>> Don't boil me, I'm still alive. >>>>>>> lobster! >>>>>>> >>>>>> >>>> >>>> -- >>>> Death to , and butter sauce. >>>> Don't boil me, I'm still alive. >>>> lobster! >>>> >>> > > -- > Death to , and butter sauce. > Don't boil me, I'm still alive. > lobster! > --0000000000001014370629f4c473 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thank you everyone for giving your valuable responses, I am = glad that everyone understands my concern. I got some good ideas about the = database design that I am following after going through some stress testing= I will implement the same.

Thank you so much Everyone


On Tue, 24 Dec 2024, 12:09=E2=80=AFam Ron Johnson, <ronljohnsonjr@gmail.com> wrot= e:
Are these = columns really unique for all 20M rows that a userid can have in the table?= =C2=A0 I'm dubious.

Split a LOT of those colum= ns out into a separate=C2=A0table named "user" with PK userid.=C2= =A0 It'll save a huge amount of disk space, and speed up queries by not= having to fetch it all every time.

useremail varc= har(600) NOT NULL,
title public.citext NULL,
authorname varchar(600) = NULL,
authoremail varchar(600) NULL,
updated varchar(300) NOT NULL,entryid varchar(2000) NOT NULL,
lastmodifiedby varchar(600) NULL,
l= astmodifiedbyemail varchar(600) NULL,
"size" varchar(300) NULL= ,
contenttype varchar(250) NULL,
fileextension varchar(50) NULL,
d= ocfoldername public.citext NULL,
folderresourceid public.citext NULL,filesize int8 DEFAULT 0 NOT NULL,
retentionstatus int2 DEFAULT 0 NOT NU= LL,
docfileref int8 NULL,
usid int4 NULL,
archivepath varchar(500)= NULL,
createddate timestamp(6) DEFAULT NULL::timestamp without time zon= e NULL,
zipfilename varchar(100) NULL,
oncreatedat timestamp(6) DEFAU= LT clock_timestamp() NOT NULL,
onupdateat timestamp(6) DEFAULT clock_tim= estamp() NOT NULL,
startsnapshot int4 DEFAULT 0 NOT NULL,
currentsnap= shot int4 DEFAULT 0 NOT NULL,
dismiss int2 DEFAULT 0 NOT NULL,
checks= um varchar NULL,
typeoffile int2 GENERATED ALWAYS AS (


=
On Mon= , Dec 23, 2024 at 1:32=E2=80=AFPM Divyansh Gupta JNsThMAudy <ag1567827@= gmail.com> wrote:

Currently I haven't created those columns , I= have created addons_json column which is a JSONB column yet in a discussio= n weather I should create or consider only one JSONB column.


On Tue= , 24 Dec 2024, 12:00=E2=80=AFam Divyansh Gupta JNsThMAudy, <ag1567827@g= mail.com> wrote:

Range partition can help when you applies filter for= a specific range but in my case I need to apply filter on userid always, h= owever I have date columns but there is less variation in timestamp which I= have that's why didn't go for range partition.


On Mon= , 23 Dec 2024, 11:57=E2=80=AFpm Ron Johnson, <ronljohnso= njr@gmail.com> wrote:

1. I bet you&= #39;d get better performance using RANGE partitioning.
2. Twenty = million rows per userid=C2=A0is a LOT.=C2=A0 No subdivisions (like d= ate range)?

On Mon, Dec 23, 2024 at 1:23=E2=80=AFPM Divyansh Gupta JNsThMAud= y <ag1567827@gmail.com> wrote:
Adrian, Pl= ease check this out;

PARTITION BY HA= SH (userid); = CREATE TABLE dbo.googledocs_tbl_clone_part_0 PARTITION OF dbo.googledocs_tbl_clone FOR VALUES WITH (modulus 84, remainder 0); ... = CREATE TABLE dbo.googledocs_tbl_clone_part_83 PARTITION OF dbo.googledocs_tbl_clone FOR VALUES WITH (modulus 84, remainder 83);

On Mon, Dec 23, 2024 at 11:48=E2=80=AFPM D= ivyansh Gupta JNsThMAudy <ag1567827@gmail.com= > wrote:

Adrian, the partition is on userid using hash partition with 84 = partitions

Ron, there could be more than 20 Million records possible fo= r a single userid in that case if I create index on userid only not on othe= r column the query is taking more than 30 seconds to return the results.

On Mon= , 23 Dec 2024, 11:40=E2=80=AFpm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
If your queries all reference= userid, then you only need indices on gdid and userid.

On Mon, Dec 23, 2024= at 12:49=E2=80=AFPM Divyansh Gupta JNsThMAudy <ag1567827@gmail.com> wrote:

I have one confusion with t= his design if I opt to create 50 columns I need to create 50 index which wi= ll work with userid index in Bitmap on the other hand if I create a JSONB c= olumn I need to create a single index ?


On Mon= , 23 Dec 2024, 11:10=E2=80=AFpm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:
Given what you jus= t wrote, I'd stick with 50 separate t* columns.=C2=A0 Simplifies querie= s, simplifies updates, and eliminates JSONB conversions.

On Mon, Dec 23, 202= 4 at 12:29=E2=80=AFPM Divyansh Gupta JNsThMAudy <ag1567827@gmail.com> wrote:

Values can be updat= ed 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 JNsThMAudy, <ag1567827@gmail.com> wrote:
Let= 9;s make it more understandable, here is the table schema with 50 columns i= n it=C2=A0

CREATE TABLE dbo.googledocs_tbl (
gdid int8 GENERATED= BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 922337203685477= 5807 START 1 CACHE 1 NO CYCLE) NOT NULL,
userid int8 NOT NULL,
t1 i= nt4 NULL,
t2 int4 NULL,
t3 int4 NULL,
t4 int4 NULL,
t5 int4= NULL,
t6 int4 NULL,
t7 int4 NULL,
t8 int4 NULL,
t9 int4 NU= LL,
t10 int4 NULL,
t11 int4 NULL,
t12 int4 NULL,
t13 int4 N= ULL,
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 int= 4 NULL,
t26 int4 NULL,
t27 int4 NULL,
t28 int4 NULL,
t29 in= t4 NULL,
t30 int4 NULL,
t31 int4 NULL,
t32 int4 NULL,
t33 i= nt4 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,
t4= 5 int4 NULL,
t46 int4 NULL,
t47 int4 NULL,
t48 int4 NULL,
t= 49 int4 NULL,
t50 int4 NULL,
CONSTRAINT googledocs_tbl_pkey PRIMARY= KEY (gdid),
);

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

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 IDE= NTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE= 1 NO CYCLE) NOT NULL,
userid int8 NOT NULL,
addons_json jsonb defa= ult '{}'::jsonb
CONSTRAINT googledocs_tbl_pkey PRIMARY KEY (gdi= d),
);

and the query would be like=C2=A0
where userid =3D 1234= 5678 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

<= br>

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 Divyans= h Gupta JNsThMAudy <ag1567827@gmail.com> wrote:


= So here my question is considering one JSONB column is perfect or consideri= ng 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.



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


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


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


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