I am trying to get some preliminary insight into the pros and cons of using either a hybrid model (jsonb document-relational) or an all jsonb document store model for customer data in PostgreSQL. Both models would key to non-changing relational tables for non-customer specific information.
I am new to database design, so please excuse me if I confuse the use of keys, indexes, links and subtables. The use case has up to 10,000 customers. I have considered and rejected the one database/customer model (too much management) and the one large relational database model (difficult to split and poor isolation and portability of customer information).
I have decided to place restrictions on each customer's data so that it is contained in either 1 jsonb document or 1 jsonb document-array table pair in the hybrid model. I anticipate approximately 10,050 or 20,050 total documents and tables depending on the model selected. Each customer document or table will be relatively small and so I expect fast reading, writing and searching for individual customer data. As a result, I believe performance will not be an issue for either model. Searching across the whole database will occur, but will be rare and can be scheduled for low use times.
I have not experimented with this yet except for writing a sample customer document store in json with metadata and some simple arrays. The use case includes making multiple text phrase string searches using the command: <-> on a 70,000 record relational table containing long text strings in order to return a single record key.
Another requirement is the user needs to be able to easily copy either a document store template with its keys or a table template with its keys (sub table links?) intact to make document stores/tables when adding customers.
It is unclear to me if the above 2 tasks are possible and if so, how well they will work with a single jsonb document store for each customer's information.
The hybrid model is the safe choice which I know will work well and I can use visusal PHP code generators for the tables, forms and indexes saving a lot of work. The single document store model is simpler to manage with only a single document store file per customer, but appears less straightforward to create, make forms for and to provide keys. I am uncertain if I will be able to index the jsonb documents adequately. I am looking for insights into the pros and cons of each approach that might save me months of design and test time. Please share your experience with searching, indexing and copying jsonb documents. Thank you in advance for your attention.