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(dot)sandor(at)gmail(dot)com
> <mailto:daku(dot)sandor(at)gmail(dot)com>> wrote:
>> Hi,
>>
>> On Thu, 21 May 2020 at 18:14, Christopher Browne <cbbrowne(at)gmail(dot)com
>> <mailto:cbbrowne(at)gmail(dot)com>> wrote:
>>
>> On Thu, 21 May 2020 at 11:53, Israel Brewster
>> <israel(at)brewstersoft(dot)com <mailto:israel(at)brewstersoft(dot)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(dot)klaver(at)aklaver(dot)com