Database design - size and performance

Posted by pcs on 03-Jan-2017 17:08

Some database platforms use narrow (EAV model) approaches to store metadata. Rollbase uses a single wide database table, RB_OBJ_DATA, to store all object records. This single table has over 500 fields (of various types) and can be extended (Private Cloud).

Should I be concerned as to the growth/performance of the database if I require a lot (millions) of narrow records e.g. daily inventory and prices across thousands of products? Do I have to consider a denormalised approach (where possible) or can I build out the objects with impunity (and lots of many to many relationships)?

All Replies

Posted by ajsharma on 25-Jan-2017 01:17

Hi Peter,

Regarding your concern about the wide database table and performance with growing number of records in database, i would like to provide some details & facts which will give you confidence on the design.

Our hosted cloud environment run on the same system design and currently we have one database handling 50+ million records in it which consist of 12K+ tenants and its running without any performance issue.

Please let me know if this provide the information to your concern.

Regards

Ajay Sharma

Product Manager - Rollbase

Posted by pcs on 25-Jan-2017 16:20

Hi Ajay,

Thank you for this. I am not concerned with the ability of RB running a large DB with many users. I am more interested in specific architectural design consideration with respect to individual objects.

With respect to space utilisation (per record) does RB_OBJ_DATA (which stores all object data in over 500 fields of various types per record) only take space (on the disk) for fields that are defined and not null i.e. are these 500+ fields variable length? This would mean I could create a narrow table (with say date and quantity) without concern.

Looking forward to your comments.

Regards,

Peter

Posted by pcs on 01-Feb-2017 15:50

To further clarify my question, the documentation defines RB_OBJ_DATA as

MaxStrFields - Maximum number of VARCHAR(100) fields (in MySQL notation) 200

MaxIntgFields - Maximum number of BIGINT fields 150

MaxDblFields - Maximum number of DECIMAL(20,8) fields 50

MaxTxtFields - Maximum number of LONGTEXT fields 50

MaxDateFields - Maximum number of DATETIME fields 50

Noting these can be extended (private cloud).

Does the OpenEdge database (and others) only allocate space per record for non null i.e. used fields?

This thread is closed