Re: dynamic schema modeling and performance

If you are asking if you should go nosql, 99% you should not.

On Tue, Apr 11, 2017 at 10:06 PM, Poul Kristensen <bcc5226(at)gmail(dot)com> wrote:

> dataverse.org uses Postgresql and is well documented + it is completely
> user driven. Maybe the concept could be usefull for you. I have installed
> and configuration a few to be uses for researchers.
>
> regards
> Poul
>
>
> 2017-04-11 19:46 GMT+02:00 Rj Ewing <ewing(dot)rj(at)gmail(dot)com>:
>
>> I'm looking for thoughts on the best way to handle dynamic schemas.
>>
>> The application I am developing revolves around user defined entities.
>> Each entity is a tabular dataset with user defined columns and data types.
>> Entities can also be related to each other through Parent-Child
>> relationships. Some entities will be 100% user driven, while others (such
>> as an entity representing a photo) will be partially user driven (all photo
>> entities will have common fields + custom user additions).
>>
>> I was hoping to get opinions on whether postgresql would be a suitable
>> backend. A couple of options I have thought of are:
>>
>> 1. Each entity is represented as a table in psql. The schema would be
>> dynamically updated (with limits) when an entity mapping is updated. I
>> believe that this would provide the best data constraints and allow the
>> best data normalization. *A concern I have is that there could be an
>> enormous amount of tables generated and the performance impacts this might
>> have in the future*. I could then run elasticsearch as a denormalized
>> cache for efficient querying and full-text-search.
>>
>> 2. Use a nosql database. This provides the "dynamic" schema aspect. A
>> concern here is the lack of relation support, thus leading to a more
>> denormalized data structure and the potential for the data to become
>> corrupted.
>>
>> Any opinions on the use of psql for this case, or other options would be
>> greatly appreciated!
>>
>> RJ
>>
>
>
>
> --
> Med venlig hilsen / Best regards
> Poul Kristensen
> Linux-OS/Virtualizationexpert and Oracle DBA
>