Re: Table partitioning for cloud service?

On 5/21/20 1:23 PM, Israel Brewster wrote:
> On May 21, 2020, at 12:12 PM, Sándor Daku <daku.sandor@gmail.com 
> <mailto:daku.sandor@gmail.com>> wrote:
>> Hi,
>>
>> On Thu, 21 May 2020 at 18:14, Christopher Browne <cbbrowne@gmail.com 
>> <mailto:cbbrowne@gmail.com>> wrote:
>>
>>     On Thu, 21 May 2020 at 11:53, Israel Brewster
>>     <israel@brewstersoft.com <mailto:israel@brewstersoft.com>> wrote:
>>
>>>
>>     - Table-based tenancy (e.g. - each table has a "tenant_id" and
>>     queries need to specify the tenant)
>>
>>
>> The database/schema per tenant solution can be tedious when you want 
>> to modify something on the structure and you have numerous tenants.
>> Therefore I used the "tables with tenant_id" version in a similar 
>> situation but with a slight twist. One of the biggest issue of this 
>> solution is that if you forget to add the tenant_id to the where 
>> clause you are going to reveal one tenant's data to another.
>> I came up with the solution that the database user have no privileges 
>> for accessing the base tables. Instead of that I generate views for 
>> each tenant and they can access their own data in the underlying table 
>> through these views. Now if forget to address the right tenant in my 
>> client code(it still happens sometimes) and try to directly access the 
>> base tables I get a strongly worded reminder from the server.
> 
> Nice solution! I think I may go to something like that once I upgrade to 
> a cloud solution that lets me add multiple users to the DB (the free 
> tier of Heroku does not). In the meantime, while I just have the single 
> customer, I can fake it easily enough.
> 
> Is there any shortcuts for referencing the proper views, or do you just 
> append/prepend something to every table reference in your SQL? One nice 
> thing about the database/schema approach is that I can just specify the 
> search_path (or database) in the connection command, and then all the 
> table references remain the same for all tenants. Also helps avoid the 
> situation you mentioned where you forget to address the right tenant, 
> since you only have to do it in one place. Of course, as you said, it 
> can be tedious when you want to modify the structure. I’ll have to think 
> about that a bit more.

If you want to take the tedium out of it take a look at Sqitch:

https://sqitch.org/

Then all you have to do is create the change once and deploy to the 
targets.

> 
> ---
> Israel Brewster
> BrewsterSoft Development
> http://www.brewstersoft.com <http://www.brewstersoft.com/>
> Home of EZPunch and Lyrics Presenter
>>
>>
>> Regards,
>> Sándor
>>
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com