Feedback-ID: i76984098:Fastmail
Message-ID: <06e1f1ee-74b2-43a2-9a63-da20ae455ae2@aklaver.com>
Date: Mon, 23 Dec 2024 08:54:04 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Need help in database design
To: Divyansh Gupta JNsThMAudy <ag1567827@gmail.com>
Cc: pgsql-general@lists.postgresql.org
References: <CAHesJ5LES3aTDf=xp7NOwrADQ_HWC-Spsv7yLu9ZY+zxzZO53A@mail.gmail.com>
 <b450927c-49da-46e5-ad74-bf38ceff166b@aklaver.com>
 <CAHesJ5+ASNoSNMiC5Ms0Ts=gw7v2_UeBpUT=phujO4yE_XCbEw@mail.gmail.com>
 <CAHesJ5JbkCBZ2f_AvUr8+KWnGPAsobu4zyfnWm8bEeb7X9oqDQ@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAHesJ5JbkCBZ2f_AvUr8+KWnGPAsobu4zyfnWm8bEeb7X9oqDQ@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/06e1f1ee-74b2-43a2-9a63-da20ae455ae2%40aklaver.com>
Precedence: bulk

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, 
> <ag1567827@gmail.com <mailto:ag1567827@gmail.com>> 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,
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> 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
>         adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com