From: | Rj Ewing <ewing(dot)rj(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | dynamic schema modeling and performance |
Date: | 2017-04-11 17:46:41 |
Message-ID: | CAOSSsV3Q67YuHB4hJG=uNiZPPWWXAsxcHzJ=XD-Ev39UzKJrwg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lifepillar | 2017-04-11 19:48:58 | [OT] Help: stories of database security and privacy |
Previous Message | John Iliffe | 2017-04-11 14:33:14 | Re: Unable to connect to Postgresql |