Question about schema table population about django-tenant-schemas HOT 5 CLOSED

Hi Greg,

all your assumptions are correct. AFAIK to span all the tenant's data you'd
have to do UNION. When I developed this app I didn't think of this
possibility. In this case I'd recommend to put the table at the public
schema.

About your suggestion to use inheritance, to be honest I didn't even know
PostgreSQL could do this. I've read the page on the documentation about it
but I couldn't tell how it works internally and therefore I don't know how
the performance would be. I feel like this isn't the purpose of inheritance
while individual schemas for each tenant seem like a perfect fit.

I'm not sure if you're aware of the other apps that also solve the
multitenancy problem in Django, but maybe this one would be better suited
for your needs: https://github.com/pombredanne/django-simple-multitenant.
This app stores everything on the public schema, and the tenants are
identified via a foreign key.

You are of course very welcome to fork this repo and try your solution. If
you have any difficulties or doubts feel free to contact me.

Cheers,
Bernardo

2013/5/16 Greg Fausak [email protected]

Hi,

I haven't installed the multi-tenant package yet. I think perhaps it
doesn't meet my requirements. If I understand correctly, the TENANT_APPS'
tables are created in each of the tenant schemas. So, if I want to run a
query that spans all of the data in all the schemas I would have to do a
union, correct?

I had an idea. One thing you can do in postgres is inherit a table. So,
for example, I can create my table in the public schema, then, when I want
that same table for a tenant I could:

1. create the tenant schema.
2. create table tenant.mytable () inherits (public.mytable);

When the tenant table is separate, data inserted into that table cannot be
accessed by another tenant. However, all data entered in a tenant schemas
can be selected in the central 'public.mytable' table. Another neat side
effect is when south updates the public table the tenant tables
automatically. I tried this a bit, I am going to do some more work with
this. What do you think about this approach?

---greg

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/45
.

Bernardo Pires Carneiro

from django-tenant-schemas.

Bernardo,

Thank you very much for your response. I am very impressed with the work
you did, the approach is a good one. I've been searching around on the
internet for a multi-tenant solution before I write my own. Yours is just
about perfect for me. I like the idea of the isolation done by the
database rather than a foreign key. You never know what might leak out!

I am fairly new to python, django and git, only using them for a few months
now. If it is OK with you I think I will take a stab at using your code
and changing it very slightly. I think you have some code that copies the
tables from a template schema when a new tenant is created. I guess I need
to figure our how to access the list of TENANT_APPS, look through them,
then create inherited tables in that tenant's schema. Other than that,
your code will work exactly the same, because the table will be exactly the
same.

You are right to be concerned about the performance. Instead of having
many small tables with indices on each of them, you would have one large
central table with indices on it. That might make what I am thinking about
trying unusable. In any event, I think I'll give it a try. It is
important to my application to be able to do a query on all of the tenants
data (as an business report spanning all tenants).

Best Regards,
---greg
Greg Fausak
Dallas, TX

On Thu, May 16, 2013 at 7:10 AM, Bernardo Pires Carneiro <
[email protected]> wrote:

Hi Greg,

all your assumptions are correct. AFAIK to span all the tenant's data
you'd
have to do UNION. When I developed this app I didn't think of this
possibility. In this case I'd recommend to put the table at the public
schema.

About your suggestion to use inheritance, to be honest I didn't even know
PostgreSQL could do this. I've read the page on the documentation about it
but I couldn't tell how it works internally and therefore I don't know how
the performance would be. I feel like this isn't the purpose of
inheritance
while individual schemas for each tenant seem like a perfect fit.

I'm not sure if you're aware of the other apps that also solve the
multitenancy problem in Django, but maybe this one would be better suited
for your needs: https://github.com/pombredanne/django-simple-multitenant.
This app stores everything on the public schema, and the tenants are
identified via a foreign key.

You are of course very welcome to fork this repo and try your solution. If
you have any difficulties or doubts feel free to contact me.

Cheers,
Bernardo

2013/5/16 Greg Fausak [email protected]

Hi,

I haven't installed the multi-tenant package yet. I think perhaps it
doesn't meet my requirements. If I understand correctly, the
TENANT_APPS'
tables are created in each of the tenant schemas. So, if I want to run a
query that spans all of the data in all the schemas I would have to do a
union, correct?

I had an idea. One thing you can do in postgres is inherit a table. So,
for example, I can create my table in the public schema, then, when I
want
that same table for a tenant I could:

1. create the tenant schema.
2. create table tenant.mytable () inherits (public.mytable);

When the tenant table is separate, data inserted into that table cannot
be
accessed by another tenant. However, all data entered in a tenant
schemas
can be selected in the central 'public.mytable' table. Another neat side
effect is when south updates the public table the tenant tables
automatically. I tried this a bit, I am going to do some more work with
this. What do you think about this approach?

---greg

