public inbox for [email protected]
help / color / mirror / Atom feedRe: Need help in database design
13+ messages / 4 participants
[nested] [flat]
* Re: Need help in database design
@ 2024-12-23 16:35 Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Adrian Klaver @ 2024-12-23 16:35 UTC (permalink / raw)
To: Divyansh Gupta JNsThMAudy <[email protected]>; [email protected]
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 key 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 first 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 that 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 would be best
> that's why I need your help to move forward, also I am ready 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
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
@ 2024-12-23 16:44 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Divyansh Gupta JNsThMAudy @ 2024-12-23 16:44 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
As per the discussion with other team members they suggested if we store 50
values for keys in an individual column that will provide 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 pm Adrian Klaver, <[email protected]>
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 key 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 first 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 that 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 would be best
> > that's why I need your help to move forward, also I am ready 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
> [email protected]
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
@ 2024-12-23 16:46 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Divyansh Gupta JNsThMAudy @ 2024-12-23 16:46 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
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
On Mon, 23 Dec 2024, 10:14 pm Divyansh Gupta JNsThMAudy, <
[email protected]> wrote:
> As per the discussion with other team members they suggested if we store
> 50 values for keys in an individual column that will provide 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 pm Adrian Klaver, <[email protected]>
> 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 key 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 first 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 that 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 would be best
>> > that's why I need your help to move forward, also I am ready 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
>> [email protected]
>>
>>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
@ 2024-12-23 16:54 ` Adrian Klaver <[email protected]>
2024-12-23 16:55 ` Re: Need help in database design David G. Johnston <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Adrian Klaver @ 2024-12-23 16:54 UTC (permalink / raw)
To: Divyansh Gupta JNsThMAudy <[email protected]>; +Cc: [email protected]
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 = 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 pm Divyansh Gupta JNsThMAudy,
> <[email protected] <mailto:[email protected]>> wrote:
>
> As per the discussion with other team members they suggested if we
> store 50 values for keys in an individual column that will provide
> 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 pm Adrian Klaver,
> <[email protected] <mailto:[email protected]>> 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 key
> 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 first
> 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 that
> 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 would
> be best
> > that's why I need your help to move forward, also I am ready
> 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
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
@ 2024-12-23 16:55 ` David G. Johnston <[email protected]>
2024-12-23 16:59 ` Re: Need help in database design Adrian Klaver <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2024-12-23 16:55 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Divyansh Gupta JNsThMAudy <[email protected]>; [email protected]
On Mon, Dec 23, 2024, 09:54 Adrian Klaver <[email protected]> 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 = 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.
>
Because the column name is the same as a dictionary key...
David J.
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:55 ` Re: Need help in database design David G. Johnston <[email protected]>
@ 2024-12-23 16:59 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2024-12-23 16:59 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Divyansh Gupta JNsThMAudy <[email protected]>; [email protected]
On 12/23/24 08:55, David G. Johnston wrote:
> On Mon, Dec 23, 2024, 09:54 Adrian Klaver <[email protected]
> <mailto:[email protected]>> 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 = 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.
>
>
>
> Because the column name is the same as a dictionary key...
Aah, got it thanks.
>
> David J.
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
@ 2024-12-23 17:01 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:05 ` Re: Need help in database design Ron Johnson <[email protected]>
2024-12-23 17:07 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:08 ` Re: Need help in database design David G. Johnston <[email protected]>
1 sibling, 3 replies; 13+ messages in thread
From: Divyansh Gupta JNsThMAudy @ 2024-12-23 17:01 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
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 pm Adrian Klaver, <[email protected]>
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 = 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 pm Divyansh Gupta JNsThMAudy,
> > <[email protected] <mailto:[email protected]>> wrote:
> >
> > As per the discussion with other team members they suggested if we
> > store 50 values for keys in an individual column that will provide
> > 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 pm Adrian Klaver,
> > <[email protected] <mailto:[email protected]>>
> 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 key
> > 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 first
> > 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 that
> > 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 would
> > be best
> > > that's why I need your help to move forward, also I am ready
> > 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
> > [email protected] <mailto:[email protected]>
> >
>
> --
> Adrian Klaver
> [email protected]
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
@ 2024-12-23 17:05 ` Ron Johnson <[email protected]>
2024-12-23 17:07 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: Ron Johnson @ 2024-12-23 17:05 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
How do you uniquely reference each set of 50 k/v pairs?
On Mon, Dec 23, 2024 at 12:01 PM Divyansh Gupta JNsThMAudy <
[email protected]> 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 pm Adrian Klaver, <[email protected]>
> 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 = 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 pm Divyansh Gupta JNsThMAudy,
>> > <[email protected] <mailto:[email protected]>> wrote:
>> >
>> > As per the discussion with other team members they suggested if we
>> > store 50 values for keys in an individual column that will provide
>> > 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 pm Adrian Klaver,
>> > <[email protected] <mailto:[email protected]>>
>> 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 key
>> > 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 first
>> > 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 that
>> > 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 would
>> > be best
>> > > that's why I need your help to move forward, also I am ready
>> > 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
>> > [email protected] <mailto:[email protected]>
>> >
>>
>> --
>> Adrian Klaver
>> [email protected]
>>
>>
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:05 ` Re: Need help in database design Ron Johnson <[email protected]>
@ 2024-12-23 17:07 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:18 ` Re: Need help in database design Adrian Klaver <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Divyansh Gupta JNsThMAudy @ 2024-12-23 17:07 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
Each row have a PK (gdid) that will uniquely refrence 50 k/v pair set
On Mon, 23 Dec 2024, 10:35 pm Ron Johnson, <[email protected]> wrote:
> How do you uniquely reference each set of 50 k/v pairs?
>
> On Mon, Dec 23, 2024 at 12:01 PM Divyansh Gupta JNsThMAudy <
> [email protected]> 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 pm Adrian Klaver, <[email protected]>
>> 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 = 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 pm Divyansh Gupta JNsThMAudy,
>>> > <[email protected] <mailto:[email protected]>> wrote:
>>> >
>>> > As per the discussion with other team members they suggested if we
>>> > store 50 values for keys in an individual column that will provide
>>> > 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 pm Adrian Klaver,
>>> > <[email protected] <mailto:[email protected]>>
>>> 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 key
>>> > 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 first
>>> > 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 that
>>> > 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 would
>>> > be best
>>> > > that's why I need your help to move forward, also I am ready
>>> > 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
>>> > [email protected] <mailto:[email protected]>
>>> >
>>>
>>> --
>>> Adrian Klaver
>>> [email protected]
>>>
>>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:05 ` Re: Need help in database design Ron Johnson <[email protected]>
2024-12-23 17:07 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
@ 2024-12-23 17:18 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2024-12-23 17:18 UTC (permalink / raw)
To: Divyansh Gupta JNsThMAudy <[email protected]>; Ron Johnson <[email protected]>; +Cc: pgsql-generallists.postgresql.org <[email protected]>
On 12/23/24 09:07, Divyansh Gupta JNsThMAudy wrote:
> Each row have a PK (gdid) that will uniquely refrence 50 k/v pair set
>
Are the 50 key/value pairs there to just define a PK or are they going
to be used in some other combination in queries?
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
@ 2024-12-23 17:07 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 13+ messages in thread
From: Adrian Klaver @ 2024-12-23 17:07 UTC (permalink / raw)
To: Divyansh Gupta JNsThMAudy <[email protected]>; +Cc: [email protected]
On 12/23/24 09:01, Divyansh Gupta JNsThMAudy 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.
>
1) How are you going to use the key:value pairs in queries?
2) I would recommend creating a sample dataset that you import into
tables that are built with either the column method or the JSON method
and test a representative set of queries.
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
@ 2024-12-23 17:08 ` David G. Johnston <[email protected]>
2024-12-23 17:21 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2 siblings, 1 reply; 13+ messages in thread
From: David G. Johnston @ 2024-12-23 17:08 UTC (permalink / raw)
To: Divyansh Gupta JNsThMAudy <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected]
On Mon, Dec 23, 2024, 10:01 Divyansh Gupta JNsThMAudy <[email protected]>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Need help in database design
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Re: Need help in database design Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:08 ` Re: Need help in database design David G. Johnston <[email protected]>
@ 2024-12-23 17:21 ` Divyansh Gupta JNsThMAudy <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Divyansh Gupta JNsThMAudy @ 2024-12-23 17:21 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected]
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 <
[email protected]> wrote:
>
>
> On Mon, Dec 23, 2024, 10:01 Divyansh Gupta JNsThMAudy <[email protected]>
> 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.
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2024-12-23 17:21 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-23 16:35 Re: Need help in database design Adrian Klaver <[email protected]>
2024-12-23 16:44 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:46 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 16:54 ` Adrian Klaver <[email protected]>
2024-12-23 16:55 ` David G. Johnston <[email protected]>
2024-12-23 16:59 ` Adrian Klaver <[email protected]>
2024-12-23 17:01 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:05 ` Ron Johnson <[email protected]>
2024-12-23 17:07 ` Divyansh Gupta JNsThMAudy <[email protected]>
2024-12-23 17:18 ` Adrian Klaver <[email protected]>
2024-12-23 17:07 ` Adrian Klaver <[email protected]>
2024-12-23 17:08 ` David G. Johnston <[email protected]>
2024-12-23 17:21 ` Divyansh Gupta JNsThMAudy <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox