Re: Multitenent architecture

Hi guys - apologies for jumping into the middle of this, but it touches on
precisely one of the topics I'll have to deal with soon. We're standing up
a pharmacovigilance / drug safety platform managing ICSRs (HL7 "individual
case safety reports" that must be FDA/ICH compliant) and will be dealing
with multi-level privacy and compliance (HIPAA, GDPR, PHI, CCPA, etc.) at
levels such as Patient, Doctor, Pharmacist. This first part deals with
adverse drug events in the "post marketing" realm, i.e., drugs already
approved and in the marketplace. Our model will also expand to support
pre-marketing, i.e., clinical trials and the like.

Far easier to have one coherent data model and tackle compliance as a
multi-tenant / multi-level-secure database issue. We just have to make sure
we do this so it's rock solid and also facilitates the business / use cases
that need to be executed.

I'd love to compare notes with anyone else doing something along those
lines. @Vasu - it sounds like you're smack in the middle of this. Let me
know if you'd be open to connecting and comparing notes on this topic.
(anyone else as well!)

Thanks,

- Jon

On Sat, Jun 6, 2020 at 1:21 PM Michel Pelletier <pelletier(dot)michel(at)gmail(dot)com>
wrote:

>
> On Sat, Jun 6, 2020 at 3:14 AM Vasu Madhineni <vasumdba1515(at)gmail(dot)com>
> wrote:
>
>> Hi Rob,
>>
>> Our environment is medical clinical data, so each clinic as a tenant.
>> Approximately 500+ tenants with 6TB data.
>>
>> Thank you in advance.
>>
>>
> There's a good article on the AWS blog on multi tenancy with postgres:
>
>
> https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/
>
> This is similar to Laurenz's second suggestion.
>
> -Michel
>
>
>> Regards,
>> Vasu Madhineni
>>
>> On Fri, Jun 5, 2020 at 6:09 PM Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>>
>>>
>>>
>>> On Jun 5, 2020, at 2:54 AM, Vasu Madhineni <vasumdba1515(at)gmail(dot)com>
>>> wrote:
>>>
>>> 
>>> If the data size is more than 6TB, which approach better?
>>>
>>> On Fri, Jun 5, 2020 at 2:57 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
>>> wrote:
>>>
>>>> On Thu, 2020-06-04 at 23:52 +0800, Vasu Madhineni wrote:
>>>> > We are planning a POC on multitenant architecture in Postgres, Could
>>>> you please
>>>> > help us with steps for multitenant using schema for each application
>>>> model.
>>>>
>>>> For few tenants, you can keep identical tables in several schemas and
>>>> set "search_path" to select a tenant.
>>>>
>>>> With many tenants, you are better off with one table that holds the
>>>> data for all clients. You can use Row Level Security to have each
>>>> tenant see only his or her data, and it might be a good idea to
>>>> use list partitioning on the tenant ID.
>>>>
>>>> Yours,
>>>> Laurenz Albe
>>>> --
>>>> Cybertec | https://www.cybertec-postgresql.com
>>>>
>>>> The question is How many separate data owners?
>>>
>>