Given what you just wrote, I'd stick with 50 separate t* columns.
Simplifies queries, simplifies updates, and eliminates JSONB conversions.
On Mon, Dec 23, 2024 at 12:29 PM Divyansh Gupta JNsThMAudy <
ag1567...@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 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 pm Divyansh Gupta JNsThMAudy, <
> ag1567...@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 = 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 = 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 PM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>>
>>>
>>> On Mon, Dec 23, 2024, 10:01 Divyansh Gupta JNsThMAudy <
>>> ag1567...@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.
>>>
>>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
