Re: Need help in database design

You might also look into using a bitmap, for some or all of those fields. It depends on how many distinct values each can have, of course, and also on how exactly they are accessed, but bitmaps can save you quite a bit of space. Cheers, Greg

Re: Need help in database design

On Mon, Dec 23, 2024 at 11:26 AM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > > 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, > > useremail varchar(600) NOT NULL, > > t

Re: Need help in database design

Just out of curiosity, not suggestion this is the solution. Why save json in PostgreSQL and not in a DB specialised on JSON like MongoDB? Divyansh Gupta JNsThMAudy escribió: Thank you everyone for giving your valuable responses, I am glad that everyone understands my concern. I got some go

Re: Need help in database design

The current application does not have support for that, If I go for that, it will require a lot of effort from the team which Is not possible right now. On Tue, 24 Dec 2024, 12:47 am , wrote: > Just out of curiosity, not suggestion this is the solution. Why save json > in PostgreSQL and not in a

Re: Need help in database design

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 am Ro

Re: Need help in database design

Are these columns really unique for all 20M rows that a userid can have in 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

Re: Need help in database design

I would like to make a suggestion, if I may. Granted, I do not understand the underlying task at hand, but: A table with multiple columns of the same type smacks of designs that harken back to the days of mainframes. (STOP THAT!) The data described is a non-normalized array of integers that is

Re: Need help in database design

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 create or consider only one JSONB column. On Tue, 24 Dec 2024, 12:00 am Divyansh Gupta JNsThMAudy, < ag1567...@gmail.com> wrote: > Range partition can help wh

Re: Need help in database design

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 why didn't go for range partition. On Mon, 23 Dec 2024, 11:57 pm Ron Johnson, wrot

Re: Need help in database design

Where are the 50 "t* columns? On Mon, Dec 23, 2024 at 1:26 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Ron here is the entire table schema FYI, userid is the mandate column on > which filter is always applies: > > CREATE TABLE dbo.googledocs_tbl ( > > gdid int8 GENERATED BY DEFAU

Re: Need help in database design

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 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > Adrian, Please check this out; > > PARTITION BY HASH (userid)

Re: Need help in database design

Ron here is the entire table schema FYI, userid is the mandate column on which filter is always applies: 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,

Re: Need help in database design

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 FOR VALUES WITH (modulus

Re: Need help in database design

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 other column the query is taking more than 30 seconds to return the results. On Mon, 23 Dec

Re: Need help in database design

If your queries all reference userid, then you only need indices on gdid and userid. On Mon, Dec 23, 2024 at 12:49 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> wrote: > I have one confusion with this design if I opt to create 50 columns I need > to create 50 index which will work with user

Re: Need help in database design

On 12/23/24 09:49, Divyansh Gupta JNsThMAudy wrote: I have one confusion with this design if I opt to create 50 columns I need to create 50 index which will work with userid index in Bitmap on the other hand if I create a JSONB column I need to create a single index ? There still is the part

Re: Need help in database design

I have one confusion with this design if I opt to create 50 columns I need to create 50 index which will work with userid index in Bitmap on the other hand if I create a JSONB column I need to create a single index ? On Mon, 23 Dec 2024, 11:10 pm Ron Johnson, wrote: > Given what you just wrote,

Re: Need help in database design

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

Re: Need help in database design

Empty JSONB by mistake, I wrote array sorry about that On Mon, 23 Dec 2024, 10:59 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

Re: Need help in database design

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 Dur

Re: Need help in database design

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 NUL

Re: Need help in database design

As I expected. Next: 1) Will all 50 of those k/v pairs be populated when you insert the record? 2) Will there be updates? 3) In each row, will some of those 50 pairs stay empty? 4) When querying a row, will the app care about all 50 k/v pairs at the same time, or just one -- or even some -- of the

Re: Need help in database design

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 adrian.kla...@aklaver.com

Re: Need help in database design

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 general

Re: Need help in database design

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, 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 < > ag1567...@gmail.com> wrote: > >> Sure , for example

Re: Need help in database design

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 t

Re: Need help in database design

How do you uniquely reference each set of 50 k/v pairs? On Mon, Dec 23, 2024 at 12:01 PM Divyansh Gupta JNsThMAudy < ag1567...@gmail.com> 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

Re: Need help in database design

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 ...

Re: Need help in database design

On 12/23/24 08:55, David G. Johnston wrote: On Mon, Dec 23, 2024, 09:54 Adrian Klaver > 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

Re: Need help in database design

On Mon, Dec 23, 2024, 09:54 Adrian Klaver 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 un

Re: Need help in database design

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 h

Re: Need help in database design

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, < ag1567...@gmail.com> wrote: > As per the discussion with other team members th

Re: Need help in database design

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

Re: Need help in database design

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,

Need help in database design

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 contain value of a specific key or should I go with JSONB data type with 50