—
Reply to this email directly or view it on GitHub<
https://github.com/bcarneiro/django-tenant-schemas/issues/45>
.

Bernardo Pires Carneiro

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/45#issuecomment-17997189
.

Greg Fausak
[email protected]

from django-tenant-schemas.

Hi Greg,

thanks for the compliments! Yes, of course, feel free to change my code.

Here's a brief explanation of how the creation of a new tenant works on
this app, you can take a look at models.py and
management/commands/sync_schemas.py to see what's happening. First the
new schema gets created. Second, the command syncdb is called, taking care
that only the appropriate apps (TENANT_APPS) are synced. If south is
installed, it will additionally also call migrate only migrating the
appropriate apps. There is no copying involved. Copying would mean I need
to have at least one tenant that already has the correct tables, which is
not the case.

If you have any doubts feel free to post here!

Bernardo

2013/5/16 Greg Fausak [email protected]

Bernardo,

Thank you very much for your response. I am very impressed with the work
you did, the approach is a good one. I've been searching around on the
internet for a multi-tenant solution before I write my own. Yours is just
about perfect for me. I like the idea of the isolation done by the
database rather than a foreign key. You never know what might leak out!

I am fairly new to python, django and git, only using them for a few
months
now. If it is OK with you I think I will take a stab at using your code
and changing it very slightly. I think you have some code that copies the
tables from a template schema when a new tenant is created. I guess I need
to figure our how to access the list of TENANT_APPS, look through them,
then create inherited tables in that tenant's schema. Other than that,
your code will work exactly the same, because the table will be exactly
the
same.

You are right to be concerned about the performance. Instead of having
many small tables with indices on each of them, you would have one large
central table with indices on it. That might make what I am thinking about
trying unusable. In any event, I think I'll give it a try. It is
important to my application to be able to do a query on all of the tenants
data (as an business report spanning all tenants).

Best Regards,
---greg
Greg Fausak
Dallas, TX
214-335-7976

On Thu, May 16, 2013 at 7:10 AM, Bernardo Pires Carneiro <
[email protected]> wrote:

Hi Greg,

all your assumptions are correct. AFAIK to span all the tenant's data
you'd
have to do UNION. When I developed this app I didn't think of this
possibility. In this case I'd recommend to put the table at the public
schema.

About your suggestion to use inheritance, to be honest I didn't even
know
PostgreSQL could do this. I've read the page on the documentation about
it
but I couldn't tell how it works internally and therefore I don't know
how
the performance would be. I feel like this isn't the purpose of
inheritance
while individual schemas for each tenant seem like a perfect fit.

I'm not sure if you're aware of the other apps that also solve the
multitenancy problem in Django, but maybe this one would be better
suited
for your needs: https://github.com/pombredanne/django-simple-multitenant.

This app stores everything on the public schema, and the tenants are
identified via a foreign key.

You are of course very welcome to fork this repo and try your solution.
If
you have any difficulties or doubts feel free to contact me.

Cheers,
Bernardo

2013/5/16 Greg Fausak [email protected]

Hi,

I haven't installed the multi-tenant package yet. I think perhaps it
doesn't meet my requirements. If I understand correctly, the
TENANT_APPS'
tables are created in each of the tenant schemas. So, if I want to run
a
query that spans all of the data in all the schemas I would have to do
a
union, correct?

I had an idea. One thing you can do in postgres is inherit a table.
So,
for example, I can create my table in the public schema, then, when I
want
that same table for a tenant I could:

1. create the tenant schema.
2. create table tenant.mytable () inherits (public.mytable);

When the tenant table is separate, data inserted into that table
cannot
be
accessed by another tenant. However, all data entered in a tenant
schemas
can be selected in the central 'public.mytable' table. Another neat
side
effect is when south updates the public table the tenant tables
automatically. I tried this a bit, I am going to do some more work
with
this. What do you think about this approach?

---greg

—
Reply to this email directly or view it on GitHub<
https://github.com/bcarneiro/django-tenant-schemas/issues/45>
.

Bernardo Pires Carneiro

—
Reply to this email directly or view it on GitHub<
https://github.com/bcarneiro/django-tenant-schemas/issues/45#issuecomment-17997189>

.

Greg Fausak
[email protected]

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/45#issuecomment-17999656
.

Bernardo Pires Carneiro

from django-tenant-schemas.

By the way, the inherit worked great. Django really did not know the difference. When I actually go live with this I'll post the results. I don't have time now to clean it up enough to make it fork-worthy. It's pretty hand for each of the tenants to have separate schemas, but I can run a query against a single table which includes all tenant info.

Best,
---greg

from django-tenant-schemas.

Hi @lgfausak!

Did your changes are live elsewhere? I'm trying to integrate django-tenant-schemas into a project and looking for a solution to use inheritance on some models.

Thanks!

from django-tenant-schemas.